Mysql or Mariadb. 2 node Replication / Cluster

peted

New Member
Credits
17
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.

servers.png


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

New Member
Credits
65
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:

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.

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.
 
$100 Digital Ocean Credit
Get a free VM to test out Linux!

Staff online

Members online


Top