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:
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: /datadir/xtra/2014-08-26_14-42-33 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/* 10.10.10.2:/datadir/xtra
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: CHANGE MASTER TO MASTER_HOST='10.10.10.1', MASTER_USER='repl', MASTER_PASSWORD='pass', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_CONNECT_RETRY=10; 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.