Connect two Percona XtraDB Clusters in two data centers

Question

I have been trying to figure this out the past couple of weeks. We used to have a five-host Percona cluster at different providers. When it started acting up I read that it is a bad idea to have synchronous replication across data centers. Now we have a three-node cluster in one data center and it is stable.

I am trying to figure out the best way to have data center redundancy.

I read that you can have two individual clusters, one in each data center and have an async link between them. Upon further reading though it seems that it is typically for a DR scenario so the slave cluster is not utilized and while the fail over is easy, the fall back is a bit involved.

I am very new to DB clustering, I am hoping for guidance. The database clients will be using HAProxy, and will only point to the hosts in one datacenter.

I am hoping that I can split the clients half to each DC and have the async link replicate the data across. How would you guys do this? What is everyone using these days?

Answer 1

Not a full answer, but some thoughts.

First, you must accept that there is not a "perfect" solution to HA, DR, etc.

Your 5 nodes split between two vendors should have been pretty good and safe.

Your proposed pair of 3-node clusters with async replication in one direction is also pretty good.

Having 3 nodes geographically separated enough to avoid having any two in the flood plain, earthquake fault, tornado alley, etc, may be slightly better than either of the above.

The main rule is to avoid any single-point-of-failure. (That includes an entire data center -- they can fail.) The secondary rule is to allow the system to run in degraded mode when there is a major failure.

With that in mind, another topology is to have a 3-node cluster in one DC, plus an async Replica in each of two other DCs, replicating from different nodes in the main cluster.

PXC nicely repairs a single node failure. All the other configurations require a lot of messy user actions. And if the single-node failure requires an SST, the cluster is almost down to a single node while another node is copying itself for the rebuilding of the new 3rd node.

5 nodes in a Galera (PXC) cluster is pushing the limits of network traffic.

Contrary to popular wisdom, a 4-node cluster can withstand a single-point-of-failure. But it does take 5 nodes to withstand 2 simultaneous node failures.

Answer 2

I decided on using Replication Manager.

It is very stable and it keeps my clusters in sync but you really need to be patient. When all the nodes are up, everything syncs within seconds. When a failure occurs, and then gets resolved, it takes several minutes for everything to synchronize.

I followed the instructions and it worked well except for the following:

1. It asks you to do a mysqldump, copy to the other clusters and then import. The import fails. In order for the import to succeed you need to change pxc_strict_mode=ENFORCING to pxc_strict_mode=PERMISSIVE in your /etc/my.cnf file then bootstrap that server.

Next go into mysql and run mysql> set wsrep_on=0; mysql> reset master;

Open up another ssh session and run the import Go back to mysql and revert the wsrep mysql> set wsrep on=1

change the /etc/my.cnf from PERMISSIVE back to ENFORCING and restart the mysql@bootstrap.

Repeat for the first node of each of your clusters.

2. The script looks for credentials in /root/.my.cnf or whatever you run the script as. I could not find the format of the credentials in any of the docs. It turns out it was just

[client]

user=root

password=<password>

That should be it. I hope this will help anyone looking for a similar solution.