How to create/restore a slave using GTID replication in MySQL 5.6

MySQL 5.6 is GA! Now we have new things to play with and in my personal opinion, the most interesting one is the new Global Transaction ID (GTID) support in replication. This post is not an explanation of MySQL replication find query with GTID and how it works internally because there are many documents about that:

http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-concepts.html

One thing that worths to mention is that if you want GTID support log_slave_updates will need to be enabled in slave server and the performance impact should be taken in account.

Anyway, this post tends to be more practical, we will see how to create/restore new slaves from a master using GTID.

How to set up a new slave

The first thing that we need to know is that now Binary Logs and Position are not needed anymore with GTID enabled. Instead, we need to know in which GTID is the master and set it on the slave. MySQL keeps two global variables with GTID numbers on it:

gtid_executed: it contains a representation of the set of all transaction logged in the binary log
gtid_purged: it contains a representation of the set of all transactions deleted from the binary log

So now, the process is the following:

• take a backup from the master and store the value of gtid_executed

• restore the backup on the slave and set gtid_purged with the value of gtid_executed from the master

The new mysqldump can do those tasks for us. Let’s see an example of how to take a backup from the master and restore it on the slave to set up a new replication server.

Now we take a backup with mysqldump from the master:

It will contain the following line:

Therefore during the dump recover process on the slave it will set GTID_PURGED to the GTID_EXECUTED value from the master.

So now, we just need to recover the dump and start the replication:

The last step is to configure the slave using the auto-configuration method of GTID:

How to restore a slave in a bad and fast way

Let’s imagine that our slave has been down for several days and the binary logs from the master have been purged. This is the error we are going to get:

So, let’s try to solve it. First, we have the bad and fast way, that is, point to another GTID that the master has in the binary logs. First, we get the GTID_EXECUTED from the master:

And we set it on the slave:

Error! Remember, we get the GTID_EXECUTED from the master and set is as GTID_PURGED on the slave.

Error again, GTID_EXECUTED should be empty before changing GTID_PURGED manually but we can’t change it with SET because is a read-only variable. The only way to change it is with reset master (yes, on a slave server):

Now, if you don’t get any error like primary/unique key duplication then you can run the pt-table-checksum and pt-table-sync.

How to restore a slave in a good and slow way

The good way is mysqldump again. We take a dump from the master like we saw before and try to restore it on the slave:

Wop! It is important to mention that these kinds of error messages can disappear on the shell buffer because the restore of the dump will continue. Be cautious.

Same problem again so same solution too:

Conclusion

With the new GTID we need to change our minds. Now binary log and position is not something we need to take into account, gtid_executed and gtid_purged are our new friends. Newer versions of Xtrabackup have the full support of GTID. You can check the following blog post:

How to Create a New (or Repair a Broken) GTID-Based Slave with Percona XtraBackup