How To Use Percona Xtrabackup To Create A MySQL Slave

Percona Xtrabackup can be used to create “hot backups” of MySQL servers fairly quickly and can avoid some of the pitfalls of mysqldump. Xtrabackup can be setup to use backup locks instead of read locks which is much less invasive, this is available on innodb tables. MyISAM and other tables will still need to be read locked to perform a backup.  Xtrabackup will work on MySQL, MariaDB and Percona (versions 5.1, 5.5, 5.6, 5.7).  You can then use this backup to easily replicate the MySQL slave. This guide assumes you already have a running SQL server and CentOS.

Install Percona Xtrabackup

First you want to install the repository, you can get additional repositories from Percona‘s site

yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

Then install Percona Xtrabackup

yum install -y percona-xtrabackup-24

Install rsync as well as we will use this to transfer the backup to the slave

yum install -y rsync

Create A Backup with Xtrabackup

First you will want to create a initial backup with innobackupex

innobackupex /root/percona-backup

Replace /root/percona-backup with path if you need to. If you need to specify a user and password use the following flags

innobackupex --user=username --password=passwordhere /root/percona-backup

When it starts you should see the following message

innobackupex: Starting the backup operation

Upon completion you should see the following

xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
 170810 21:37:19 completed OK!

If you look in the /root/percona-backup directory you set, you will see a time-stamp directory, this is the SQL backup

# ls /root/percona-backup/
 2017-08-10_21-37-17

You will now need to apply the transaction log to the backup by performing the following

innobackupex ---apply-log /root/percona-backup/2017-08-10_21-37-17

You will again be looking for the ‘OK’ message at the end of the program

xtrabackup: The latest check point (for incremental): '1597945'
 xtrabackup: Stopping log copying thread.
 .170810 21:44:58 >> log scanned up to (1597945)

170810 21:44:58 Executing UNLOCK TABLES
 170810 21:44:58 All tables unlocked
 170810 21:44:58 Backup created in directory '/root/percona-backup/2017-08-10_21-37-17/2017-08-10_21-44-56/'
 170810 21:44:58 [00] Writing /root/percona-backup/2017-08-10_21-37-17/2017-08-10_21-44-56/backup-my.cnf
 170810 21:44:58 [00] ...done
 170810 21:44:58 [00] Writing /root/percona-backup/2017-08-10_21-37-17/2017-08-10_21-44-56/xtrabackup_info
 170810 21:44:58 [00] ...done
 xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
 170810 21:44:59 completed OK!

Prepare the SQL Master

On the master server, if it is not already prepared for slaves, you will want to get it ready. You will want to make sure a bin log is configured in my.cnf

nano /etc/my.cnf

Add the following

log_bin = /var/log/mysql/mysql-bin.log

Also set a server-id in /etc/my.cnf

server-id = 1

The server-id can be any number it just needs to be unique on each server in the replication.

You will need to restart MySQL for it to take affect

systemctl restart mysql

You will also want to ensure the firewall is open for the port 3306

firewall-cmd --zone=public --add-service=mysql --permanent
firewall-cmd --zone=public --add-port=3306/tcp --permanent

Then reload the firewall

firewall-cmd --reload

Prepare the SQL Slave

You will want to add a server-id to /etc/my.cnf on the slave

server-id=2

You will also want to open the same port in the firewall as you did the on the master. Since we are copying the entire Xtrabackup you can go ahead and shutdown MySQL on the Slave

systemctl stop mysql

Go ahead and make a backup of /var/lib/mysql incase something goes wrong

cp -R /var/lib/mysql /var/lib/mysql.bak

You will also want to ensure the firewall is open for the port 3306 on the slave as well

firewall-cmd --zone=public --add-service=mysql --permanent
firewall-cmd --zone=public --add-port=3306/tcp --permanent

Then reload the firewall

firewall-cmd --reload

Configure MySQL Replication

Sync the backup from the master to the slave

rsync -vPa /root/percona-backup/2017-08-10_21-37-17 192.168.1.100:/var/lib/mysql

You will want to replace the path with the updated path to your backup and the  192.168.1.100 ip address with  IP address of your slave.

Once the rsync has finished you will want to change the permissions of /var/lib/mysql to be owned by the slave

chown -R mysql. /var/lib/mysql

On the master enter the mysql console:

mysql

Grant permissions for replication to the slave

grant replication slave on *.* to [email protected] identified by 'password';

You will want to change the replicationuser to a desired user name, 192.168.1.100 to the IP address of the slave and password to your desired password.

Then flush privileges on the master

flush privileges;

Start The MySQL Slave

On the slave you will need to gather the needed slave information so

 cat /var/lib/mysql/xtrabackup_binlog_info
mysql-bin.000001 245

This contains the MASTER_LOG_FILE and MASTER_LOG_POS information you will need to enter in MySQL to connect to the master

Connect to MySQL on the slave

mysql -uroot -p

Enter the following information

CHANGE MASTER TO MASTER_HOST='192.168.1.101',
 MASTER_USER='replicationuser',
 MASTER_PASSWORD='password',
 MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS= 245;

MASTER_HOST will be the IP address of the MySQL master.
MASTER_USER will be the replicationuser you set earlier on the master.
MASTER_PASSWORD will be the password you configured earlier on the master.
MASTER_LOG_FILE  and MASTER_LOG_POS will be obtained from the xtrabackup_binlog you viewed earlier

Once you have added that information in MySQL you should see the following

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.101',
 -> MASTER_USER='replicationuser',
 -> MASTER_PASSWORD='password',
 -> MASTER_LOG_FILE='mysql-bin.000001',
 -> MASTER_LOG_POS= 245;
Query OK, 0 rows affected (0.04 sec)

You can go ahead and start slaving now:

start slave;

To check slave status perform type show slave status;

MariaDB [(none)]> show slave status\G;
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.1.101
 Master_User: replicationuser
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 483
 Relay_Log_File: mariadb-relay-bin.000002
 Relay_Log_Pos: 767
 Relay_Master_Log_File: mysql-bin.000001
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB:
 Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
 Skip_Counter: 0
 Exec_Master_Log_Pos: 483
 Relay_Log_Space: 1063
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 0
 Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 1
 1 row in set (0.00 sec)

The specific lines you are looking for to ensure replication is working are

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

That is all that is needed for creating MySQL replication with Perconna Xtrabackup.