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 what is 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | master > show global variables like 'gtid_executed'; +---------------+-------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------+ | gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-13 | +---------------+-------------------------------------------+ master > show global variables like 'gtid_purged'; +---------------+------------------------------------------+ | Variable_name | Value | +---------------+------------------------------------------+ | gtid_purged | 9a511b7b-7059-11e2-9a24-08002762b8af:1-2 | +---------------+------------------------------------------+ |
Now we take a backup with mysqldump from the master:
1 | # mysqldump --all-databases --single-transaction --triggers --routines --host=127.0.0.1 --port=18675 --user=msandbox --password=msandbox > dump.sql |
It will contain the following line:
1 2 | # grep PURGED dump.sql SET @@GLOBAL.GTID_PURGED='9a511b7b-7059-11e2-9a24-08002762b8af:1-13'; |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | slave1 > show global variables like 'gtid_executed'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_executed | | +---------------+-------+ slave1 > show global variables like 'gtid_purged'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_purged | | +---------------+-------+ slave1 > slave1> source test.sql; [...] slave1 > show global variables like 'gtid_executed'; +---------------+-------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------+ | gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-13 | +---------------+-------------------------------------------+ slave1 > show global variables like 'gtid_purged'; +---------------+-------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------+ | gtid_purged | 9a511b7b-7059-11e2-9a24-08002762b8af:1-13 | +---------------+-------------------------------------------+ |
The last step is to configure the slave using the auto-configuration method of GTID:
1 | slave1 > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_USER="msandbox", MASTER_PASSWORD="msandbox", MASTER_PORT=18675, MASTER_AUTO_POSITION = 1; |
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:
1 2 3 | Slave_IO_Running: No Slave_SQL_Running: Yes Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.' |
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:
1 2 3 4 5 6 | master > show global variables like 'GTID_EXECUTED'; +---------------+-------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------+ | gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-14 | +---------------+-------------------------------------------+ |
And we set it on the slave:
1 2 | slave> set global GTID_EXECUTED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14" ERROR 1238 (HY000): Variable 'gtid_executed' is a read only variable |
Error! Remember, we get the GTID_EXECUTED from the master and set is as GTID_PURGED on the slave.
1 2 | slave1 > set global GTID_PURGED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14"; ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty. |
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):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | slave1> reset master; slave1 > show global variables like 'GTID_EXECUTED'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_executed | | +---------------+-------+ slave1 > set global GTID_PURGED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14"; slave1> start slave io_thread; slave1> show slave status\G [...] Slave_IO_Running: Yes Slave_SQL_Running: Yes [...] |
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:
1 2 3 4 | slave1 [localhost] {msandbox} ((none)) > source test.sql; [...] ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty. [...] |
Wop! It is important to mention that these kind of error messages can dissapear on the shell buffer because the restore of the dump will continue. Be cautious.
Same problem again so same solution too:
1 2 3 4 5 6 7 8 | slave1> reset master; slave1> source test.sql; slave1> start slave; slave1> show slave status\G [...] Slave_IO_Running: Yes Slave_SQL_Running: Yes [...] |
Conclusion
With the new GTID we need to change our minds. Now binary log and position is not something we need to take in account, gtid_executed and gtid_purged are our new friends. Xtrabackup still doesn’t support it but we are working on it. I will update this post and create a one when we publish a xtrabackup version with full support of GTID.
About Miguel Angel Nieto
Hi,
I thought the whole idea was for slaves *not* needing to know the GTID in advance, but rather negotiate it with the server.
For example, I set a replication setup via mysqlsandbox, had some replicating statements going, switched to gtid, had some further statements going around; stopped slave, RESET SLAVE, reconnected slave via CHANGE MASTER TO, but only with credentials (host, port, user, password), followed by START SLAVE. So it found out the gtid by itself and resumed from the right place.
So, again, when is a need to pass around the GTID_PURGED from master to slave?
As I see, to do replicas, percona xtrabackup still be better than this when we’re talking about large databases.
Shlomi, yes, if everything goes as expected then you don’t need to play with GTID_PURGED or GTID_EXECUTED. That’s the reason I forced a problem in my blog post:
“Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.’”
That can happen often if you purge binary logs from the master and then some manual process and dealing with GTID_PURGED is needed. Usually tools like mysqldump do this for you, but as you can see in the last example, it can also fail.
“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.”
Is the performance impact any different then using log_slave_updates as it currently stands in 5.5?
Marc, no, the same impact. The only difference is that with GTID log_slave_updates it’s mandatory.
Also, any idea how this process would work using xtrabackup? I’m assuming there would be an option to dump the GTID_PURGED or GTID_EXECUTED into a file?
marc, that’s the idea. Create a file in the backup with the content of GTID_EXECUTED. But it is still something we have to decide and test.