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
- 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 )
- 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,
- Application running on both DC1 has to be stopped
- Replication on Cluster B running in DC2 has to be stopped by updating the CR.
- Cluster A running on DC1 has to be made readonly
- Cluster B running on DC2 has to be made readwrite
- 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 !