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

$ 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:

MySQL on ZFS:
http://dev.mysql.com/tech-resources/articles/mysql-zfs.html

EBS With OpenSolaris AMI:
http://blogs.sun.com/ec2/entry/ebs_is_supported_on_opensolaris

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


Comments:

Can I use a single MySQL EBS volume to attach, from several application AMIs, wherein each of the AMIs are essentially attaching to the same MysQL database (EBS volume)? Will this work?

Posted by Bindu Rama Rao on April 26, 2010 at 02:16 AM PDT #

Hi Bindu,

You can not attach single EBS volume to multiple instances at the same time. However you can set up a file server on one of the instance and do file sharing or you can mount S3 bucket on all your instances.

Regards,
Divyen Patel

Posted by Divyen Patel on April 26, 2010 at 02:46 AM PDT #

Hello Divyen: Nice Blog, and thanks for your response. Could you please provide or hint at a solution wherein a single MySQL DB can be shared with several EC2 instances (each instance is a copy of the same app server / tomcat based website). Is it better to have a MySQL EBS volume to share (which you just said cannot be shared ) or is it better to create a separate MySQL EC2 instance (EBS based instance), that runs only MySQL, to which other AMIs can interact with using JDBC.

If the question is nebulus ,or crazy, please ignore.

Thanks
Bindu

Posted by Bindu Rama Rao on April 26, 2010 at 03:36 AM PDT #

Set up a separate mysql database AMI (with data on EBS volume). All other appserver/tomcat AMIs can communicate to MySQL via JDBC. You can not have same EBS volume attached to multiple AMIs. but if you need to share files you can NFS share directory where you mounted EBS volume or you can mount S3 file systems using FUSE. For communicating with MySQL database you need not worry about sharing file system, you just need to communicate via JDBC.

Posted by Divyen Patel on April 26, 2010 at 08:37 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Divyen Patel

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today