5

I'm not worried about split brain since the connection between the two servers is solid (and because I don't have a third machine)

I want to have some MariaDB replication with automatic failover so even if one database dies, it continues working. I've seen MaxScale, but since I only have two machines it'd have to run on the same machine as one of the servers and if that server dies, then nothing works. AFAIK, MariaDB Galera clusters will refuse to allow me to run on only two and have automatic failover (will require quorum). However, I might be able to run an arbitrator on another machine or even run another database on it, but it'd be slow.

Additionally, the backend is PHP - I'm willing to change the mysqli setup and such but I don't know if or what I'd have to change there.

EDIT: I'm willing to forgo auto failover, but the behavior I would then want would be the following:

If I connect to Server A, it connects to Database A (master) and reads/writes normally.

If I connect to Serer B, it connects to Database B (read-only slave) and reads just fine. If it has to write, it'll be able to but it'll push them to Database A.

Would this be possible using MaxScale on both servers or something like that?

forkwasher
  • 75
  • 1
  • 4
  • 1
    See MHA; it may be the best available for _two_ servers. It either prevents split-brain, or throws up its hands, saying that it cannot prevent split-brain. That is, you either get a safe failover, or no action is taken. – Rick James Aug 26 '20 at 20:21
  • How many servers with the PHP application do you have? One option is to place MaxScale on the application server itself and have it behave like a smart connector. – markusjm Aug 27 '20 at 05:34
  • I have two servers with the applications - the other question I have is if it's possible for Server A to connect to Database A (master) and make writes and reads. Server B connects to server B (slave) and if it makes a read, it works. If it writes it has to push the update back to Database A. – forkwasher Aug 27 '20 at 14:22

3 Answers3

3

You have two nodes. Don't use master-master of any kind, it's incredibly prone to split-brain on two nodes (it's almost guaranteed to happen eventually).

This kind of stateful application can't be expected to handle a two node cluster deployment on its own very well - either operator intervention or a CRM will be necessary to make the cluster at all robust in the case of failure - which is the reason it's clustered in the first place.

You have a two node cluster. You absolutely should be worried about split-brain, because that architecture is very prone to split-brain conditions. Just because the inter-node network link is solid today doesn't mean it will always be that way, and this is one of the largest components of risk in a two node cluster. Losing that link will instantly split-brain the cluster unless FENCING or QUORUM is established between nodes. This is one of the the biggest considerations in a two-node cluster, as fencing reduces the chances of split-brain conditions from high to near-zero.

I would recommend handling this with Pacemaker/Corosync. It's a complicated stack, but provides the mechanisms required to yield a production-grade cluster in two nodes. I would also recommend using only a single master instance at a time, rather than multi-master, even when under the enforcement of such a cluster manager.

There's a good guide for HA MariaDB that can serve as a starting point. It does NOT cover the use of fencing. If you cannot accomplish fencing, Corosync also has the ability to use a small arbitrator node running a voting daemon to provide the overall implementation with quorum at no application overhead cost (see information on Corosync qdevice).

It's behind a subscription wall, but its an end to end guide on configuring an active-passive MySQL cluster, running on one node at a time and replicating block storage between nodes

Pacemaker advanced resource types cover most of your questions regarding how to gracefully orchestrate failover, with the ability to group resources into linear dependency chains, as well as express multi-state leader-election semantics for running more than one instance of an application across nodes. That can be found here.

Bundles are a way to accomplish application isolation in Pacemaker via container runtimes like Docker and RKT. This opens up another avenue of fencing, as these bundles appear to the cluster as Pacemaker nodes themselves - so they can be "fenced" by the cluster independently of other applications. That can be found here.

Spooler
  • 7,046
  • 18
  • 29
  • 1
    If I can add an arbitrator on a third small node, would it be possible to do something simpler? I'm hoping to only make the databases HA since there are already solutions in place for the main server, so would it possible to do that easily in a way that's easy to integrate? – forkwasher Aug 27 '20 at 14:00
  • master-master is fine, just make nodes read-only by default an make keepalived, heartbeat, corosync, or whatever you are using, set there node deemed the master writable. That mitigates split-brain risk. – Gordan Bobić Aug 27 '20 at 15:57
  • If you use an arbitrator to establish quorum, you don't necessarily need fencing to ensure splits don't happen - though fencing is still an important part of cluster recovery even with quorum. This can be semi-accomplished using suicide fencing (an isolated node reboots itself). That's not "true" fencing, as there is no established guarantee of state in that case - but it's better than nothing. If you wish to make only the databases HA, you could package them in a VM or container and treat those as their own "virtual" cluster. Just make sure they run on differing nodes. – Spooler Aug 29 '20 at 23:48
  • And regarding master-master, you could perform read-write replica elections across two nodes using the appropriate resource agent and defining the database as a stateful clone within Pacemaker, relying on SQL replication with each replica using its own local storage. Or you can define a single master with a "primitive" resource that gets started on only one node at a time, with its storage replicated via DRBD. The second one is much simpler, but yes - both work. – Spooler Aug 29 '20 at 23:52
2

I ran various DBs (Mongo, Elasticsearch, SQL Server, others) with the same philosophy "I don't care about problems, I can only run two nodes."

It was a WORLD of hurt.

If you run master-slave, fine. But there will be headaches.

After years of dancing around the issue, and dealing with various devops headaches created by my insistence on only two nodes (which I insisted on because our databases were really large, and the cost of a third node was material), I finally started running three nodes.

And then everything got better.

The lesson I took, from years of dancing, is: There are two options:

  1. Single node with warm ish spare (e.g. master-slave)
  2. Three nodes

From my experience, I would never run two nodes active-active again (unless there is a magic piece that completely prevents split brain, which I have seen, and which is crazy ugly).

From five years of running multiple 0.5-1.5TB databases on various stacks.

Jonesome Reinstate Monica
  • 5,445
  • 10
  • 56
  • 82
  • Given a primary site and a secondary site, where does one place the third node? – Yolo Perdiem Sep 02 '20 at 02:26
  • @YoloPerdiem The third node can go anywhere: Third site, one of the existing sites, whatever. That is kind of a different question. The OP is saying "I'm only gonna run two nodes." The OP is not saying "I need robust failover between two sites" (which is a deeply different question). – Jonesome Reinstate Monica Sep 02 '20 at 16:35
  • Agreed, if you want two nodes, it's totally possible to do so in a robust way - with a primary/secondary node relationship, not primary/primary. The drawback is that the failover time is potentially higher (as it depends on how fast your database can stop/start), but the gain is that it doesn't freak out and split if anything troublesome happens. In general, three nodes is *always* better regardless of the clustering system in place - and the ability to use a small arbitrator as the third node lays the cost issue to rest. – Spooler Sep 08 '20 at 19:17
0

One option would be to run asynchronous master-master replication with keepalived to fail over a floating IP. It's not great, but it would cover the outright server failure scenario.

Do you have ILO or some other way of making one machine forcefully power the other off (STONITH)? This is really needed to prevent partial failure, e.g. a machine crashes but not completely, so it is still alive enough to respond to heartbeat packets but is otherwise not working. This can result in failover not occurring when it should.

Gordan Bobić
  • 971
  • 4
  • 11