MySQL Replication to EC2

Servers die.  People make mistakes. Solar flares, um, flare.  There are many things that can cause you to lose your your data.  Fortunately, there is a pretty easy way to protect yourself from data loss if you use MySQL.

My preferred solution is to store a copy on EC2 through replication.  One big reason I like to replicate to EC2 is that it becomes a pretty easy warm-failover site.  All of your database data will be there, to switch over you’ll just need to start up webservers or other systems required by your architecture and make a DNS change.  If your datacenter became a smoking hole in the ground, you could be back up and running on EC2 in 15 minutes or less with proper planning.

No matter where your MySQL master server is hosted, you can replicate to an EC2 instance over the internet.  Latency generally isn’t an issue when compared to the lag that may be introduced by the replication process itself.  I typically see a max of 5-10 second replication lag during general use.  That lag is due to the replication process being single-threaded (only one modification is made to the database at a time.)

Here are a few things to keep in mind when setting up replication:

  • Use a separate EBS volume partition for your mysql data directory
  • There is good replication documentation for MySQL
  • Use SSL
  • Set expire_logs_days to an acceptable value on the slave and server.  The value of this setting will vary depending on the volume of data you send to the slave each day.  Don’t make it so small that recovery with the binlogs will be difficult or impossible.
  • Store your binlogs on the same partition as the mysql data directory.  This simplifies the snapshot and recovery process.

Here’s a sample EBS snapshot perl script for MySQL that can be modified and used to create snapshots of the mysql data on the slave server:

Since this is a mysql slave server, you can create volume snapshots whenever you want without any impact on your master database. By default, AWS imposes a 500 volume snapshot limit.  If you have that many snapshots, you’ll have to delete some before you will be able to create more.

With the periodic snapshots and binlogs, you can recover to any point in time.  I’ve been able to recover from a “bad” query that unintentionally modified all rows in a table as well as accidentally dropped tables.

Can you replicate from multiple database servers to a single server?  Yes, but a rule of replication is that a slave can only have one master.  To make it possible for one server to be a slave to multiple master servers you need to run multiple mysql daemons.  Each daemon runs with its own configuration and separate data directory.  I’ve used this method to run 20 mysql slaves on a single host and I’m sure you could run many more than that.

Millcreek Systems is available to help you setup and maintain MySQL replication for you.  Please contact us if you’d like to discuss our services further.

UVEF Crunch Lunch Presentation Video

In September, I presented at Crunch Lunch and the video of my presentation is now online. Each presenter was given exactly five minutes to present using 20 slides.  Slides automatically advanced every 15 seconds.  Enjoy watching me fumble over my words as I try to pass on too much information in that short timeframe.

The original video is available on the UVEF website.

Slides from my presentation are available here:
http://uvef.net/crunchlunch/crunch_lunch_michael_jensen.pdf

More information on other presenters is available here:
http://crunchlunch.com/crunch-lunch-at-uvef-september-2010/

Crunch Lunch presentations

I recently presented at Crunch Lunch, hosted by UVEF and they published everyone slides here.  It was quite an experience in rapid-fire presenting.  20 slides in 5 minutes with slides auto-advancing every 15 seconds.  Hardly any time to blink.

I’m told that they’ll be posting the video too, which will be great since the slides contain only a fraction of the information shared at the event.  I’ll post another update when the videos go live so you can see how it all went (and watch me fumble my words as I try to say everything I wanted to.)