datablogs: gcp
Showing posts with label gcp. Show all posts
Showing posts with label gcp. Show all posts

Wednesday, February 19, 2020

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
Get gtid values

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

#wsrep_provider = /usr/lib/galera/libgalera_smm.so

  • Once mysql service is restarted , we can able to do reset master in percona galera cluster server and set GTID_PURGED values

Set GTID_PURGED Value :

Configure gtid values

Check Show Slave Status G :

Slave Status

We able to see slave server is get synced in few minutes , and seconds_behind_master is 0

Check Show Processlist :

Highlighted the multi master servers ( two servers ) is syncing to slave server

Processlist

Once completed all the steps , enable cluster wsrep_provider variable in wsrep.conf and restart mysql service in slave server

Verify once again the slave status and processlist :)

References :

External Replica Setup : https://cloud.google.com/sql/docs/mysql/replication/configure-external-replica

Get gtid_purged : https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source-provision-slave.html

Set gtid_purged and troubleshoot : https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source-provision-slave.html