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

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 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):
    • 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:
    • Enable GTID mode by setting the gtid_mode and enforce_gtid_consistency variables. This can be done dynamically:
    • Point the slave to the current master position with CHANGE MASTER TO:
    • Restart the slave threads:

Switching from GTID to Non-GTID 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:
    • Restart the MySQL master server.
  3. Reconfigure Slaves for Non-GTID Replication:
    • On each slave, execute:
    • Change the replication mode to non-GTID. Specify the master log file and position explicitly:
    • Restart the slave threads:

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.
About Shiv Iyer 422 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.