Percona XtraBackup 2.0.7 has been published with support for GTID based replication. As promised, here is the step-by-step guide on how to create a new GTID based slave (or repair a broken one) using XtraBackup. The process is pretty straightforward.
1- Take a backup from any server on the replication environment, master or slave:
# innobackupex /destination/
In the destination folder there will be a file with the name xtrabackup_binlog_info:
# cat xtrabackup_binlog_info mysql-bin.000002 1232 c777888a-b6df-11e2-a604-080027635ef5:1-4
Now it contains both, binary log coordinates and GTID information.
That information is also printed by innobackupex after backup is taken:
innobackupex: MySQL binlog position: filename 'mysql-bin.000002', position 1232, gtid_executed c777888a-b6df-11e2-a604-080027635ef5:1-4
2- Apply the logs to the backup:
# innobackupex --apply-log /destination/2013-05-07_08-33-33/
3- Move the backup to the destination server and put the content on the mysql’s datadir. Follow the usual restore procedure, for example remember to change the permissions to mysql:mysql.
4- Start the new slave from that GTID position:
slave1 > SET GLOBAL gtid_purged="c777888a-b6df-11e2-a604-080027635ef5:1-4"; slave1 > CHANGE MASTER TO MASTER_HOST="10.0.1.1", master_user="msandbox", master_password="msandbox", MASTER_AUTO_POSITION = 1;
5- Check the replication status:
slave1 > show slave status\G
[...]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[...]
Retrieved_Gtid_Set: c777888a-b6df-11e2-a604-080027635ef5:5
Executed_Gtid_Set: c777888a-b6df-11e2-a604-080027635ef5:1-5
We can see that the slave has retrieved a new transaction with number 5, so transactions from 1 to 5 are already on this slave.
That’s all, we have created a new slave in our GTID based replication environment.
The post How to create a new (or repair a broken) GTID based slave with Percona XtraBackup appeared first on MySQL Performance Blog.