bench backup Is Not a Backup Strategy: Binlogs and Point-in-Time Recovery for ERPNext

Ask a Frappe team what their backup strategy is and you will usually hear "we run bench backup every night and push it to object storage". That is a copy. It is not a strategy. It answers exactly one question - "what did the database look like at 2am?" - and it answers it badly, because a nightly dump means your worst case is losing an entire working day of invoices, payments and stock movements.

Here is the thesis: a backup you cannot restore to an arbitrary minute is not a backup, it is a souvenir. What you need is a physical base backup plus continuous binary logs, and a restore procedure you have personally executed at least once. Everything below is that.

Decide your RPO before you choose tools

Two numbers drive every decision:

• RPO (Recovery Point Objective) - how much data you can afford to lose. Nightly dump only = up to 24 hours. Nightly dump plus binlogs shipped every 5 minutes = 5 minutes.
• RTO (Recovery Time Objective) - how long you can be down. Restoring a 40GB logical dump on a 4 vCPU VPS takes 3 to 6 hours because it replays every INSERT and rebuilds every index. Restoring a physical backup of the same database takes as long as it takes to copy 40GB, plus a minute.

For a business running on ERPNext, an honest answer is usually "we can lose 15 minutes and be down for one hour". That answer rules out logical dumps as your primary recovery path.

Turn on binary logging. Today.

Without binlogs, point-in-time recovery is mathematically impossible. Add this and restart once:

[mysqld]
server_id                      = 1
log_bin                        = /var/log/mysql/mariadb-bin
log_bin_index                  = /var/log/mysql/mariadb-bin.index
binlog_format                  = ROW
binlog_row_image               = FULL
max_binlog_size                = 256M
sync_binlog                    = 1
innodb_flush_log_at_trx_commit = 1

# MariaDB 10.6 and later
binlog_expire_logs_seconds     = 1209600
# older versions use:
# expire_logs_days             = 14

Why these exact values:

• binlog_format = ROW records the before and after image of each changed row. STATEMENT format replays SQL text, which can diverge when a query is non-deterministic. ROW is also what makes flashback-style forensic reading possible.
• sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1 mean a committed transaction is genuinely on disk. If your binlog and your data diverge after a power cut, your PITR is built on sand. Yes, this costs write throughput. Buy the durability.
• Binlogs on a separate filesystem from the datadir if you can. A binlog partition filling up stops writes; a binlog on the same disk as your data means one disk failure takes both.

Verify:

SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;
SHOW MASTER STATUS;

The base backup: mariadb-backup, not mysqldump

mariadb-backup (the binary is also available as mariabackup) copies InnoDB data files while the server is running and captures the redo log stream to make them consistent. Create a dedicated user:

CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'strong-password-here';
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariabackup'@'localhost';
FLUSH PRIVILEGES;

Full backup:

TARGET=/backup/full-$(date +%Y%m%d-%H%M)
mariadb-backup --backup \
  --target-dir="$TARGET" \
  --user=mariabackup \
  --password='strong-password-here'

The target directory must be empty or must not exist. Then, and this is the step people forget until the night of the incident, prepare it:

mariadb-backup --prepare \
  --use-memory=4G \
  --target-dir="$TARGET"

Set --use-memory close to your innodb_buffer_pool_size. An unprepared backup is not restorable, and you will only discover that during a fire.

Incrementals for the days between

mariadb-backup --backup \
  --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full-20260720-0200 \
  --user=mariabackup --password='strong-password-here'

# chain the next one off the previous incremental
mariadb-backup --backup \
  --target-dir=/backup/inc2 \
  --incremental-basedir=/backup/inc1 \
  --user=mariabackup --password='strong-password-here'

Merging them uses --apply-log-only for every step except the last:

mariadb-backup --prepare --apply-log-only --target-dir=/backup/full-20260720-0200
mariadb-backup --prepare --apply-log-only --target-dir=/backup/full-20260720-0200 --incremental-dir=/backup/inc1
mariadb-backup --prepare                  --target-dir=/backup/full-20260720-0200 --incremental-dir=/backup/inc2

Where mysqldump still earns its place

Keep a weekly logical dump too. Physical backups are version-and-architecture bound; a logical dump restores anywhere and lets you extract one table without restoring the world.

mariadb-dump --single-transaction --quick --routines --events --triggers \
  --master-data=2 --max-allowed-packet=512M \
  _5f1a2b3c4d5e6f70 | zstd -T0 -9 > /backup/erp-weekly-$(date +%F).sql.zst

--single-transaction gives a consistent snapshot without locking InnoDB tables. --master-data=2 writes the binlog file and position as a comment at the top of the dump, which is exactly the coordinate PITR needs. And note that from MariaDB 11.0 the mysqldump symlink is deprecated - use mariadb-dump.

The actual disaster, and the actual recovery

Real scenario. At 14:20 on a Wednesday, a well-meaning accounts clerk runs a bulk operation that cancels and amends 412 Sales Invoices for the wrong fiscal period. Nobody notices until 16:05. Your last full backup is from 02:00 that morning. Between 02:00 and 14:19 the business posted a full morning of legitimate transactions you cannot afford to throw away.

Step 0: stop the bleeding. Put the site in maintenance mode so no further writes land.

bench --site erp.example.co.ke set-maintenance-mode on
bench --site erp.example.co.ke disable-scheduler

Step 1: preserve the binlogs. Before anything else, copy them somewhere safe. They are the only record of the morning's work.

mariadb -e "FLUSH BINARY LOGS;"
sudo cp -a /var/log/mysql/mariadb-bin.* /backup/binlog-rescue/

Step 2: restore the base backup to a scratch server. Never recover in place on production until you have validated the result. Note that --copy-back requires an empty datadir.

sudo systemctl stop mariadb
sudo mv /var/lib/mysql /var/lib/mysql.broken
sudo mkdir /var/lib/mysql

sudo mariadb-backup --copy-back --target-dir=/backup/full-20260722-0200
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mariadb

Step 3: find where the backup stopped. mariadb-backup records the binlog coordinate it captured.

cat /backup/full-20260722-0200/xtrabackup_binlog_info
# mariadb-bin.000117    41288294

On MariaDB 11.1 and later this file is named mariadb_backup_binlog_info. Check for both.

Step 4: find the exact position of the bad transaction. Decode the row events to human-readable pseudo-SQL and search:

mariadb-binlog --base64-output=DECODE-ROWS --verbose \
  --start-datetime="2026-07-22 14:00:00" \
  --stop-datetime="2026-07-22 14:40:00" \
  /backup/binlog-rescue/mariadb-bin.000117 \
  | grep -n -E "tabSales Invoice|^# at |^#26072[0-9] " | less

You are looking for the # at NNNNN marker immediately before the first event of the damaging transaction. Say it is # at 918442017. Cross-check with:

SHOW BINLOG EVENTS IN 'mariadb-bin.000117' FROM 918440000 LIMIT 20;

Step 5: replay everything up to that point, and nothing after.

mariadb-binlog \
  --start-position=41288294 \
  --stop-position=918442017 \
  --disable-log-bin \
  /backup/binlog-rescue/mariadb-bin.000117 > /tmp/replay.sql

wc -l /tmp/replay.sql
mariadb --force=0 < /tmp/replay.sql

Two details that matter. --disable-log-bin prevents the replayed statements from being written into the new server's own binlog, which would double-count if you later use it as a replication source. And if the window spans several binlog files, pass them all in one command in chronological order, because --start-position applies only to the first named file and --stop-position only to the last:

mariadb-binlog --start-position=41288294 --stop-position=918442017 --disable-log-bin \
  mariadb-bin.000117 mariadb-bin.000118 mariadb-bin.000119 > /tmp/replay.sql

If you know the wall-clock moment but not the byte offset, use --stop-datetime="2026-07-22 14:19:00" instead. It is less precise, because it stops at the first event whose timestamp is at or after that value, but it is fast and usually good enough.

Step 6: skip the bad window and keep what came after. This is the advanced move most guides omit. If legitimate transactions continued after 14:20 while the damage was happening, replay in two ranges: up to the start of the bad transaction, then from the end of it onwards. Be honest about the risk - if the later transactions read data the bad transaction wrote, you will produce a logically inconsistent ledger. On accounting data, prefer stopping cleanly and re-entering the afternoon's work by hand.

Step 7: validate before you go live.

SELECT COUNT(*), MAX(creation) FROM `tabSales Invoice`;
SELECT COUNT(*) FROM `tabGL Entry` WHERE posting_date = CURDATE();
SELECT SUM(debit) - SUM(credit) AS imbalance
FROM `tabGL Entry` WHERE is_cancelled = 0;

That last one must return zero. Then run bench --site ... execute erpnext.accounts.utils.check_and_delete_linked_reports-style sanity checks appropriate to your version, clear the cache, and only then take the site out of maintenance mode.

The rules that keep this boring

• Test restores on a schedule. Once a month, restore last night's backup onto a scratch VM and run the balance-sheet query. A backup that has never been restored has a roughly 30 percent chance of being unusable, in my experience.
• 3-2-1. Three copies, two media, one offsite. For teams in Nairobi or Lagos, "offsite" over a 20 Mbps uplink means a 40GB restore takes over four hours to download. Keep the most recent full backup on local disk and only the archives in object storage.
• Ship binlogs continuously, not nightly. A cron that runs mariadb-binlog --read-from-remote-server --raw --stop-never against the server, or simply rsync every 5 minutes, converts a 24 hour RPO into a 5 minute one.
• Back up more than the database. sites/*/private/files, sites/*/public/files and site_config.json are not in the SQL dump. bench --site all backup --with-files covers these; keep it as a secondary track.
• Encrypt before it leaves the building. Your database contains customer PII, salaries and margins. age or gpg on the archive, keys stored somewhere that is not the same server.
• Purge binlogs by policy, never by rm. Use PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 14 DAY); so the index file stays consistent.

Write the recovery steps down, with the real paths and hostnames, and keep them somewhere reachable when the server is down. At 16:05 on a Wednesday, nobody remembers flag syntax.