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

Source: https://srvscripts.com/guides/mariadb-point-in-time-recovery-binlogs/
Updated: 2026-10-03
Publisher: srvScripts (https://srvscripts.com/)

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.

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.

**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](/guides/mariadb-dump-vs-mysqldump/) 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](/guides/mariadb-dump-vs-mysqldump/) 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](/scripts/backup-verify/) checks the first two automatically.

## MariaDB point-in-time recovery at a glance

**Official documentation:** [MariaDB documentation](https://mariadb.com/docs/), [Linux man pages](https://man7.org/linux/man-pages/).

**Related guides:** [mariadb-dump vs mysqldump: modern backup commands and wildcard database dumps](https://srvscripts.com/guides/mariadb-dump-vs-mysqldump/) · [How to upgrade MySQL 8.0 to MariaDB 11.8 in WHM without losing databases](https://srvscripts.com/guides/upgrade-mysql-8-to-mariadb-11-8-whm/) · [MySQL 8.4 vs 9.7 LTS: what hosting admins need to know](https://srvscripts.com/guides/mysql-8-4-vs-9-7-lts/).

## 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.