Very fast MySQL slave setup with zero downtime using rsync

Sunday, November 6th, 2016

Most online tutorials for setting up a slave replication database involve dumping and loading the database on the master with mysqldump. Unfortunately, loading data with mysqldump can be very slow. My friend Cris suggest a much faster method using rsync.

Benefits

The benefits of this method are:

• Very fast setup of a slave by avoiding having to load a logical dump into MySQL.
• Virtually no downtime for the master. There is a small window (seconds, usually) during which writes are temporary blocked.
• If the slave was already set up and has become corrupt for whatever reason, rsync will ensure we won’t have to copy all the data again that’s already on the slave.

In this article I’m going to assume you’ve already correctly configured the master and slave for replication. If not, please see this guide on how to configure MySQL. You can skip the step where they perform a mysqldump, since that’s what this article is replacing with rsync in the first place.

How it works

 Roughly speaking, we’ll be doing the following steps:

1. Stop the slave
2. Rsync the binary files from the master to the slave while the master is running.
3. Set a write lock on the master, record the master log position and do another rsync from the master to the slave.
4. Unlock the master write lock.
5. On the slave, we change the master log position and start replication again.

The benefits are obvious. Since we first do a sync to the slave without a write lock, the master can keep receiving writes. However, this leaves the slave in a potentially corrupt state, since we might have copied the master data in the middle of a transaction. That’s why, after the initial bulk sync, we set a write lock on the master and perform another sync. This only needs to synchronize the new data since the last sync, so it should be fast. The master will only need to be write-locked for a short amount of time. Meanwhile, we record the master log position for the slave. 

Do note that this will only work if your master and slave database are the architecture and run the same MySQL version. If not, this will end in disaster.

Walkthrough

Let’s see a practical example:

On the SLAVE, stop MySQL:

root@slave# sudo /etc/init.d/mysql stop

Configure the SLAVE not to automatically start replication on startup. We need this so we can modify the master log pos after starting the slave. Add the ‘skip-slave-start’ setting to the [mysqld] section:

root@slave# vi /etc/mysql/my.cnf

[mysqld]
skip-slave-start

Now we rsync the MASTER binary data to the SLAVE. For this you’ll need to have root ssh access enabled. Depending on your setup, there are a few files you’ll have to exclude. For example, if you don’t want to overwrite the users on your slave, you’ll have to exclude the ‘mysql’ directory. You should experiment a bit with what you should and shouldn’t exclude.

root@master# rsync -Sa --progress --delete --exclude=mastername* --exclude=master.info --exclude=relay-log.info /var/lib/mysql root@192.168.57.3:/var/lib

We’ve now synchronized the bulk of the master data to the slave. Now we need another sync to put the slave in a valid state. We do this by locking the MASTER to prevent write actions:

root@master# mysql
mysql> flush tables with read lock;
mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000001 |    16242 |              | mysql information_schema performance_schema |
+------------------+----------+--------------+---------------------------------------------+

Record the value of the Position field, since we’ll need it later on the slave. The master is now locked against writes.

Keep this MySQL session open and run the next rsync in a different terminal:

root@master# rsync -Sa --progress --exclude=mastername* --exclude=master.info --exclude=relay-log.info /var/lib/mysql root@192.168.57.3:/var/lib

As soon as the final rsync is done, we can unlock the master. Go back to the terminal containing your MySQL session and unlock the tables:

mysql> unlock tables;

The master can now receive writes again. Time to start the SLAVE:

root@slave# /etc/init.d/mysql start

On the slave, we’ll change the master log position and start replication:

root@slave# mysql
mysql> change master to master_log_pos=16242;
mysql> start slave;

Verify that the slave is working properly:

mysql> show slave status\G

That’s it, your slave is now up and running!

Disclaimer

And now for the big disclaimer:

Copying binary files is always more risky than doing a logical dump and load. By necessity, there are differences between a master and a slave and how the handle the binary files. While in theory the above method should work, I have to caution against using this method if your slave is going to be vital to your setup. If your slave has been set up with the method above, be extra careful when relying on that slave for making logical backups and such.