I was wondering what’s the general consensus these days on replicating PXC over WAN.
We are running a 6-node PXC 5.7 with a 140GB dataset, a single master writer and around 20-30 writes per second (inserts/updates/deletes).
The plan is to bring up a separate cluster or segment in another data center for backup and later migration without significant downtime to move data around.
So replication can be asynchronous and the nodes in secondary site will be mostly passive receivers ond standby for the time being.
I’m dreading the latency (around 150ms) between data centers which could cause the primary writer to stall under pressure waiting for remote sync to confirm the writes.
So what’s the best practice for such a scenario? I’m leaning towards using two separate clusters and master-slave replication over chosen nodes since the sync has to be near-realtime and ideally would want just two nodes exchanging data on each site and then propagating writes locally. Any gotchas or caveats?
Thanks in advance!