How to change the mode of replication from and to GTID mode without having to take the MySQL server offline? - shiviyer/Blogs GitHub Wiki

Changing the replication mode from and to GTID (Global Transaction Identifiers) in MySQL without taking the server offline involves several steps. This process ensures a smooth transition with minimal impact on the database availability.

Here’s a high-level overview:

1. Ensure GTID Requirements Are Met:

   • Before enabling GTIDs, make sure all your servers (master and replicas) meet the prerequisites for using GTIDs.

2. Configure Binary Logging and GTIDs on Master:

   • Set gtid_mode to ON.
   • Enable enforce_gtid_consistency.
   • Enable binary logging by setting the log_bin variable.
   • Ensure that all transactions are using row-based replication (binlog_format=ROW).

3. Change Replication Mode on Replicas:

   • Stop the slave threads: STOP SLAVE;
   • Set gtid_mode to ON.
   • Enable enforce_gtid_consistency.
   • Configure the replica to use the GTID of the current transaction executed on the master:

     CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
     

   • Restart the slave threads: START SLAVE;

4. Monitor Replication Status:

   • Use SHOW SLAVE STATUS\G to monitor the replication status and ensure there are no errors.

5. Testing and Verification:

   • Test replication consistency and performance to ensure everything is functioning as expected.

6. Update Application Configurations if Needed:

   • Ensure that your application and any replication management tools are compatible with GTID-based replication.

This process should enable a switch to GTID replication with minimal downtime. Always ensure to back up your