Multi Source Replication from GCP Cloud MySQL to Percona XtraDB Cluster

It is easy to migrate the MySQL databases from one cloud provider to another cloud provider , but without downtime is little difficult to migrate the 1 TB of data . Using GTID based replication is easy to achieve in few hours of effort

Before go into detail , we will go through explanations of each things,

What is Cloud MySQL ?

Google Cloud Managed service for MySQL Database servers . It is supporting MySQL 5.6 and 5.7 with first and second generation instances . First and Second generation differs with allocating RAM and Storage

What is Percona xtradb cluster ?

Synchronous multi-master replication using percona MySQL server and percona xtrabackup using galera Library . As recommended it should contains odd number of nodes and same data spanned with all nodes

What is multi-source replication ?

MySQL multi-source replication enables a replication slave to receive transactions from multiple immediate masters in parallel

In this scenario , we have to migrate more than 3 GCP Cloud MySQL Managed instances into

Percona Galera Cluster . Before proceed to migrate the data , it is requires to compare MySQL 5.7 and Percona MySQL 5.7

https://www.percona.com/doc/percona-server/5.7/feature_comparison.html

So that we can avoid impact after migrating the data into new server

Instructions follow to setup multi source replication ,

In GCP MySQL Instance , take a user database backup from each instances separately through mysqldump or using export in GCP GUI Console and move to backup files to destination server using gsutil or scp utility

mysqldump --user=root --password --host=gcpinstance1 sourcedb1 > mysqldump1.sql

mysqldump --user=root --password --host=gcpinstance2 sourcedb2 > mysqldump2.sql

Before goes to next steps , we have to ready with three node percona XtraDB cluster environment before proceeds with next steps
In Percona XtraDB Cluster end , need to modify below server variables in my.cnf file for GTID based replication and restart mysql service

[mysqld]
server-id=[SERVER_ID]
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
replicate-ignore-db=mysql
binlog-format=ROW
log_bin=mysql-bin
expire_logs_days=1
read_only=ON

Same Changes needs to edit for additional node of percona cluster

Restore multiple source cloud mysql backup into one percona cluster instance

mysql --user=root --password --host=perconainstanceslave < mysqldump1.sql

mysql --user=root --password --host=perconainstanceslave < mysqldump2.sql

Once restore is completed , we need to add multiple source of GCP Cloud MySQL instances into single percona cluster instance using below command

CHANGE MASTER TO MASTER_HOST="gcpinstance1", MASTER_USER="slaveuser1",MASTER_PASSWORD="slaveuser1", MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;

CHANGE MASTER TO MASTER_HOST="gcpinstance2", MASTER_USER="slaveuser2",MASTER_PASSWORD="slaveuser2", MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;

One we execute this , we will get below error due to applied transactions exists already in slave server or we will get duplicate transactions issues commonly

Slave_IO_Running: No

Slave_SQL_Running: Yes

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

So,We need to get gtid_purged values from each separate source backups to ignore the deleted transactions in the binary log and troubleshoot above issues

cat mysqldump1.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'''

cat mysqldump2.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'''

To set global GTID_PURGED values , needs to do reset master in percona galera cluster . If server is in cluster not possible to execute reset master command . Needs to disable wsrep_provider variable in wsrep.conf to execute reset master and restart mysql service in slave server