MariaDB point-in-time recovery with binary logs (and 13.0’s innodb_log_archive)

How to enable and retain binary logs on a MariaDB hosting server so a customer's accidental DELETE can be undone to the second, the exact mariadb-binlog replay procedure, and what the innodb_log_archive feature in MariaDB 13.0 changes for recovery.

Published 29 Sep 2026Updated 3 Oct 2026 7 min read By srvScripts team

A nightly dump gets a customer back to last night. Point-in-time recovery gets them back to 14:37, one minute before the plugin update that emptied their orders table. The mechanism is the binary log: a record of every change the server made, which can be replayed on top of a restored dump up to any chosen moment. Most hosting servers have it switched off because it was never needed until it was. This guide turns it on, keeps it manageable, and walks through an actual recovery, then explains what MariaDB 13.0’s new redo-log archiving adds.

Applies to MariaDB 11.x binary logs; innodb_log_archive in MariaDB 13.0

Short answer: Turn on log_bin with binlog_format = ROW, sync_binlog = 1 and about seven days of retention, and take nightly dumps with --master-data=2 --flush-logs so each one records its binary log position. To recover, restore last night’s copy of the affected schema into a recovery database, find the offending event’s timestamp with mariadb-binlog --verbose, then replay from the dump’s position with --stop-datetime one second before the damage and --rewrite-db pointing at the recovery schema, and swap the data back. MariaDB 13.0’s innodb_log_archive adds physical redo-log archiving for the same purpose, but it is a rolling-release feature until the 13.3 LTS.

Enable binary logging

Add to /etc/my.cnf.d/binlog.cnf under [mysqld] and restart in a quiet moment:

[mysqld]
log_bin = /var/lib/mysql-binlog/mariadb-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7
max_binlog_size = 512M
sync_binlog = 1
server_id = 1

Create the directory owned by mysql first. ROW format records the actual row changes rather than the statement, which is what makes it possible to replay a single table or undo a change precisely. sync_binlog = 1 costs a little write latency and guarantees the log survives a crash. Seven days of retention is enough for most support tickets; size it against your disk after a week of observation. On a busy shared server the binary logs can be tens of gigabytes a day, so put them on a separate filesystem where possible.

Confirm:

mariadb -e "SHOW GLOBAL VARIABLES LIKE 'log_bin%'; SHOW BINARY LOGS;"

Make the nightly dump PITR-ready

The dump must record which binary log position it corresponds to, otherwise you cannot know where replay should start:

mariadb-dump --all-databases --single-transaction --routines --events --triggers --hex-blob \
  --master-data=2 --flush-logs | gzip -1 > /backup/all-$(date +%F).sql.gz

--master-data=2 writes a commented CHANGE MASTER line at the top of the dump with the log file name and position. --flush-logs rotates the binary log at the same moment, so the dump corresponds to the start of a fresh file, which makes the replay bookkeeping simpler. On 11.x the option is also spelled --source-data=2 and both are accepted; check mariadb-dump --help for your build. Our mariadb-dump guide covers the rest of the dump options.

Recovery walkthrough

A customer reports that their shop_orders database lost its data at around 14:35 today. The procedure:

Find the exact moment. Decode the binary logs from the last dump forward and look for the destructive statement or, in ROW format, the mass delete event:

mariadb-binlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql-binlog/mariadb-bin.000123 | grep -nE 'DELETE FROM `shop_orders`|TRUNCATE|DROP' | head

Note the timestamp and the position just before the offending event; --verbose prints each event’s # at NNNN position and #YYMMDD HH:MM:SS header.

Restore last night’s copy of that schema into a recovery schema on the same server. Do not restore over the live schema yet. Extracting one schema from an all-databases dump is awkward (the client’s --one-database option works but reads the whole file), which is why the mariadb-dump guide recommends per-database dumps alongside the full one:

mariadb -e "CREATE DATABASE shop_orders_recovery"
zcat /backup/db/shop_orders-$(date +%F).sql.gz | sed '1,/^USE `shop_orders`/{/^USE `shop_orders`/d}' | mariadb shop_orders_recovery

The sed strips the USE line so the restore lands in the recovery schema rather than the live one; check the head of your dump for CREATE DATABASE lines and remove those too if present.

Replay the binary log from the dump’s recorded position up to just before the damage, rewriting the schema name so the replay lands in the recovery copy:

mariadb-binlog --start-position=4 --stop-datetime="2026-09-29 14:34:59" \
  --database=shop_orders --rewrite-db='shop_orders->shop_orders_recovery' \
  /var/lib/mysql-binlog/mariadb-bin.000123 | mariadb

Take --start-position from the CHANGE MASTER comment in the dump. If the damage spans several log files, list them all on the command line in order.

Check the recovered copy has the data, then swap it in, either by renaming tables or by dumping the recovered schema and importing it over the live one during a brief maintenance window.

What innodb_log_archive changes

MariaDB 13.0, released on 15 September 2026, introduced innodb_log_archive, which keeps a continuous archive of the InnoDB redo log rather than letting it be overwritten. Combined with a physical backup from mariadb-backup, it allows recovery to any point by applying archived redo up to a target LSN, without the binary log at all. It is physical rather than logical, so it is faster for large data sets and captures everything, including changes the binary log filters out.

It is a rolling release feature today and should not be relied upon on production hosting until it lands in the 13.3 LTS. When it does, the likely pattern is a weekly mariadb-backup full backup plus continuous redo archiving for recovery, with binary logs kept for the per-schema surgical restores that hosting support actually performs. Read the 13.x documentation for the exact variable set once your target LTS is announced.

Common pitfall

Binary logs filling the disk. expire_logs_days only purges when the server rotates a log, and a server that was restarted with logs pointing at a small partition can fill it in a day. Monitor the directory and purge manually if needed:

mariadb -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;"

Never delete the files with rm; the server tracks them in an index file and becomes confused.

Verify

Do a rehearsal before you need it. Create a throwaway table, insert rows, note the time, delete them, then recover the table to a scratch schema using the steps above. The whole exercise takes twenty minutes and turns a theoretical capability into one your team has performed. Then confirm the pieces stay in place:

mariadb -e "SHOW BINARY LOGS;" | tail -3
zcat /backup/all-$(date +%F).sql.gz | grep -m1 'CHANGE MASTER'
du -sh /var/lib/mysql-binlog

Logs should rotate daily, the latest dump should carry a position, and the directory size should be stable. Our backup verify script checks the first two automatically.

MariaDB point-in-time recovery at a glance

MariaDB point-in-time recovery with binary logs summary card: Turn on log_bin with binlog_format = ROW, sync_binlog = 1 and about seven days of retention, and take nightly dumps… — In short: Turn on log_bin with binlog_format = ROW, sync_binlog = 1 and about seven days of retention, and take nightly dumps with –master-data=2 –flush-logs so each one records its binary log position.

Official documentation: MariaDB documentation, Linux man pages.

Frequently asked questions

Can I recover a single database or table with MariaDB binary logs?

Yes. mariadb-binlog --database=name filters the replay to one schema, and --rewrite-db='name->name_recovery' lands it in a scratch copy so the live data is untouched until you have checked the result. Recovering a single table means replaying the schema into the recovery copy and moving just that table across.

How much disk space do MariaDB binary logs use on a shared server?

With ROW format and binlog_row_image = FULL, a busy shared server can write tens of gigabytes a day. Keep the logs on a separate filesystem, size retention after a week of observation, and purge with PURGE BINARY LOGS rather than rm, since the server tracks the files in an index.

Does innodb_log_archive in MariaDB 13.0 replace binary logs for recovery?

Not for hosting support work. It enables physical recovery to any LSN from a mariadb-backup full backup plus archived redo, which is faster for large data sets, but it is a rolling-release feature until the 13.3 LTS and cannot do the per-schema surgical restores that binary logs allow.

Maintenance record

This guide changes servers, data or security settings, so we re-check it against current versions on a fixed schedule. Take a backup or snapshot before you start.

Maintained by: srvScripts editorial team
Supported versions: MariaDB 11.x binary logs; innodb_log_archive in MariaDB 13.0
Last full review: 3 Oct 2026
Next review: 1 Apr 2027
Sources: mariadb.com/docs