Does GTID have only benefits in MySQL over "standard" Replication?

Question

I'm used to setup MySQL replication in the old-fashioned way and notice now there is a method using GTID. From what I read, GTID is presented has an improvement over "standard" replication.

But I'm sceptical because if GTID is so good I'm surprised this is not the default behaviour, so I'm curious if is there any downside using GTID ?

I'm using MySQL 5.7 as of now.

Best.

Answer 1

The biggest advantage is auto-positioning. So you don't have to check and double-check the binlog file and position when you start replication. You just point the replica at its source and it figures out where to start replication from based on the GTID range it has already processed.

This greatly simplifies operations when you want to change your replication topology. For example when you want to change a replica so it is subscribed to a different source. You would do this during a failover operation, or if you are juggling replication during an upgrade.

The reason it isn't enabled by default is for backward compatibility with older MySQL deployments. MySQL supported GTID starting with version 5.6 (around 2013), so there were a lot of sites that weren't using GTID and needed to convert. Conversion was kind of a hassle in MySQL 5.6, so many sites did not adopt GTID. In MySQL 5.7, there were improvements that made it easier to make the transition. Still there are lots of sites that don't use GTID yet.

Answer 2

(So I'm answering to my self question)

After playing with GTID during few days I found a specific case where it does not work: when I need to move databases regularly between different replication clusters intermittently replicated.

I want to migrate a database from a replication setup to another group of servers replicated without causing any downtime on the application side, so I do this way.

1. Create a dump of the database (using mysqldump for instance) that I want to move from the active cluster master (called m1).
2. Inject the data on the master of the new replication setup (a master "m2" + 2 slaves). the replication thread apply the value of GTID_NEXT from the binlog and at the end of the injection the value of GTID_PURGED is modified with the latest GTID from master m1 at the time of the dump.
3. Create a replication between the master m2 as replica to m1 to catch up the missing data with a replication filter on the database only.
4. When ready to do the switch-over, stop the replication slave on m2 and make the application use m2 as SQL server.

It works fine for the first database to move, but not for the subsequent ones. From my experience on MySQL 5.7.33 at step 2 the replication thread does not honor the GTID_NEXT value in the binlog (and creates local GTID) and at the end the injection cannot modify the variable GTID_PURGED because GTID_EXECUTED is not empty any-more.