1

I need to move a busy OLTP database from self hosted MariaDB 10.5 to Google Cloud SQL for MySQL 8.0 with minimal downtime.

The source is a self managed VM instance in Googles Cloud with replicas, ProxySQL as load balancer, etc. The destination is a Google Cloud SQL. The databse is ~400 GiB on disk and it takes about four hours to load it from a logicak dump.

I used to do this with replication and switchover, but MySQL 8.0 can't read the binary logs from MariaDB.

I would be grateful for any advice on how this move can be implemented with less than 10 minutes of downtime.

Claus Koch
  • 23
  • 4

1 Answers1

3

I had the same experience recently when trying to move to CloudSQL, I could not get MariaDB (source) to replicate to MySQL 8.0 (replica).

Upon starting replication, as soon as a binary log got replicated from the source, the replica failed with: Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, the server was unable to fetch a keyring key required to open an encrypted relay log file, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

I assume the cause is that the MariaDB and MySQL projects have diverged too much to replicate between eachother with newer versions of MySQL.

The workaround for me was to use multi-stepped replication. Firstly the MariaDB (10.7 in my case) source replicates to a MySQL 5.7 replica, which replicates to a MySQL 8.0 replica, which then replicates to CloudSQL.

MariaDB -> MySQL 5.7 -> MySQL 8.0 -> CloudSQL MySQL 8.0

To allow my MySQL 8.0 replica to replicate to CloudSQL, I had to set GTID_MODE=ON in the server setting, and enable ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=LOCAL on its replication settings (from the MySQL 5.7 instance). This is because CloudSQL requires GTIDs and does not permit anonymous transactions (binary logs). The replication setting helps to migrate from anonymous transactions to GTIDs, more information can be found here.

Using this setup we:

  • Migrate from MariaDB to MySQL 8.0
  • Switch from binary logs to GTID-based replication along the way
  • Replicate into CloudSQL
OscarVanL
  • 146
  • 2