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 Percona XtraBackup. I strongly recommend reviewing the official documentation on Percona’s site at:
How innobackupex works
Official guide for setting up replication with Percona XtraBackup
So why use Percona XtraBackup for setting up or rebuilding MySQL Replication? Percona XtraBackup performs hot backups on unmodified versions of MySQL, MariaDB and Percona on versions 5.1 and above.
This basically means that you can generally run the backup on InnoDB tables without having the interruption/downtime associated with table locking on your site, like you would normally experience using MySQLdump. However, it is critical to note that table locking WILL still occur on tables using MyISAM and other non-InnoDB tables.
Some important prerequisites before proceeding:
1. Recommend using Percona XtraBackup on MySQL, MariaDB, and Percona 5.5 and above. Example:
[root@db01 ~]# mysql -V mysql Ver 14.14 Distrib 5.5.49, for Linux (x86_64) using readline 5.1
2. Confirm the MySQL client libraries are installed.
3. The MySQL master and slave server both have the same innodb-log-file-size defined in the my.cnf, and that they are at least 48M in size.
[root@db01 ~]# grep innodb-log-file-size /etc/my.cnf innodb-log-file-size = 128M
4. The master does not have symlinks in the MySQL datadir that could cause space to be underestimated. You can check for this by running:
[root@db01 ~]# du -sch /var/lib/mysql/ $(for i in $(find /var/lib/mysql/ -type l); do readlink $i; done)
5. Confirm there is only one instance of MySQL running on the master. When you have multiple versions of MySQL running, its easy to backup the wrong data.
6. Event scheduler is not enabled on the slave. You can check for this by running:
[root@db01 ~]# mysql mysql> show variables where Variable_name like 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.00 sec)
7. Confirm that no tables are using the MEMORY engine on the master. You can check for this by running:
[root@db01 ~]# mysql
mysql> select concat('ALTER TABLE `',table_schema,'`.`',table_name,'` ENGINE=INNODB;') from information_schema.tables where engine='memory' and table_schema not in ('information_schema','performance_schema');
Empty set (0.03 sec)
8. Confirm that both the master and slave server have NTP enabled and running, and that both servers are using the same timezone. Example:
[root@db01 ~]# ps waux |grep ntp ntp 7276 0.0 0.0 30740 1684 ? Ss Aug09 0:02 ntpd -u ntp:ntp -p /var/run/ntpd.pid -g [root@db01 ~]# date Tue Aug 30 05:06:50 UTC 2016
9. Confirm the master and slave have the same binlog_format and expire_log_days variables in the my.cnf when binary logging is enabled on the slave. Example:
[root@db01 ~]# egrep 'binlog-format|expire-logs-days' /etc/my.cnf expire-logs-days = 5 # binlog-format = STATEMENT
Now that you confirmed that the prerequisites are out of the way, proceed with setting up MySQL Replication.
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
Install Percona XtraBackup
For reference, the rest of this guide will refer to the servers as follows:
db01 - Master MySQL Server db02 - Slave MySQL Server
On db01 only, install Percona XtraBackup using Percona’s repos:
[root@db01 ~]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm [root@db01 ~]# yum install percona-xtrabackup-24
On db01 only, confirm it installed XtraBackup version 2.3 or newer:
[root@db01 ~]# xtrabackup --version
On db01 only, to prevent future issues, it is extremely important to remove or disable the yum repo for Percona:
[root@db01 ~]# yum remove percona-release
Setup MySQL replication using Percona XtraBackup
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, create the backup, make the snapshot consistent by applying the log, and rsync it over to db02:
[root@db01 ~]# mkdir /root/perconabackup [root@db01 ~]# innobackupex /root/perconabackup [root@db01 ~]# innobackupex --apply-log /root/perconabackup/TIMESTAMP/ [root@db01 ~]# rsync -axvz -e ssh /root/perconabackup/TIMESTAMP/ root@db02:/var/lib/mysql/
On db02, fix the ownership of the datadir, startup MySQL, and grab the binlog name and position:
[root@db02 ~]# chown -R mysql:mysql /var/lib/mysql [root@db02 ~]# service mysqld start [root@db02 ~]# cat /var/lib/mysql/xtrabackup_binlog_info db01-bin-log.000001 1456783
On db02, startup slave replication:
[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