Repairing a Broken MariaDB Cluster at 3 am: Lost Quorum, Stopped Replication and the Database Cluster Recovery Add-On

Database clusters fail in ways single databases cannot: three nodes that each believe a different thing about who is in charge, a replica whose thread stopped on a duplicate key eleven days ago, a Galera cluster where every node refuses to start because none is sure it holds the latest transaction. Fixing these by hand at 3 am is where careers are made and data is lost. This post follows an SRE through two such incidents on MassiveGRID PaaS, which is built on Virtuozzo Application Platform, formerly Jelastic, with the Database Cluster Recovery add-on installed beforehand.

The SRE looks after the databases of an online ticketing company we will call Portcullis Tickets: a three-node MariaDB Galera cluster behind ProxySQL for the shop, and a primary-secondary MariaDB cluster for reporting, both from the marketplace's MySQL/MariaDB/Percona cluster package. The Database Cluster Recovery add-on runs a diagnostic of replication state, Galera cluster size and status and node connectivity, and can repair the cluster automatically.

What the add-on installs, and why beforehand matters

The add-on installs onto a MySQL, MariaDB or Percona cluster layer created from the marketplace, in any of its topologies: primary-secondary, primary-primary, Galera or XtraDB. At install it does two things that are only possible while the cluster is healthy: it creates a replica user the diagnostic uses to inspect the nodes (and which ProxySQL's configuration also uses), and it sets up SSH keys between the database nodes so that recovery can copy data between them with rsync. That is why the SRE installed it on day one rather than on the night it was needed: a broken cluster is a bad place to be distributing keys. The add-on then offers two actions on the layer: Cluster Diagnostic and Cluster Recovery, plus optional scheduled diagnostics with alerts.

Incident one: the Galera cluster that would not start

A hardware fault in the data centre took down the hosts under all three Galera nodes within the same minute. The platform restored the containers on healthy hosts, but Galera is not a set of independent databases: when every node stops, the cluster must be bootstrapped from the node that holds the most recent transaction, and each node's state file records a safe-to-bootstrap flag of zero because none shut down last in an orderly way. Started blindly, the nodes refused to form a cluster, and the shop's ProxySQL had no healthy backend.

The by-hand procedure, which the platform documents, is to inspect each node's Galera state, find the one with the highest sequence number, mark it safe to bootstrap, start it as the new cluster, then start the others so they take a state transfer from it. Done wrong, you bootstrap from a stale node and the newer transactions on the other nodes are discarded. At 3 am the SRE ran the add-on's Cluster Diagnostic instead. It reported: cluster size zero, all three nodes down, and the sequence numbers of each node's state, identifying node two as the most advanced. He clicked Cluster Recovery. The add-on bootstrapped node two, started nodes one and three, and they took state transfer from node two, incremental where the cache allowed and full via rsync over the SSH keys where it did not. Twelve minutes after the containers came back, ProxySQL saw three healthy backends and the shop was selling tickets.

Incident two: the replica nobody noticed

The reporting cluster is primary-secondary. A developer's ad-hoc insert directly on the secondary, months earlier, had created a row that a later replicated insert collided with; the replication thread stopped on the duplicate-key error, and the secondary quietly stopped receiving changes. Reports were eleven days stale before anyone noticed. The SRE had by then enabled the add-on's scheduled diagnostic, which is how it was noticed: the daily check reported the replication thread stopped and the GTID position lagging, with an alert to the operations channel.

Recovery here is a judgement the add-on makes conservatively: it selects the most advanced node, the primary, as the source and resynchronises the secondary from it, which discards the secondary's divergent local row and restores a consistent replica. The SRE approved that, because a reporting replica has no business holding writes of its own. He also revoked the developer's direct write access to the secondary, which the add-on cannot do.

What the add-on will not do for you

It repairs replication and cluster membership. It does not repair corrupted tables or pages; the separate Database Corruption Diagnostic add-on checks table integrity, and a restore from the Database Backup/Restore add-on is the remedy for data damage. It does not support PostgreSQL clusters, whose replication and failover work differently. And it cannot recover writes that reached only a node it must resynchronise from a more advanced one: committed data on the chosen source is preserved, and anything that existed only on the discarded node is gone, which is why Galera's synchronous certification, or a primary that is the only writer, is the design that makes the add-on's choice safe.

The runbook after two incidents

• Install the add-on on every MariaDB cluster the day the cluster is created, while it is healthy.
• Enable the scheduled diagnostic daily with alerts to the operations channel.
• On any cluster incident, run Cluster Diagnostic first and read it; then Cluster Recovery; then verify with the application, not just with the cluster status.
• Rehearse both incidents twice a year on a clone of each cluster: stop all Galera nodes at once, break a replica with a deliberate duplicate row, and recover. The clone costs cents and the muscle memory is the point.
• Keep the backup add-on's dumps current, because the recovery add-on fixes membership, not data.

The SRE's note in the incident review: the add-on turned the most stressful database procedure he knows into a dashboard button he trusts, because he has watched it choose the right node twice, once at 3 am and once on purpose.

Frequently Asked Questions

Does the add-on work with the multi-region MariaDB packages?

Yes. It supports MySQL, MariaDB and Percona clusters created from the marketplace, including the multi-region variants, in primary-secondary, primary-primary, Galera and XtraDB topologies.

Can automatic recovery lose data?

It selects the most advanced node as the source and resynchronises the others from it, so committed data on that node is preserved. Writes that reached only a node being resynchronised, for example a divergent write made directly on a replica, are discarded. In Galera, synchronous certification means such divergence should not exist.

How do I stop a Galera cluster safely so this never happens?

Stop the nodes one at a time, so the last node to stop is marked safe to bootstrap, and start that node first when bringing the cluster back. Stopping the whole environment from the dashboard does this sequentially; a power event does not, which is what the add-on is for.