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:
- Is this a bad idea? I personally see no major drawbacks here, but am I missing something?
- 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)?
- 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!