Sometimes, MySQL slaves may become inconsistent and thus, they may need to be restored. This post will explain what you need to do in order to rebuild your slaves from a backup and to make them function correctly.
Rebuilding Slaves from Backup using Percona’s XtraBackup
In order to rebuild slaves from backup using Percona’s XtraBackup, perform the following steps:
- Install Percona XtraBackup on both your master and on your slave server. Here’s how to do that on a RedHat-Based OS using yum:
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
- Snapshot the master data directory:
$ innobackupex /home/backups
3. To make the backup consistent, apply the binary logs using –apply-log:
$ innobackupex --apply-log /home/backups/2020-01-01_00-00-00/
The 2020-01-01_00-00-00/ directory contains the backups of your current master databases. The directory should be created automatically, so adjust the name according to your situation.
- Now, copy the backup to the slave server:
$ scp -r /home/backups/2020-01-01_00-00-00 [email protected]:/home/backups/
- Replace the MySQL data directory of the slave server with the backup you just took – stop MySQL, move the /var/lib/mysql folder elsewhere, move the /home/backups/2020-01-01_00-00-00/ (adjust the name according to your situation) folder to /var/lib/mysql and start MySQL again:
$ service mysqld stop
$ mv /var/lib/mysql /var/lib/mysql_old
$ mv /home/backups/2020-01-01_00-00-00 /var/lib/mysql
$ service mysqld start
Restart the replication by setting the master log file and master log positions to the appropriate values – you can do this by checking the contents of xtrabackup_binlog_info:
$ cat /var/lib/mysql/xtrabackup_binlog_info
The command above should produce an output similar to the following:
mysql-bin.0000001 file represents the master log file, numbers after it represent the master log position. Update the replication:
CHANGE MASTER TO MASTER_HOST = ‘127.0.0.1’, MASTER_USER=’user’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.0000001’, MASTER_LOG_POS=000000000;
Replace 127.0.0.1 with the IP of your master server, user with the user of your master server, password with the password of your master server,
mysql-bin.0000001 with the name of your master log file, and 000000000 with your master log position.
Rebuilding Slaves from Backup using MySQL Plugins
If you’re running MySQL 8.0, you can also rebuild your slaves using a clone plugin that was introduced in MySQL 8.0.17: the plugin is intended to provide a way to automatically provision and rebuild failed nodes – a post on Severalnines should give you more insight about it.
In this post we have covered the usage of Percona’s XtraBackup to rebuild slaves from a backup. In newer versions of MySQL slaves can also be rebuilt using a clone plugin that was introduced in MySQL 8.0.17.