MySQL GTID: restore a master from a replica’s backup

To avoid infinite replication loops MySQL doesn’t allow you to have log_slave_updates and replicate-same-server-id.

When using GTIDs that may lead to something not expected that you may not be aware of.

In this scenario, we have 2 MySQL servers using GTID. The sever uuid part of the GTID has been modified in the illustration to make it more clear. Both servers have log_slave_updates enabled too:

So far nothing unusual. So let’s write data on the master (MySQL A):

We can see that this first transaction is identified by its GTID where the uuid matches MySQL A and the sequence number is 1. Let’s write some data again:

All good.

Now let’s take a backup on the replica (MySQL B):

Backup is consistent and matches the data on both servers.

Now let’s write again some data:

Of course the backup that was taken earlier does not change.

All suddenly, MySQL A crashes and goes away… We promote MySQL B as new writer and we use it to write again some data:

We can notice that the GTID changed to use the uuid of MySQL B. (This information is contained in the variable gtid_executed).

It’s time to restore our backup on MySQL A:

And we configure MySQL A to become replica of MySQL B:

Wow ! All the transactions happened after the backup on MySQL A have been ignored !

In fact, this is again to protect our user to have problems like circular replication and infinite loop.

To be able to replicate the missing transaction is to change the server_id to a new unique value after the restore and before starting replication. Then replication will work as expected:

In summary, if you have enabled log_slave_updates and you want to recreate a master from a backup taken on a replica, you must change the server_id. Even if you use GTIDs, server_id is for the moment still to be taken in consideration.

If you plan to write back on MySQL A, it’s always safer to also change its server_uuid to avoid any split-brain situation.