Server — MariaDB — Backup

The borgmatic backup sub-series earlier in this collection covered February’s general backup strategy: borgmatic runs daily, backs up source directories to the NAS, and keeps a sensible retention history. The /var/lib/mysql data directory was included in those source directories.

That approach has a problem specific to databases. InnoDB is designed for concurrent access. At any given moment, some pages in the data directory may be mid-write, some transactions may be uncommitted, and the write-ahead log may contain changes that have not yet been flushed to the data files. Copying these files while MariaDB is running produces a snapshot that is internally inconsistent. MariaDB can recover from this on startup via crash recovery, but the result is a backup you cannot reliably restore to a specific known state.

This matters in practice. If you restore the file-level backup and MariaDB runs crash recovery, you will get a database that is consistent at some point between the last checkpoint and the copy time, but you cannot know exactly what that point is or whether all committed transactions made it in. For a homelab server this is usually acceptable. For a database you care about precisely, it is not.

The right solution is a logical backup: a dump of the database content as SQL statements, taken in a way that is consistent with the running transaction state. Borgmatic supports this natively for MariaDB, and that is what this article covers.

The borgmatic MariaDB hook

Borgmatic’s mariadb_databases hook takes a logical dump of each configured database and streams it directly into the Borg archive, without writing to disk first. The dump is taken with a consistent read, meaning the snapshot reflects a single transaction-consistent point in time regardless of what else is writing to the database at that moment.

This replaces the file-level backup of /var/lib/mysql for the purposes of database recovery. The file-level backup can stay in source_directories for completeness, but the borgmatic hook is what you would actually use to restore a database.

Configuration

Add the mariadb_databases section to /etc/borgmatic/config.yaml. It sits at the top level alongside repositories and source_directories:

mariadb_databases:
  - name: powerdns
    username: root
    options: "--single-transaction --skip-lock-tables"

  - name: pdnsadmin
    username: root
    options: "--single-transaction --skip-lock-tables"

A few notes on these settings.

username: root — borgmatic connects to MariaDB to run the dump. On Ubuntu 24.04, root uses unix_socket authentication, so no password is needed. Borgmatic running as root (which it does by default for system backups) connects as the MariaDB root user automatically.

--single-transaction — tells mariadb-dump to wrap the entire dump in a single transaction, producing a consistent snapshot without locking the tables. This is the right option for InnoDB databases. It ensures the dump reflects a single consistent point in time.

--skip-lock-tables — prevents mariadb-dump from acquiring table locks before dumping. Combined with --single-transaction, this allows the dump to proceed without blocking application writes. Without it, mariadb-dump would lock every table it reads, potentially causing application timeouts.

To add all databases in a single entry:

mariadb_databases:
  - name: all
    username: root
    options: "--single-transaction --skip-lock-tables"
    exclude_databases:
      - information_schema
      - performance_schema
      - sys

Using name: all is convenient as February’s database list grows. The exclude_databases list keeps the system databases out of the dump, since they are managed by MariaDB itself and do not need to be backed up.

Validating the configuration

sudo borgmatic config validate

A clean validation confirms borgmatic can parse the new section.

Running the first backup with the hook

sudo borgmatic create --verbosity 1 --stats

Watch the output for lines showing the MariaDB dump in progress. You should see something like:

mariadb: Dumping database powerdns
mariadb: Dumping database pdnsadmin

If you see an error about mariadb-dump not being found, install it:

sudo apt install mariadb-client

After the backup completes, confirm the dumps are in the archive:

sudo borgmatic list --archive latest

The archive contents should include .sql dump files for each configured database alongside the regular file backups.

Restoring from a borgmatic database backup

Restoring a specific database from the borgmatic archive:

sudo borgmatic restore --database powerdns

This extracts the SQL dump from the latest archive and pipes it directly into MariaDB, recreating the database. By default it restores from the most recent archive. To restore from a specific archive:

sudo borgmatic restore --archive february-2026-05-10T02:00:00 --database powerdns

Before restoring, stop any application that is actively using the database to prevent writes arriving mid-restore:

sudo systemctl stop pdns
sudo borgmatic restore --database powerdns
sudo systemctl start pdns

Verify the restored database is intact by querying it:

sudo mariadb -e "SELECT COUNT(*) FROM powerdns.records;"
dig february.home.arpa @127.0.0.1

The standalone fallback: mysqldump

The borgmatic hook is the primary backup method. But borgmatic may not always be available: a corrupted borgmatic installation, a scenario where you need to send a database dump somewhere by email, or a quick pre-maintenance snapshot before a schema change. For those situations, knowing how to take a standalone dump directly is useful.

sudo mariadb-dump   --single-transaction   --skip-lock-tables   --databases powerdns pdnsadmin   --add-drop-database   | gzip > /tmp/mariadb-$(date +%Y%m%d-%H%M%S).sql.gz

This produces a compressed SQL dump of both application databases. The --add-drop-database flag includes DROP DATABASE IF EXISTS statements before each database creation, making the dump self-contained for a clean restore.

To dump all user databases at once:

sudo mariadb-dump   --single-transaction   --skip-lock-tables   --all-databases   --add-drop-database   | gzip > /tmp/mariadb-all-$(date +%Y%m%d-%H%M%S).sql.gz

Confirm the dump is not empty and is valid gzip:

ls -lh /tmp/mariadb-*.sql.gz
zcat /tmp/mariadb-*.sql.gz | tail -5

The last few lines of a valid dump should show UNLOCK TABLES; and similar SQL housekeeping statements, not binary noise or an error message.

Restoring from a standalone dump

zcat /tmp/mariadb-20260510-020000.sql.gz | sudo mariadb

This pipes the decompressed SQL into MariaDB. The --add-drop-database flag in the dump ensures any existing databases with the same names are dropped cleanly before being recreated.

For a specific database only:

zcat /tmp/mariadb-20260510-020000.sql.gz   | sudo mariadb --one-database powerdns

What the borgmatic hook replaces and what it does not

The borgmatic mariadb_databases hook produces consistent, restorable database dumps. It is what you should use for routine recovery.

What it does not replace: the binary log. A borgmatic backup captures the database state at the moment the backup ran. Everything written between the last backup and a failure is captured only in the binary log. The two are complementary: restore from the borgmatic dump to get back to the last backup point, then replay the binary log forward to the failure point as described in the binary log article.

What it does not need to replace: the /var/lib/mysql entry in source_directories. Keeping that in the borgmatic config provides a belt-and-suspenders second copy. It also preserves the InnoDB redo log and binary logs alongside the data files, which are needed for the point-in-time recovery procedure. Remove it only if disk space on the NAS becomes a constraint.

Pre-maintenance snapshot pattern

Any time a schema-modifying operation is planned — an application upgrade, a manual ALTER TABLE, importing a large dataset — take a standalone dump first:

sudo mariadb-dump   --single-transaction   --skip-lock-tables   --add-drop-database   --databases powerdns pdnsadmin   > /tmp/pre-maintenance-$(date +%Y%m%d-%H%M%S).sql

This takes seconds, costs nothing, and means that if the operation goes wrong you can restore to exactly the pre-maintenance state without waiting for the next borgmatic archive. Keep the file until the operation is confirmed successful, then delete it.