How to Setup MySQL Master Slave Replication using Percona XtraBackup

Percona XtraBackup is free and available for download, it is open source hot backup utility for MySQL based server that does not lock database during the backup process.

You can backup data from InnoDB, XtraBackup, and MyISAM tables on MySQL 5.1 to 5.7 servers. There are lots of advanced features including see Percona XtraBackup.

In this article I will show you how to setup MySQL Master Slave Replication using Percona XtraBackup.

Prerequisites

1. Using Percona XtraBackup on MySQL and MariaDB, Percona 5.5 and above should be available. Type below command to show version.

# mysql -V
mysql  Ver 14.14 Distrib 5.5.49, for Linux (x86_64) using readline 5.1

2. MySQL Client libraries should be installed.

3. On Master and Slave server both have same innodb-log-file-size defined in my.cnf file, and it should be at least 48M in size.

# grep innodb-log-file-size /etc/my.cnf 
innodb-log-file-size = 128M

4. There should be no symlinks available in master MySQL datadir. Follow the below link to check.

# du -sch /var/lib/mysql/ $(for i in $(find /var/lib/mysql/ -type l); do readlink $i; done)

5. On master server there should be one instance of MySQL running. If there are multiple versions of MySQL running, its easy to backup the wrong data.

6. Event scheduler should be disabled. Check with following command.

# mysql
mysql> show variables where Variable_name like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

7. On master there should be no tables are using the MEMORY engine. Check using below command.

# 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. NTP should be enabled on both master and slave server and both server should have same timezone.

# ps waux |grep ntp
ntp       4323  0.0  0.0  30740  1684 ?        Ss   Sep09   0:02 ntpd -u ntp:ntp -p /var/run/ntpd.pid -g
# date
Tue Sep 20 05:06:50 UTC 2017

9. On master and slave should have the same binlog_format and expire_log_days variables in my.cnf if binary logging is enabled on the slave.

# egrep 'binlog-format|expire-logs-days' /etc/my.cnf 
expire-logs-days = 7
# binlog-format = STATEMENT

Lets go to setup MySQL replication.

Setup MySQL Master Server

Edit the my.cnf file as shown below:

# vim /etc/.my.cnf

log-bin=/var/lib/mysql/db0-binary-log
expire-logs-days=7
server-id=1

Restart MySQL service to apply settings:

On CentOS / RHEL

# service mysqld restart

On Ubuntu / Debian

# service mysql restart

Now grant access to the Slave so it has access to communicate with the Master:

mysql> GRANT REPLICATION SLAVE ON *.* to 'repluser’@’10.x.x.x’ IDENTIFIED BY 'your_password';

Setup MySQL Slave Server

Edit the my.cnf file as shown below:

# vim /etc/my.cnf

relay-log=/var/lib/mysql/db1-relay-log
relay-log-space-limit = 4G
read-only=1
server-id=2

Restart MySQL service to apply the settings:

On CentOS / RHEL

# service mysqld restart

On Ubuntu / Debian

# service mysql restart

Percona XtraBackup Installation

For example we have two database servers is available:

db0 – MySQL Master Server
db1 – MySQL Slave Sever

Install Percona XtraBackup on master (db0). Follow the below link to install Percona XtraBackup.

• How to Install Percona XtraBackup

Now confirm installed Percona XtraBackup version it should be 2.3 or newer:

# xtrabackup --version

Setup MySQL replication using Percona XtraBackup

Rename the existing MySQL datadir on slave (db1) and create a fresh directory:

# service mysqld stop
# mv /var/lib/mysql /var/lib/mysql-old
# mkdir /var/lib/mysql
# chown -R mysql:mysql /var/lib/mysql

On master (db0), create the backup, make the snapshot consistent by applying the log, and rsync it over to slave (db1):

# mkdir /root/perconabackup
# innobackupex /root/perconabackup
# innobackupex --apply-log /root/perconabackup/TIMESTAMP/
# rsync --progress -axvz -e ssh /root/perconabackup/TIMESTAMP/ root@db1:/var/lib/mysql/

On slave (db1), grab the binlog name and position.

# cat /var/lib/mysql/xtrabackup_binlog_info
db01-bin-log.000001     1255741

Now on slave (db1) start the replication.

# mysql
mysql> CHANGE MASTER TO MASTER_HOST='10.x.x.x', MASTER_USER='repluser', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='db0-bin-log.000001', MASTER_LOG_POS=1255741;
mysql> start slave;
mysql> show slave status\G
...
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
...

If you get the same value as mention above it means replication is working properly.