This article is part of a series of setting up MySQL replication. As with most things, there is always more than one way to do something. In the case of setting up MySQL replication, or rebuilding it, some options are better than others depending on your use case.
The articles in the series are below:
Setting up MySQL Replication using mysqldump
Setting up MySQL Replication using Percona XtraBackup
Setting up MySQL Replication using Rsync
Setting up MySQL Replication using LVM snapshots
This guide will document how to setup MySQL Master / Slave Replication using Rsync. So why use Rsync for setting up or rebuilding MySQL Replication? If your databases and tables are large, but fairly quiet, you can limit the downtime felt by syncing over the majority of the content live with Rsync, then you perform a final Rsync during a scheduled maintenance window to catch any of the tables that may have changed by using a READ LOCK. This is also very useful when you do not have enough disk space available on db01 to perform a traditional backup using mysqldump or using Percona’s XtraBackup as the data is being rsync’ed directly over to db02.
This is a fairly simplistic method of setting of MySQL Replication, or rebuilding it. So in the sections below, we’ll configure the Master and Slave MySQL server for replication, then we’ll sync over the databases.
Setup the Master MySQL server
Configure the my.cnf as shown below:
log-bin=/var/lib/mysql/db01-binary-log expire-logs-days=5 server-id=1
Then restart MySQL to apply the settings:
# CentOS / RHEL: [root@db01 ~]# service mysqld restart # Ubuntu / Debian: [root@db01 ~]# service mysql restart
Finally, grant access to the Slave so it has access to communicate with the Master:
mysql> GRANT REPLICATION SLAVE ON *.* to 'repl’@’10.x.x.x’ IDENTIFIED BY 'your_password';
Setup the Slave MySQL server
Configure the my.cnf as shown below:
relay-log=/var/lib/mysql/db02-relay-log relay-log-space-limit = 4G read-only=1 server-id=2
Then restart MySQL to apply the settings:
# CentOS / RHEL: [root@db02 ~]# service mysqld restart # Ubuntu / Debian: [root@db02 ~]# service mysql restart
Rsync the databases
For reference, the rest of this guide will refer to the servers as follows:
db01 - Master MySQL Server db02 - Slave MySQL Server
On db02 only, rename the existing MySQL datadir, and create a fresh folder:
[root@db02 ~]# service mysqld stop [root@db02 ~]# mv /var/lib/mysql /var/lib/mysql.old [root@db02 ~]# mkdir /var/lib/mysql
On db01 only, perform the initial sync of data over to db02:
[root@db01 ~]# rsync -axvz /var/lib/mysql/ root@db02:/var/lib/mysql/
Now that you have the majority of the databases moved over, its time to perform the final sync of data during a scheduled maintenance window as you will be flushing the tables with a READ LOCK, then syncing over the data.
On db01 only, flush the tables with READ LOCK, and grab the master status information as we’ll need that later. It is critical that you do NOT exit MySQL while the READ LOCK is in place. Once you exit MySQL, the READ LOCK is removed. Therefore, the example below will run this in a screen session so it continues to run in the background:
[root@db01 ~]# screen -S mysql [root@db01 ~]# mysql mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; (detach screen session with ctrl a d)
On db01 only, perform the final rsync of the databases to db02, then release the READ LOCK on db01:
[root@db01 ~]# rsync -axvz --delete /var/lib/mysql/ root@db02:/var/lib/mysql/ [root@db01 ~]# screen -dr mysql mysql> quit [root@db01 ~]# exit
On db02 only, remove the stale mysql.sock file, startup MySQL, configure db02 to connect to db01 using the information from the show master status command you ran on db01 previously, and start replication:
[root@db02 ~]# rm /var/lib/mysql/mysql.sock
[root@db02 ~]# rm /var/lib/mysql/auto.cnf
[root@db02 ~]# service mysqld start
[root@db02 ~]# mysql
mysql> CHANGE MASTER TO MASTER_HOST='10.x.x.x', MASTER_USER='repl', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='db01-bin-log.000001', MASTER_LOG_POS=1456783;
mysql> start slave;
mysql> show slave status\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
...
If those values are the same as what is shown above, then replication is working properly! Perform a final test by creating a test database on the Master MySQL server, then check to ensure it shows up on the Slave MySQL server. Afterwards, feel free to drop that test database on the Master MySQL server.
From here, you should be good to go! Just be sure to setup a monitoring check to ensure that replication is always running and doesn’t encounter any errors. A very basic MySQL Replication check can be found here:
https://github.com/stephenlang/system-health-check