Enabling Binary Logs on Galera Nodes for PITR

This setup guide extends Point-in-Time Recovery in Multi-Master Galera and answers a question that must be settled long before an incident: how do you configure binary logging across a Galera cluster so that point-in-time recovery is actually possible? The trap is subtle — a Galera node with log_bin enabled but log_slave_updates left off will happily write a binary log that looks complete but silently omits every transaction that originated on another node. When a bad DELETE runs on peer node 2, that node’s binlog records it, but node 1’s does not, so a recovery from node 1’s logs cannot even see the event to stop before it. This page shows you the exact parameters that make every node’s binlog a complete, replayable record of all cluster writes, plus the GTID and retention settings that let you target recovery precisely.

Context: Why Binlog Config Is Different in Multi-Master #

On a standalone MariaDB server, log_bin alone gives you a full transaction history, because every write is a local write. Galera breaks that assumption. Each node executes locally originated transactions and applies write-sets replicated from its peers through the write-set certification process. By default, only the locally originated transactions are written to that node’s binary log; the applied replica-stream write-sets are not. The result is a binary log that is complete from the perspective of one node’s clients but blind to everything the rest of the Galera cluster did — useless as the basis for cluster-wide point-in-time recovery.

log_slave_updates=ON closes the gap by instructing the server to also log the transactions it applies as a replica of the group. With it enabled on every node, any single node’s binary log becomes a faithful, ordered record of all cluster writes, which is exactly what PITR replay consumes. This one setting is the difference between a recoverable cluster and a Galera cluster that only discovers its blind spot during an outage.

Solution: Configure Complete, Recoverable Binary Logging #

Apply the same binlog block to every node, then verify each one logs the full group stream. The block sits in the [mysqld] section alongside your Galera identity settings from the wsrep.cnf Configuration Deep Dive.

Step 1 — Enable the binary log and full group logging #

Turn on the binary log, force row format for deterministic replay, and — the critical line — enable log_slave_updates so applied write-sets are logged locally:

[mysqld]
# --- Binary logging for PITR ---
log_bin                    = /var/lib/mysql/binlog
log_bin_index              = /var/lib/mysql/binlog.index
binlog_format              = ROW          # mandatory for Galera and deterministic replay
log_slave_updates          = ON           # log peer write-sets too -> complete PITR source
server_id                  = 1            # MUST be unique per node

binlog_format=ROW is already required by Galera, so it is not an extra cost. server_id must be unique on every node even though Galera does not use it for its own replication — the binary log infrastructure and any attached async replicas do, and duplicate IDs corrupt replication topologies.

Step 2 — Set GTID alignment for precise recovery targets #

Galera stamps its own GTIDs on write-sets in a dedicated domain. Enabling wsrep_gtid_mode aligns the server’s GTID with Galera’s so you can express a recovery stop point as an exact GTID rather than a fuzzy timestamp:

[mysqld]
# --- GTID alignment so PITR can stop at an exact transaction ---
wsrep_gtid_mode            = ON
wsrep_gtid_domain_id       = 100          # identical on every node in this cluster
gtid_domain_id             = 100          # match the wsrep domain
log_slave_updates          = ON           # required for GTID continuity across nodes

wsrep_gtid_domain_id must be identical across the Galera cluster so every node agrees on the domain that identifies group transactions; a divergent value fragments the GTID sequence and defeats GTID-based replay. Keep gtid_domain_id matched to it.

Step 3 — Set retention so logs reach back far enough #

Binary logs consume disk, so MariaDB purges them on a schedule. Retention defines the horizon PITR can reach: if logs expire after a day, you cannot recover to two days ago. Use the modern binlog_expire_logs_seconds rather than the deprecated expire_logs_days, and size it to your recovery-point objective plus a margin:

[mysqld]
# --- Retention: keep enough history to cover your recovery window ---
binlog_expire_logs_seconds = 604800       # 7 days; supersedes expire_logs_days
max_binlog_size            = 512M         # rotate at a manageable file size

Seven days of retention lets PITR reach any point in the last week; balance it against disk capacity from Galera Cluster Hardware Requirements. Note that binlog_expire_logs_seconds and expire_logs_days are mutually exclusive — set only the seconds form.

Step 4 — Apply with a rolling restart #

log_bin and log_slave_updates are not dynamic; enabling them requires a restart. Roll the change out one node at a time, waiting for Synced before moving on, exactly as in Graceful Node Join and Leave Procedures:

sudo systemctl restart mariadb
# wait for Synced before the next node:
mysql -N -B -e "SHOW STATUS LIKE 'wsrep_local_state_comment';"

Parameter Reference #

The two parameters operators most often get wrong are log_slave_updates (left OFF, silently breaking PITR) and server_id (accidentally duplicated across nodes). Both fail quietly: nothing errors at configuration time, and the damage only surfaces during recovery or when attaching an async replica.

Verification #

Prove that binary logging is active and that it captures the full group stream — the second check is the one that matters. First confirm the basics on each node:

SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'log_bin',              -- expect ON
  'log_slave_updates',    -- expect ON  <- the critical one
  'binlog_format',        -- expect ROW
  'wsrep_gtid_mode',      -- expect ON
  'server_id'             -- must be unique per node
);

Then prove the group-stream capture empirically: write a row on node 2, and confirm it appears in node 1’s binary log. If log_slave_updates is working, node 1 logged a transaction it did not originate:

# On node 2:
mysql -e "INSERT INTO app.pitr_probe (note) VALUES ('written on node2');"

# On node 1, inspect the latest binlog for that transaction:
sudo mysqlbinlog --base64-output=DECODE-ROWS -vv \
  $(mysql -N -B -e "SHOW BINARY LOGS;" | tail -1 | awk '{print "/var/lib/mysql/"$1}') \
  | grep -A3 pitr_probe

Seeing the peer-originated INSERT in node 1’s decoded binlog is the definitive confirmation that this node is a complete PITR source. A small Python check can assert the flags across the fleet and handle the wsrep contention codes 1213 (deadlock) and 1205 (lock wait timeout):

import sys
import pymysql

REQUIRED = {"log_bin": "ON", "log_slave_updates": "ON", "binlog_format": "ROW"}

def audit(host: str) -> bool:
    try:
        conn = pymysql.connect(host=host, user="monitor", password="secret", connect_timeout=5)
    except pymysql.err.OperationalError as exc:
        print(f"[FATAL] {host}: {exc}", file=sys.stderr)
        return False
    try:
        with conn.cursor() as cur:
            cur.execute("SHOW GLOBAL VARIABLES WHERE Variable_name IN "
                        "('log_bin','log_slave_updates','binlog_format','server_id')")
            v = {name: val for name, val in cur.fetchall()}
    except pymysql.err.OperationalError as exc:
        if exc.args and exc.args[0] in (1213, 1205):
            print(f"[WARN] {host} contended ({exc.args[0]}); retry", file=sys.stderr)
            return False
        raise
    finally:
        conn.close()
    ok = all(v.get(k) == want for k, want in REQUIRED.items())
    print(f"[{'OK' if ok else 'FAIL'}] {host}: {v}")
    return ok

if __name__ == "__main__":
    hosts = sys.argv[1:] or ["127.0.0.1"]
    server_ids = []  # collect to check uniqueness in a fuller version
    sys.exit(0 if all(audit(h) for h in hosts) else 1)

Edge Cases & Gotchas #

• log_slave_updates OFF is the silent PITR killer. It is the single most common misconfiguration, because a Galera cluster runs perfectly with it off — until you try to recover and discover the binlogs never captured other nodes’ writes. Audit it explicitly across the whole fleet, not just the node you happen to back up from.
• Duplicate server_id corrupts replica topologies. Galera does not use server_id for its own replication, so operators clone a node image and forget to change it. The duplicate is harmless until you attach an async replica or set up cross-cluster replication, at which point transactions are dropped as “own” events. Assign a unique ID per node at provisioning time.
• Retention must outlast your backup interval plus RPO. If you take full backups weekly but keep only three days of binlogs, you cannot recover to a point four days ago even though the backup exists — replay has no logs to roll forward from. Set binlog_expire_logs_seconds to at least the backup interval plus your recovery-point objective.

Related #

• Point-in-Time Recovery in Multi-Master Galera — the recovery procedure these logs make possible
• Restoring One Node Without Triggering a Full SST — single-node restore that pairs with binlog replay
• Backup & Restore Sync — the parent guide to backups and recovery strategy
• wsrep.cnf Configuration Deep Dive — where the binlog block lives alongside Galera identity settings