Hybrid Cloud : Replicating data from Cloud SQL MySQL to a Self Managed MySQL server

Published in

Google Cloud - Community

4 min readApr 17, 2023

In the earlier blog we setup a replication from a self-managed/managed MySQL to Cloud SQL MySQL.
In this blog ,we would promote the CloudSQL as master and demote the self managed MySQL as Replica.

Use cases for setting up Replication to an external MySQL server

Replicating to an external server can help you achieve the following goals:

Hybrid Cloud : Replicate data to a self managed MySQL in the same/other cloud.
Minimal downtime fallback strategy with RPO=0 while migrating from a self managed mysql to Cloud SQL .
Retain colocation and control of your replicas.

Replicating to a external MySQL includes the following instances:

CloudSQL Instance as a Master.
A self managed MySQL on GCE.

Overview:

At the end of earlier blog , we had the following setup:

Master : Self managed mysql with GTID on GCE
Replica : CloudSQL MySQL with GTID

Using the below steps , we will promote the CloudSQL MySQL as master and convert self managed MySQL as replica

Master : CloudSQL MySQL with GTID
Replica : Self managed mysql with GTID on GCE

Stop writes on the master and let replication lag settle down.
Promote CloudSQL
Create Replication user and grant privileges on CloudSQL MySQL
Execute change master on self managed MySQL

Lets start !

1. Status check on Master and Slave

Master : Self managed MySql on GCE

Slave : CloudSQL MySQL

2. Stop writes on the master and let Replication lag settle down.
3. Promote CloudSQL

After promotion:

4. Create Replication user and grant privileges on CloudSQL MySQL

CREATE USER 'replicationUser'@'%' IDENTIFIED BY '****';
GRANT SELECT, SHOW VIEW ON *.* TO 'replicationUser'@'%';
GRANT REPLICATION SLAVE, EXECUTE ON *.* TO 'replicationUser'@'%';
grant FLUSH_TABLES ON *.* TO 'replicationUser'@'%';     
grant process ON *.* TO 'replicationUser'@'%';
FLUSH PRIVILEGES;

5. Execute change master on self managed MySQL

CHANGE MASTER TO MASTER_HOST='<cloudsql-ip>',
MASTER_USER='replicationUser',
MASTER_PASSWORD='****',
MASTER_AUTO_POSITION=1;
start slave;

6. Status check on Master and Slave

Master : CloudSQL MySQL

Slave : Self managed MySQL on GCE

7. Validation

Insert a record on Master

Validate the data replication to Slave

Learnings :

Error 1: Start slave fails with the following error:

2023-04-16T21:01:25.174361Z 77 [ERROR] [MY-010584] [Repl] Slave SQL for channel
 '': Worker 1 failed executing transaction '3a9a32a7-dc82-11ed-83b6-42010a31e0e3:902' 
at master log mysql-bin.000002, end_log_pos 278330; 
Could not execute Update_rows event on table mysql.heartbeat; 
Can't find record in 'heartbeat', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; 
the event's master log FIRST, end_log_pos 278330, Error_code: MY-001032

Fix:

Step 1: Take a dump of mysql.heartbeat table from CloudSQL MySQL:

      mysqldump - host=**.**.**.** - port=3306 - user=replicationUser - password=****
       - databases mysql - tables heartbeat - hex-blob - no-autocommit - default-character-set=utf8mb4 
      - single-transaction - set-gtid-purged=on - add-drop-table > mysql_dump_fromcloudsql.sql
      
Step 2: On Slave 

reset master;

Step 3: Import the dump into self managed mysql:

    mysql -u root -p mysql < mysql_dump_fromcloudsql.sql

Step 4 : start slave;

Step 5 : SHOW SLAVE STATUS\G;

Till next time , Happy Learning !