Enabling GTID base replication without production pxc downtime

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi Team,
I am having to pxc clusters one in production and another in DR, Now we want to setup async replication between 1st pxc production node to 1st DR pxc node but we can’t bring down the service of pxc production node and GTID is increasing at the rate of 100/sec now issue is when we take a dump from production node and restore it on DR node and try to start async replication using start slave command it doesn’t work as GTID have increased in thousand on production env and GTID are now not same on prod and DR node.

How can we start async replication between 1st pxc production node to 1st DR pxc node without taking downtime as on 1st pxc production node GTID will keep on increasing at very high speed?

1 Like
2

We have 3 nodes each in Prod and DR PXC.

1 Like
3

Hi, you need to enable binary logs for async replication. Then simply record the binary log position when you take the dump, and run the change master command with that position. Have a look at How to set up a replica for replication in 6 simple steps with Percona XtraBackup - Percona XtraBackup for more detailed steps.

2 Likes
4

@adi GTID increasing 100/sec is normal. When you go to DR, CHANGE REPLICATION SOURCE TO … SOURCE_AUTO_POSITION=1, and MySQL will handle all the GTID stuff. You don’t ever need to stop prod in order to configure replication. As Ivan said, take a dump/backup of prod (which includes the GTID), restore to DR and start replication using AUTO_POSITION.

2 Likes
5

Hi @matthewb and @Ivan_Groenewold,
Thanks a lot for responding, yes I am able to follow the steps and start replication without downtime, I was earlier using mysqldump to restore on replicainstead of xtrabackup, and instead of “change replication source” I was using “change master” command maybe because of which GTID were not updating after starting replica. Thanks, I am good now.

1 Like