Tuesday, September 11, 2018

Unforeseen use case of my GTID work: replicating from AWS Aurora to Google CloudSQL

A colleague brought an article to my attention.  I did not see it on Planet MySQL where I get most of the MySQL news (or it did not catch my eye there).  As it is interesting replication stuff, I think it is important to bring it to the attention of the MySQL Community, so I am writing this short post.

The surprising part for me is that it uses my 4-year-old work for online migration to GTID with MySQL 5.6.  This is a completely unforeseen use case of my work as I never thought that my hack would be useful after Oracle include an online migration path to GTID in MySQL 5.7 (Percona did something similar for MySQL 5.6).

The thing that my 4-year-old hack allows, which is not provided by Oracle or Percona, is to generate GTID on the replication path.  Both Oracle and Percona migration path only generate GTIDs on the master.  In that respect, Oracle and Percona migration path allow to migrate online to GTID, but both are all-in migrations: if something goes wrong in production because of GTIDs, you need to quickly fix it and your production might be down in the meantime.

Update 2022-12-28: my trick was implemented in MySQL 8.0.23 with the replication channel option ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS.  Percona blogged about this in their post A Useful GTID Feature for Migrating to MySQL GTID Replication.

My work not only allows to migrate online but it also allows to experiment with GTIDs.  With the ANONYMOUS_IN-GTID_OUT intermediate master, part of the replication setup is using GTIDs while the rest (including the master) is still in legacy mode.  This is a subtle but important difference.  If GTIDs are breaking something, only the subset of replication using them is broken, and the master with the non-GTID slaves are still working.  This is exactly what was needed by the people at YoungCapital to migrate from AWS Aurora to Google CloudSQL.

ANONYMOUS_IN-GTID_OUT allows to migrate
from AWS Aurora to Google CloudSQL

I have no experience with Aurora, so what I am writing here is my understanding from the YoungCapital article.  What I gathered is that Aurora does not use GTIDs.  But CloudSQL is only working with GTIDs.  As the people at YoungCapital wanted to migrate from Aurora to CloudSQL, they were facing a challenging problem.  To solve it, they used my hack; the details are in their post Moving from AWS Aurora to Google CloudSQL.

I did a follow-up post on my GTID migration path in 2015 (one year after the original work was published), but I never thought that it would still be useful 3 years later.  As pointed out by Karst Hammer in the YoungCapital article, my feature request (Bug#71543) about the ANONYMOUS_IN-GTID_OUT mode is still open (actually, it was reopened at the request of Simon Mudd).  Maybe AIGO (this is the short name we gave internally at Booking.com to the ANONYMOUS_IN-GTID_OUT mode) will eventually land in a GA version of Oracle MySQL or Percona Server, so people needing it will not have to recompile MySQL.

This reminds me of other unforeseen use cases of my work.  When I started working on Binlog Servers, it was to allow extreme replication scaling.  As the work evolved, it became obvious that it could also be used for master failover.  Then, after a Binlog Server talk at Percona Live, someone came to me suggesting using it for point-in-time recoveryFacebook also published related work and I am sure there is more about this out there...

This is the power of the MySQL Community: I find this very cool !

So if you solve an interesting problem, blog about it to allow others to build on your work.  You do not have a blog, then use the Percona Community Blog.

And the MySQL Community European Conference - Percona Live - is in two months in Frankfurt, join us there.

No comments:

Post a Comment