Moving a MySQL Database from One EBS Volume to Another on AWS EC2

Recently at Cloud Sidekick we had a booth at Cloud Expo West and it was my job to stand up a demo environment to show off our latest product Maestro. I was using a single server with all of the application components installed. The demo env was installed on an Amazon AWS EC2 m1.medium with the standard 8 gig root device EBS volume. Usually for a demo environment 8 gig is fine, however with all of the monitoring metrics I was collecting on day two of demoing the application we started to run out of disk space. The monitoring polling interval frequency was set very low (several times a minute) so that we could collect more data quickly and make the graphs pretty.

It turned out it was my MySQL database that was growing and consuming the disk so I needed either create a new demo instance with a larger disk and forego all the demo data or move the database off to an external EBS volume. Here’s how to move an existing MySQL database from one EBS volume to another in Amazon EC2.

First a little background: These instructions are for Ubuntu 12.04 Precise with MySQL 5.5. Mileage may vary for other flavors and versions.

Ssh into your EC2 instance and get an idea of your current disk space:

df -h

Now let’s create a new EC2 EBS volume and attach it to our instance. You can create the volume any number of ways (command line, script) but for this example we’ll use the EC2 console.

Login to the AWS Console, select the AWS cloud in which your database instance resides. Navigate to Services, EC2, Instances. Find your MySQL database instance and make note of the availability zone and instance id.

Now navigate to Volumes. Click on the Create Volume button. I leave it up to you to determine the size of the new volume, just remember that disk is relatively cheap. Select the same availability zone as your database server as EBS volumes cannot be attached to servers in different zones.

Note: these images appear small on the post, just click on them to view larger size.

When the volume status shows that it is “available”, select the volume and go ahead and tag it.

Now from the toolbar above the volume list, select More, Attach Volume.

Select the instance with the MySQL database. You should be able to leave the Device as the default unless you have other EBS volumes other than the root device attached.

After refreshing your console a couple times you should see the volume status as attached.

We’re done with the AWS Console, now back to ssh.

Now use fdisk to view all volumes. You will see the new volume (in my case /dev/xvdf) as well as the root volume and another volume which is known as the ephemeral disk. We will use the ephemeral disk later on. For now note the device name that Ubuntu uses (versus /dev/sdf that Amazon reports).

sudo fdisk -l

Format the new volume as an ext4 filesystem.

sudo mkfs -t ext4 /dev/xvdf

The new volume is now attached and formatted. We will mount it later.

Since I was coming close to being out of disk space, I needed some temporary space to move database files around. AWS EC2 comes with free temporary space called ephemeral disk already on instance sizes greater than t1.micro. Some images automatically mount this additional storage when booted, with other images you have to mount the disk yourself. With my Ubuntu 12.04 image, this disk was already mounted under /mnt.

Note about ephemeral disk: this disk space is released when an EC2 instance is terminated or stopped (stays intact on a reboot). Take care what you use it for.

Change your pwd to the ephemeral disk. If your image doesn’t have the ephemeral disk already attached, google “attach ephemeral disk EC2” for plenty of examples.

cd /mnt

Shut down any external processes and connections to the MySQL database. This will be application specific.

Since the MySQL database will be moved, we will want to take a backup of the database. The EC2 root volume is not big enough so we will use the ephemeral disk.

mysqldump -uusername -ppassword databasename | sudo tee database_backup.sql > /dev/null

Compress the backup.

sudo gzip database_backup.sql

Upload the backup to S3 or otherwise copy the file elsewhere. For S3 I used s3put, which is a command line utility that is installed with the Python Boto package and existed already on my Ubuntu image.

s3put -a accesskey -s secretkey -b bucketname database_backup.sql.gz

Stop the MySQL database service.

sudo service mysql stop

Now copy the MySQL data directory from it’s home to the ephemeral disk. Note: you could also tar and gzip the whole directory and store in s3 as well for added safety. Also rsync could be used here as well.

sudo cp -R /var/lib/mysql /mnt/mysql

Verify the files copied as expected.

sudo ls -l /mnt/mysql

Remove the original directory.

sudo rm -rf /var/lib/mysql

Create a new directory in it’s place.

sudo mkdir /var/lib/mysql

Mount the new volume to the new directory.

sudo mount /dev/xvdf /var/lib/mysql

Edit fstab to make sure the new volume is attached at boot. Add another line and use the new device and directory we created.

sudo vi /etc/fstab

Now copy the database files to the mounted volume.

sudo cp -R /mnt/mysql /var/lib/.

Change the permissions back to owner rwx only.

sudo chmod 700 /var/lib/mysql

And change the owner and group back to mysql.

sudo chown -R mysql:mysql /var/lib/mysql

Verify all files are in place, permissions are correct, etc.

sudo ls -l /var/lib/mysql

Start the MySQL service back up.

sudo service mysql start

Test that MySQL is functioning properly by logging into the database. If everything is correct, start your application back up.

That’s it. The database is now on the new, larger EBS volume and the root volume now has space to breath. There are a couple other advantages to putting the database on a separate EBS volume:

  • Snapshots – you can now take EBS volume snapshots of your database on a regular basis without snapshotting the whole OS. This adds security to your database backup strategy (you do have a strategy right?).
  • EBS persistance – If your EC2 server instance is terminated, your database volume will persist. You can always bring up a new server, attach the EBS volume and with some MySQL configuration point to the database.Please feel free to leave comments. Good luck.
Advertisements
Leave a comment

3 Comments

  1. Hi my friend! I wish to say that this article is amazing, great written and include almost all significant infos. I’d like to look extra posts like this .

    Reply
  2. Gautham Nookala

     /  February 19, 2014

    Thanks a ton mate!

    Reply
  3. Meenakshi Sundaram

     /  December 18, 2014

    Great article. Easy to follow. Thanks a lot

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: