XtraDB Cluster Offline Working

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi,

I’m trying to run XtraDB Cluster with 2 nodes. Those nodes are connected by using not so good WAN connection. All MySQL client of both nodes need to work independently on online and offline condition. One node is for incoming insert from web based forms, the other node is for approval process of inserted row by updating the status column. Below are 2 possible condition of working for that case.

  1. Clients of each node never work on the same row with the other node clients
  2. Clients of each node can work on the same row with other node clients but never update the same row at the same time

Is it possible to do that with XtraDB Cluster? How can I make both nodes still available on WAN link outage? I know it will become split brain condition, but since mostly works is reading and the update will never conflict on above case, is it a way to do that? On my current setup, the lower priority node will be not available on WAN link outage condition even for read only works. What we need is to make both nodes still available on WAN link outage condition and both node will synchronize any update happen on offline time when the WAN link is become normal again.

Best regards,

2

Let me summarize the problem based on my understanding

  1. 2 nodes both of them located in different data-center (separated by WAN)
  2. nodes don’t execute conflicting workload
  3. nodes may touch same tables but at different time so again no conflicting workload.

With that problem statement ?

a. Can we use PXC ?

  • Yes, you can use PXC

b. Is this recommended setup ?

  • No. PXC recommends at-least 3 nodes to avoid split brain. You can replace 3 node with garbd (arbitrator) to ensure quorum

c. WAN link is not so good ?

  • This means you may have frequent disconnection. This is not good use-case for PXC or multi-master replication setup but you can still work this out provided you carefully adjust different timeout and configuration (like segment, etc) related to WAN. If things are lot more flaky and split brain happens both nodes will refuse to proceed but if you have garbd then you can get one of the site to proceed to avoid complete non-availability.

Other alternative is to use 2 PXC cluster (say 2 node each may be garbd + real pxc node) and connect them using mysql async replication.

3

Hi,

Is PXC support asynchronous Master-Master replication? If not how can I do that? I only has two server for this case. Is it possible to make PXC waiting for long time before yhe lowest priority node become not available? At such long waiting time, the transaction should still in

4

PXC support Master-Master but let me clarify my understanding of this setup

PXC cluster-1 with n nodes (n = 1 in your case)
PXC cluster-2 with m nodes (m = 1 in your case)

And you would like to create a MASTER-SLAVE link between cluster-1 and cluster-2 and vice-versa so that updates to any of the cluster are replicated.
This is supported.

Also known as PXC Cluster Replication.

BTW if you only have 2 server both are in different DC
a. You really don’t need PXC. What you need is Percona-Server with Master-Master Replication.

PXC is to multi-master solution where-in your cluster has n servers. Local or Spread across the WAN.