Konsistente Datenbank-Snapshots erstellen

1. Warum ein Dateisystem-Snapshot allein keine Konsistenz garantiert

Ein Datenbank-Snapshot, der einfach ein Dateisystem-Abbild der Datenverzeichnisse zieht, waehrend die Datenbank aktiv Schreibvorgaenge verarbeitet, laeuft Gefahr, mitten in einer Transaktion eingefroren zu werden. InnoDB und PostgreSQL schreiben Daten nicht in einem einzigen atomaren Schritt auf die Platte, sondern ueber mehrere Dateien und Puffer verteilt, Redo-Logs, Tablespaces, Write-Ahead-Logs. Ein Datenbank-Snapshot, der genau in diesem Moment entsteht, kann Tabellen und zugehoerige Indizes in unterschiedlichen Zustaenden festhalten.

Das Tueckische daran: Ein solcher inkonsistenter Datenbank-Snapshot faellt beim Erstellen nicht auf, kein Fehler, kein Warnhinweis. Erst beim tatsaechlichen Restore, oft Wochen oder Monate spaeter im Ernstfall, zeigt die Datenbank Integritaetsfehler, fehlende Indizes oder sogar stillschweigend falsche Daten. Ein konsistenter Datenbank-Snapshot muss deshalb technisch sicherstellen, dass alle beteiligten Dateien exakt denselben logischen Zeitpunkt widerspiegeln, nicht nur zufaellig zur gleichen Sekunde kopiert wurden.

2. Crash-konsistent versus anwendungskonsistent

Bei Datenbank-Snapshots unterscheidet man zwei Konsistenzstufen. Ein crash-konsistenter Snapshot entspricht dem Zustand, den die Datenbank nach einem harten Stromausfall vorfaende: durch Redo-Logs und Crash-Recovery-Mechanismen kann InnoDB oder PostgreSQL aus diesem Zustand automatisch wieder in einen konsistenten Zustand zurueckfinden, sofern der Snapshot wirklich atomar entstanden ist, also alle Bloecke exakt zum selben Zeitpunkt eingefroren wurden. Genau das leisten LVM- und Btrfs-Snapshots, wenn sie korrekt eingesetzt werden.

Ein anwendungskonsistenter Datenbank-Snapshot geht einen Schritt weiter: Die Datenbank wird vor dem Snapshot aktiv angewiesen, alle gepufferten Aenderungen auf die Platte zu schreiben und temporaer keine neuen Schreibvorgaenge anzunehmen. Das Ergebnis ist ein Zustand, der beim Restore ohne jede Crash-Recovery sofort nutzbar ist. Fuer die meisten produktiven Setups ist die Kombination aus einem kurzen anwendungskonsistenten Freeze und einem darauf folgenden crash-konsistenten Snapshot der praktikabelste Weg zu einem wirklich verlaesslichen Datenbank-Snapshot.

3. FLUSH TABLES WITH READ LOCK und LVM-Snapshots kombinieren

Fuer MySQL und MariaDB ist FLUSH TABLES WITH READ LOCK das klassische Kommando, um kurzzeitig einen konsistenten Zustand fuer einen Datenbank-Snapshot zu erzwingen. Es schreibt alle gepufferten Aenderungen auf die Platte und sperrt neue Schreibvorgaenge, bis die Sperre wieder aufgehoben wird. Waehrend dieser Sperre, die im Idealfall nur wenige Sekunden dauert, wird ein LVM-Snapshot des darunterliegenden Logical Volumes erstellt, der den eingefrorenen Zustand dauerhaft festhaelt.

Der entscheidende Vorteil dieser Kombination: Der eigentliche mysqldump- oder Kopiervorgang findet nicht mehr waehrend der Sperre statt, sondern erst danach, auf dem bereits erstellten LVM-Snapshot. Die Sperre selbst kann deshalb nach wenigen Sekunden wieder aufgehoben werden, waehrend die eigentliche, potenziell langwierige Sicherung vom eingefrorenen Snapshot aus laeuft, ohne den Produktivbetrieb weiter zu blockieren. Ein Datenbank-Snapshot, der so entsteht, ist sowohl konsistent als auch mit minimaler Downtime verbunden.

#!/usr/bin/env bash
# flush-lock-lvm-snapshot.sh — consistent database snapshot via short freeze + LVM
set -euo pipefail

readonly DB_USER="backup_user"
readonly VG_NAME="data-vg"
readonly LV_NAME="mysql-lv"
readonly SNAP_NAME="mysql-snap-$(date +%Y%m%d%H%M%S)"

# Step 1: freeze the database briefly — flush buffers, block new writes
mysql -u "$DB_USER" -e "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(2);" &
FLUSH_PID=$!
sleep 1

# Step 2: while frozen, create the LVM snapshot — this is the atomic moment
lvcreate --size 5G --snapshot --name "$SNAP_NAME" "/dev/${VG_NAME}/${LV_NAME}"

# Step 3: release the lock immediately — the snapshot already captured a
# consistent state, the database can resume writing right away
mysql -u "$DB_USER" -e "UNLOCK TABLES;"
wait "$FLUSH_PID" 2>/dev/null || true

echo "[OK] Consistent database snapshot created: $SNAP_NAME"

# Step 4: mount the snapshot read-only and run the actual backup from there
mkdir -p /mnt/db-snapshot
mount -o ro "/dev/${VG_NAME}/${SNAP_NAME}" /mnt/db-snapshot
tar -czf "/var/backup/db-snapshot-$(date +%Y%m%d).tar.gz" -C /mnt/db-snapshot .
umount /mnt/db-snapshot
lvremove -f "/dev/${VG_NAME}/${SNAP_NAME}"

4. Mariabackup fuer Hot-Backups ohne globalen Lock

Fuer grosse InnoDB-lastige Datenbanken ist ein globaler FLUSH TABLES WITH READ LOCK selbst fuer wenige Sekunden manchmal nicht akzeptabel. Mariabackup, der Fork von Percona XtraBackup fuer MariaDB, loest dieses Problem, indem es InnoDB-Tabellen ohne Sperre direkt aus den Datendateien liest und dabei parallel die Redo-Logs mitschneidet. Erst am Ende, fuer eine sehr kurze Phase, wird kurzzeitig gesperrt, um auch nicht-transaktionale Tabellen wie MyISAM konsistent einzufangen.

Das Ergebnis ist ein Datenbank-Snapshot, der praktisch ohne merkliche Downtime entsteht, waehrend die Konsistenz durch das Nachspielen der mitgeschnittenen Redo-Logs beim Prepare-Schritt sichergestellt wird. Dieser zweistufige Ablauf, Backup und anschliessendes Prepare, ist der Grund, warum ein Mariabackup-Datenbank-Snapshot nicht direkt nach dem Kopieren restore-faehig ist, sondern erst nach dem expliziten --prepare-Lauf, der die aufgezeichneten Transaktionen anwendet und einen konsistenten Endzustand erzeugt.

#!/usr/bin/env bash
# mariabackup-hot.sh — consistent database snapshot without a global lock
set -euo pipefail

readonly BACKUP_DIR="/var/backup/mariabackup/$(date +%Y%m%d-%H%M%S)"
readonly PREPARE_DIR="${BACKUP_DIR}-prepared"

mkdir -p "$BACKUP_DIR"

# Phase 1: copy InnoDB data files without locking, tracking redo log changes
mariabackup --backup \
  --target-dir="$BACKUP_DIR" \
  --user=backup_user \
  --password="$(cat /etc/mysql/backup.pass)"

# Phase 2: apply the tracked redo log to reach a consistent, restorable state
cp -a "$BACKUP_DIR" "$PREPARE_DIR"
mariabackup --prepare --target-dir="$PREPARE_DIR"

echo "[OK] Consistent database snapshot prepared at $PREPARE_DIR"

# Restore: stop MariaDB, clear the data directory, copy back, fix ownership
# mariabackup --copy-back --target-dir="$PREPARE_DIR"
# chown -R mysql:mysql /var/lib/mysql

5. LVM- und Btrfs-Snapshots fuer konsistente Dateisystem-Zustaende

LVM-Snapshots basieren auf Copy-on-Write: Beim Anlegen des Snapshots wird kein Datenblock kopiert, sondern lediglich vermerkt, dass zukuenftige Schreibvorgaenge auf das Original zuerst den alten Blockinhalt in den Snapshot-Bereich sichern muessen. Diese Technik macht das Anlegen eines Datenbank-Snapshots nahezu instantan, unabhaengig von der Groesse des Volumes, weil eben nicht wirklich kopiert wird, sondern nur ein Copy-on-Write-Zeiger gesetzt wird.

Btrfs bietet dieselbe Grundidee nativ auf Dateisystemebene, ohne separate Snapshot-Volumes verwalten zu muessen. Ein btrfs subvolume snapshot ist ebenfalls Copy-on-Write und laesst sich noch einfacher automatisieren als LVM, weil kein separates Logical Volume mit fester Groesse vorab reserviert werden muss. Fuer beide Technologien gilt gleichermassen: Der Snapshot selbst ist nur crash-konsistent, wenn er waehrend eines FLUSH TABLES WITH READ LOCK oder eines aequivalenten Datenbank-Freezes erstellt wird, sonst bleibt die zugrunde liegende Inkonsistenz aus Abschnitt eins bestehen.

6. PostgreSQL: pg_basebackup und WAL-Archivierung

PostgreSQL verfolgt einen anderen, in vielen Faellen eleganteren Ansatz fuer konsistente Datenbank-Snapshots. pg_basebackup kopiert die Datenverzeichnisse online, waehrend die Datenbank normal weiterlaeuft, ohne globale Sperre. Die waehrend der Kopie entstehenden Aenderungen werden ueber das Write-Ahead-Log (WAL) mitgeschnitten und dem Backup als Teil des Ergebnisses beigelegt. Beim Restore spielt PostgreSQL dieses WAL-Segment automatisch nach und erreicht so einen konsistenten Zustand, exakt zum Zeitpunkt des Endes der Sicherung.

Fuer Point-in-Time-Recovery, also die Wiederherstellung eines exakten Zeitpunkts zwischen zwei Basissicherungen, muss zusaetzlich die kontinuierliche WAL-Archivierung aktiviert werden. Damit entsteht ein durchgehender Strom aller Transaktionen, der es erlaubt, einen Datenbank-Snapshot bis zu einer beliebigen Sekunde vor einem fehlerhaften DELETE-Statement wiederherzustellen, statt nur bis zum letzten naechtlichen Backup zurueckzugehen. Diese Praezision unterscheidet PostgreSQL-Sicherungen deutlich von einem einfachen taeglichen Datenbank-Snapshot ohne WAL-Archivierung.

#!/usr/bin/env bash
# pg-basebackup.sh — consistent PostgreSQL snapshot with WAL archiving
set -euo pipefail

readonly BACKUP_DIR="/var/backup/postgres/$(date +%Y%m%d-%H%M%S)"
readonly WAL_ARCHIVE="/var/backup/postgres-wal"

mkdir -p "$BACKUP_DIR" "$WAL_ARCHIVE"

# pg_basebackup runs online, no global lock, WAL changes are captured
pg_basebackup \
  --pgdata="$BACKUP_DIR" \
  --format=tar \
  --gzip \
  --wal-method=stream \
  --checkpoint=fast \
  --label="nightly-consistent-snapshot" \
  --host=localhost \
  --username=replicator

echo "[OK] Consistent PostgreSQL snapshot created at $BACKUP_DIR"

# postgresql.conf must enable continuous WAL archiving for point-in-time recovery:
# archive_mode = on
# archive_command = 'test ! -f /var/backup/postgres-wal/%f && cp %p /var/backup/postgres-wal/%f'

7. Snapshot-Automatisierung mit Locking-Strategie

Ein manuell ausgefuehrter Datenbank-Snapshot ist im Ernstfall wertlos, wenn er nicht regelmaessig und zuverlaessig automatisiert laeuft. Die Automatisierung muss dabei zwei Dinge sicherstellen: Erstens, dass die Sperrzeit bei FLUSH TABLES WITH READ LOCK tatsaechlich kurz bleibt und nicht durch eine haengende LVM-Operation unbegrenzt verlaengert wird, was Produktivsysteme blockieren wuerde. Zweitens, dass ein fehlgeschlagener Snapshot-Versuch die Sperre garantiert wieder freigibt, selbst wenn ein Skriptschritt dazwischen abbricht.

Ein trap-basiertes Cleanup, das die Sperre in jedem Fall aufhebt, egal ob das Skript normal durchlaeuft oder mit einem Fehler abbricht, ist fuer produktive Datenbank-Snapshot-Automatisierung Pflicht. Ebenso sollte die Snapshot-Groesse beim LVM-Ansatz grosszuegig bemessen sein, denn ein Copy-on-Write-Snapshot, dem der reservierte Speicherplatz waehrend einer laufenden Sicherung ausgeht, wird von LVM automatisch verworfen und macht den gesamten Datenbank-Snapshot ungueltig.

#!/usr/bin/env bash
# automated-db-snapshot.sh — reliable locking with guaranteed cleanup
set -euo pipefail

readonly DB_USER="backup_user"
readonly VG_NAME="data-vg"
readonly LV_NAME="mysql-lv"
readonly SNAP_NAME="auto-snap-$(date +%Y%m%d%H%M%S)"
LOCK_ACQUIRED=0

cleanup() {
  if [[ "$LOCK_ACQUIRED" -eq 1 ]]; then
    mysql -u "$DB_USER" -e "UNLOCK TABLES;" 2>/dev/null || true
    echo "[INFO] Lock released during cleanup"
  fi
}
trap cleanup EXIT

mysql -u "$DB_USER" -e "FLUSH TABLES WITH READ LOCK;"
LOCK_ACQUIRED=1

# Generously sized snapshot: 20% of source volume, avoids running out of COW space
lvcreate --size 10G --snapshot --name "$SNAP_NAME" "/dev/${VG_NAME}/${LV_NAME}"

mysql -u "$DB_USER" -e "UNLOCK TABLES;"
LOCK_ACQUIRED=0

echo "[OK] Database snapshot $SNAP_NAME created with guaranteed lock cleanup"

8. Snapshot-Validierung nach der Sicherung

Ein Datenbank-Snapshot, der als konsistent gilt, aber nie ueberprueft wurde, ist eine unbewiesene Annahme. Die verlaesslichste Validierung ist ein tatsaechlicher Testrestore in eine isolierte MariaDB- oder PostgreSQL-Instanz, gefolgt von CHECK TABLE beziehungsweise pg_amcheck, um strukturelle Integritaetsfehler in Tabellen und Indizes aufzudecken. Ein Datenbank-Snapshot, der zwar kopiert, aber nie tatsaechlich wieder eingespielt wurde, kann Fehler enthalten, die erst beim echten Restore zutage treten.

Zusaetzlich zur strukturellen Pruefung lohnt sich eine inhaltliche Stichprobe: Stimmt die Anzahl der Zeilen in kritischen Tabellen ungefaehr mit dem erwarteten Wachstum ueberein, sind zentrale Fremdschluessel-Beziehungen intakt. Diese Validierung sollte automatisiert nach jedem erstellten Datenbank-Snapshot laufen und bei Abweichungen eine Warnung ausloesen, nicht erst manuell Wochen spaeter beim tatsaechlichen Ernstfall entdeckt werden.

9. Snapshot-Methoden im Vergleich

Die Wahl der richtigen Methode fuer einen konsistenten Datenbank-Snapshot haengt stark von Datenbankgroesse, akzeptabler Sperrzeit und verfuegbarer Infrastruktur ab.

Fuer die meisten Magento- und PHP-Anwendungen mit MariaDB ist die Kombination aus Mariabackup fuer die taegliche Vollsicherung und einem gelegentlichen validierten LVM-Snapshot fuer schnelle Zwischenstaende der pragmatischste Weg zu einem verlaesslichen Datenbank-Snapshot. PostgreSQL-Umgebungen profitieren am staerksten von durchgehender WAL-Archivierung, weil sie Point-in-Time-Recovery ohne zusaetzliche Werkzeuge ermoeglicht.

10. Zusammenfassung

Ein konsistenter Datenbank-Snapshot entsteht nicht durch simples Kopieren von Dateien, sondern erfordert entweder einen kurzen anwendungskonsistenten Freeze mit FLUSH TABLES WITH READ LOCK vor einem crash-konsistenten LVM- oder Btrfs-Snapshot, oder ein spezialisiertes Werkzeug wie Mariabackup, das Konsistenz ueber Redo-Log-Replay ohne globale Sperre erreicht. PostgreSQL loest dasselbe Problem eleganter mit pg_basebackup und kontinuierlicher WAL-Archivierung, die zusaetzlich Point-in-Time-Recovery ermoeglicht.

Unabhaengig von der gewaehlten Methode gilt: Ein Datenbank-Snapshot, der nie durch einen echten Testrestore validiert wurde, ist eine unbewiesene Annahme. Nur die Kombination aus korrektem Locking, automatisierter Ausfuehrung mit garantiertem Cleanup und regelmaessiger Validierung macht aus einem Datenbank-Snapshot eine verlaessliche Grundlage fuer den Ernstfall.

11. FAQ: Konsistente Datenbank-Snapshots erstellen