How to convert standard replication to GTID replication in MySQL

Question

Currently, I have a master-slave structure using binary replication. Since GTID replication is better and easier to manage, I want to convert my replication to GTID with minimal downtime and data loss.

MySQL version is 8, the database size is above 1GB, How to do this operation safely?

Answer 1

The general idea to enable gtid on a master slave configuration(no downtime) is to allow slaves to replicate both gtid and non gtid based transaction for sometime, enable gtid on master and then enable on the slaves. The steps involved are:

On master:

1. set global enforce_gtid_consistency=WARN;

Now monitor the mysql log for sometime, it throws warnings if there are transactions that are not supported with gtids. If there are warnings, adapt your application to use only features which are compatible with gtids. If no warning appears, proceed.

On each slave:

1. set global enforce_gtid_consistency=ON;
2. set global gtid_mode=OFF_PERMISSIVE;
3. set global gtid_mode=ON_PERMISSIVE;

Now on master:

1. set global enforce_gtid_consistency=ON;
2. set global gtid_mode=OFF_PERMISSIVE;
3. set global gtid_mode=ON_PERMISSIVE;
4. SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'; wait until it becomes 0
5. set global gtid_mode=ON;

On slaves:

1. set global gtid_mode=ON;

Don't forget add these lines on my.cnf (on both master-slave)

[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON

You can leave slave as it is which will work perfectly fine, but can also switch to gtid based auto positioning using the command: CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

Follow same steps if you have multi channel enabled in slave. Make sure to enable gtid(gtid_mode=ON) in all masters while gtid_mode on slave is ON_PERMISSIVE. After enabling on all masters, enable gtid on slave.