EBS Snapshots of a MySQL Slave Database on EC2

At our company, CampusExplorer.com, we regularly snapshot the EBS volume which holds our MySQL database using the basic procedure I outlined in the article “Running MySQL on Amazon EC2 with Elastic Block Store”, though the snapshot code has been significantly improved through our experience in the last year.

As others have reported, we also found that the background EBS snapshot process on EC2 increased IO wait on the source EBS volume holding the production database, which had a negative impact on the performance of the production web site itself. So, we moved the frequent snapshot process to a slave database which is always completely up to date with the production master database.

Aside: Having complete backups is not the only reason to do EBS snapshots. They also increase the reliability and failsafe-ness of the EBS volume itself, so we still run occasional snapshots on the production database, just during off-peak hours.

Steve Caldwell (chief tech at CampusExplorer.com) has automated some great push button EC2 system launches and configurations including EBS database volumes created from snapshots, attached, mounted, etc. This is convenient to do things like setting up a temporary development system for a contractor, starting a staging environment for QA, or running some database intensive reports on near-production data.

skip-slave-start

When EBS volumes were created from the snapshots of the slave database, Steve found that as soon as the MySQL server came up, it started replication from the master thinking it was a slave database. In some cases this is fine, but in others we want to see the database in exactly the state it was in at the time of the snapshot. We tried running “STOP SLAVE” just before creating the snapshot, but replication still resumed when the server started.

We finally found the solution in the option –skip-slave-start which tells MySQL to not start the replication process when the server comes up.

I haven’t looked into how to pass options to mysqld when running /etc/init.d/mysql so we settled on adding this line to the [mysqld] section of /etc/mysql/my.cnf by default on our non-slave servers:

skip-slave-start

On a system which we know should start as a slave, we omit this directive. On a system we want to turn into a slave, we simply run “START SLAVE” and remove this line for future restarts. If the binary logs available on the master go back to the point where the snapshot was created, then replication begins and it eventually catches up to the present.

[Update 2009-08-06: Clarify skip-start-slave meaning]