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:
There is! Injecting empty transactions. Let’s imagine that the replication in slave server is not working because of an error:
Last_SQL_Error: Error 'Duplicate entry '4' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into t VALUES(NULL,'salazar')'
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
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.