Point-in-Time Recovery in Multi-Master Galera

This guide builds on the backup foundations in Backup & Restore Sync and solves the hardest recovery scenario a synchronous cluster faces: rolling an entire multi-master Galera group back to a specific instant — the moment before a bad deployment, an accidental DELETE, or a corrupting migration — and then rebuilding the live cluster around that recovered state. Point-in-time recovery (PITR) is not a single command; it is a disciplined sequence of restoring a physical mariabackup image, replaying binary logs up to an exact timestamp or GTID, bootstrapping a fresh primary component from that one recovered node, and re-seeding the remaining members so the whole group converges on the rolled-back dataset. This page is the end-to-end procedure for database administrators and platform teams running MariaDB 10.6 through 11.x with Galera 4, where the twist is that recovery must respect quorum, grastate.dat, and the write-set replication contract rather than treating the node as a standalone server.

Concept: What PITR Means When Every Node Is a Writer #

On a standalone server, PITR is well-trodden: restore a full backup, replay binary logs to a stop point, done. Galera complicates every step because there is no single source of truth to restore to — all three nodes hold the same certified state, so a logical error committed on one node was replicated and certified everywhere within milliseconds. You cannot fix it by promoting a “good” replica, because there is no lagging replica; the mistake is uniform across the group. Recovery therefore means taking the Galera cluster down to a single authoritative node, reconstructing the correct state on that one node from backup plus binlog replay, and then treating it as the seed of a brand-new primary component that the other nodes rejoin from scratch.

The three phases are strict and ordered. Restore puts a physical backup image onto one node’s datadir and replays binlogs to the target. Bootstrap turns that node into a one-member primary component with galera_new_cluster, which requires safe_to_bootstrap: 1 in grastate.dat. Re-seed brings the remaining nodes back so they take a State Snapshot Transfer from the recovered node, adopting its rolled-back dataset. Binary logs are the linchpin of the restore phase, which is why every node must have them enabled ahead of time — the configuration is detailed in Enabling Binary Logs on Galera Nodes for PITR.

Prerequisites & Environment #

PITR only works if you prepared for it before the incident. Confirm all of the following:

• A recent, verified full backup taken with mariabackup, stored off the Galera cluster. The streaming-backup workflow that produces these images is mariabackup Streaming SST & Backups.
• Binary logs enabled with log_slave_updates=ON on every node, so replicated write-sets are themselves logged and thus replayable — without this, the binlogs on a node contain only its own local writes and PITR is impossible. Full setup is in Enabling Binary Logs on Galera Nodes for PITR.
• GTID mode understood. Galera maintains its own wsrep_gtid_domain_id; recording it lets you stop replay at a precise GTID instead of a wall-clock time, which is more accurate than --stop-datetime.
• A maintenance window with the application stopped. PITR discards committed data after the target instant, so the Galera cluster must not accept writes during recovery. Coordinate an application-level freeze.
• mariabackup and matching MariaDB binaries installed on the recovery node, at the same major/minor version as the backup was taken from.

Step-by-Step: Roll the Galera Cluster Back to a Point in Time #

The sequence is: stop everything, restore one node, replay binlogs to the target, bootstrap it alone, then rejoin the peers.

Step 1 — Stop the Galera cluster and pick the recovery node #

Halt MariaDB on every node so no member keeps replicating the bad state. Choose one node as the recovery target — any node will do, since all held identical state:

# On every node:
sudo systemctl stop mariadb

# Confirm none are running before touching any datadir.
systemctl is-active mariadb || echo "stopped"

Step 2 — Restore the full backup image #

Prepare the mariabackup image (apply its redo log to make it consistent), then copy it into an emptied datadir on the recovery node. Never restore over a live datadir:

# Make the physical backup internally consistent.
sudo mariabackup --prepare --target-dir=/backups/full-2026-07-16

# Empty the datadir and copy the prepared image back.
sudo rm -rf /var/lib/mysql/*
sudo mariabackup --copy-back --target-dir=/backups/full-2026-07-16
sudo chown -R mysql:mysql /var/lib/mysql

The single-node restore mechanics, including how to do this without triggering a later SST, are expanded in Restoring One Node Without Triggering a Full SST.

Step 3 — Replay binary logs up to the target instant #

The restored image is consistent as of the backup time; binlog replay rolls it forward to the moment before the damage. Identify the binlog files spanning backup-time to target-time, then pipe them through mysqlbinlog with a stop condition. Start the server first with replication disabled so replay does not re-broadcast to a Galera cluster:

# Start standalone (no Galera) so replay is local and does not replicate.
sudo mariadbd --wsrep-provider=none --skip-networking --datadir=/var/lib/mysql &

# Replay from the backup's binlog coordinate up to the target timestamp.
sudo mysqlbinlog \
  --start-position=4 \
  --stop-datetime="2026-07-16 14:29:45" \
  /var/lib/mysql/binlog.000042 /var/lib/mysql/binlog.000043 \
  | sudo mysql -u root

Prefer a GTID stop point when you have one — --stop-position or a GTID range is exact, whereas --stop-datetime can straddle a busy second. Inspect the binlog around the incident with mysqlbinlog --base64-output=DECODE-ROWS -vv to find the precise event to stop before.

Step 4 — Mark the node safe and bootstrap it #

Shut down the standalone server, then edit grastate.dat so the recovered node is allowed to form a new primary component. Only this node may bootstrap:

sudo mysqladmin -u root shutdown

# Authorize this node to seed a fresh primary component.
sudo sed -i 's/^safe_to_bootstrap:.*/safe_to_bootstrap: 1/' /var/lib/mysql/grastate.dat

# Form the new one-member primary component.
sudo galera_new_cluster

galera_new_cluster starts MariaDB with the bootstrap flag, creating a Primary component of exactly one node whose dataset is your rolled-back state. The single-bootstrap rule — never bootstrap two nodes — is covered in Bootstrapping Your First Galera Cluster.

Step 5 — Re-seed the remaining nodes #

Wipe the datadir on each remaining node and start MariaDB normally. Because their state is gone, they take a State Snapshot Transfer from the recovered node and adopt its rolled-back dataset:

# On each peer node:
sudo systemctl stop mariadb
sudo rm -rf /var/lib/mysql/*   # force a clean SST from the recovered seed
sudo systemctl start mariadb   # joins via SST, adopting the recovered state

Bring them up one at a time, waiting for each to reach Synced, so the growing cluster never loses quorum during the rebuild.

Parameter Deep-Dive: The Knobs That Govern PITR #

log_slave_updates=ON is the parameter operators most often discover missing at the worst possible moment: without it a node’s binary log records only transactions that originated locally, so a DELETE executed on another node is absent from this node’s binlog and cannot be replayed or stopped-before. wsrep_gtid_mode and wsrep_gtid_domain_id together let you express the recovery target as a GTID, which is immune to the sub-second ambiguity of timestamp stops. Retention (binlog_expire_logs_seconds) sets the horizon of what PITR can reach — if logs expire after 24 hours, you cannot recover to two days ago. These are configured cluster-wide per Enabling Binary Logs on Galera Nodes for PITR.

Verification & Health Checks #

After the rebuild, prove three things: the target state is correct, the Galera cluster is whole, and every node is synced. First confirm the recovered data reflects the rollback — check a sentinel row you know was affected by the incident:

-- The bad DELETE removed order 88123; after PITR to before it, the row is back.
SELECT id, status, updated_at FROM orders WHERE id = 88123;

Then confirm quorum and sync across the rebuilt group:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'wsrep_cluster_size',          -- must equal node count
  'wsrep_cluster_status',        -- must be 'Primary'
  'wsrep_local_state_comment'    -- must be 'Synced' on every node
);

A Python probe turns the rebuild into a gate. It verifies every node is a synced member of a primary component and handles the wsrep contention codes 1213 (deadlock / certification conflict) and 1205 (lock wait timeout) so a busy node under post-recovery load is not misjudged:

import sys
import mysql.connector
from mysql.connector import errorcode

EXPECT = {"wsrep_cluster_status": "Primary", "wsrep_local_state_comment": "Synced"}

def verify_node(host: str, expected_size: int) -> bool:
    try:
        conn = mysql.connector.connect(host=host, user="monitor", password="secret",
                                       connection_timeout=5, database="information_schema")
    except mysql.connector.Error as err:
        print(f"[FATAL] {host} unreachable: {err}", file=sys.stderr)
        return False
    try:
        cur = conn.cursor()
        cur.execute("SHOW GLOBAL STATUS WHERE Variable_name IN "
                    "('wsrep_cluster_size','wsrep_cluster_status','wsrep_local_state_comment')")
        st = {name: val for name, val in cur.fetchall()}
    except mysql.connector.Error as err:
        if err.errno in (errorcode.ER_LOCK_DEADLOCK, errorcode.ER_LOCK_WAIT_TIMEOUT):
            print(f"[WARN] {host} contended ({err.errno}); retry", file=sys.stderr)
            return False
        raise
    finally:
        conn.close()

    ok = all(st.get(k) == v for k, v in EXPECT.items()) and int(st.get("wsrep_cluster_size", 0)) == expected_size
    print(f"[{'OK' if ok else 'FAIL'}] {host}: {st}")
    return ok

if __name__ == "__main__":
    nodes = sys.argv[1:] or ["127.0.0.1"]
    sys.exit(0 if all(verify_node(h, len(nodes)) for h in nodes) else 1)

Automation Integration #

Scripting PITR end to end is dangerous to improvise mid-incident, so codify it. Store the recovery runbook as an Ansible playbook that stops the Galera cluster, restores the image, prompts for the target timestamp or GTID, replays, bootstraps the recovery node, and re-seeds peers with a serialized rejoin. Gate the destructive steps behind an explicit confirmation variable so a misfire cannot wipe a datadir. Wire the post-recovery verification into the same play using the probe above, and reuse the health patterns in Monitoring Galera Cluster State with Python. The re-seed phase leans on state transfer, so ensure gcache and SST are tuned per gcache Sizing for IST & Fast Recovery before you need them.

Troubleshooting #

Frequently Asked Questions #

Can I point-in-time recover just one table instead of the whole cluster? Physical mariabackup PITR restores the whole instance, so recovering a single table means restoring to a scratch server, replaying to the target, and exporting just that table (for example with mysqldump or a transportable tablespace) back into the live cluster. Doing it in place on the live group is not possible because the restore replaces the entire datadir and re-seeds every peer.

Why must log_slave_updates be ON for Galera PITR? In Galera each node applies write-sets from its peers, but by default those replicated transactions are not written to the local binary log — only locally originated statements are. With log_slave_updates=ON, every applied write-set is also logged, so the binlog on any node is a complete, replayable record of all cluster writes. Without it you cannot replay or stop-before a transaction that ran on a different node.

How do I choose between a timestamp and a GTID stop point? Use a GTID whenever you have wsrep_gtid_mode=ON, because it identifies the exact transaction to stop before and is immune to the ambiguity of multiple events sharing one wall-clock second. Fall back to --stop-datetime only when GTIDs are unavailable, and verify the boundary first with mysqlbinlog --base64-output=DECODE-ROWS -vv so you stop before, not after, the damaging event.

Related #

• Enabling Binary Logs on Galera Nodes for PITR — the log_bin and log_slave_updates setup PITR depends on
• Restoring One Node Without Triggering a Full SST — restoring a single datadir and rejoining via IST
• Backup & Restore Sync — the parent guide to backups and state transfer
• mariabackup Streaming SST & Backups — producing the physical images PITR restores from
• Bootstrapping Your First Galera Cluster — the single-bootstrap rule the recovery node follows