Transferring our enormous databases without causing downtime was turning into one of the biggest challenges we faced so far.

When we firstly started exploring the options of transferring our databases, there was no native option to launch a Google CloudSQL 2nd generation instance that replicated from an external master (AWS Aurora in our case), this meant that we had to rely on external tools to provide this functionality.

We tried using Tungsten Replicator to do the replication by reading from our Aurora instance and writing to a new Google CloudSQL instance, but quickly we ran into several issues that made us reconsider this choice. Lucky for us, just when we were trying to get Tungsten to work, we noticed Google offering a new migration feature, allowing CloudSQL to replicate from an external master (https://cloud.google.com/sql/docs/mysql/migrate-data).

There is however one caveat that prevented us from directly using our AWS Aurora machine as an external master. One of the requirements Google poses for the external master is the required support for GTID transactions, which unfortunately AWS Aurora does not support.

We attempted to setup a custom MySQL machine between our Aurora machine and CloudSQL to see if MySQL natively offers a possibility to read from a server with ANONYMOUS transactions (non-GTID) and is able to act as a master to a GTID enabled slave. This attempt failed out of the box, since standard MySQL only supports either ANONYMOUS or GTID transactions, and the setting needs to be consistent across the entire replication chain.

We found help in an article posted by Booking.com that explains how they introduced GTID-based replication into their MySQL setup. While our goal is different, essentially we were bumping into the same problem, namely, how do we replicate from a master using ANONYMOUS transactions to a GTID enabled slave.

The solution proposed by the article involved using a patched MySQL that disables verification that GTID-based replication is enabled on the entire chain, a mode that they named ANONYMOUS_IN-GTID_OUT. They have also created a feature request in the MySQL bug tracker to have this functionality supported in the original MySQL, but this has not been released yet.

Inspired by their article, we built a new version of MySQL that included the ANONYMOUS_IN-GTID_OUT patch to see if we would be able to setup replication between Aurora and Google Cloud using the patched MySQL in between.

Our attempts succeeded and we were able to have a working replication between AWS Aurora and Google CloudSQL using the patched MySQL as a middle-man. It takes ANONYMOUS transactions from AWS and introduces GTIDs to all transactions to allow for direct replication to Google Cloud.