To avoid infinite replication loops MySQL doesn’t allow you to have
<a href="https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_log_slave_updates">log_slave_updates</a> and
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:
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
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_uuidto avoid any split-brain situation.