MySQL and MariaDB Logical Backups with Point-in-Time Recovery

A logical backup of MySQL or MariaDB is a SQL dump: data, schema, and optionally routines, written as statements that any compatible server can replay. That portability is the whole point of logical backups, and mysqldump provides it. But a dump alone is consistent only for the instant it ran. Reaching true point-in-time recovery (PITR) means pairing that base dump with the binary log, and automating both.

Why a dump alone is not point-in-time

A mysqldump started at 02:00 captures the state at 02:00. If the host dies at 23:00, the newest dump restores to 02:00 and you lose 21 hours of writes. The binary log records every committed transaction, in order. Restore the base, then replay binary log events from the base time up to either the failure moment or a defined stop time. Base plus binary log is the only way to make RPO approach zero without exotic tooling.

The baseline dump with the right flags

For InnoDB tables, --single-transaction takes an internally consistent snapshot without locking the whole database, which matters on a live VPS. Add --quick to stream rows directly to the output, and --routines --triggers --events so you export more than raw table data.

mysqldump --single-transaction --quick --routines --triggers --events   --no-tablespaces --set-gtid-purged=OFF   -u backup -p'CHANGE_ME' appdb   | gzip -9 > /var/backups/mysql/appdb.20260801.sql.gz

On MariaDB the same flags work with mariadb-dump. Verify the compressed archive on disk with gunzip -t before you trust it.

Turn on binary logging, deliberately

Binary logging must be on before base dumps begin, not after. Configure it in the server section, then restart once:

[mysqld]
server-id = 1
log-bin = /var/lib/mysql/binlog
binlog_format = ROW
expire_logs_days = 7

Row format makes replay from a specific point deterministic, which statement format cannot guarantee. Note where the base dump begins so you know exactly where playback starts:

SHOW BINARY LOGS;
SHOW MASTER STATUS;

Automate: nightly base, hourly log copies

The binary logs are only useful while they exist, so move them off the box long before expire_logs_days reaps them. Copy new logs to a backup host hourly:

#!/usr/bin/env bash
# /usr/local/bin/binlog_capture.sh — run hourly from cron
set -euo pipefail
mysql -u backup -p'CHANGE_ME' -N   -e "SHOW BINARY LOGS;" > /var/backups/mysql/binlogs/manifest.txt
rsync -a /var/lib/mysql/binlog.*   backup-host:/var/backups/mysql/binlogs/

Daily, take the full logical dump that the replay will start from, first.

Replay to an exact point

Play the base dump, then pipe binary logs through a time window into the restored database:

gunzip -c /var/backups/mysql/appdb.20260801.sql.gz | mysql -u root appdb
mysqlbinlog --start-datetime="2026-08-01 02:00:01"             --stop-datetime="2026-08-01 22:41:05"   /var/backups/mysql/binlogs/binlog.000014   /var/backups/mysql/binlogs/binlog.000015   | mysql -u root appdb

Every transaction committed between 02:00 and the failure moment at 22:41 is now present. Rehearse the replay once a month in a scratch database so the stop-time logic stays fresh.

Takeaway

Point-in-time recovery is a consistent logical base plus binary logs captured before rotation deletes them. Automate the base nightly, move the logs off-box hourly, and practice a replay monthly. When a MariaDB or MySQL VPS on Netbay needs this pipeline, a fresh instance from netbayhosts.in gives you a clean staging ground in under a minute.