Sauvegarde pour MySQL/MariaDB : instantanés LVM, Percona XtraBackup et RESTauration point-in-time

Une sauvegarde pour MySQL/MariaDB est au quotidien rapidement « d’une manière ou d’une autre présente » – jusqu’à la première RESTauration réelle. C’est alors que l’on voit si vous avez seulement copié des fichiers ou si vous disposez d’une sauvegarde de base de données RESTaurable. Dans des environnements productifs, il s’agit rarement de tables isolées, mais d’un RPO fiable (Recovery Point Objective : perte de données maximale tolérable) et d’un RTO (Recovery Time Objective : durée d’indisponibilité maximale tolérée), de processus traçables et de sauvegardes qui fonctionnent aussi sous charge, avec de grands volumes InnoDB et des accès en écriture actifs.

Cet article situe de manière pragmatique trois éléments centraux : instantanés LVM (snapshot au niveau des blocs pour des « points de gel » locaux et rapides), Percona XtraBackup (sauvegarde à chaud pour InnoDB sans arrêt) et le Point-in-Time-RESTore (PITR) via les Binary Logs (binlogs : journal des modifications de données). L’accent est mis sur les prérequis, les risques, les écueils typiques, les étapes de vérification, la mise en œuvre et une stratégie de repli qui ne doit pas être improvisée en cas d’incident.

Backup für MySQL/MariaDB: Was „konsistent“ bei MySQL/MariaDB wirklich bedeutet

Chez MySQL/MariaDB, la « cohérence » est multi-niveaux. Il est important de distinguer entre crash-consistent et transaction-consistent :

• Crash-consistent : la sauvegarde correspond à une coupure de courant soudaine. InnoDB peut, au démarrage, ramener l’état à la cohérence par crash-recovery (Redo/Undo-Logs). Cela suffit souvent, mais pas toujours de façon prévisible en termes de rapidité.
• Transaction-consistent : la sauvegarde représente un point d’arrêt propre où toutes les transactions sont complètes. Cela réduit les surprises lors de la récupération et convient mieux aux RTO contraints.

InnoDB (engine de stockage par défaut) est transactionnel et peut effectuer une crash-recovery. D’autres moteurs (p. ex. MyISAM) ont des caractéristiques différentes ; dans des environnements mixtes, les risques augmentent. En pratique, vous devriez donc d’abord clarifier : quels moteurs et quelles fonctionnalités sont utilisés ? Cela inclut aussi la réplication, le chiffrement (at-REST), la compression de tables, les gros BLOB et, en particulier, le Binary Logging.

Baustein 1: LVM-Snapshots für MySQL/MariaDB – schnell, aber nicht magisch

LVM (Logical Volume Manager) permet des snapshots au niveau du bloc. Un snapshot capture l’état d’un Logical Volume à un instant donné. Techniquement, cela fonctionne via le copy-on-write : à partir du snapshot, les blocs modifiés sont redirigés vers l’espace du snapshot. C’est attractif car un snapshot se crée en quelques secondes – mais ce n’est pas une sauvegarde complète, c’est un « point de gel » temporaire que vous devez ensuite sauvegarder (p. ex. via rsync ou un logiciel de sauvegarde).

Prérequis et architecture typique

Condition essentielle : le datadir MySQL/MariaDB (typiquement /var/lib/mysql) doit résider sur un LVM-Logical-Volume. Piège fréquent : les Binlogs, Relay-Logs, tmpdir ou des partitions séparées ne se trouvent pas sur le même volume. Pour une RESTauration cohérente, tous les chemins de données associés doivent être pris en compte : Datadir, Binlogs, fichiers de configuration, éventuellement fichiers Keyring (en cas de chiffrement at-REST), certificats et Scripts/Units.

Pourquoi les LVM-Snapshots peuvent échouer avec des bases de données en écriture

• Le snapshot se remplit : sous forte charge d’écriture, l’espace du snapshot augmente rapidement. S’il est plein, le snapshot devient invalide — et votre opération de sauvegarde est sans valeur.
• Dégradation des performances : le copy-on-write génère des I/O supplémentaires. Sous charge, cela peut être nettement perceptible.
• Chemins de données non unifiés : si les Binlogs ou le Keyring sont situés à l’extérieur, vous avez bien les données, mais pas un état reproductible.

Règle pratique : les LVM-Snapshots conviennent si vous les conservez pendant une courte durée et effectuez immédiatement la sauvegarde à partir du snapshot. Ils sont moins appropriés si le snapshot existe longtemps ou si la charge d’écriture est imprévisible.

Mise en œuvre : créer un snapshot, le sauvegarder, puis le supprimer

Un processus opérationnel combine LVM avec un court « freeze » de la base de données. Avec InnoDB, on utilise souvent FLUSH TABLES WITH READ LOCK (FTWRL) pour obtenir un point cohérent pour les parties non transactionnelles ; pour des charges purement InnoDB, ce n’est souvent pas nécessaire, mais dans des environnements mixtes c’est un filet de sécurité. Important : un verrou global en lecture bloque les écritures et peut ralentir les applications. Planifiez-le délibérément et brièvement.

#!/usr/bin/env bash
set -euo pipefail

MYSQL_SOCK="/var/run/mysqld/mysqld.sock"
MYSQL_USER="backup"
MYSQL_PWD_FILE="/etc/mysql/backup.pwd"
LV="/dev/vg0/mariadb"
SNAP_NAME="mariadb_snap"
SNAP_SIZE="30G"   # muss zur Schreiblast passen
MOUNTPOINT="/mnt/mariadb_snap"

mysql_exec() {
  mysql --protocol=socket --socket="$MYSQL_SOCK" -u"$MYSQL_USER" 
    --password="$(cat "$MYSQL_PWD_FILE")" -e "$1"
}

# 1) optional: kurzer Read-Lock für konsistenten Schnitt
mysql_exec "FLUSH TABLES WITH READ LOCK;"
# Positionen für PITR/Diagnose notieren (bei MySQL/MariaDB Versionen unterschiedlich)
mysql_exec "SHOW MASTER STATUS;"

# 2) Snapshot erstellen
lvcreate -s -n "$SNAP_NAME" -L "$SNAP_SIZE" "$LV"

# 3) Lock sofort wieder lösen
mysql_exec "UNLOCK TABLES;"

# 4) Snapshot mounten und sichern
mkdir -p "$MOUNTPOINT"
mount -o ro "/dev/vg0/$SNAP_NAME" "$MOUNTPOINT"

# Beispiel: tar in Backup-Ziel (Platzhalter)
BACKUP_DIR="/backup/mariadb"
mkdir -p "$BACKUP_DIR"
DATE="$(date +%F_%H%M%S)"

tar -C "$MOUNTPOINT" -cpf "$BACKUP_DIR/datadir_${DATE}.tar" var/lib/mysql

# 5) Aufräumen
umount "$MOUNTPOINT"
lvremove -f "/dev/vg0/$SNAP_NAME"

Important : l’exemple illustre volontairement le déroulement, pas tous les détails de durcissement. En pratique, vous devez libérer les locks proprement dans des handlers trap, planifier dynamiquement les tailles de snapshot et vérifier l’intégrité des sauvegardes (voir ci‑dessous). De plus : un tar du Datadir n’a de sens que si vous sauvegardez également les autres chemins pertinents (Binlogs, Konfig, Keyring) ou les consignez clairement séparés.

Étapes de vérification et supervision pour les sauvegardes par snapshot LVM

• Utilisation du snapshot : intégrer lvs -a -o+seg_monitor,lv_size,data_percent,metadata_percent dans la supervision. Alerter avant que data_percent ne devienne critique.
• Durée du snapshot : mesurer et limiter la durée de vie du snapshot. Plus elle est longue, plus le risque qu’il « se remplisse » augmente et plus la surcharge d’E/S est importante.
• Test de RESTauration : décompresser régulièrement sur un système de test, démarrer la base de données, observer le crash-recovery, puis exécuter sélectivement CHECK TABLE ou des contrôles de santé applicatifs.

Baustein 2: Percona XtraBackup – Hot-Backups für InnoDB mit besserer Planbarkeit

Percona XtraBackup ist ein Tool, das InnoDB-Daten „im laufenden Betrieb“ sichern kann (Hot-Backup). Es liest die InnoDB-Datendateien und nutzt Log-Informationen, um einen konsistenten Zustand herzustellen. Für Admin-Teams ist XtraBackup oft der praktikabelste Weg, wenn Downtime vermieden werden soll und LVM-Snapshots zu riskant sind (Snapshot-Füllung, getrennte Pfade, Storage-Layout).

Wichtig für MariaDB: Abhängig von Version und Distribution gibt es Unterschiede bei XtraBackup-Varianten und Kompatibilität. Prüfen Sie vorab, ob die eingesetzte XtraBackup-Version Ihre MariaDB-Version unterstützt und ob Features wie Verschlüsselung, Galera oder bestimmte Tabellenformate sauber abgedeckt sind. In heterogenen Landschaften lohnt sich ein standardisiertes „Backup-Toolchain“-Matrix-Dokument.

Was XtraBackup sichert – und was Sie zusätzlich brauchen

XtraBackup erzeugt typischerweise ein physisches Backup (Datei-/Seitenebene) inklusive Metadaten. Für einen vollständigen Wiederanlauf benötigen Sie darüber hinaus:

• Konfiguration (z. B. my.cnf, includes, systemd overrides), weil Parameter wie innodb_buffer_pool_size, datadir, log_bin oder server_id das Verhalten beim Start beeinflussen.
• Binary Logs für PITR, wenn RPO kleiner als Ihr Backup-Intervall ist.
• Keyring-/Encryption-Artefakte (je nach Setup), sonst sind Daten zwar vorhanden, aber nicht entschlüsselbar.

Basis-Runbook: Full-Backup und Prepare-Phase

Der typische Ablauf besteht aus „backup“ und „prepare“. In der Prepare-Phase werden die notwendigen Log-Replays durchgeführt, damit das Backup startfähig ist. Ohne Prepare ist ein RESTore häufig unvollständig oder startet mit Fehlern.

#!/usr/bin/env bash
set -euo pipefail

BACKUP_BASE="/backup/mariadb/xtrabackup"
DATE="$(date +%F_%H%M%S)"
TARGET="$BACKUP_BASE/full_$DATE"

mkdir -p "$TARGET"

# Full backup (Beispielparameter; Zugangsdaten sicher per Datei/Secret-Store verwalten)
xtrabackup 
  --backup 
  --target-dir="$TARGET" 
  --user=backup 
  --password-file=/etc/mysql/backup.pwd 
  --socket=/var/run/mysqld/mysqld.sock

# Prepare: macht das Backup konsistent/startfähig
xtrabackup --prepare --target-dir="$TARGET"

Piège typique : la sauvegarde est créée, mais la phase « prepare » est oubliée ou exécutée sur un autre hôte avec une version inappropriée. Planifiez la phase de prepare comme un composant fixe du job et consignez les versions (base de données et outil) dans les métadonnées de la sauvegarde.

Sauvegardes incrémentielles : économie d’espace, mais RESTauration plus complexe

Les sauvegardes incrémentielles XtraBackup réduisent le volume de données et la durée d’exécution, mais augmentent la complexité de la RESTauration : vous devez maintenir la chaîne intacte (full + tous les incréments) et appliquer « apply-log »/prepare dans le bon ordre. Pour des RTO serrés, c’est le temps de RESTauration de la chaîne qui compte — pas le temps de sauvegarde.

Recommandation opérationnelle : n’utilisez les incréments que si vous exercez régulièrement la chaîne de RESTauration via une probe de RESTore. Sinon, vous gagnez du temps en exploitation normale et le perdez en cas d’incident, souvent à plusieurs reprises.

Troubleshooting : problèmes fréquents avec XtraBackup

• „permission denied“ oder SELinux/AppArmor: XtraBackup a besoin de droits de lecture sur le datadir et les logs. Avec SELinux/AppArmor, les profils doivent être corrects, sinon la sauvegarde s’arrête en cours d’exécution.
• Disque plein sur la cible: Les sauvegardes physiques sont volumineuses. Sans vérification préalable de l’espace libre et sans stratégie de rétention, vous risquez des sauvegardes partielles.
• Combinaisons incompatibles: Les versions MariaDB/MySQL et XtraBackup doivent être compatibles. Les symptômes incluent des erreurs de prepare ou des échecs de démarrage après RESTauration.
• Positions de binlog manquantes: Pour le PITR, vous avez besoin de points de départ définis (fichier/position ou GTID, si utilisé). Assurez-vous de stocker ces informations pour chaque sauvegarde.

Baustein 3: Point-in-Time-RESTore (PITR) mit Binary Logs – der Weg zu engem RPO

Un PITR permet de RESTaurer une base de données non seulement au moment de la dernière sauvegarde, mais à un moment arbitraire ultérieur — typiquement « juste avant l’erreur ». Cela fonctionne en appliquant une sauvegarde de base (p. ex. XtraBackup ou un état basé sur LVM) puis en rejouant les Binary Logs (Binlogs) jusqu’à un point d’arrêt. Les binlogs constituent le journal des modifications de la base (événements DML/DDL), également utilisé pour la réplication.

Prérequis : activer correctement les binlogs et les conserver

Sans binlogs, il n’y a pas de PITR. Vérifiez dans la configuration (my.cnf) si log_bin est activé et comment la conservation est définie (p. ex. binlog_expire_logs_seconds ou des paramètres plus anciens). Faites attention aux emplacements : si les binlogs se trouvent sur le même volume et qu’un défaut de stockage survient, la sauvegarde de base et les binlogs sont compromis ensemble. Pour une capacité PITR solide, les binlogs devraient être répliqués/sauvegardés rapidement vers une destination séparée.

[mysqld]
log_bin=/var/log/mysql/mysql-bin
binlog_format=ROW
# Aufbewahrung (Beispiel): 7 Tage
binlog_expire_logs_seconds=604800
sync_binlog=1

Remarque : binlog_format=ROW (Row-based Replication) est dans de nombreux environnements d’exploitation le choix le plus robuste, car les modifications sont enregistrées comme des événements au niveau des lignes. Cela aide à la consistance de la réplication, mais peut augmenter la taille des binlogs. sync_binlog=1 accroît la sécurité (le binlog est flushé sur disque plus fréquemment), mais peut affecter les performances d’écriture. Ces paramètres sont des compromis ; documentez-les dans le cadre de votre politique de sauvegarde/reprise.

Runbook PITR en étapes (opérationnel)

1. Définir le point d’arrêt : « Jusqu’à quand » faut-il RESTaurer ? Souvent il s’agit d’un point juste avant un déploiement défectueux, un job erroné ou une suppression.
2. RESTaurer la sauvegarde de base : XtraBackup-RESTore ou sauvegarde LVM vers un répertoire de données vierge.
3. Déterminer le point de départ des binlogs : à partir des métadonnées de la sauvegarde (fichier/position du binlog ou GTID). Sans ce point de départ, le PITR est sujet aux erreurs.
4. Appliquer les binlogs : extraire et appliquer avec mysqlbinlog les binlogs pertinents jusqu’à l’heure cible.
5. Validation : vérifications applicatives, intégrité des données (par sondage), état de la réplication (si pertinent), paramètres de performance.

Exemple : appliquer les binlogs jusqu’à une heure cible

L’exemple suivant illustre le principe. Il suppose que vous disposez des binlogs et que vous pouvez déterminer précisément l’heure cible (prenez en compte les fuseaux horaires !).

#!/usr/bin/env bash
set -euo pipefail

# Zielzeit (lokal/UTC bewusst wählen und dokumentieren)
STOP_TIME="2026-07-28 10:15:00"

# Beispiel: Binlogs aus zentralem Archiv in lokales Verzeichnis
BINLOG_DIR="/RESTore/binlogs"

# Verbindung zur wiederhergestellten Instanz
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USER="root"
MYSQL_PWD_FILE="/etc/mysql/root.pwd"

apply_binlogs() {
  local binlogs=("$@")
  mysqlbinlog --stop-datetime="$STOP_TIME" "${binlogs[@]}" 
    | mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" --password="$(cat "$MYSQL_PWD_FILE")"
}

# Beispiel: Alle Binlogs in zeitlicher Reihenfolge anwenden
mapfile -t FILES < <(ls -1 "$BINLOG_DIR"/mysql-bin.* | sort)
apply_binlogs "${FILES[@]}"

Pièges courants lors du PITR :

• Fuseau horaire/dérive d’horloge : les horodatages des événements dans les binlogs et votre « heure d’incident » doivent correspondre. La synchronisation de l’heure (NTP) n’est pas facultative.
• Chaîne de binlogs incomplète : s’il manque un segment, il y a des lacunes. Par conséquent : sauvegardez les binlogs en continu et vérifiez régulièrement leur complétude.
• GTID vs fichier/position : lorsque GTID (Global Transaction ID) est utilisé, le PITR/le basculement est souvent plus simple, mais seulement s’il est configuré et compris de manière cohérente. Sans GTID, on RESTe sur fichier/position.
• Événements ligne et grosses transactions : des transactions très volumineuses peuvent prolonger l’application. Cela a un impact direct sur votre RTO.

Quelle combinaison est « correcte » ? Guide de décision selon les objectifs d’exploitation

En pratique, les éléments sont combinés, pas opposés. Un guide pragmatique pour décider :

• Snapshot LVM + sauvegarde de fichiers : adapté pour des points de sauvegarde locaux très rapides, si le layout de stockage est propre et que la charge d’écriture RESTe prévisible. Risque : saturation du snapshot et chemins inconsistants.
• XtraBackup (complet/incrémental) : adapté comme sauvegarde standard pour de grandes bases InnoDB sans downtime. Risque : gestion des versions/compatibilités et chaînes de RESTauration pour les incréments.
• PITR via les binlogs : obligatoire si vous avez des RPO stricts ou si vous devez fréquemment rattraper des « erreurs logiques » (suppression, job erroné, déploiement). Risque : conservation des binlogs et documentation précise du point de départ.

Une architecture standard robuste pour de nombreux environnements est : XtraBackup comme base (par ex. quotidiennement) plus sauvegarde des binlogs (continue/à cadence serrée) pour le PITR. Les LVM-Snapshots peuvent être un complément utile, par exemple comme « snapshot pré-change » local et rapide avant des opérations de maintenance, à condition que les risques liés aux snapshots soient activement gérés.

Checklist : Avant la première mise en production « réelle »

1) Inventaire et chemins

• Datadir, Binlog-Verzeichnis, Relay-Logs (bei Replikation), tmpdir, Konfig-Includes erfasst
• Fichiers de chiffrement/Keyring et certificats identifiés et inclus dans le plan de sauvegarde
• Layout de stockage documenté (LVM, RAID, SAN, volumes cloud), y compris les dépendances

2) Jobs de sauvegarde et rétention

• Fenêtres de sauvegarde, limites d’I/O et priorités définies (pour éviter que la production ne « s’effondre »)
• Rétention pour Full/Inc et binlogs adaptée aux RPO/RTO et aux exigences de conformité
• Garde-fous d’espace : vérifications préalables, seuils d’alerte, nettoyage fiable des anciennes sauvegardes

3) Validation et tests de RESTauration

• Test de RESTauration automatisé (par ex. hebdomadaire) sur un système isolé
• Critères de réussite : la DB démarre, l’application peut lire/écrire, les requêtes critiques sont plausibles
• Points de mesure : durée de la RESTauration et du replay des binlogs (mesurer réellement le RTO, ne pas l’estimer)

Stratégie de repli : si la RESTauration ne se passe pas comme prévu

Même de bonnes sauvegardes échouent parfois en pratique à cause du contexte : mauvaise version, binlogs manquants, espace insuffisant, paramètres non documentés. Une stratégie de repli ne signifie pas « abandonner », mais préparer des voies alternatives :

• RESTore parallèle plutôt qu’In-Place : RESTauration sur hôte/VM séparé, puis basculement contrôlé. Réduit le risque et facilite l’analyse.
• Dernière sauvegarde garantie démarrable : si le PITR échoue, définissez quelle sauvegarde démarre obligatoirement (XtraBackup préparé, LVM-Backup vérifié).
• Replay des binlogs par étapes : si le replay échoue à un point donné (p. ex. DDL incohérente), procédez avec arrêts clairs, logs et un procédé reproductible, plutôt que par essais/erreurs.
• Runbook et points de décision : définissez quand passer du plan A (PITR) au plan B (dernier Full) pour ne pas dépasser le RTO.

Aspects sécurité et exploitation souvent oubliés

Accès et secrets

Les utilisateurs de sauvegarde doivent avoir des droits minimaux. Les mots de passe ne doivent pas se trouver dans des scripts. Utilisez les droits de fichier (root-only), des magasins de secrets ou des mécanismes dédiés de gestion d’identifiants. Ne consignez pas accidentellement des mots de passe dans les logs (p. ex. via set -x).

Résilience face aux ransomwares

Les sauvegardes sont une cible. Séparez les cibles de sauvegarde logiquement et si possible aussi organisationnellement (identifiants séparés, options Write-once/immutable, copies offline). Un PITR ne sert à rien si les binlogs et les sauvegardes ont été chiffrés ensemble.

Versioning et capacité de migration

Planifiez comment une RESTauration fonctionnera sur du matériel plus récent ou dans un nouvel environnement : chemins, systemd Units, versions des paquets, kernel/système de fichiers. En particulier pour MariaDB/MySQL, les paramètres par défaut varient entre versions. Documentez les versions exactes de la DB et de l’outil de sauvegarde pour chaque exécution de sauvegarde.

Conclusion: les sauvegardes ne sont « bonnes » que lorsque le PITR et la vérification de RESTauration ont été effectués

Une sauvegarde fiable sauvegarde pour MySQL/MariaDB consiste en bien plus qu’une tâche nocturne. Les LVM-Snapshots fournissent des points de sauvegarde locaux très rapides, mais ils doivent être gérés activement en raison du remplissage des snapshots, de la surcharge I/O et de la cohérence des chemins. Percona XtraBackup est, pour de nombreux environnements axés InnoDB, le standard stable pour les sauvegardes à chaud – à condition que la phase de préparation, les versions et les droits soient correctement maîtrisés. Le saut décisif en matière de RPO est assuré par le Point-in-Time-RESTore via les Binlogs : il intercepte les erreurs « logiques » typiques, qui sont en pratique plus fréquentes que les défaillances de stockage.

Si vous ne devez prioriser qu’une seule chose : automatisez une vérification de RESTauration régulière (y compris le replay des Binlogs jusqu’à un point de test) et mesurez RTO/RPO avec des durées réelles. Ce n’est qu’ainsi que « nous avons des sauvegardes » devient un chemin de reprise fiable.

Pour ce sujet, Lvm Snapshot MySQL et Percona Xtrabackup Mariadb sont également importants. L’article replace ces aspects de manière compréhensible et montre ce qui compte au quotidien.