MySql Galera Multi site high availability

galerahigh-availabilityMySQLperconareplication

We have a datacenter with 3 percona glera nodes

We have a store that wants to be in sync with our datacenter(multi master)

our store's internet connection is not reliable ( may be down for 30 min), it is adsl , then
we decided to get two internet connections from two different ISPs with static ip address
galera cluster with local store

Questions are:

  1. Is it possible for a single galera node to have multiple public ips?

2.When both internet connections go down. Local galera node will be
unavailable too , How can we can configure it to avoid this?

3.Do you have any recommendation
or better solution in this regard?

Best Answer

Consider:

Make it a 4-node Galera Cluster. Have gcache set large enough to handle more than 30 minutes' worth of changes. Then let Galera take care of the rest.

Meanwhile, your clients should not connect just to galera4, but via a proxy to any node. (You may want to tell the proxy that "galera4" is preferred.)

But the real problem comes when you try to write to galera4 when it has lost connection to the other nodes. A core principle of Galera says that an isolated node (like that) shall not accept writes. Otherwise the data can become inconsistent.

Once you face that problem, you realize that "galera4" is not the solution. Instead, working on the network is the solution, and having the proxy robust enough to find a way to the 3-node datacenter is sufficient.

Or...

NDB Cluster is a different clustering offering from MySQL. It has a "eventually consistent" model for replication. You can have any number of disconnected Masters, write to any of them. The issue comes when you have conflicting writes (for example, two clients write with the same unique key). The inconsistency is "eventually" caught, but this could be long after the client finished talking to the Master that it happened to reach. At that point, some rule (such as "oldest timestamp wins") controls which record is kept and which is rejected.

NDB works well if you naturally are not going to write conflicting records.

Related Question