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 LVM snapshots. So why use LVM snapshots for setting up or rebuilding MySQL Replication? If your databases and tables are large, you can greatly limit the downtime felt to the application using LVM snapshots. This should still be performed during a scheduled maintenance window as you will be flushing the tables with READ LOCK.
Some prerequisites before proceeding are below:
1. Confirming that your datadir is indeed configured on a partition running LVM:
[root@db01 ~]# lvs
2. Confirming that you have enough free space in your Volume Group for the LVM snapshot:
[root@db01 ~]# vgs
So in the sections below, we’ll configure the Master and Slave MySQL server for replication, then we’ll use an LVM snapshot for syncing the databases over to db02.
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
Use LVM snapshots for syncing over 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 [root@db02 ~]# chown mysql:mysql /var/lib/mysql
On db01 only, create a snapshot script for MySQL to ensure things move quick to limit downtime:
[root@db01 ~]# vim /root/lvmscript.sql FLUSH LOCAL TABLES; FLUSH LOCAL TABLES WITH READ LOCK; SHOW MASTER STATUS; SYSTEM lvcreate -L 10G -s vglocal00/mysql00 -n mysqlsnapshot00 3>&- SHOW MASTER STATUS; UNLOCK TABLES;
On db01 only, during a scheduled maintenance window, run the script to create the LVM snapshot, and be sure to take note of master status information as that will be needed later:
[root@db01 ~]# mysql -t < /root/lvmscript.sql
On db01 only, mount the snapshot, sync over the contents to db02, and then remove the snapshot since it will no longer be needed:
[root@db01 ~]# mount /dev/mapper/vglocal00-mysqlsnapshot00 /mnt [root@db01 ~]# rsync -axvz --delete -e ssh /mnt/ root@db02:/var/lib/mysql/ [root@db01 ~]# umount /mnt [root@db01 ~]# lvremove vglocal00/mysqlsnapshot00
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 ~]# 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