Backup Streaming using Percona Xtrabackup from Master to Slave for MySQL 5.6

Backup using xtrabackup does not lock the database, which can be performed online with very minimal disruption to the production database. The database server should be operating like normal, with a bit higher load due to data copying/streaming/compressing which depending on the backup command and database size.

We can use xtrabackup to re-sync our replication slave without downtime to the master by using the backup streaming feature. The backup is taken on the master and will be streamed to the slave server on-the-fly, suitable for a huge database.

Consider the following MySQL replication topology:

• Master: 192.168.10.101
• Slave1: 192.168.10.102

1) Make sure the necessary packages are installed. We need socat, pv and Percona Xtrabackup itself:

yum install -y epel-release
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y socat pv percona-xtrabackup-22

For MySQL 5.6, Percona Xtrabackup 2.2 should be installed on the backup host. Other versions might work, but this version is specifically built for 5.6.

2) Create a database user for backup purpose on the master (only if it doesn’t exist):

CREATE USER backup@localhost IDENTIFIED BY 'Kt78nb%SGvC3w';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON . TO backup@localhost;

3) On the destination server, for example slave1, 192.168.10.102, stop the MySQL service and wipe out the /var/lib/mysql (don’t worry, we are going to sync the content from the master):

systemctl stop mysql

4) On the destination server, for example slave1, run socat to listen on port 9999 and redirect the output to MySQL datadir, /var/lib/mysql (the date command at the beginning just to print out the current time when this is about to start so we can measure/estimate the timing):

date; socat -u tcp-listen:9999,reuseaddr stdout 2>/tmp/socat.log | xbstream -x -C /var/lib/mysql

5) On the source server (the host that you want to backup a.k.a the master), start Percona Xtrabackup using innobackupex command, point the the local MySQL datadir and send the data stream to slave1, 192.168.10.102 (slave1 is already listening to port 9999 as shown in step #4):

ulimit -n 256000 && /usr/bin/innobackupex --user backup --password 'Kt78nb%SGvC3w' --socket=/var/lib/mysql/mysql.sock --stream=xbstream /var/lib/mysql/ | socat - TCP4:192.168.10.102:9999

** You shall see some output. Make sure you see ‘Completed OK’ in the last line. That’s the indicator the backup is completed successfully.

If throttling is necessary due to limited bandwidth, pipe the output to pv right before socat with a rate limit option. The following command will limit the streaming bandwidth to 80MB (inbound) + 80MB (outbound), where the total bandwidth is going to be around 160MB/s:

ulimit -n 256000 && /usr/bin/innobackupex --user backup --password 'Kt78nb%SGvC3w' --slave-info --socket=/var/lib/mysql/mysql.sock --stream=xbstream /var/lib/mysql/ | pv -q -L 80m | socat - TCP4:192.168.10.102:9999

** You shall see some output. Make sure you see ‘Completed OK’ in the last line. That’s the indicator the backup is completed successfully.

6) Now the inconsistent backup streamed from the source server is located under /var/lib/mysql on the destination server (in this case is slave1). We need to make it consistent again by using –apply-log flag (this is also known as prepare backup). On the destination server, slave1, run:

cd /var/lib/mysql
innobackupex --apply-log .

** You shall see some output. Make sure you see ‘Completed OK’ in the last line. That’s the indicator the backup preparation is completed successfully.

7) Apply correct ownership:

chown -Rf mysql:mysql /var/lib/mysql

8) Start MySQL server on the destination server:

systemctl start MySQL

9) At this point, MySQL is started on slave1 but it’s not replicating from the master yet. We need to establish the replication link to the master to start the replication. In order to start the replication, we need to have a starting point in the format of binlog file and position. Get the binlog file name and position from xtrabackup_binlog_info file inside the datadir, /var/lib/mysql:

cat /var/lib/mysql/xtrabackup_binlog_info
binlog.000735 745386144

10) We can now use the binary log file and position to point to the correct master:

CHANGE MASTER TO MASTER_HOST = '192.168.10.101', MASTER_USER = 'replication', MASTER_PASSWORD='M7ius98^s$Nw', MASTER_LOG_FILE = 'binlog.000735', MASTER_LOG_POS = 745386144;

11) Start replication slave:

START SLAVE;

12) Check replication slave status:

SHOW SLAVE STATUS\G

** Ensure you see “Slave_IO_running: Yes” and “Slave_SQL_running: Yes“. The slave might be lagging behind, please also monitor “Seconds_Behind_Master“.

13) Enable read-only on the slave:

SET GLOBAL read_only = ON;