EBS Snapshots of a MySQL Slave Database on EC2

| 3 Comments

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]

3 Comments

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

Leave a comment

Ubuntu AMIs

Ubuntu AMIs for EC2:


More Entries

When Are Your SSL Certificates Expiring on AWS?
If you uploaded SSL certificates to Amazon Web Services for ELB (Elastic Load Balancing) or CloudFront (CDN), then you will want to keep an eye on the expiration dates and…
Throw Away The Password To Your AWS Account
reduce the risk of losing control of your AWS account by not knowing the root account password As Amazon states, one of the best practices for using AWS is Don’t…
AWS Community Heroes Program
Amazon Web Services recently announced an AWS Community Heroes Program where they are starting to recognize publicly some of the many individuals around the world who contribute in so many…
EBS-SSD Boot AMIs For Ubuntu On Amazon EC2
With Amazon’s announcement that SSD is now available for EBS volumes, they have also declared this the recommended EBS volume type. The good folks at Canonical are now building Ubuntu…
EC2 create-image Does Not Fully "Stop" The Instance
The EC2 create-image API/command/console action is a convenient trigger to create an AMI from a running (or stopped) EBS boot instance. It takes a snapshot of the instance’s EBS volume(s)…
Finding the Region for an AWS Resource ID
use concurrent AWS command line requests to search the world for your instance, image, volume, snapshot, … Background Amazon EC2 and many other AWS services are divided up into various…
Changing The Default "ubuntu" Username On New EC2 Instances
configure your own ssh username in user-data The official Ubuntu AMIs create a default user with the username ubuntu which is used for the initial ssh access, i.e.: ssh ubuntu@<HOST>…
Default ssh Usernames For Connecting To EC2 Instances
Each AMI publisher on EC2 decides what user (or users) should have ssh access enabled by default and what ssh credentials should allow you to gain access as that user.…
New c3.* Instance Types on Amazon EC2 - Nice!
Worth switching. Amazon shared that the new c3.* instance types have been in high demand on EC2 since they were released. I finally had a minute to take a look…
Query EC2 Account Limits with AWS API
Here’s a useful tip mentioned in one of the sessions at AWS re:Invent this year. There is a little known API call that lets you query some of the EC2…
Using aws-cli --query Option To Simplify Output
My favorite session at AWS re:Invent was James Saryerwinnie’s clear, concise, and informative tour of the aws-cli (command line interface), which according to GitHub logs he is enhancing like crazy.…
Reset S3 Object Timestamp for Bucket Lifecycle Expiration
use aws-cli to extend expiration and restart the delete or archive countdown on objects in an S3 bucket Background S3 buckets allow you to specify lifecycle rules that tell AWS…
Installing aws-cli, the New AWS Command Line Tool
consistent control over more AWS services with aws-cli, a single, powerful command line tool from Amazon Readers of this tech blog know that I am a fan of the power…
Using An AWS CloudFormation Stack To Allow "-" Instead Of "+" In Gmail Email Addresses
Launch a CloudFormation template to set up a stack of AWS resources to fill a simple need: Supporting Gmail addresses with “-” instead of “+” separating the user name from…
New Options In ec2-expire-snapshots v0.11
The ec2-expire-snapshots program can be used to expire EBS snapshots in Amazon EC2 on a regular schedule that you define. It can be used as a companion to ec2-consistent-snapshot or…
Replacing a CloudFront Distribution to "Invalidate" All Objects
I was chatting with Kevin Boyd (aka Beryllium) on the ##aws Freenode IRC channel about the challenge of invalidating a large number of CloudFront objects (35,000) due to a problem…
Email Alerts for AWS Billing Alarms
using CloudWatch and SNS to send yourself email messages when AWS costs accrue past limits you define The Amazon documentation describes how to use the AWS console to monitor your…
Cost of Transitioning S3 Objects to Glacier
how I was surprised by a large AWS charge and how to calculate the break-even point Glacier Archival of S3 Objects Amazon recently introduced a fantastic new feature where S3…
Running Ubuntu on Amazon EC2 in Sydney, Australia
Amazon has announced a new AWS region in Sydney, Australia with the name ap-southeast-2. The official Ubuntu AMI lookup pages (1, 2) don’t seem to be showing the new location…
Save Money by Giving Away Unused Heavy Utilization Reserved Instances
You may be able to save on future EC2 expenses by selling an unused Reserved Instance for less than its true value or even $0.01, provided it is in the…
Installing AWS Command Line Tools from Amazon Downloads
This article describes how to install the old generation of AWS command line tools. For the most part, these have been replaced with the new AWS cli that is…
Convert Running EC2 Instance to EBS-Optimized Instance with Provisioned IOPS EBS Volumes
Amazon just announced two related features for getting super-fast, consistent performance with EBS volumes: (1) Provisioned IOPS EBS volumes, and (2) EBS-Optimized Instances. Starting new instances and EBS volumes with…
Which EC2 Availability Zone is Affected by an Outage?
Did you know that Amazon includes status messages about the health of availability zones in the output of the ec2-describe-availability-zones command, the associated API call, and the AWS console? Right…
Installing AWS Command Line Tools Using Ubuntu Packages
See also: Installing AWS Command Line Tools from Amazon Downloads Here are the steps for installing the AWS command line tools that are currently available as Ubuntu packages. These include:…
Ubuntu Developer Summit, May 2012 (Oakland)
I will be attending the Ubuntu Developer Summit (UDS) next week in Oakland, CA. ┬áThis event brings people from around the world together in one place every six months to…
Uploading Known ssh Host Key in EC2 user-data Script
The ssh protocol uses two different keys to keep you secure: The user ssh key is the one we normally think of. This authenticates us to the remote host, proving…
Seeding Torrents with Amazon S3 and s3cmd on Ubuntu
Amazon Web Services is such a huge, complex service with so many products and features that sometimes very simple but powerful features fall through the cracks when you’re reading the…
CloudCamp
There are a number of CloudCamp events coming up in cities around the world. These are free events, organized around the various concepts, technologies, and services that fall under the…
Use the Same Architecture (64-bit) on All EC2 Instance Types
A few hours ago, Amazon AWS announced that all EC2 instance types can now run 64-bit AMIs. Though t1.micro, m1.small, and c1.medium will continue to also support 32-bit AMIs, it…
ec2-consistent-snapshot on GitHub and v0.43 Released
The source for ec2-conssitent-snapshot has historically been available here: ec2-consistent-snapshot on Launchpad.net using Bazaar For your convenience, it is now also available here: ec2-consistent-snapshot on GitHub using Git You are…