MySQL on EBS with OpenSolaris based AMI

Amazon Elastic Block Store (EBS) is the persistent storage which can be attached to the AMI instance. Data can be safely placed and accessed independent of the life of the instance. There are plenty of benefits using EBS for MySQL. For detailed explanation click here.
Following steps describes how to use MySQL on EBS volume with OpenSolaris based AMI.

1. Create a new EBS Volume.

$ ec2-create-volume --size 10 --availability-zone us-east-1a
VOLUME    vol-688d4c01    10        us-east-1a    creating    2010-01-13T21:54:57+0000
$ ec2-describe-volumes vol-688d4c01
VOLUME    vol-688d4c01    10        us-east-1a    available   2010-01-13T21:54:57+0000

Click here for ec2-create-volume API help.

2. Create a new MySQL 5.1 AMI Instance based on OpenSolaris.

$ ec2-run-instances ami-752ccb1c -k my-keypair
RESERVATION    r-2486124c    987654321123    default
INSTANCE       i-9e6858f6    ami-752ccb1c    pending    my-keypair    0        m1.small    2010-01-17T00:01:24+0000    
us-east-1a     aki-6552b60c  ari-6452b60d    monitoring-disabled
Click here for more details on this AMI.

3. Attach EBS volume to this AMI instance

$ ec2-attach-volume -i i-9e6858f6 -d 2 vol-688d4c01
ATTACHMENT    vol-688d4c01    i-9e6858f6    2    attaching    2010-01-13T22:02:51+0000
In the above command -d option specifies the device number. In OpenSolaris devices are named from 0 to 23. Device number 0 and 1 are used for ephemeral storage. Therefore, we can use the devices starting from 2 until 23.
$ 
ec2-describe-volumes vol-688d4c01
VOLUME    vol-688d4c01    10        us-east-1a    in-use    2010-01-13T21:54:57+0000
ATTACHMENT    vol-688d4c01    i-9e6858f6    2    attached    2010-01-13T22:02:51+0000
Click here for ec2-attach-volume API help.
Click here for ec2-describe-volumes API help.

4. Create a ZFS storage pool on the previously attached EBS volume.

Once the instance is up and running, login into the instance using ssh with your key-pair.
To verify new EBS volume attached, execute the format command and press Control + C to come out.
root@domU-12-31-39-03-21-E4:~# format
Searching for disks...done
AVAILABLE DISK SELECTIONS:
0. c3d0 <DEFAULT cyl  1274 alt 0 hd 255 sec 63>  /xpvd/xdf@0
1. c3d1 <DEFAULT cyl 19464 alt 0 hd 255 sec 63>  /xpvd/xdf@1
2. c3d2 <DEFAULT cyl  1305 alt 0 hd 255 sec 63>  /xpvd/xdf@2
Specify disk (enter its number): ^C
root@domU-12-31-39-03-21-E4:~#
Create a ZFS storage pool on the EBS volume.
root@domU-12-31-39-03-21-E4:~# zpool create ebsmysql c3d2

root@domU-12-31-39-03-21-E4:~# zpool list
NAME       SIZE   USED  AVAIL    CAP  HEALTH  ALTROOT
ebsmysql  9.94G  73.5K  9.94G     0%  ONLINE  -
mnt        149G  73.5K   149G     0%  ONLINE  -
rpool     9.75G  3.50G  6.25G    35%  ONLINE  -
root@domU-12-31-39-03-21-E4:~#
root@domU-12-31-39-03-21-E4:~# zpool status ebsmysql
pool: ebsmysql
state: ONLINE
scrub: none requested
config:
NAME        STATE     READ WRITE CKSUM
ebsmysql    ONLINE       0     0     0
c3d2        ONLINE       0     0     0
errors: No known data errors
root@domU-12-31-39-03-21-E4:~#
Move MySQL on ZFS storage pool based on EBS
Make sure MySQL service is disabled.
root@domU-12-31-39-03-21-E4:~# svcs svc:/application/mysql:default
STATE          STIME    FMRI
disabled       21:57:48 svc:/application/mysql:default
root@domU-12-31-39-03-21-E4:~#
If MySQL service is running make sure to disable it using  svcadm disable svc:/application/mysql:default
Move MySQL installation directory to /ebsmysql
root@domU-12-31-39-03-21-E4:~# mv /usr/local/mysql/ /ebsmysql/
root@domU-12-31-39-03-21-E4:~#
Create a soft link to /ebsmysql/mysql/ under /usr/local with the name ‘mysql’
root@domU-12-31-39-03-21-E4:~# cd /usr/local/
root@domU-12-31-39-03-21-E4:/usr/local# ln -s /ebsmysql/mysql/ mysql
root@domU-12-31-39-03-21-E4:/usr/local# ls -la
total 5
drwxr-xr-x  2 root root  3 2010-01-13 22:08 .
drwxr-xr-x 32 root sys  46 2009-02-19 23:20 ..
lrwxrwxrwx  1 root root 16 2010-01-13 22:08 mysql -> /ebsmysql/mysql/
root@domU-12-31-39-03-21-E4:/usr/local#
Enable MySQL service
root@domU-12-31-39-03-21-E4:~# svcadm enable svc:/application/mysql:default
root@domU-12-31-39-03-21-E4:~# svcs svc:/application/mysql:default
STATE          STIME    FMRI
online         22:09:43 svc:/application/mysql:default
root@domU-12-31-39-03-21-E4:~#
Test MySQL
root@domU-12-31-39-03-21-E4:~# /ebsmysql/mysql/bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.25-rc Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sbtest             |
| test               |
+--------------------+
4 rows in set (0.01 sec)
mysql>

5. Detach EBS volume from AMI instance

root@domU-12-31-39-03-21-E4:~# svcadm disable svc:/application/mysql:default

root@domU-12-31-39-03-21-E4:~# svcs svc:/application/mysql:default
STATE          STIME    FMRI
disabled       3:09:07 svc:/application/mysql:default
root@domU-12-31-39-0A-0E-64:~#

root@domU-12-31-39-03-21-E4:~# zpool export ebsmysql

root@domU-12-31-39-03-21-E4:~# zpool list
NAME    SIZE   USED  AVAIL    CAP  HEALTH  ALTROOT
mnt     149G  73.5K   149G     0%  ONLINE  -
rpool  9.75G  3.22G  6.53G    33%  ONLINE  -
root@domU-12-31-39-03-21-E4:~#
$ ec2-detach-volume vol-688d4c01
ATTACHMENT    vol-688d4c01   i-9e6858f6    2    detaching    2010-01-17T02:58:33+0000</div>

$ ec2-describe-volumes vol-688d4c01
VOLUME    vol-688d4c01    10        us-east-1a    available    2010-01-17T02:50:34+0000
divyen-patels-macbook:ec2 divyenpatel$

6. Re-attach EBS volume to the AMI instance

$ ec2-attach-volume -i i-9e6858f6 -d 2 vol-688d4c01
ATTACHMENT    vol-5f0ccd36    i-9e6858f6    2    attaching    2010-01-17T03:18:25+0000

$ ec2-describe-volumes vol-688d4c01
VOLUME    vol-688d4c01    10        us-east-1a    in-use    2010-01-17T02:50:34+0000
ATTACHMENT    vol-688d4c01    i-9e6858f6    2    attached    2010-01-17T03:18:25+0000
Import the pool
root@domU-12-31-39-03-21-E4:~# zpool import ebsmysql

root@domU-12-31-39-03-21-E4:~# zpool list
NAME       SIZE   USED  AVAIL    CAP  HEALTH  ALTROOT
ebsmysql  9.94G   285M  9.66G     2%  ONLINE  -
mnt        149G  73.5K   149G     0%  ONLINE  -
rpool     9.75G  3.22G  6.53G    33%  ONLINE  -
root@domU-12-31-39-03-21-E4:~#

7. Add another EBS Volume to pool

$ ec2-create-volume --size 10 --availability-zone us-east-1a
VOLUME    vol-990ccdf0    10        us-east-1a    creating    2010-01-17T03:24:24+0000

$ ec2-describe-volumes vol-990ccdf0
VOLUME    vol-990ccdf0    10        us-east-1a    available    2010-01-17T03:24:24+0000

$ ec2-attach-volume -i i-9e6858f6 -d 3 vol-990ccdf0
ATTACHMENT    vol-990ccdf0    i-9e6858f6    3    attaching    2010-01-17T03:25:02+0000

$ ec2-describe-volumes vol-990ccdf0
VOLUME    vol-990ccdf0    10        us-east-1a    in-use    2010-01-17T03:24:24+0000
ATTACHMENT    vol-990ccdf0    i-9e6858f6    3    attached    2010-01-17T03:25:02+0000

root@domU-12-31-39-03-21-E4:~# zpool list
NAME       SIZE   USED  AVAIL    CAP  HEALTH  ALTROOT
ebsmysql  9.94G   285M  9.66G     2%  ONLINE  -
mnt        149G  73.5K   149G     0%  ONLINE  -
rpool     9.75G  3.22G  6.53G    33%  ONLINE  -

root@domU-12-31-39-03-21-E4:~# zpool add ebsmysql c3d3

root@domU-12-31-39-03-21-E4:~# zpool list
NAME       SIZE   USED  AVAIL    CAP  HEALTH  ALTROOT
ebsmysql  19.9G   285M  19.6G     1%  ONLINE  -
mnt        149G  73.5K   149G     0%  ONLINE  -
rpool     9.75G  3.22G  6.53G    33%  ONLINE  -
root@domU-12-31-39-03-21-E4:~#
root@domU-12-31-39-03-21-E4:~# zpool status ebsmysql
pool: ebsmysql
state: ONLINE
scrub: none requested
config:
NAME        STATE     READ WRITE CKSUM
ebsmysql    ONLINE    0     0    0
c3d2        ONLINE    0     0    0
c3d3        ONLINE    0     0    0
errors: No known data errors
root@domU-12-31-39-03-21-E4:~#

Important Links:

Amazon EBS, OpenSolaris, and ZFS Guide to Getting Started
http://blogs.sun.com/ec2/resource/OpenSolaris-ZFS-EBS-v1.3.pdf

Connecting MySQL server on Amazon EC2 instance using ssh tunnel

SSH tunnel basically does the port forwarding via secure ssh service.

Let’s start with launching a latest hardened AMP stack AMI (ami-e0b05389) instance.
Once the instnace is up and running, open the terminal client and connect to the instance using ssh.

Edit the /etc/ssh/sshd_config file and set AllowTcpForwarding to yes

AllowTcpForwarding yes

Restart ssh service

svcadm restart svc:/network/ssh:default

Comment out skip-networking from /etc/mysql/my.cnf and save the file

#skip-networking

Restart MySQL service

svcadm restart svc:/application/database/mysql:version_51

exit the ssh connection and recreate ssh connection using following command.

ssh -i <keypair> -L 3307:localhost:3306 root@ec2-xxx-xxx-xxx-xx.compute-1.amazonaws.com

Keep the terminal opened, and start MySQL Query Browser / MySQL administrator from your local machine. and  connect to the MySQL server on remote EC2 instance via the port 3307, as shown in the following sanpshots.

 

When done close the ssh connection and it should close the ssh tunnel.

Administering OpenSolaris Based MySQL AMI using MySQL Administator Tool

before you begin, have a quick look at the opensolaris MYSQL AMI(Amazon Machine Images) catalog

[Step -1]

Launch OpenSolaris MySQL 5 AMI

-bash # ec2-run-instances ami-63ce2a0a -k your-keypair

[Step -2]

get the public DNS name associated with the instance

-bash # ec2-describe-instances i-xxxxxxxx

[Step -3]

if the instance is in the creation or booting up phase, you may see
‘Pending’ in place of address.

Once the instance is up and running you will get the public DNS name
which should look like as ec2-xx-xxx-xx-xx.compute-1.amazonaws.com

[Step -4]

login into the instancce with the your keypair

ssh -i <your keypair file path>
root@ec2-xx-xxx-xx-xx.compute-1.amazonaws.com

[Step -5]

The first step to start MySQL server.

To do this, “su – mysql” and type “mysqld &” and “exit”

-bash # su – mysql
mysqld &

[Step-6]

if you have changed the original my.conf file (/etc/my.cnf) make sure
that there is no “skip-networking” option in this file.

[Step-7]

test if mysql is listening to external connections on the port 3306

-bash # netstat -a | grep -i 3306

*.3306
*.*
0      0
49152      0 LISTEN

[Step-8]

Login into mysql database with root user

-bash # su – mysql
-bash # mysql -u root -p

[Step-9]

Execute this query to grant access to external remote host for
administering mysql databases.

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
INDEX,ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO ‘root’@’%’
IDENTIFIED BY ‘password’;

Query OK, 0 rows affected (0.00 sec)

here  ‘%; is the wild card character and used for any remote
host. (if you want to give access to specific remote host, we can
replace it with host address) and ‘password’ is the password you want
to set for the remote host which needs to be provided to make
connection.

[Step-10]

Open “MySQL Administrator” tool.

Provide follwing details to connect to the instance

Server Hostname: ec2-xx-xxx-xx-xx.compute-1.amazonaws.com

Username: root

password: password

click on the connect and you are ready to administer mysql server
running on ec2 instance  with mysql administrator tool.