How to use Xtrabackup to rebuild Slave from Master in MySQL

In this article we will learn about the Percona’s Xtrabackup to rebuild slave from master in minimum time and without locking out the tables.


What is Percona XtraBackup:

Percona XtraBackup is a free, open source, complete online backup solution for all versions of Percona Server for MySQL, MySQL ® and MariaDB®. With over 2,100,000 downloads, Percona XtraBackup performs online non-blocking, tightly compressed, highly secure backups on transactional systems so that applications remain fully available during planned maintenance windows.


Advantages of Percona XtraBackup over MySQLdump:

  • Fast and reliable backups
  • Uninterrupted transaction processing during backups
  • Savings on disk space and network bandwidth with better compression
  • Automatic backup verification
  • Higher uptime due to faster restore time


XtraBackup is mostly used while we copy Master data to Slave, while rebuilding Slave server we can use it very effectively without any downtime on master.

Using XtraBackup to rebuild Slave from Master MySQL:


On Master:


Create a directory to store the backup
mkdir /datadir/xtra
Take the backup
innobackupex --user=root --password=pass /datadir/xtra
* It will put the files in a timestamped directory like:
innobackupex --user=root --password=pass --stream=tar ./ | gzip - > /datadir/xtra.tar.gz --> to take backup as compressed tar.gz file.


On Slave:

create desitination directory on slave
mkdir /datadir/xtra


On Master:

Move the data to the slave:
scp -r /datadir/xtra/2014-08-26_14-42-33/*

Make sure scp is enabled between the servers.

On Slave:

Apply the logs:
innobackupex --user=backup --password=pass --use-memory=2G --apply-log /datadir/xtra/
Check Permissions:
chown mysql:mysql /datadir
Stop mysql
/etc/init.d/mysql stop
Rename directories
mv /datadir/mysql /datadir/mysql.bak
mv /datadir/xtra /datadir/mysql
innodb files
remove files from /mysql_index
rm -rf /mysql_index/ib*
mv /datadir/mysql/ib* /mysql_index
Start mysql
/etc/init.d/mysql start

Get GTID from xtrabackup_binlog_info:
mysql-bin.001871 58835583 50976568-7d33-11e3-b344-00163e002565:1-689091

Login to mysql:
reset master;
reset slave;
SET GLOBAL gtid_purged="50976568-7d33-11e3-b344-00163e002565:1-689091";

Change to master:


start slave;

show slave status\G


  • innobackupex records the point in the innodb log where it started.
  • It then copies all of the logs to a file. At the same time it does a raw copy of all the data files in the datadir.
  • You need to apply the logs, either before or after you move the logs.
  • After you have applied the logs, put the files in the right place.
  • Once all files are in place and have the correct permissions.
  • Start mysql, reset master and slave, point GTID to the correct place, change master, and start slave.
  • The slave should now be insync and catching up to the master.

That’s all for XtraBackup, please comment for any suggestion or concerns.


Leave a Comment