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:
1 | # innobackupex /destination/ |
In the destination folder there will be a file with the name xtrabackup_binlog_info:
1 2 | # 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:
1 | 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:
1 | # 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:
1 2 | 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; |
In case your source has applied event from different servers, your GTID executed will be compounded by a list of gtid sets separated by comma: [SOURCE_ID1]:[ID1],[SOURCE_ID2]:[ID2] . In this case you need to specify all of them on the above step.
5- Check the replication status:
1 2 3 4 5 6 7 | slave1 > show slave statusG [...] 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.
Can’t wait til the production ready version of 5.6 comes out just for the GTIDs.
On an unrelated note.. anyone notice that the code blocks don’t work in Chrome? Works fine in IE.
Hi Marc,
In my case it works in Chrome but not in Safari. We will check it, thank you.
Miguel, –
I think code blocks not working is an AdBlock (and alike) issue so if you disable it, should work with any browser, but when AdBlock is on, they do not work.
Hi Miguel,
would it be possible to stream the backup to the slave and apply the logfiles there? The first part works but I’m not sure about the “apply-log”-part.
Regards,
Jens
Jens:
better late than never 🙂
you can run –apply-log on the slave as innobackupex has nothing to do with the original redo-logs in this phase as it saves the changes in transaction logs to “xtrabackup_logfile” during the first run. In fact innobackupex with –apply-log calls the xtrabackup two times: first time to perform a crash recovery(using “xtrabackup_logfile”) and for the second time to generate the new translogs.
cheers,
Miklos
Don’t forget to run “reset master;”
in case you get “ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.” error