MySQL Bi-directional Async Replication between 2 PXC Clusters

MySQL & MariaDB Percona XtraDB Cluster 5.x
,
1

Hi,

I would like to know the preferred approach for setting up replication between 2 PXC MySQL clusters running on different geographical datacenters.

The current approach that we follow is as below

  1. Cluster A runs on DC1 configured as source and exposes the nodes via NodePort and fronted by a LB URL say ( clusterA-db-dc1.xyz.net )
  2. Cluster B runs on DC2 configured as replica and replication target being the LB URL of source. ClusterB is put on ReadOnly Mode so that no writes happens here.

As a result we could see data being replicated from ClusterA to ClusterB

This makes the DB Active-Passive. Application that connects to this DB is deployed in both datacenter and its Active-StandBy and connects to the ClusterA database on a regular day.

Incase of a DR Event, we would have to do the following,

  1. Application running on both DC1 has to be stopped
  2. Replication on Cluster B running in DC2 has to be stopped by updating the CR.
  3. Cluster A running on DC1 has to be made readonly
  4. Cluster B running on DC2 has to be made readwrite
  5. Application running on both DC2 has to be scaled up.

Traffic to application will be served by one running in DC2 connecting to ClusterB

For the PostDR ( Fallback ) scenario we would have to reverse the process we did for DR.

After which we face some issue in replication between Cluster A and Cluster B.

Will the writes that happened in ClusterB in DC2 (during DR time ) will be propagated to Cluster A. ( if cluster A is made source )

We would like to know the PostDR recovery process

It would be more interesting to know if Bi-directional replication is fully functional with MySQL and it can be used to avoid such failover-fallback and make the DB Active-Active !

Thanks
Sridhar J

2

Hi @SridharJ

Please take a look at Replication manager for Percona XtraDB Cluster - Percona Distribution for MySQL

I think this is what you are looking for.

Pep

3

Hello Sridhar

As Pep mentioned, replication manager might help you to set up the multi-source async replication between the PXC clusters
However, in order to avoid writes in the “passive” cluster, you need to take care of the global super_read_only variable in every of the nodes (manually or by automation) in the passive cluster, since by default PXC is multi-source between its nodes as well

Hope that information completes a little more your requirement

Regards
Leo F.