Backing up MariaDB / MySQL to Proxmox Backup Server: a tested method, measured figures

• Inline deduplication: only new chunks are sent. On our internal database, a full daily dump costs only a few more MiB from one pass to the next.
• Client-side encryption: the key stays with you. Without it, nobody can read your dumps, not even us.
• Off-site: backups leave the production server, and the client account has no delete rights by default on NimbusBackup. An air-gapped copy is available on top.
• An application-level dump rather than a VM freeze: a well-known Proxmox forum thread describes MariaDB hanging on overlapping fsfreeze calls. A dump doesn't depend on that mechanism.

Each database is exported to its own file, uncompressed, with --skip-dump-date and --order-by-primary so the content stays identical from one day to the next wherever nothing changed. The directory is then sent as a pxar archive, chunked by content.

Each database is consistent on its own (one transaction per database), not with the others. The script stops as soon as a dump fails, before anything is sent.

#!/bin/bash
# /usr/local/sbin/mariadb-pbs-dump : daily logical dump -> Proxmox Backup Server
set -euo pipefail
. /etc/mariadb-pbs/env
ID="$(hostname --fqdn)"
STAGE=/var/backups/mariadb-pbs
KEY=/root/.config/proxmox-backup/encryption-key.json
CHUNK=1024  # KiB: same dedup as 256 within 0.1 point on our test, 4x fewer chunks

rm -rf "$STAGE"; install -d -m 700 "$STAGE"
mapfile -t DBS < <(mariadb -N -e "SELECT schema_name FROM information_schema.schemata
  WHERE schema_name NOT IN ('information_schema','performance_schema','sys','mysql')")
[ "${#DBS[@]}" -gt 0 ] || { echo "no database listed, aborting" >&2; exit 1; }
# binlog position in the dump header (needed for point-in-time recovery), only if binlogs are on
BINPOS=(); [ "$(mariadb -N -e 'SELECT @@log_bin')" = 1 ] && BINPOS=(--master-data=2)

for db in "${DBS[@]}"; do
  # one file per database, NOT compressed (PBS compresses and deduplicates itself)
  mariadb-dump --single-transaction --skip-dump-date --order-by-primary \
    --routines --events --triggers --hex-blob "${BINPOS[@]}" \
    --databases "$db" > "$STAGE/$db.sql"       # set -e stops here if the dump fails
done
# system tables are NOT dumped raw (reloading them on another version is risky):
# accounts and grants as portable SQL instead (tested with mariadb-dump 11.8)
mariadb-dump --system=users > "$STAGE/_users.sql"
(cd "$STAGE" && sha256sum *.sql > SHA256SUMS)   # lets you self-check a restore later

proxmox-backup-client backup "dumps.pxar:$STAGE" \
  --backup-id "$ID-mariadb-dumps" --chunk-size "$CHUNK" \
  --keyfile "$KEY" --crypt-mode encrypt
rm -rf "$STAGE"

# /etc/cron.d/mariadb-pbs
15 2 * * *   root  /usr/local/sbin/mariadb-pbs-dump     >> /var/log/mariadb-pbs.log 2>&1
*/5 * * * *  root  /usr/local/sbin/mariadb-pbs-binlogs  >> /var/log/mariadb-pbs.log 2>&1

A dump is restored by replaying SQL: every INSERT, every index rebuilt. On 30 GB that can take hours. A physical copy goes back in place at disk speed. On our 5 small databases, our test on an internal database gives 43 s to fetch and bring back a 4.05 GiB physical copy, against about 195 s to fetch and reload the 1.7 GiB dump. But mariadb-backup needs temporary space the size of the database, which our server lacked.

FLUSH TABLES … FOR EXPORT freezes the tables, writes a .cfg file next to each .ibd and blocks writes until UNLOCK TABLES. Meanwhile pxar reads the database directory in place, launched from the same session (the mariadb client's system command) so the lock holds. In metadata mode, unchanged files, like the closed partitions of a time series, aren't even re-read. To keep the lock short from the first night, a pre-pass without lock first uploads almost everything into the same group: its snapshot isn't consistent and is annotated as such.

#!/bin/bash
# /usr/local/sbin/mariadb-pbs-datadir : physical copy per database, NO temporary space
# 1) pre-pass without lock (not consistent, annotated)  2) locked pass: FLUSH TABLES ... FOR EXPORT,
# pxar of the database directory sent FROM THE SAME SESSION, then UNLOCK TABLES.
set -euo pipefail
. /etc/mariadb-pbs/env
ID="$(hostname --fqdn)"
KEY=/root/.config/proxmox-backup/encryption-key.json
O="--chunk-size 1024 --change-detection-mode=metadata --keyfile $KEY --crypt-mode encrypt"
note() { t=$(proxmox-backup-client snapshot list "host/$1" --output-format json |
    python3 -c 'import json,sys; print(max(x["backup-time"] for x in json.load(sys.stdin)))')
  proxmox-backup-client snapshot notes update "host/$1/$(date -u -d @$t +%Y-%m-%dT%H:%M:%SZ)" "$2"; }

# schema + system tables as portable SQL (needed to rebuild a fresh instance)
S=/var/backups/mariadb-pbs-schema; rm -rf "$S"; install -d -m 700 "$S"
mariadb-dump --no-data --routines --triggers --all-databases > "$S/schema.sql"
mariadb-dump --system=all > "$S/system.sql"
proxmox-backup-client backup "schema.pxar:$S" --backup-id "$ID-mariadb-schema" $O
rm -rf "$S"

for db in $(mariadb -N -e "SELECT schema_name FROM information_schema.schemata
    WHERE schema_name NOT IN ('mysql','information_schema','performance_schema','sys')"); do
  T=$(mariadb -N -e "SELECT GROUP_CONCAT(table_name) FROM information_schema.tables
      WHERE table_schema='$db' AND engine='InnoDB' AND table_type='BASE TABLE'")
  [ "$T" != "NULL" ] || continue
  G="$ID-mariadb-datadir-$db"
  # 1. pre-pass, no lock, low priority: uploads almost everything while the database runs
  PRE_START=$(date +%s)
  nice -n 19 ionice -c3 proxmox-backup-client backup "$db.pxar:/var/lib/mysql/$db" --backup-id "$G" $O
  note "$G" "PRE-PASS without lock: NOT CONSISTENT, do not restore"
  # 2. locked pass: only files changed since the pre-pass are re-read (metadata mode).
  #    Under the lock, every file modified since the pre-pass began is touched: it is then fully
  #    re-read, even if a write during the pre-pass left the same mtime (ext4 timestamp granularity).
  mariadb "$db" <<SQL
FLUSH TABLES $T FOR EXPORT;
system find /var/lib/mysql/$db -type f -newermt @$((PRE_START-2)) -exec touch {} +
system proxmox-backup-client backup $db.pxar:/var/lib/mysql/$db --backup-id $G $O
UNLOCK TABLES;
SQL
  note "$G" "CONSISTENT (FLUSH TABLES FOR EXPORT): restore with IMPORT TABLESPACE"
done

# On a FRESH instance of the same major version (mysql_install_db, then start MariaDB)
. /etc/mariadb-pbs/env; ID="$(hostname --fqdn)"; K="--keyfile /root/.config/proxmox-backup/encryption-key.json"
# pick the snapshots annotated CONSISTENT, never the pre-pass ones
proxmox-backup-client snapshot list "host/$ID-mariadb-datadir-mydb"     # read the notes column

# 1. schema and system tables
proxmox-backup-client restore "host/$ID-mariadb-schema/<time>" schema.pxar /srv/restore/schema $K
mariadb < /srv/restore/schema/schema.sql
mariadb < /srv/restore/schema/system.sql

# 2. tablespace files of the database (.ibd + .cfg)
proxmox-backup-client restore "host/$ID-mariadb-datadir-mydb/<time>" mydb.pxar /srv/restore/mydb $K
D=/var/lib/mysql/mydb

# 3a. non-partitioned table (repeat per table: that's also the partial restore)
mariadb mydb -e "ALTER TABLE customers DISCARD TABLESPACE"
cp /srv/restore/mydb/customers.ibd /srv/restore/mydb/customers.cfg $D/ && chown mysql:mysql $D/customers.*
mariadb mydb -e "ALTER TABLE customers IMPORT TABLESPACE"

# 3b. partitioned table: MariaDB refuses DISCARD on it -> one partition at a time via EXCHANGE
mariadb mydb -e "CREATE TABLE _imp LIKE measurements; ALTER TABLE _imp REMOVE PARTITIONING;
                 ALTER TABLE _imp DISCARD TABLESPACE"
cp "/srv/restore/mydb/measurements#P#p202609.ibd" $D/_imp.ibd
cp "/srv/restore/mydb/measurements#P#p202609.cfg" $D/_imp.cfg; chown mysql:mysql $D/_imp.*
mariadb mydb -e "ALTER TABLE _imp IMPORT TABLESPACE;
                 ALTER TABLE measurements EXCHANGE PARTITION p202609 WITH TABLE _imp; DROP TABLE _imp"

# 4. check the pages of every restored tablespace (catches a torn page), BEFORE importing in production
for f in /srv/restore/mydb/*.ibd; do innochecksum "$f" || echo "PAGE ERROR: $f"; done
# 5. check: row count + logical checksum, to compare with the source
mariadb -N mydb -e "SELECT COUNT(*), BIT_XOR(CRC32(CONCAT_WS('#', id, measured_at, v))) FROM measurements"

What we measured

• Container prototype (table partitioned by month, 2.45 million rows): 1st upload with a 2.5 s lock; 2nd upload after 50,000 inserts: 18.6 MiB re-read out of 160.8, 0.3 s lock.
• Prototype restore with IMPORT TABLESPACE and EXCHANGE PARTITION: row count and logical checksum identical to the source.
• Pre-pass without lock on our server (6 databases): 45 GiB → 9.6 GiB stored (−79%), read in 14 min at low priority. That's about twice the dump: the physical copy holds the indexes and the free space inside the .ibd files.
• First locked pass on our server (30/09/2026), two days after the pre-pass: lock held 138.5 s on the time-series database (2.51 GiB re-read and sent out of 41.7 GiB, closed partitions reused without re-reading), then 4.4 s, 2.2 s, 2.0 s, 1.7 s and 0.4 s on the five other databases. This first pass covered two days of changes: an ordinary night's lock will be shorter.

• Writes to the locked tables are blocked during the locked pass: that's why the pre-pass exists, and why the locked upload doesn't run at low priority.
• InnoDB only. The mysql system database goes as portable SQL (mariadb-dump --system=all), with the schema.
• It isn't a datadir you can start on as is: there's no system tablespace and no logs. Restoring goes through a fresh instance, the schema, then the import. To start the engine directly on the backup, you need a full mariadb-backup copy.
• MariaDB refuses DISCARD TABLESPACE on a partitioned table: import partition by partition, through a temporary table and EXCHANGE PARTITION.
• Pre-pass snapshots aren't restorable: filter on the "CONSISTENT" note. The import requires the same schema and a compatible version.
• In a mariadb client system line, a ; turns the whole line into SQL, even inside single quotes: our first production attempt failed that way (error 1064). Chain with && or call a script.
• metadata mode only re-reads a file if its modification time changed. On ext4 that timestamp has a granularity of a few milliseconds: a write during the pre-pass could leave the same time, and the locked pass would reuse a half-written page. Hence the touch, under the lock, of every file modified since the pre-pass began, which forces a full re-read, and the innochecksum check in the restore procedure.

The SHA256SUMS file produced by the script lets you check a restore bit for bit. Then reload the dump into a throwaway instance: that's the only real test. Restore drills run by our team are part of our managed services, not of the backup offer.

. /etc/mariadb-pbs/env; ID="$(hostname --fqdn)"
KEY=/root/.config/proxmox-backup/encryption-key.json

# list snapshots (paths are host/<backup-id>/<RFC 3339 date>, not an epoch)
proxmox-backup-client snapshot list "host/$ID-mariadb-dumps"

# restore every dump of a snapshot, then check it bit for bit
SNAP="host/$ID-mariadb-dumps/2026-09-28T12:06:51Z"
proxmox-backup-client restore "$SNAP" dumps.pxar /srv/restore --keyfile "$KEY"
cd /srv/restore && sha256sum -c SHA256SUMS

# or pick a single database file interactively
proxmox-backup-client catalog shell "$SNAP" dumps.pxar --keyfile "$KEY"
#   pxar:/ > find --select *.sql      # --select is required, otherwise nothing is restored
#   pxar:/ > restore-selected /srv/restore

On Rocky Linux 10.2, Proxmox's raw static binary looks for certificates in /usr/lib/ssl, which doesn't exist, and fails:

certificate validation failed - Certificate fingerprint was not confirmed.
Error: client error (Connect)
Caused by:
    0: error:0A000086:SSL routines:tls_post_process_server_certificate:certificate verify failed:../ssl/statem/statem_clnt.c:2124:

The package from our unofficial proxmox-backup-client repository (RPM, Arch, Alpine, arm64) adds the two links needed: installed with dnf and GPG checks, it restored our dumps with the key alone, identical SHA256. Otherwise, SSL_CERT_FILE=/etc/pki/tls/certs/ca-bundle.crt works around the problem (tested on Rocky 10.2 with the raw binary).

A backup-id can't contain a /, and the client account can't create namespaces. We use the server's fully qualified name followed by the backup type, in separate groups: schedules differ, and PBS deduplicates from one snapshot to the next within the same group. If a namespace was created for the server at onboarding, use it.

ID="$(hostname --fqdn)"
--backup-id "$ID-mariadb-dumps"      # daily dump
--backup-id "$ID-mariadb-binlogs"    # every 5 minutes
--backup-id "$ID-mariadb-datadir"    # mariadb-backup, if you use it
# or, when a namespace was created for the server: --ns "$ID" --backup-id dumps

Retention is configured on the Nimbus side, since the client can't delete: that's also what protects your backups if the server is compromised.