September 16, 2014

Repair MySQL 5.6 GTID replication by injecting empty transactions

In a previous post I explained how to repair MySQL 5.6 GTID replication using two different methods. I didn’t mention the famous SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n for a simple reason, it doesn’t work anymore if you are using MySQL GTID. Then the question is:

Is there any easy way to skip a single transaction?

There is! Injecting empty transactions. Let’s imagine that the replication in slave server is not working because of an error:

There are different ways to find the failed transaction. You can examine the binary logs or you can also check Retrieved_Gtid_Set and Executed_Gtid_Set from the SHOW SLAVE OUTPUT as we can see in the example. This slave server has retrieved transactions 1 to 5 but has only executed 1 to 4. That means that transaction 5 is the one that is causing the problems.

Since SQL_SLAVE_SKIP_COUNTER doesn’t work with GTID we need to find a way to ignore that transaction. The way to do it is creating a new empty transaction with it the GTID we want to skip.

After the START SLAVE the slave checks that transaction 5 is already in its own binary log and that means that it has been executed.

This is an easy way to skip some transactions but take in account that by doing this you will end up with data inconsistencies between Master and Slave servers. pt-table-checksum can help you here, which is found in Percona Toolkit for MySQL.

Last week I gave a talk at Percona MySQL University @Toronto about GTID. It includes an overview of MySQL 5.6 GTID that can help people to start working with this new feature in MySQL 5.6. Here are the slides from that session. I hope you find it useful: MySQL 5.6 GTID in a nutshell

About Miguel Angel Nieto

Miguel joined Percona in October 2011. He has worked as a System Administrator for a Free Software consultant and in the supporting area of the biggest hosting company in Spain. His current focus is improving MySQL and helping the community of Free Software to grow. Miguel's roles inside Percona are Senior Support Engineer and Manager of EMEA Support Team.

Comments

  1. Steve Jackson says:

    Maybe wrong place to post this, but as its MySQL 5.6 related, I thought I would bring up the topic (for lack of a better place to do it)

    What is going on with the mysql 5.5 / 5.6 changelogs on MySQL.com. Anyone else noticed how extremely quiet they have been since 5.6 went GA? I usually look at them every week… and I have seen no changes for weeks and weeks.

    So either Oracle released what they believe to be an almost bug-free MySQL release… or they are taking bug fixing / development behind closed doors

    I believe it deserves some kind of blog post on Percona asking what’s going on, or answering it [if anyone at Percona knows someone at Oracle who knows]

  2. Lalit says:

    I stopped slave and while executing SET GTID_NEXT=”0c7b6d40-e55d-11e2-952b-00221950c39:15114″; on slave getting error Error Code: 1231. Variable ‘gtid_next’ can’t be set to the value of ’0c7b6d40-e55d-11e2-952b-00221950c39:15114′

  3. Lalit says:

    sorry is ‘t my mistake it’s working fine , thanks for the post

  4. Van says:

    What this article does not cover is a multi-master environment – such as a circular replication topology. In that case, you need to know which servers transaction (GTID) to skip. This does explain how to accomplish this. Please update the article.

  5. egezon says:

    I tried to do this, but I got an error.
    I did,
    STOP SLAVE;
    SET GTID_NEXT= “8b61c055-e539d-22e3-8894-00163e01174b:962″
    BEGIN; COMMIT;
    SET GTID_NEXT=”AUTOMATIC”;
    START SLAVE;

    I got the same error in SHOW SLAVE STATUS. Now the value is set to
    Executed_Gtid_Set:
    8b61c045-e29d-11e3-8894-00163e01174b:2-17:962

    What does “2-17:962″ means? All i wanted is to skip everything and start from transaction number 962.

    Thank you

  6. Van says:

    The “2-17:962″ is says that GTIDs 2 through 17 and 962 have been executed. That is important – EXECUTED. That means it was applied to the database If you have an error its probably for GTID 963 (presumably the next one in sequence). However, you need to view the error and you may need to use the mysqlbinlog on the slave-bin-xxxx.log file to see what the next transaction is.

Speak Your Mind

*