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:

Master:
10.10.10.1
Slave:
10.10.10.2

On Master:

 

 

On Slave:

 

On Master:

Make sure scp is enabled between the servers.

On Slave:

 

 

  • 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.