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.