Doing impossible - migrating gcp CloudSQL live from 5.6 to 5.7
Google Cloud - Google Cloud Platform, very versatile cloud offering and comes loaded with a lot of very interesting products for data professionals, personally I'm a huge fan of BQ aka BigQuery...
CloudSQL is google's fork of MySQL databases that is fully managed relational database service, similar to Amazon aws RDS, need database nodes - just make some click ...click or terraform and the new database is ready to go, need a reader ? No problem click...click or terraform (to be cool) and its ready.
Great then. A lot of users moved to managed CloudSQL - awesome, fast, managed, all good. Now comes the inevitable time to upgrade moving from 5.6 to MySQL 5.7 and oops! You can 't do it live - frantic searches and google support confirmed there is no path to do it live or simply put one can not replicate from CloudSQL 5.6 to CloudSQL 5.7, its a BIG oops!
MySQL experts long ago learned how move your databases from release to release via MySQL replication, replicate MySQL 5.6 nodes you have to MySQL 5.7 nodes and then switch to the new 5.7 MySQL databases. With enough craft and ProxySQL one can migrate with virtually no downtime.
Problem. So how do suppose to migrate from CloudSQL 5.6 to CloudSQL 5.7 then? Put the users on hold a take an outage ? Thankfully not since gcp has awesome "Compute Engine" facility, aka ec2 in aws, or virtual machines for simplicity, so here is how we do it.
Strategy. Google CloudSQL has capability to replicate from an "external master", in gcp's terminology "external master" is any MySQL that is not CloudSQL, this was build that one can migrate from on-prem MySQL to CloudSQL. For our purposes we know that we can replicate from MySQL on vm to CloudSQL instance, very awesome. Here's a very good document of on "external masters" https://cloud.google.com/sql/docs/mysql/replication/replication-from-external
Next step, We create gcp "Compute Engine" vm and install MySQL database 5.7 on it, after that we provision CloudSQL slave, stop the replication on it, dump the data and migrate to MySQL 5.7 on the vm aka "Compute Engine" that we created. Finally we replicate from from CloudSQL master database that is release 5.6 to our "Compute Engine" vm that is MySQL release of 5.7 database - this step is very well supported in MySQL standalone. One small note here is that CloudSQL is only using GTID, but that is not an issue for MySQL 5.7 on the vm, so what we will have is this:
CloudSQL 5.6----replicating-----> MySQL 5.7 vm aka "Compute Engine"
Very cool! Now what ?
The last step. Remember gcp's "external master" concept ? External master is MySQL database that is not CloudSQL however CloudSQL can replicate from it using this "external master" concept. Here is more info https://cloud.google.com/sql/docs/mysql/replication/replication-from-external
All and all "external master" makes your MySQL database on vm visible to CloudSQL databases. Remember that MySQL reader vm that we created above ? It will serve as an "external master" for shiny new CloudSQL 5.7 edition node. Its fairly easy to create a replica node from our vm's 5.7 "external master". Think of "external master" as a pointer for CloudSQL to locate your vm database. When replicating from "external master" aka vm CloudSQL will need your entire MySQL dump file and will create CloudSQL replica from the dump file and establish the replication to your MySQL vm node, via GTID of course:)
When we assemble the entire thing, we will end up with this:
5.6 CloudSQL ---->MySQL 5.7 vm "Compute Engine" -----> 5.7 CloudSQL
Very Cool. Now since the data is nicely flowing from CloudSQL 5.6 to CloudSQL 5.7 via "Compute Engine" MySQL 5.7 vm we can switch off by sending the data to the new CloudSQL 5.7 with very minimal downtime if any :)
Hope you like it! MySQL database is so versatile, it can be adapted to virtually anything.