Automating MySQL GTID Migration With Ansible

MySQL 5.6 onwards introduced GTID based replication drastically simplifying replication setup and increasing reliability. New MySQL cluster 5.6+ setups are already done with GTID enabled by default.

But if you are one of those people who migrated from a pre-5.6 MySQL version, you probably avoided enabling GTID to make the upgrade easier. In such a situation, typically, you would upgrade the slaves to the new MySQL version, and then failover the master to an upgraded slave. This would let you do an online upgrade of a MySQL cluster from a pre-5.6 to a 5.6+ installation, but you will not be able to do this with GTID enabled.

I recently had a requirement to move a bunch of such MySQL 5.7 clusters using the old binlog position based replication to a GTID based replication setup.

Now there is a pretty good official document about how to do this manually, which you should definitely read up before doing this. But it is a pain to do this manually on a bunch of servers.

I captured all the steps mentioned in the document into an Ansible playbook to automate the whole process. It also includes a procedure missing in the official document, to actually flip the ongoing replication to GTID protocol.

The playbook source is here with step by step documentation here.