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:
1 2 3 | 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-5 Executed_Gtid_Set: 7d72f9b4-8577-11e2-a3d7-080027635ef5:1-4 |
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.
1 2 3 4 5 6 7 8 | 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-5 Executed_Gtid_Set: 7d72f9b4-8577-11e2-a3d7-080027635ef5:1-5 |
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.
MySQL 5.6 GTID
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
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]
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’
sorry is ‘t my mistake it’s working fine , thanks for the post
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.
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
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.
i meet a problem..
“ERROR 1231 (42000): Variable ‘gtid_next’ can’t be set to the value of ‘xxxx’ ”
—
” set gtid_next=’xxx’ ” not work ? why ??
Hi
I am facing the same problem as like Lithium. No errors and no issues in sql and io status. But retrieved_gtid_set is empty on slave.
Intentionally i have stopped replication and restored dump seperately on both the servers. Then i issued reset master and reset slave. Now starting replication its started but thr transaction id from master to slave is not updating. please help to solve this
Hi,
what happen when I have multiple entries for Executed_Gtid_Set?
Retrieved_Gtid_Set: e1493393-7c7b-11e4-b3ee-001a4a010192:1186360318-1186905836
Executed_Gtid_Set: 370048ef-f18c-11e7-bf48-fa163e31bd7a:1-69,
b4878526-f208-11e7-8274-fa163e31bd7a:1-67,
e1493393-7c7b-11e4-b3ee-001a4a010192:1-1186761158
Auto_Position: 0
Hi Fernando,
you have to find out the “Master_UUID” in “SHOW SLAVE STATUS”.
In your case it is probably: e1493393-7c7b-11e4-b3ee-001a4a010192
Greetings
Andre
Hi,
what GTID_NEXT value should I take in this case?
Executed_Gtid_Set: 07c6e70b-52b4-11e9-9332-1402ec87c47c:1-801368897:801956080-814877202
Greetings
Andre
This is an easy way to skip some transactions but take in account that
There is! Injecting empty transactions. Let’s imagine that the replication in slave server