Setting up a MySQL slave server can be a daunting task, but the RightScale MySQL Manager makes it very painless! The goal of the slave set-up procedure is to take a fresh EC2 instance with MySQL installed, copy an initial database snapshot for the master server onto it, and then start the standard MySQL replication process.
- The instance is set-up with the database files located on /mnt which is formatted using LVM2, i.e. ready for snapshots.
- A backup is made on the master using our snapshot procedure except that:
- the tar archive is copied to the soon-to-be slave instead of to S3.
- the master log file positions are recorded.
- MySQL is stopped on the slave.
- The master’s binlogs that have been copied to the slave are deleted and binary logging is disabled on the slave.
- MySQL is started on the slave and the replication is set-up and started.
Wow, that sounds easy! Of course the devil is in the details…
Database replication is not without its gotchas, and MySQL is no exception. The two most important things to keep in mind is that only transactional storage systems replicate accurately, this means you should be using InnoDB tables exclusively unless you have good reasons not to and know what you’re getting into. Even then, it is possible to construct non-deterministic SQL update statements that produce different results on the slave than on the master. All this is one reason we take backups on both the slave and the master node. Backups on the slave have little impact on the master and can thus be done very frequently at little cost, but a daily backup on the master provides insurance in case something went awry in the replication process.