MariaDB Binary Logs and Point-in-Time Recovery

A nightly dump saves you from a dead disk. It does not save you from a DELETE without a WHERE at 15:41 when the dump ran at 02:00. Binary logs are the stream of changes after that dump. Point-in-time recovery (PITR) is: restore the dump, then replay the binlog up to the moment before the mistake. Without both pieces you have a backup, not a recovery story.

MariaDB writes binlogs if you turn them on. Many Ubuntu installs still ship with them off. Turn them on before you need them, size the retention, and run a PITR drill on a second VPS. The feature is not exotic. The exotic part is discovering binlog_format and expire_logs_days during an incident.

Enable binary logs with a retention you can afford

ROW format is the default you want for PITR and for replicas. MIXED is a compromise. STATEMENT can break on non-deterministic functions. Keep binlogs on High-Speed SSD, but not necessarily on the same filesystem as ibdata if you have a second disk; they are sequential writes and they are the first thing a full disk will kill.

# /etc/mysql/mariadb.conf.d/92-binlog.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mariadb-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 256M
sync_binlog = 1

server-id must be unique among any hosts that will ever replicate. sync_binlog = 1 flushes the binlog at commit so a crash does not lose events you thought were durable. expire_logs_days (or binlog_expire_logs_seconds on newer series) must cover your dump interval plus a margin. A 7-day expire with a nightly dump is comfortable. A 1-day expire with a dump that sometimes fails is how PITR becomes impossible on Tuesday.

Restart, then confirm:

SHOW VARIABLES WHERE Variable_name IN ('log_bin','binlog_format','expire_logs_days');
SHOW MASTER STATUS;
SHOW BINARY LOGS;

If log_bin is OFF, the drop-in did not load. Check the includedir and the file suffix. MariaDB only reads .cnf and .ini from the conf.d directories.

Take dumps with the binlog coordinates recorded. --master-data=2 writes a comment into the dump with the file and position. That comment is the starting point for mysqlbinlog. Without it you guess, and guessing is how you replay the DELETE you were trying to undo.

Replay up to, not through, the bad event

The drill is the product. Restore yesterday's dump on a throwaway host, then apply binlogs to a stop datetime.

# restore the dump first on the drill host
sudo gzip -dc /var/backups/mariadb/appdb.2026-05-03-0200.sql.gz | mariadb
# dump header contains CHANGE MASTER / CHANGE REPLICATION SOURCE coordinates
# replay from that file/position to just before 15:41:00
mysqlbinlog --start-position=194   --stop-datetime="2026-05-03 15:41:00"   /var/log/mysql/mariadb-bin.000214   /var/log/mysql/mariadb-bin.000215   | mariadb

--stop-datetime is wall clock on the original server. If you know the exact position of the bad statement from mysqlbinlog --verbose, stop at that position instead. Read the events around the incident before you replay anything onto a host you care about. mysqlbinlog --verbose --base64-output=DECODE-ROWS turns ROW events into something a human can inspect.

Common failures:

• Binlogs were purged before you noticed the bug. Retention shorter than your detection time is not retention.
• You replayed onto production instead of a drill host.
• The dump was taken without coordinates, so you replayed from the wrong origin and duplicated key errors filled the error log.
• Disk filled with binlogs because expire_logs_days was unset and a replica was still holding an old file.

FLUSH LOGS before a dump if you want the dump to line up on a new binlog file. That makes the restore notes tidier. It is not required if --master-data recorded the position.

PITR does not replace the dump

Binary logs are not a full backup. They are a delta. If you lose the datadir and the dump, leftover binlogs cannot rebuild the schema you had last month. Keep the dump, keep the binlogs that cover the dump's age, and keep a copy of both off the database host. On a Lucknow VPS, that off-host copy can be a second instance or any store that does not share the live disk.

Practice with a cheap, reversible mistake: insert a marker row, dump, insert another, then PITR to before the second insert and prove only the marker remains. Write down the commands that worked. That file is the runbook.

Turn on ROW binary logs, stamp dumps with coordinates, and rehearse a stop-datetime replay until it is boring. A DELETE without a WHERE is then an afternoon, not a restoration from last night. You can stand up a drill VPS on Netbay in Lucknow in under 60 seconds and practice the replay at netbayhosts.in.