MariaDB Binlog: The Point-in-Time Recovery Setup Most Guides Skip

Every guide I've found on MariaDB's binary log spends 80% of its words on replication. Set up a replica, sync it, done. That's useful, but it's not what saves you when someone runs DELETE FROM orders without a WHERE clause on a production database at 2pm on a Tuesday. Point-in-time recovery (PITR) on a single VM is what saves you then, and the setup is just different enough from replication that most tutorials gloss over it completely.

I got burned on this about four years ago with a small e-commerce client. We had nightly mysqldumps. We did not have binlog enabled. The client's developer ran a bad migration, wiped a table, and we lost six hours of orders. We restored from the previous night's dump and spent the rest of the day manually re-entering orders from email confirmations. Never again.

What the Binlog Actually Does for You Here

The binary log is a sequential record of every change made to the database - every INSERT, UPDATE, DELETE, DDL statement. It was designed for replication, but it's equally useful as a change journal you can replay forward from a known good backup.

The mental model: take a full backup at midnight, enable binlog continuously, and at any point during the day you can restore the midnight snapshot and then replay the binlog up to any specific timestamp or position. If the bad query ran at 2:14pm, you replay up to 2:13pm and you're whole.

This is not complicated technology. It's been in MySQL/MariaDB forever. The part most guides skip is the operational setup: where to put the binlog files, how to make sure they survive a crash, how long to keep them, and what the actual recovery workflow looks like so you're not reading man pages at midnight.

The Config That Actually Works

Here's the my.cnf block I use for a typical single-VM setup. This goes in /etc/mysql/mariadb.conf.d/50-server.cnf on Debian/Ubuntu, or directly in [mysqld] in /etc/my.cnf on RHEL-based systems.

[mysqld]
# Binary log - point-in-time recovery
log_bin                     = /var/log/mysql/mariadb-bin
log_bin_index               = /var/log/mysql/mariadb-bin.index
binlog_format               = ROW
expire_logs_days            = 7
max_binlog_size             = 100M
server_id                   = 1
sync_binlog                 = 1
binlog_row_image            = FULL

A few things here that matter:

binlog_format = ROW - Use ROW, not STATEMENT. Statement-based logging records the SQL as written. If a stored proc or trigger produces nondeterministic output, you can't reliably replay it. ROW records the actual before/after state of each row changed. It's larger on disk but it's trustworthy.

sync_binlog = 1 - This tells MariaDB to call fsync() after every binlog write. It's slower (you'll feel it on write-heavy workloads), but without it you can lose the last few transactions if the server crashes before the OS flushes the buffer. For most of my clients, the safety is worth it.

binlog_row_image = FULL - Logs the complete before and after image of every changed row. The default in older versions is MINIMAL, which only logs the primary key plus changed columns. FULL is what you want for PITR because it gives you complete data to reason about.

expire_logs_days = 7 - Keeps a week of binlogs. Tune this based on your disk budget. I generally keep 7-14 days. If you're taking nightly full backups, 2 days is technically sufficient, but 7 gives you breathing room.

After changing the config, restart MariaDB and confirm it took:

SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'binlog_format';
SHOW MASTER STATUS;

That last one should show your current binlog file and position. If it returns empty, something's wrong with your config.

The Full Backup Side of the Equation

Binlog alone is useless without a baseline to restore from. I use mariabackup (the MariaDB fork of Percona XtraBackup) for this, not mysqldump. Here's why: mysqldump locks tables (or at minimum uses FLUSH TABLES WITH READ LOCK for consistency), which is painful on a live system. mariabackup does a hot backup using the InnoDB engine's own mechanisms and records the exact binlog position at backup time.

#!/bin/bash
# /usr/local/bin/mariadb-backup.sh

BACKUP_DIR="/var/backups/mariadb"
DATE=$(date +%Y%m%d_%H%M%S)
DEST="${BACKUP_DIR}/${DATE}"

mariabackup --backup \
  --target-dir="${DEST}" \
  --user=backup_user \
  --password="$(cat /etc/mysql/backup.passwd)"

mariabackup --prepare --target-dir="${DEST}"

tarball="${BACKUP_DIR}/${DATE}.tar.gz"
tar -czf "${DEST}.tar.gz" -C "${BACKUP_DIR}" "${DATE}"
rm -rf "${DEST}"

echo "Backup complete: ${tarball}"

The --prepare step is critical. After a backup, the data files are in a partially applied state (like InnoDB after a crash). --prepare runs crash recovery on the backup so it's consistent and ready to restore without touching the live server. I've seen people skip this and then wonder why their restore takes forever.

The backup directory will contain an xtrabackup_binlog_info file that looks like:

mariadb-bin.000042  47291883

That file and position is your starting point for replaying the binlog during recovery. Write it down, keep it with the backup.

The Actual Recovery Workflow

Something bad happened. Here's what I actually do, in order.

Step 1: Find the bad event in the binlog.

mysqlbinlog --start-datetime="2024-03-15 13:00:00" \
            --stop-datetime="2024-03-15 15:00:00" \
            /var/log/mysql/mariadb-bin.000042 | grep -A 5 "DELETE FROM orders"

You want the binlog position just before the disaster. mysqlbinlog can filter by datetime or by position. I usually start with datetime to find the approximate range, then switch to position for precision.

Step 2: Restore the backup.

mariabackup --copy-back \
  --target-dir=/var/backups/mariadb/20240315_000001 \
  --datadir=/var/lib/mysql

chown -R mysql:mysql /var/lib/mysql

Don't start MariaDB yet.

Step 3: Replay the binlog up to just before the bad event.

The position in xtrabackup_binlog_info from the backup was 47291883 in file mariadb-bin.000042. The bad DELETE happened at position 51847203. So I replay from the backup position up to, but not including, the bad event:

mysqlbinlog --start-position=47291883 \
            --stop-position=51847200 \
            /var/log/mysql/mariadb-bin.000042 \
            /var/log/mysql/mariadb-bin.000043 | \
  mysql -u root -p

Note that I included the next binlog file too. If your window crosses a binlog rotation boundary, you need all the files in the range. mysqlbinlog handles multiple files in sequence correctly.

Step 4: Verify, then resume.

Check that your data looks right before bringing the application back up. With ROW format logging and binlog_row_image = FULL, you can also use mysqlbinlog --verbose to actually read the before/after row images in human-readable form - useful for confirming exactly what got destroyed.

Gotchas That Will Catch You

The binlog directory needs to be separate from your data directory. If your disk fills up and MariaDB can't write to the binlog, it stops. If the binlog and data are on the same volume, a runaway binlog can corrupt your data directory by filling the volume. I put binlogs on a separate mount or at minimum a separate partition.

expire_logs_days doesn't delete logs until a new log file is created. If you have a low-traffic server that never rotates logs, old files stick around. You can run PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY in a cron job as a safety valve.

DDL statements are always statement-logged, even in ROW mode. ALTER TABLE, DROP TABLE, CREATE INDEX - these go in as SQL text. That's usually fine, but if you have a migration that drops a column and you try to replay the binlog across that point, your application might break in confusing ways. Know where your DDL events are.

Test your recovery process before you need it. I do a quarterly restore drill for any client where data loss is genuinely catastrophic. It takes a couple hours. It has also caught configuration problems twice that I didn't know existed.

When I'd Reach for This (and When I Wouldn't)

This setup is appropriate for any VM running a MariaDB instance where you care about the data: SaaS apps, internal business tools, healthcare data, e-commerce. The overhead is real but small - sync_binlog = 1 will cost you some write latency, and binlog files eat disk, but on modern hardware it's not significant for typical web app workloads.

I wouldn't lean on this as my only recovery strategy. PITR gets you back from application-level mistakes - bad queries, bad migrations, someone running the wrong script. It doesn't help you if the disk fails and takes the binlog with it. Binlogs need to ship offsite too, ideally with the same frequency as your full backups. I use a simple rsync to object storage after each backup run.

If you're running a managed database like RDS or PlanetScale, most of this is handled for you and you don't need to think about it. But if you're on a VPS or bare metal doing your own database ops, this is not optional infrastructure. It's the difference between a recoverable incident and a catastrophic one.

One More Thing

Set up a monitoring check that verifies the binlog is still running. SHOW MASTER STATUS returning an empty result set means logging stopped, and you won't know until you need it. A simple script that checks this and pages you is worth fifteen minutes of your time right now.

The setup isn't glamorous. It's about twenty lines of config and a backup script. But it's the thing that lets me tell a client "we can get you back to 2:13pm" instead of "we can get you back to last night."