Repair MySQL 5.6 GTID replication by injecting empty transactions

In the previous article I mentioned two kinds of how to repair Mysql 5.6 GTID Master-slave database.
I did not mention the well-known approach-' GLOBAL Sql_slave_skip_counter = n '. The reason is simple, if you are using Mysqlgtid, it is not working.
So here's the question:


Is there a simple way to skip this single transaction?
Yes! Injects an empty transaction. Let's imagine that replication from the server does not work because of the following error:

Last_sql_error:error ' Duplicate entry ' 4 ' for key ' PRIMARY "on query. Default database: ' Test '. Query: ' INSERT INTO T VALUES (NULL, ' Salazar ') ' Retrieved_gtid_set:7d72f9b4-8577-11e2-a3d7-080027635ef5:1-5executed_ Gtid_set:7d72f9b4-8577-11e2-a3d7-080027635ef5:1-4

There are different ways to find the failed transaction. You can check the binary logs, or you can also check retrieved_gtid_set and executed_gtid_set from the display output examples we can see. This retrieves 1 to 5 transactions from the server, but only 1 to 4 are executed. This means that trading 5 is a problem that is causing the problem.

Because Sql_slave_skip_counter does not work in Gtid, we need to find a way to ignore the business. We can create an empty transaction in Gtid to skip it.

STOP SLAVE; SET gtid_next= "7d72f9b4-8577-11e2-a3d7-080027635ef5:5"; BEGIN; COMMIT; SET gtid_next= "AUTOMATIC"; START SLAVE; [...] Retrieved_gtid_set:7d72f9b4-8577-11e2-a3d7-080027635ef5:1-5executed_gtid_set: 7d72f9b4-8577-11e2-a3d7-080027635ef5:1-5

START SLAVE after checking that transaction 5 is already in its own binaries, which means it has been executed.

This is an easy way to skip some transactions, but you should expect data inconsistencies between the master and slave servers. Pt-table-checksum can help you here, it can be found in Percona Toolkit for MySQL.

Last week I talked a lot about Gtid's stuff in Toronto Percona Mysql University. It includes a working overview of MySQL 5.6 gtid This new feature that can help people. This is a slide from the conference. I hope you find it useful: MySQL 5.6 GTID In a nutshell

Original: https://www.percona.com/blog/2013/03/26/repair-mysql-5-6-gtid-replication-by-injecting-empty-transactions/

This article is from the "Jeffrey blog" blog, please be sure to keep this source http://oceanszf.blog.51cto.com/6268931/1718689

Repair MySQL 5.6 GTID replication by injecting empty transactions