ELC

Running MySQL on your Amazon EBS volume with RightScale

Getting Setup

Here at ELC we are big supporters of RightScale#8482 so this tutorial will be driven with that in mind. To start off I simply created a new EC2 instance equipped with Nginx and Mongrel, and setup a very simple Rails app with one table (blobs) just to show the db was working properly.

Ok great, now we have our working Rails app and MySQL database running on EC2

Configuring the EBS Volume

RightScale#8482 makes it quite easy to create and attach an EBS volume to your instance. Simply click on the Volumes tab,

then Create and Attach Volume.

Choose a name and a size for it and click create, and in just a few minutes, we can see that the volume has been created at /dev/sdj:


[dlabare@domU-12-31-38-00-2D-82:/dev] ls -la
...
brw-r----- 1 root disk 8, 3 Jan 5 12:50 sda3
brw-r----- 1 root disk 8, 144 Jan 5 15:01 sdj
drwxr-xr-x 2 root root 40 Jan 5 12:50 shm
...


Now let’s format it.

[dlabare@domU-12-31-38-00-2D-82:/dev] sudo /sbin/mkfs.ext3 /dev/sdj
mke2fs 1.39 (29-May-2006)
/dev/sdj is entire device, not just one partition!
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
6553600 inodes, 13107200 blocks
655360 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=0
400 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424

This filesystem will be automatically checked every 34 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.


Great, now we’ve got our drive, let’s mount it.

[dlabare@domU-12-31-38-00-2D-82:/dev] sudo mkdir /mnt/ebs
[dlabare@domU-12-31-38-00-2D-82:/dev] sudo mount /dev/sdj /mnt/ebs
[dlabare@domU-12-31-38-00-2D-82:/dev] df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda1 10320888 2080172 7716380 22% /
/dev/sda2 153899044 252388 145829032 1% /mnt
/dev/sdj 51606140 184272 48800428 1% /mnt/ebs


Cool, now we’ve got our EBS volume located at /mnt/ebs (it’s only 50 gigs for demo purposes, you’d probably choose a larger size for anything serious)

MySQL Config

First thing to do is stop mysql for now

[dlabare@domU-12-31-38-00-2D-82:/mnt/ebs] sudo /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]


Now let’s move over the database files onto the EBS volume

sudo mkdir /mnt/ebs/lib /mnt/ebs/log
sudo mv /var/lib/mysql /mnt/ebs/lib/
sudo mv /var/log/mysql* /mnt/ebs/log/


Then we need to tell MySQL about where to find these files. Edit your /etc/my.cnf file to point to the correct locations.


[client]
#password = your_password
#port = 3306
socket = /mnt/ebs/lib/mysql/mysql.sock

...


Now go ahead and restart mysql:

[dlabare@domU-12-31-38-00-2D-82:/mnt/ebs] sudo /etc/init.d/mysqld start
Starting mysqld daemon with databases from /mnt/ebs/lib/mysql
Starting MySQL: [ OK ]


Great, it worked*. Now save this config file to your EBS volume so it doesn’t get lost if your instance crashes.


sudo cp /etc/my.cnf /mnt/ebs/my.cnf-bak


Volume Snapshots

You can manually take snapshots of your volume through the RightScale#8482 interface when needed, as well as have a cron job run back ups periodically. Eric Hammond has written a nice little backup script that that uses EC2 API tools, the script can be found here.

You can also read his detailed post on how to do this whole setup using the command line tools, which was a great help for me when getting setup.

Why this is a good thing

There are many good reasons to try this method of deployment:

• Easily bump up (or down) your instance size without having to copy your database each time. Simply mount the volume to your new instance, copy over your mysql config, and you’re good to go.
• Rest assured knowing your snapshot data is backed up in multiple geographic locations
• Know that your database storage is unaffected by your instance failing
• Grow your volume size easily when space gets low