Performance Zone is brought to you in partnership with:

I specialise MySQL Server performance as well as in performance of application stacks using MySQL, especially LAMP. Web sites handling millions of visitors a day dealing with terabytes of data and hundreds of servers is king of applications I love the most. Peter is a DZone MVB and is not an employee of DZone and has posted 240 posts at DZone. You can read more from them at their website. View Full User Profile

How to create a new (or repair a broken) GTID based slave with Percona XtraBackup

05.10.2013
| 2104 views |
  • submit to reddit

This post comes from  at the MySQL Performance Blog.

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.




Published at DZone with permission of Peter Zaitsev, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)