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:

https://www.percona.com/blog/2013/05/09/how-to-create-a-new-or-repair-a-broken-gtid-based-slave-with-percona-xtrabackup/

Share this post

Comments (21)

  • Shlomi Noach Reply

    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?

    February 8, 2013 at 2:08 pm
  • Fernando Reply

    As I see, to do replicas, percona xtrabackup still be better than this when we’re talking about large databases.

    February 8, 2013 at 2:22 pm
  • Miguel Angel Nieto Reply

    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.

    February 8, 2013 at 4:04 pm
  • marc castrovinci Reply

    “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?

    February 11, 2013 at 12:00 pm
  • Miguel Angel Nieto Reply

    Marc, no, the same impact. The only difference is that with GTID log_slave_updates it’s mandatory.

    February 11, 2013 at 12:01 pm
  • marc castrovinci Reply

    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?

    February 11, 2013 at 12:03 pm
  • Miguel Angel Nieto Reply

    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.

    February 11, 2013 at 12:04 pm
  • roger samuel Reply

    Is there any way to just reset the replication for 1 specific table that has LOTS of skipped/bad transactions?

    January 2, 2014 at 11:51 am
  • Lalit Reply

    mysql 5.6 GTID replication error

    I recently upgraded from mysql 5.5 to 5.6.15 and enabled GTID based replication, now problem is for some reasons we restarted our slave and after that replication break.

    I Checked SHOW SLAVE STATUS error is “‘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.”

    i am suspecting it happens because of master old binary log (7 days before) deleted form master as we are using expire_logs_days=7 and slave still looking for this old binary log for some GTID reference to replicate with master.
    I tried following link solution ,
    http://www.mysqlperformanceblog.com/…-in-mysql-5-6/
    but it still not working.
    Note :using mysql 5.6.15

    Now only option for me to re-create slave by taking mysqldump from master.

    Is there any other option if we can start replication without recreating new slave.

    Thanks in Advance

    January 10, 2014 at 6:32 am
  • shahid Reply

    Hi ,
    I m getting one error and unable to trace it.
    In my master server i have disabled master-verify-checksum but still show binlog event not ignoring it..
    mysql> show global variables like ‘%check%’;
    +—————————+——–+
    | Variable_name | Value |
    +—————————+——–+
    | binlog_checksum | NONE |
    | foreign_key_checks | ON |
    | innodb_checksum_algorithm | innodb |
    | innodb_checksums | ON |
    | innodb_file_format_check | ON |
    | master_verify_checksum | OFF |
    | slave_checkpoint_group | 512 |
    | slave_checkpoint_period | 300 |
    | slave_sql_verify_checksum | OFF |
    | unique_checks | ON |
    mysql> show binlog events in ‘mysql225-bin.000002’;
    ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error

    March 18, 2014 at 9:48 am
  • Javier Bautista Reply

    Hello

    First of all thank you for your post. It is very useful. I have a mysql cluster with gtid replication and my problem is when slave lose sync, automatically it becomes as a master server and this is a problem for us because we have a load balancer that redirect querys based on server’s role and we can have two masters at same time which it is a problem. Is there any way to make that slave server loses sync it does not change the role and will be the same?

    Thank you in advance

    November 5, 2014 at 2:03 pm
  • Evgeny Reply

    it is not worked, if i use galera cluster with 3 nodes. if node 1 is down, and i like to reconnect slive to another “live” node – is now work. say “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.”

    December 23, 2014 at 9:28 am
    • perlmonk Reply

      Find binlog files mtime on new master near your slave error time.

      Query slave “show global variables like ‘gtid_%’;” find gtid_purged.

      May be: gtid_purged=’uuix:x-y’;

      Now using “mysqlbinlog binlog.xxxxxx | less” on master search “SET @@SESSION.GTID_NEXT” to determinate which binlog file contains last executed statement.

      Using “mysqlbinlog” again to find the correct position of gtid “y”;

      You will find some thing like this(GTID_NEXT=gtid “y” + 1):

      # at 252784267
      #170907 14:59:12 server id 1 end_log_pos 252784332 CRC32 0x4e20432d GTID last_committed=53650
      2 sequence_number=536503
      SET @@SESSION.GTID_NEXT= ’78cc0825-1359-11e7-ad91-5254184ea8bf:
      4447144901’/*!*/;

      Now: you get binlog.xxxx and position 252784267;

      Setup slave by

      CHANGE MASTER TO
      MASTER_LOG_FILE=’binlog.xxxx’, MASTER_LOG_POS=252784832,
      MASTER_AUTO_POSITION = 0;
      start slave

      Enjoy!

      September 8, 2017 at 12:12 pm
  • Clement N Reply

    Great illustration of this scenario. Thanks for sharing…

    September 18, 2015 at 3:17 am
  • Zafar Reply

    It is really helpful…just to remove confusion change test.sql to dump.sql as in this article backup name is dump.sql.

    March 17, 2016 at 5:39 am
  • Vladlen Reply

    There are 3 servers, all included GTID. A is the single server, server B is the master server to C.
    We need to start replication between the server A and B. In this case, the need to perform reset master command on server B. Is it not break replication between servers B and C?

    June 14, 2016 at 7:45 am
  • Chieh lin Reply

    Thank you for sharing !! It’s really help.

    March 20, 2017 at 5:25 am
  • Karthik Appigatla Reply

    If the slave has no binary logging enabled, is there anyway for fix apart from enabling binary logging and restarting the slave?

    mysql> SET @@GLOBAL.GTID_PURGED=’4ef99cbb-3db7-11e6-a692-42010af00005:1-1536′;
    ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

    mysql> reset master;
    ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER

    mysql> show binary logs;
    ERROR 1381 (HY000): You are not using binary logging

    October 5, 2017 at 9:35 am
    • JK Reply

      @Karthik Appigatla : 1.STOP SLAVE 2.RESET SLAVE and check the value

      October 31, 2017 at 1:13 pm
  • Sylvie Reply

    Thanks for your article. However I’ve got an issue as soon as I want to restart MySQL slave instance. GTID position is lost and replication tries to start to a GTID older than the GTID_PURGED and so tries to reexecute statements imported from mysqldump. Any idea ?

    November 27, 2018 at 5:52 am
    • Lorraine Pocklington, Community Manager Reply

      Hello Sylvie thanks for your question. Would you be able to raise this on the Percona Forum? It’s more likely to get someone’s attention there as this is an old post. Please list your versions of software, OS etc plus provide config files and error logs if you have them. … https://www.percona.com/forums

      November 27, 2018 at 8:52 am

Leave a Reply