Mysql or Mariadb. 2 node Replication / Cluster

[peted]

We have moved to working from home, and our CRM solutions is a LAMP application and we want to provide an HA environment. (We are currently running on a single server using a VPN to access it. )

I have set up the following environment as a Lab and all works really well until one site loses connectivity. Both databases stop taking connectivity, I understand this is a design of Galera cluster with only two nodes, there is an option using their Arbitrator to help with 2 nodes, But this will only keep one instance alive.

Is there a mysql or mariadb solution that will allow both server to keep running and resynchronise after the connection is restored? I know that this is a possible slit brain issue, but we would be happy with the last edit on a field (or possible record) take precedence.

Any suggestion very much welcome, Or if it is not possible, would rather know now, than waste more time!

Thanks Pete

 

[dbdemon]

There is a possible issue with the architecture shown in your diagram if you intend to use Galera: It looks like you intend to write to both database nodes. While this is possible, it can result in deadlocks at unexpected times which your application must be designed to handle. The problem is described well in this article:

Galera’s big gotcha for MySQL users

Galera allows administrators to run a multi-master MySQL database cluster. It scales well for read loads and can offer greater redundancy and resilience to your infrastructure. However, there are some important nuances in the way Galera handles transactions that can catch out those used to...

ghostaldev.com

For this reason I use a database proxy with read-write splitting - either ProxySQL (open-source GPL licence) or MaxScale (BSL licence - free to use with 2 database/arbitrator nodes).

Is there a mysql or mariadb solution that will allow both server to keep running and resynchronise after the connection is restored?

Run the Galera arbitrator daemon on each of the two database servers.

Maintaining a Two-Node Galera Cluster

The article ^^ describes two failure scenarios. In the scenario where one database node crashes, I can imagine you could automate bootstrapping of the remaining node using a well written and tested monitoring script.

Be aware that both nodes will be unavailable to applications while the joiner node is synchronizing with the donor node.