3

MySQL 5.6.35-log (community)

How can I remove a incorrectly manually injected GTID from a GTID set without doing a RESET MASTER?

STOP SLAVE;
SHOW MASTER STATUS;

278bfda1-b93d-11e4-801b-14feb5d284bc:1-129116182,
69cf02cd-1731-11e3-9a19-002590854928:1-649285403:1009231661,
708bb615-d393-11e3-a682-003048c3ab22:1-1009669227,
819c985c-d384-11e3-a621-00259002979a:1-234906555,
9204e764-d379-11e3-a5d9-0013726268ea:1-360176454,
c32252c2-1ce5-11e6-8f4b-c80aa91f9ec4:1

We need to remove GTID 1009231661 from this set:

69cf02cd-1731-11e3-9a19-002590854928:1-649285403:1009231661,

Does anyone know where/how the GTID sets are stored? I read in the 5.7 documentation that the GTIDs are stored in table. But where are they stored in 5.6? I would like to shutdown the server, edit the file, remove the bad GTID, and then restart so the server can pickup and continue.

Improve this question

4 Answers 4

Reset to default
4

For those who find this later in life..

This was a catastrophic mistake. There is no way to fix this problem. A RESET MASTER / CHANGE MASTER TO must be performed farm wide. Why is it so catastrophic? Because, in a Multi-Master circular replication, ALL the MySQL servers need to have the RESET MASTER / CHANGE MASTER TO performed.

In MySQL 5.6, the GTID sets are stored in the master BINLOG files - hence why the RESET MASTER must be done. However, when you do a RESET MASTER the command also resets the servers last used GTID to 0. Now the slaves have GTID's larger than the master server. The slave will skip transactions until the GTID exceeds the slaves GTID_EXECUTED_SET value for the master server. That is why you have to perform RESET MASTER / CHANGE MASTER TO on all the slave servers too.

After performing the RESET MASTER / CHANGE MASTER TO we should only need to dump/restore the databases that were being updated on the affected master server (aka master databases) so that they slaves can be in sync.

Improve this answer
2

I think the correct way to solve this is to run the slave just before the bad GTID is executed. START SLAVE supports an UNTIL clause. Read more about it here.

Then you can insert an empty transaction for the bad one to skip it.

STOP SLAVE;
SET GTID_NEXT="GTID_you_want_to_skip";
BEGIN; COMMIT;
#now resume just as normal:
SET GTID_NEXT="AUTOMATIC";
START SLAVE;
Improve this answer
1
  • This is what got us into trouble. Except the wrong Master Server UUID was specified. Look at the gap: 69cf02cd-1731-11e3-9a19-002590854928:1-649285403:1009231661, It's huge. Meanwhile, the server is running and has written transactions AFTER 649285403 but are before 1009231661. We can't let the slave skip those transactions.
    – Van
    Commented Apr 3, 2017 at 14:36
1

When you backup a database with GTID enabled in mysqldump. You can see that at the begining of the file, it sets the gtid from the master with the following command:

SET @@global.gtid_purged='GTID_SET';

I tried it on my replication environment and it worked.

Improve this answer
0

It's a pity that you cannot delete gtid directly. In mysql-5.6 version, you can find the system variable gtid_executed which show the executed gtid sets. But you can not modify it directly because it is not a real variable, it just shows some content of global variable 'gtid_state' which is a C code.

The only solution is modify the binlog which contain the gtid 1009231661 if you know the protocol of binlog.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.