Sauvegarder MariaDB / MySQL vers Proxmox Backup Server : méthode testée, chiffres mesurés

• Déduplication au fil de l'eau : seuls les morceaux nouveaux partent. Sur notre base interne, un dump quotidien complet ne coûte que quelques Mio de plus d'un passage à l'autre.
• Chiffrement côté client : la clé reste chez vous. Sans elle, personne ne peut lire vos dumps, pas même nous.
• Hors site : les sauvegardes quittent le serveur de production, et le compte client n'a aucun droit de suppression par défaut sur NimbusBackup. Une copie air-gapped est possible en complément.
• Un dump applicatif plutôt qu'un gel de VM : un fil connu du forum Proxmox décrit un MariaDB figé par des fsfreeze qui se chevauchent. Le dump ne dépend pas de ce mécanisme.

Chaque base est exportée dans son propre fichier, sans compression, avec --skip-dump-date et --order-by-primary pour que le contenu reste identique d'un jour à l'autre là où rien n'a changé. Le répertoire part ensuite en archive pxar, découpée selon le contenu.

Chaque base est cohérente en elle-même (une transaction par base), pas avec les autres. Le script s'arrête dès qu'un dump échoue, avant tout envoi.

#!/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

Un dump se restaure en rejouant le SQL : chaque INSERT, chaque index reconstruit. Sur 30 Go, cela peut se compter en heures. Une copie physique se remet en place au débit du disque. Sur nos 5 petites bases, notre test sur une base interne donne 43 s pour rapatrier et remettre en service une copie physique de 4,05 Gio, contre environ 195 s pour rapatrier et recharger le dump de 1,7 Gio. Mais mariadb-backup demande un espace temporaire de la taille de la base, qui manquait sur notre serveur.

FLUSH TABLES … FOR EXPORT fige les tables, écrit un fichier .cfg à côté de chaque .ibd et bloque les écritures jusqu'à UNLOCK TABLES. Pendant ce temps, pxar lit le répertoire de la base sur place, lancé depuis la même session (commande system du client mariadb) pour que le verrou tienne. En mode metadata, les fichiers inchangés, comme les partitions fermées d'une série temporelle, ne sont même pas relus. Pour que le verrou reste court dès le premier soir, un pré-passage sans verrou envoie d'abord presque tout dans le même groupe : son snapshot n'est pas cohérent, il est annoté comme tel.

#!/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"

Ce que nous avons mesuré

• Prototype en conteneur (table partitionnée par mois, 2,45 millions de lignes) : 1er envoi avec un verrou de 2,5 s ; 2e envoi après 50 000 insertions : 18,6 Mio relus sur 160,8, verrou de 0,3 s.
• Restauration du prototype par IMPORT TABLESPACE et EXCHANGE PARTITION : nombre de lignes et checksum logique identiques à la source.
• Pré-passage sans verrou sur notre serveur (6 bases) : 45 Gio → 9,6 Gio stockés (−79 %), lus en 14 min en priorité basse. C'est environ deux fois le dump : la copie physique contient les index et l'espace libre interne des fichiers .ibd.
• Premier passage verrouillé sur notre serveur (30/09/2026), deux jours après le pré-passage : verrou tenu 138,5 s sur la base de séries temporelles (2,51 Gio relus et envoyés sur 41,7 Gio, les partitions fermées étant reprises sans relecture), puis 4,4 s, 2,2 s, 2,0 s, 1,7 s et 0,4 s sur les cinq autres bases. Ce premier passage cumulait deux jours de modifications : le verrou d'une nuit ordinaire sera plus court.

• Les écritures sur les tables verrouillées sont bloquées pendant le passage verrouillé : c'est pour cela que le pré-passage existe, et que l'envoi verrouillé ne tourne pas en priorité basse.
• InnoDB uniquement. La base système mysql part en SQL portable (mariadb-dump --system=all), avec le schéma.
• Ce n'est pas un datadir sur lequel démarrer tel quel : il n'y a ni espace système ni journaux. La restauration passe par une instance neuve, le schéma, puis l'import. Pour pouvoir démarrer le moteur directement sur la sauvegarde, il faut une copie complète par mariadb-backup.
• MariaDB refuse DISCARD TABLESPACE sur une table partitionnée : on importe partition par partition, via une table temporaire et EXCHANGE PARTITION.
• Les snapshots du pré-passage ne sont pas restaurables : filtrez sur la note « CONSISTENT ». L'import exige le même schéma et une version compatible.
• Dans une ligne system du client mariadb, un ; fait basculer toute la ligne en SQL, même entre apostrophes : notre premier essai en production a échoué ainsi (erreur 1064). Enchaînez avec && ou appelez un script.
• Le mode metadata ne relit un fichier que si sa date de modification a changé. Sur ext4, cette date a une granularité de quelques millisecondes : une écriture pendant le pré-passage pourrait laisser la même date, et le passage verrouillé reprendrait une page à moitié écrite. D'où le touch, sous verrou, de tous les fichiers modifiés depuis le début du pré-passage, qui force leur relecture, et le contrôle innochecksum dans la procédure de restauration.

Le fichier SHA256SUMS produit par le script permet de vérifier une restauration au bit près. Rechargez ensuite le dump dans une instance jetable : c'est le seul vrai test. Les tests de restauration réalisés par nos équipes font partie de notre infogérance, pas de la prestation de sauvegarde.

. /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

Sur Rocky Linux 10.2, le binaire statique brut de Proxmox cherche les certificats dans /usr/lib/ssl, qui n'existe pas, et échoue :

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:

Le paquet de notre dépôt non officiel proxmox-backup-client (RPM, Arch, Alpine, arm64) ajoute les deux liens nécessaires : installé par dnf avec vérification GPG, il a restauré nos dumps avec la seule clé, SHA256 identiques. À défaut, SSL_CERT_FILE=/etc/pki/tls/certs/ca-bundle.crt contourne le problème (testé sur Rocky 10.2 avec le binaire brut).

Un backup-id ne peut pas contenir de /, et le compte client ne peut pas créer de namespace. Nous utilisons le nom complet du serveur, suivi du type de sauvegarde, dans des groupes séparés : les rythmes diffèrent, et PBS déduplique d'un snapshot au suivant dans le même groupe. Si un namespace a été créé pour le serveur à la mise en service, utilisez-le.

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

La rétention se règle côté Nimbus, puisque le client n'a pas le droit de supprimer : c'est aussi ce qui protège vos sauvegardes si le serveur est compromis.