|
By Eric Hammond, Internet
Startup Technologist (@esh on Twitter)
This guide covers:
- Benefits of using EBS for MySQL
- Setting up an EBS volume on an EC2 instance for use with MySQL
- Configuring MySQL to use the EBS volume for data files and binary logs
- Snapshotting the database
- Restoring the snapshotted database on a second EC2 instance
This explicitly does not cover:
- How to set up an Amazon EC2 account
- How to use MySQL
- Advanced multi-host redundancy, master/slave, failover, scaling,
clusters
The focus here is on simplicity and to help the reader get a feel for
the basics of what EBS offers to database servers.
Benefits of using EBS for MySQL
There are numerous reasons why you will want to run your MySQL
database on EBS. Some of them are obvious and some are wonderful.
They include:
Persistent storage in the event of instance failure -
If an EBS volume is used as the storage for a MySQL database, then the data
is protected from instance termination or failure. You can simply
attach/mount the volume on another instance and MySQL will run its
normal recovery procedures to bring the database up to date with
the binary logs.
Safety & Replication - According to Amazon, "EBS volume data
is replicated across multiple servers". This makes your data safer
than the default instance storage.
Improved performance - Early reports from studies on EBS
disk IO performance indicate that EBS IO rates can be multiple
times faster than ephemeral storage and even local disk IO. This
has obvious benefits for databases which are often IO bound.
Large data storage capacity - EBS volumes can be up to 1TB
in size. In theory you could go larger with LVM or RAID across EBS
volumes, or by placing different databases or table files on
different EBS volumes.
Instance type portability - If you find that your current
small EC2 instance is not able to handle your growing demand, you
could switch the EBS volume holding your MySQL database to a
running extra large instance in a matter of seconds without having
to copy the database across the network. Downgrade instance types
later to save money.
Fast and easy backups - EBS snapshots alone could be a
sufficiently attractive reason to move a database server to Amazon
EC2. Being able to take live, consistent, binary snapshots of the
database in just a few seconds is a thing of beauty. Add in the
ability to create a new EBS volume from a snapshot so another EC2
instance can run against an exact copy of that database... and
you've opened up new worlds of possibilities.
Technology choices
This guide shows examples using the Amazon EC2 API command line
tools which are assumed to be installed on your local system.
Many of the same results can be accomplished with the
AWS Console, the
Firefox EC2 plugin or with
some of the 3rd party web management interfaces to Amazon EC2 such as
RightScale.
This guide shows examples using
Ubuntu 9.04 (Jaunty).
The same concepts would apply with slight
modifications on Debian, Fedora, Red Hat, CentOS, and other Linux
distributions. The latest version of Ubuntu AMIs can be
found on the following page maintained by the author:
http://alestic.com
This guide shows examples using the XFS file system. Besides
being a stable, modern, high performance, journaling file system, XFS
supports file system freeze/thaw which is extremely useful for
ensuring a consistent state during EBS snapshots. XFS also supports
file system growth which can be used if your database and other data
are reaching the limits of your current volume size.
Setting up an EBS volume on an EC2 instance for use with MySQL
To get started, we'll run an EC2 instance, create an EBS volume, and
attach the volume to the instance. You can run the instance in any
availability zone, just make sure to create the volume in the same
zone so it can be attached to the instance.
As mentioned in the previous section, we assume that you have
installed the EC2 API command line tools on your local system.
Run an instance of Ubuntu 9.04 Jaunty base install specifying your ssh
keypair name. Make a note of the instance id (say,
i-IIII1111). Check to see if the instance is running
(might take a minute) and make a note of the external hostname (say,
HOST1111).
ec2-run-instances -z us-east-1a --key YOURKEYPAIR ami-ed46a784
ec2-describe-instances i-IIII1111
Create a new 10 GB EBS volume and make a note of the volume id (say,
vol-VVVV1111). Check to see if the new volume is
available (might take a few seconds).
ec2-create-volume -z us-east-1a -s 10
ec2-describe-volumes vol-VVVV1111
Once it's available, attach the EBS volume to the instance as
/dev/sdh
ec2-attach-volume -d /dev/sdh -i i-IIII1111 vol-VVVV1111
Now we can set things up on the instance itself, so connect to it and
install the necessary software on the instance. Enter and record a
secure MySQL root password when prompted.
ssh -i YOURSSHKEYFILE root@HOST1111
sudo apt-get update && sudo apt-get upgrade -y
sudo apt-get install -y xfsprogs mysql-server
Create an XFS file system on the EBS volume and mount it as /vol
sudo modprobe xfs
sudo mkfs.xfs /dev/sdh
echo "/dev/sdh /vol xfs noatime 0 0" | sudo tee -a /etc/fstab
sudo mkdir -m 000 /vol
sudo mount /vol
You now have a 10 GB (or whatever size you specified) EBS volume
mounted under /vol with an XFS file system, and it will be
automatically mounted if the instance reboots.
Anything you store under /vol (including MySQL files) will persist
beyond the life of the current instance, and you can take snapshots of
this volume for backup or replication.
Configuring MySQL to use the EBS volume
Now that we have an EBS volume mounted on the instance with a good
file system, let's put the MySQL database on that volume and tell
MySQL where it is.
Stop the MySQL server.
sudo /etc/init.d/mysql stop
Move the existing database files to the EBS volume. Point MySQL to
the correct database files on the EBS volume using mount bind.
sudo mkdir /vol/etc /vol/lib /vol/log
sudo mv /etc/mysql /vol/etc/
sudo mv /var/lib/mysql /vol/lib/
sudo mv /var/log/mysql /vol/log/
sudo mkdir /etc/mysql
sudo mkdir /var/lib/mysql
sudo mkdir /var/log/mysql
echo "/vol/etc/mysql /etc/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /etc/mysql
echo "/vol/lib/mysql /var/lib/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/lib/mysql
echo "/vol/log/mysql /var/log/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/log/mysql
Restart the MySQL server.
sudo /etc/init.d/mysql start
You are now running MySQL with all of the data and binary log files
persistently stored on a high performance, redundant EBS volume!
To prove to yourself that a later snapshot saved your database
changes, you might want to load some data or simply create a
placeholder database with a SQL statement like:
mysql -u root -p -e 'CREATE DATABASE tutorial_sample'
Your data is pretty safe here, but let's make it even safer with
snapshot backups.
Snapshotting the database
An EBS snapshot is a point in time copy of the complete EBS volume.
It will save the current state of all files that were placed on that
volume at a block level including the MySQL database data and binary
logs. Snapshots are saved to Amazon S3 which is even more secure than
EBS in that it is archived in multiple geographic locations.
Since file systems and databases often have things in memory on their
way to disk and active processes can be changing the state of the disk
so that it isn't always entirely consistent with itself, we take pains
to flush, lock, and freeze the database and the file system for the
few moments that it takes to create the EBS snapshot. This may cause
a slight pause in activity on that system, but if the process is
automated, it tends to be sufficiently short that most folks won't
notice.
To be able to type all the required commands in a single connection to
the EC2 instance, this example uses the mysql "SYSTEM" statement to
run command line programs, but these commands don't really have to be
run from inside MySQL to be effective.
Start a MySQL session on the instance, using the password you set
above.
mysql -u root -p
In the mysql session, flush the tables to disk and acquire a lock.
Flush the file system to disk and freeze it. Do not exit the
MySQL session or you will lose the lock and snapshot potentially
inconsistent database files!
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
SYSTEM xfs_freeze -f /vol
Note: The output from SHOW MASTER STATUS can be handy if
you later want to start a slave database from the snapshot.
On your local system (which has the EC2 API command line tools
installed) create a snapshot of the EBS volume. Make a note of the
snapshot id (say, snap-SSSS1111).
ec2-create-snapshot vol-VVVV1111
Back in the same MySQL session on the instance, unfreeze the file
system, release the database lock, and you're done!
SYSTEM xfs_freeze -u /vol
UNLOCK TABLES;
EXIT
Though the ec2-create-snapshot command returns quickly, the actual
snapshot may take a while to complete writing to S3. Have no fear, it
will be consistent as of the time the snapshot was initiated above and
no further database or disk writes on the instance will affect it.
You can monitor the progress of the snapshot with this command:
ec2-describe-snapshots snap-SSSS1111
Note: If you have a busy database with some long running
statements, then acquiring a database lock may be difficult without
interfering with operations. However, if all of your tables use the
InnoDB engine, then you may skip the LOCK/UNLOCK statements above.
MySQL will restore the database to a consistent state when it is run
against the binary backup.
Automated snapshot program
The above sequence of MySQL statements and shell commands can be a bit
of a hassle when performed manually, not to mention that database
activity will be blocked while you are typing, so...
The ec2-consistent-snapshot program automates the appropriate flushing and
locking of MySQL and XFS file systems while the EBS snapshot is initiated.
To install this on Ubuntu, use the following steps:
codename=$(lsb_release -cs)
echo "deb http://ppa.launchpad.net/alestic/ppa/ubuntu $codename main"|
sudo tee /etc/apt/sources.list.d/alestic-ppa.list
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys BE09C571
sudo apt-get update
sudo apt-get install -y ec2-consistent-snapshot
sudo PERL_MM_USE_DEFAULT=1 cpan Net::Amazon::EC2
The documentation can be accessed using:
man ec2-consistent-snapshot
This program can be run from a scheduled cron job on the EC2 instance
to automate backup snapshots while you sleep.
Since each snapshot only takes up space based on which blocks have
changed since the last snapshot and since there may be additional
compression applied, the incremental cost of frequent snapshots can
sometimes be small.
Restoring the snapshotted database
So you want to check out the snapshot of your MySQL database to see if
it really has the right data. Or, you'd like to test some code on an
instance which has nearly current production data without risk to the
live production database. Or, you want to start a MySQL replication
slave. Or, you need to recover from an unfortunate DELETE statement
without a WHERE clause...
We'll start another EC2 instance to run the second database server.
It is not necessary to terminate the original instance; both can be
running in parallel. The second instance and volume must be together
in the same availability zone, though it does not have to be where the
first instance/volume reside.
Run a second instance, making a note of the instance id (say,
i-IIII2222). Check to see if the instance is running
(might take a minute) and make a note of the external hostname (say,
HOST2222).
ec2-run-instances -z us-east-1b --key YOURKEYPAIR ami-ed46a784
ec2-describe-instances i-IIII2222
Create a second EBS volume based on the snapshot of the original
volume and make a note of the volume id (say,
vol-VVVV2222). Check to see if the second volume is
available (might take a while).
ec2-create-volume -z us-east-1b --snapshot snap-SSSS1111
ec2-describe-volumes vol-VVVV2222
Once it's available, attach the second EBS volume to the second
instance as /dev/sdh
ec2-attach-volume -d /dev/sdh -i i-IIII2222 vol-VVVV2222
Note: If the original instance had crashed or been terminated,
we could skip the creation of a second volume from a snapshot and
simply attach the original volume to the second instance. The
remainder of these instructions would apply just the same for getting
up and running with that original volume, though you might want to run
xfs_check just before mounting to make sure the file
system is in a consistent state after the crash.
Now, hop on to the second instance, install the required software,
and mount the attached EBS volume. (The MySQL password does not
matter here because we will be using a pre-existing database.)
We also make sure the files are owned by mysql, just in case
UIDs are different on the new instance.
ssh -i YOURSSHKEYFILE root@HOST2222
sudo apt-get update && sudo apt-get upgrade -y
export DEBIAN_FRONTEND=noninteractive
sudo -E apt-get install -y xfsprogs mysql-server
echo "/dev/sdh /vol xfs noatime 0 0" | sudo tee -a /etc/fstab
sudo mkdir -m 000 /vol
sudo mount /vol
sudo find /vol/{lib,log}/mysql/ ! -user root -print0 |
xargs -0 -r chown mysql
sudo find /vol/{lib,log}/mysql/ ! -group root -a ! -group adm -print0 |
xargs -0 -r chgrp mysql
Point MySQL to the correct database files on the EBS volume.
sudo /etc/init.d/mysql stop
echo "/vol/etc/mysql /etc/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /etc/mysql
echo "/vol/lib/mysql /var/lib/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/lib/mysql
echo "/vol/log/mysql /var/log/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/log/mysql
sudo /etc/init.d/mysql start
You now have a second machine running against an exact copy of the
first database at the time of the snapshot. Imagine the possibilities
and smile!
You can verify this by looking for the "tutorial_sample" database you
created earlier.
mysql -u root -p -e 'SHOW DATABASES'
Note: Each of these instances has mounted a different volume and is
using a different set of database files. Though they would have
started out sharing similar values in the database, any changes made
in one will not be reflected in the other.
Cleanup
If you followed the above steps, you will have created resources which
will continue to be charged against your EC2 account until you release
them. Here are the steps to shut down and delete everything created
in this tutorial. The first commands run on the instances:
Unmount the EBS volume file systems on each of the EC2 instances.
sudo /etc/init.d/mysql stop
sudo umount /vol
The rest of the commands should be run on your local system (which has
the EC2 API command line tools installed):
Detach EBS volumes from EC2 instances, delete the volumes, delete the
snapshots, and shutdown the instances.
ec2-detach-volume vol-VVVV1111
ec2-detach-volume vol-VVVV2222
ec2-delete-volume vol-VVVV1111
ec2-delete-volume vol-VVVV2222
ec2-delete-snapshot snap-SSSS1111
ec2-terminate-instances i-IIII1111
ec2-terminate-instances i-IIII2222
Verify that it's all gone.
ec2-describe-instances
ec2-describe-volumes
ec2-describe-snapshots
Feedback
Please send feedback, corrections, and recommended enhancements to the author:
Eric Hammond <ehammond-ec2@thinksome.com>
Questions may be posted to the Amazon EC2 Forum so others can pitch in with and benefit from answers.
I am especially interested in notes about what specific steps you
changed to get this guide to work on other Linux distributions. These
may be included in an update to this tutorial.
Thanks for ideas, code, and suggestions:
Mark Callaghan,
petterim,
Rodney Quillo,
smarttux,
B. True,
nrforum,
mattjive
Resources
UPDATES
The following information has come out since the original publication
of this tutorial:
- There is a serious bug when trying to run some CentOS releases
with XFS on EBS on Amazon's 2.6.21fc8 kernel. There also used to be
similar problems with Debian Lenny and above, and Ubuntu Intrepid and above.
[more info in this thread]
The latest Ubuntu and Debian images listed on
http://alestic.com have a corrected
xfs.ko kernel module and or updated kernel installed and XFS works
fine on any of these images.
[fixes in this thread]
-
The ext3 and other file systems can also be used to create consistent
snapshots with EBS if they are layered on top of LVM or dmsetup. The "dmsetup
suspend" and "dmsetup resume" commands take care of the file system
freezing.
[more info about setting up],
[more info about suspending/resuming]
HISTORY
2009-10-02
- Updated script pointer to ec2-consistent-snapshot
- Converted to Jaunty
2009-08-14
- Create mount point with mode "000" to help protect against
accidentally using it when the EBS volume file system is not
mounted.
- Mention the AWS console
- Update Ubuntu AMI id
2009-05-24
- Switch to using mount bind so that these instructions will work
with the default Apparmor configuration.
- Add "sudo" so that these instructions will work with the Ubuntu
images for EC2 published by Canonical (which require you to log in as
a normal user).
2008-08-20
- Initial document publication.
|
