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]
Ah man, NOW you tell me you've significantly improved your snapshot code. Care to share at least some pointers in the right direction? Just two weeks ago I went through the whole thing on my production server and thought I could rest easy knowing everything was running smoothly. Now I know there's lots of room for improvement...
Thanks!
Jed: The new EBS snapshot script is currently being tested privately by some volunteers. If you'd like a copy, please contact me and provide your email address. It will eventually be published as open source.
Here are some of the improvements we've made from hard lessons learned:
- Orders of magnitude faster (Perl instead of Java).
- Handle errors and exceptions to prevent frozen file systems on failures.
- Timeouts and retries to prevent interfering with long running SQL statements.
- Pre-"sync" to reduce file system flush/freeze time.
- Pre-"FLUSH" to reduce database FLUSH/LOCK time
- Don't send FLUSH/LOCK statements to replicating slaves to prevent interfering with long running SQL statements.
- XFS and MySQL processing optional.
- Multiple volume support (RAIDed EBS).
The new EBS snapshot script has been published as open source:
http://alestic.com/2009/09/ec2-consistent-snapshot