Recovering Galera cluster from crash

I have unfortunately been in a situation with a lot of deadlocks in a MariaDB Galera cluster. So I learned the best method to recover a crashed cluster, whether I wanted to or not.

Note

This guide is written from my perspective and the particular issues I was having. Obviously you should have backups and I'm not responsible for anything that goes wrong in your life and/or marriage.

Step 1: Is it crashed?

The mytop tool will show a bunch of processes stuck and their Time just ticking up without moving. They might even be stuck in Killed state.

As far as I know there is no way to recover from this. Your application might just pile on new connections and threads but the old ones won't ever finish.

Step 2: Force shutdown

If a nice systemd shutdown doesn't work, you'll have to pkill -9 mysqld on all nodes eventually.

Just make sure they're all dead before moving on to the next step.

In a disaster mode this might have happened due to power outage, flooding or terrorist attack.

Step 3: Find the node with the latest data

Run this on all nodes and note which has the highest log position.

# sudo -u mysql mysqld --wsrep-recover
2016-11-18 01:42:16 36311 [Note] WSREP: Recovered position: 37bb872a-ad73-11e6-819f-f3b71d9c5ada:345628

The position is the last number after the colon.

Note the node with the highest number and consider that your master node from now on.

Step 4: Startup master node

You have two options here

1. Try to recover your old cluster

   On relatively modern clusters (at least 10.1 and up) this is simple.

   $ sudo galera_recovery

   This should update your grastate.dat file in your datadir with the correct position. So you don't have to yourself. It should also set safe_to_bootstrap: 1.

   $ sudo systemctl start mariadb

   Now just start it and see what happens.

2. Start a new cluster with old data

   This is also dead simple.

   $ sudo galera_new_cluster

   This should use systemd to start MariaDB as a new cluster node.

Step 5: Join your other nodes

With one node up and healthy, and your old configuration with wsrep_cluster_address you can simply start them with systemctl.

Warning

Make sure to purge their datadir first. MariaDB should do this but I've been in situations where an SST has failed and then kept failing until I purged every single file from the datadir. I won't type that command out because you have to be absolutely sure you're writing it on the correct node.

$ sudo systemctl start mariadb

Monitor SST progress and wait for each to come up before starting the next.

---

Last update: August 13, 2021