We currently have a 3 node Percona Xtradb cluster running in a single data center. We want to add fault tolerance in case of disaster to prevent reliance on a single data center and synchronize our Percona data with a second data center so that we may easily switch our apps to connect to a new, synced, cluster in case of such an event.

While I am aware I can simply add more master nodes in many datacenters, we cannot sacrifice the increase round trip write time of a data center in a different location. Instead, I was wondering if it's possible to do this with MySQL replication. My thinking is to set it up this way:

3 node Main Percona Cluster -> MySQL replica DB -> 3 node Secondary Percona Cluster

While I realize there may be some minor replica lag and data could be out of sync by a few seconds, this is a tradeoff we're willing to make. With such a setup, it would be easy for us to switch clusters at the flip of a switch.

I have a few questions:

  1. Is this a bad idea? I personally see no major drawbacks here, but am I missing something?
  2. Is it possible for a MySQL slave replica to both receive data (from the Main Percona Cluster) and write data (to the Secondary Percona Cluster)?
  3. What will happen if the Main Percona Cluster goes down and we begin using/writing to the Secondary Percona Cluster? When the Main Percona Cluster comes back, how can we synchronize it with the new data from the Secondary Percona Cluster easily? Can we simply (temporarily) make a 6 node cluster and Percona will do the work?

I know this a lot of questions, but thanks so much for your help!


Cluster -> MySQL -> Cluster works. Though I haven't tried it, myself, I know that Cluster->Cluster was to be an option Soon™ as of some months ago, so you may be able to skip the intermediate step. From the PoV of PXC, you enslave yourself off of one of the machines and the other machines enslaves itself onto you; you aren't so much "both receiving data and writing data" as you are receiving data and allowing data to be received from you, if that distinction makes sense.

To re-synchronize, simply setup like master-master with hand-tuned auto_increment_increment and auto_increment_offset on each of the clusters. Probably straightforward, but at worst, it'll mean constraining the auto_increment_{increment,offset} chosen by PXC, which would be a small patch.

Bonus points: for maximum replication speed, modify (with sql_log_bin=0 in the session!!!) your tables on the intermediate database to use the blackhole engine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.