We use MySQL replication for several reasons. First of all, we use it to make sure we can quickly recover from a failure of a primary database server. As an added benefit, it makes backups easier. Of course, getting replication set up can be a pain, especially if you can’t afford to take the main database server down. Here’s how we do it.
First, start by reading the online documentation. The MySQL documentation is well written and accurate. It goes through all of the steps in a good level of detail except for one. Unfortunately, that one step is critical. To start MySQL replication, you need your new slave server to have an exact snapshot of the master at a point in time. MySQL recommends shutting down the master to take a backup. That sounds nice, but I can’t afford several hours of downtime. The naive approach is just to use mysqldump to export a copy of the master and load it on the slave server. This works if you only have one database. With multiple database, you’ll end up with inconsistent data. Mysqldump will dump data from each database on the server in a different transaction. That means that your export will have data from a different point in time for each database.
Instead of dumping all of the data at once, I dump my databases one at a time. Once the first dump completes, I then dump the next database. All databases are dumped using
mysqldump -u root -e -q --single-transaction --master-data database_name
Once I have dumped all of the databases on the server, I load the slave by importing the first database . I configure replication to only process replication events for this database by setting
Next, I find out what the master log position is for my next import. It’s in the first 25 lines of the MySQL dump. Once I have that, I start the slave and tell mysql to replicate until until this position. You can do this by passing the until argument to start slave. For example:
START SLAVE UNTIL MASTER_LOG_FILE='bin.000029', MASTER_LOG_POS=651322976;
Now, the database will be in the same state as when I dumped the next database. I then load that export. Once the load is done, I shutdown mysql and add a record for the newly import database to the replication setup. It will now look like:
I then start mysql and immediately stop the slave. I can the repeat as necessary, running replication until the same time as the previous export, exporting the data then adding the new database to replication.
This process allows me to easily build a new slave server from an existing MySQL master with no downtime.
This solution still works, but the wonderful folks at Percona have made it even easier. You can read how to use their Xtrabackup tool to more quickly and easily set up a new slave.