Server — MariaDB — Binary Log

The borgmatic backup configuration from earlier in this series takes a daily snapshot of everything on February, including the MariaDB data directory. If February loses a drive tomorrow, you restore from last night’s backup and lose at most 24 hours of data. For most of what February stores, that is acceptable. PowerDNS zone data changes infrequently. ChirpStack device registrations change rarely. The loss window is bounded and manageable.

The binary log changes that calculation. The binary log records every change made to every database in real time, as a sequence of events that can be replayed forward from any backup point. With a binary log running alongside the borgmatic backup, the recovery window shrinks from “since last backup” to “since the last binlog entry before the failure.” On a server that takes changes continuously, that can be the difference between losing hours of data and losing seconds.

The binary log is not free. It adds write overhead, consumes disk space proportional to the rate of database changes, and adds complexity to the restore procedure. For February’s workload, those costs are modest. This article covers the decision, the configuration, and the restore procedure.

What the binary log records

The binary log records every statement or row change that modifies data: INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE, DROP TABLE, and so on. It does not record SELECT queries. It records changes in sequence with timestamps, giving you a complete ordered history of every modification made to the database.

This serves two purposes. Replication uses the binary log to propagate changes from a primary server to replicas. Point-in-time recovery uses it to replay changes from a known good backup state forward to any specific moment in time.

February has no replicas, so replication is not the motivation here. Point-in-time recovery is.

Binlog formats

MariaDB supports three binary log formats, set with the binlog_format configuration option:

STATEMENT records the SQL statement that caused the change. Compact, but occasionally unreliable: non-deterministic functions like NOW() or RAND() may produce different results when replayed, leading to data divergence.

ROW records the actual row data before and after each change, not the statement. Larger log files, but perfectly reliable for replay regardless of how the change was triggered. This is the right format for point-in-time recovery.

MIXED uses STATEMENT by default and switches to ROW automatically for statements that would be unsafe in STATEMENT mode. A reasonable compromise for replication. For pure point-in-time recovery on a standalone server, ROW is cleaner and more explicit.

Use ROW.

Configuration

Add the following to /etc/mysql/mariadb.conf.d/50-server.cnf under [mysqld]:

# Binary log
server_id              = 1
log_bin                = /var/log/mysql/mariadb-bin
binlog_format          = ROW
binlog_row_image       = FULL
expire_logs_days       = 7
max_binlog_size        = 100M

server_id is required when binary logging is enabled. It is an arbitrary integer that uniquely identifies this server instance. Since February has no replicas, the value does not matter functionally, but it must be set.

log_bin sets the base path for binary log files. MariaDB appends a sequence number to create individual log files: mariadb-bin.000001, mariadb-bin.000002, and so on. Storing them in /var/log/mysql/ keeps them alongside the other MariaDB logs and within the borgmatic backup scope.

binlog_row_image = FULL records the complete before and after image of every changed row. The alternative MINIMAL records only the columns that changed. FULL is safer for point-in-time recovery because it preserves the complete state at every point.

expire_logs_days = 7 automatically purges binary log files older than seven days. This bounds the disk usage. Seven days gives you a recovery window that extends back one week from any point, which is more than sufficient for catching accidental changes that were not noticed immediately.

max_binlog_size = 100M caps individual log files at 100 MB. When a file reaches this size, MariaDB rotates to a new one. This makes the files manageable when inspecting or replaying them.

Restart MariaDB to enable binary logging:

sudo systemctl restart mariadb

Confirm binary logging is active:

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

The first command should show log_bin = ON. The second should list the current binary log file with its size.

Monitoring disk usage

Binary logs accumulate continuously. For February’s workload, the rate of change is low: PowerDNS updates a handful of records when you add or change DNS entries, and ChirpStack writes device join data occasionally. The daily log volume should be small, well under the 100 MB per-file limit.

Check current binary log disk usage:

sudo du -sh /var/log/mysql/mariadb-bin.*
sudo mariadb -e "SHOW BINARY LOGS;"

If log volume is unexpectedly high, something is writing to the database more frequently than expected. The slow query log and SHOW PROCESSLIST are the first places to look.

To manually purge logs older than a specific date without waiting for the automatic expiry:

sudo mariadb -e "PURGE BINARY LOGS BEFORE '2026-05-03 00:00:00';"

Never delete binary log files manually with rm. Always use PURGE BINARY LOGS so MariaDB can update its index file. Deleting files directly leaves the index out of sync and causes errors on startup.

Point-in-time recovery procedure

The recovery scenario: the borgmatic backup ran at 2am. At 11am, an application bug corrupts data in a table. You notice at 11:30am. You need to restore the database to its state at 10:59am, not to its state at 2am.

Step one: restore the borgmatic backup

Stop the affected application to prevent further writes. Restore the MariaDB data directory from the borgmatic backup. This returns the database to its state at 2am.

sudo systemctl stop mariadb
sudo borgmatic restore --archive latest --path /var/lib/mysql
sudo systemctl start mariadb

Step two: identify the binlog position at the target time

The binary logs from between the backup and the failure should still exist in /var/log/mysql/, since they were created after the backup was taken. Use mariadb-binlog to find the log position corresponding to 10:59am:

sudo mariadb-binlog --start-datetime="2026-05-10 02:00:00"   --stop-datetime="2026-05-10 10:59:00"   /var/log/mysql/mariadb-bin.000001   /var/log/mysql/mariadb-bin.000002   > /tmp/recovery.sql

This extracts all events between 2am and 10:59am into a SQL file. The --stop-datetime is one minute before the corruption occurred, giving a clean stopping point.

Step three: replay the binlog events

sudo mariadb < /tmp/recovery.sql

This replays every change recorded between 2am and 10:59am against the restored backup, bringing the database forward to the target state.

Step four: verify and restart

Check the affected data to confirm the restore reached the expected state. Then restart the application.

sudo systemctl start your-application

A note on the borgmatic backup and binary logs

The borgmatic configuration backs up /var/lib/mysql, which is the MariaDB data directory. It also now backs up /var/log/mysql, which is where the binary logs live. Both paths should be in the borgmatic source_directories list.

Confirm this is the case:

sudo borgmatic config validate
grep -A 20 'source_directories' /etc/borgmatic/config.yaml

If /var/log/mysql is not in the source directories, add it. Without backing up the binary logs, point-in-time recovery after a complete server failure is not possible, because the logs would be lost along with everything else.

There is a subtlety worth knowing: a file-level backup of a running MariaDB data directory is not guaranteed to be consistent, because InnoDB may have uncommitted transactions in flight when borgmatic takes the snapshot. MariaDB handles this gracefully on restart via crash recovery, but the restored database will always roll back any transaction that was not committed at the time of the backup. For February’s use case, this is acceptable. For a database processing financial transactions, a proper hot backup with mariabackup would be required. That is a more complex setup than February needs.

The honest position

For February’s current workload, the binary log is a nice-to-have rather than a requirement. PowerDNS zone data can be reconstructed. ChirpStack device registrations are a nuisance to re-enter but not catastrophic to lose. The daily borgmatic backup is sufficient for most realistic failure scenarios.

Enable it anyway. The configuration is a handful of lines. The disk overhead is negligible for a server making infrequent database changes. The one scenario where it matters — an accidental bulk delete or a bug that corrupts data between backup windows — is exactly the scenario you cannot predict in advance.

The binary log is insurance. The premium is low. The payout is the difference between a two-minute recovery and a half-day one.