Seamlessly Switching MySQL Replication Modes: Transitioning Between GTID and Non-GTID Without Downtime

Introduction

Changing the mode of replication in MySQL from and to GTID (Global Transaction Identifier) mode without taking the server offline requires careful planning and execution. The process involves ensuring that all current transactions are replicated and then switching the replication mode. Here's a step-by-step approach.

Switching from Non-GTID to GTID MySQL Replication Mode

1. Ensure Binary Logging and GTID Logging are Enabled:
   • On the master, enable binary logging and GTID-based replication. Modify the MySQL configuration file (my.cnf or my.ini):
     Shell

     [mysqld] log-bin=mysql-bin gtid_mode=ON enforce_gtid_consistency=ON

     1	[mysqld] log-bin=mysql-bin gtid_mode=ON enforce_gtid_consistency=ON

   • Restart the MySQL server for changes to take effect.
2. Wait for Slaves to Catch Up:
   • Before switching the replication mode, ensure that all slaves have processed all binary logs from the master.
   • Use SHOW MASTER STATUS on the master and SHOW SLAVE STATUS on the slaves to confirm.
3. Change Slave to GTID Mode:
   • On each slave, stop the slave threads:
     Shell

     STOP SLAVE;

     1	STOP SLAVE;

   • Enable GTID mode by setting the gtid_mode and enforce_gtid_consistency variables. This can be done dynamically:
     Shell

     SET GLOBAL gtid_mode = ON; SET GLOBAL enforce_gtid_consistency = ON;

     1	SET GLOBAL gtid_mode = ON; SET GLOBAL enforce_gtid_consistency = ON;

   • Point the slave to the current master position with CHANGE MASTER TO:
     Shell

     CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

     1	CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

   • Restart the slave threads:
     Shell

     START SLAVE;

     1	START SLAVE;

Switching from GTID to Non-GTID Mode MySQL Replication Mode

1. Ensure All Slaves are Up-to-Date:
   • Confirm that all slaves have processed all GTID transactions from the master.
2. Disable GTID on the Master:
   • Stop the master and modify the configuration to disable GTID:
     Shell

     [mysqld] gtid_mode=OFF enforce_gtid_consistency=OFF

     1	[mysqld] gtid_mode=OFF enforce_gtid_consistency=OFF

   • Restart the MySQL master server.
3. Reconfigure Slaves for Non-GTID Replication:
   • On each slave, execute:
     Shell

     STOP SLAVE;

     1	STOP SLAVE;

   • Change the replication mode to non-GTID. Specify the master log file and position explicitly:
     Shell

     CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000001', MASTER_LOG_POS=12345;

     1	CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000001', MASTER_LOG_POS=12345;

   • Restart the slave threads:
     Shell

     START SLAVE;

     1	START SLAVE;

Important Considerations

• Backups: Before making changes, ensure you have recent backups of your databases.
• Monitoring: Closely monitor replication status and error logs during and after the transition.
• Consistency: Verify data consistency across the master and slave servers after the change.
• Version Compatibility: Ensure that all servers in your replication setup are running a MySQL version that supports GTID if switching to GTID mode.

Conclusion

Switching replication modes in MySQL requires careful execution to avoid data inconsistencies or replication errors. Always plan and test the process in a staging environment before applying changes to a production system. Regular backups and close monitoring of replication status are essential for a smooth transition.