Datenbank-Dumps automatisiert analysieren mit Bash

1. Warum ein Dump mehr verrät als nur "Backup gelungen"

Die meisten Backup-Routinen prüfen nur, ob mysqldump mit Exit-Code null durchgelaufen ist, und markieren das Backup dann als erfolgreich. Eine Datenbank-Dump-Analyse, die tiefer geht, erkennt Probleme, die trotz erfolgreichem Exit-Code auftreten können: eine Tabelle, die plötzlich leer ist, weil eine Migration schiefging, ein Dump, der nur einen Bruchteil der erwarteten Größe hat, weil die Verbindung während des Exports abbrach, oder ein Schema, das sich unbemerkt verändert hat.

Der Grund, warum eine Datenbank-Dump-Analyse direkt auf der Dump-Datei ansetzt, statt den Dump in eine Test-Datenbank einzuspielen, ist Geschwindigkeit und Ressourcenschonung. Ein vollständiger Restore-Test braucht eine eigene Datenbankinstanz, Zeit für den Import und Speicherplatz für eine komplette Kopie. Textbasierte Analyse mit grep, awk und regulären Ausdrücken liefert einen Großteil der relevanten Kennzahlen in einem Bruchteil der Zeit, direkt auf der Dump-Datei als reinem Text.

Dieser Artikel zeigt, wie eine Datenbank-Dump-Analyse in Bash Größenverlauf, Zeilenzahlen pro Tabelle, Schema-Änderungen und strukturelle Integrität prüft, sowohl für unkomprimierte als auch für gzip-komprimierte Dumps, und wie das Ergebnis automatisiert als Report verschickt wird.

2. Größenverlauf über Zeit ohne Einspielen erkennen

Die einfachste Form einer Datenbank-Dump-Analyse ist der Vergleich der Dateigröße über mehrere aufeinanderfolgende Backups. Eine Datenbank wächst in der Regel kontinuierlich, ein plötzlicher Sprung nach unten deutet fast immer auf ein Problem hin: eine gelöschte Tabelle, ein fehlgeschlagener Export oder eine versehentliche TRUNCATE-Operation kurz vor dem Backup-Lauf. Diese Prüfung braucht keine Datenbankverbindung, nur die Dateigrößen der letzten Backups im Verzeichnis.

Für eine robuste Datenbank-Dump-Analyse reicht ein einfacher Schwellenwertvergleich: Ist die aktuelle Dump-Größe mehr als zwanzig Prozent kleiner als der gleitende Durchschnitt der letzten sieben Tage, wird das als Anomalie markiert. Dieser Prozentsatz muss pro Umgebung angepasst werden, weil manche Datenbanken durch reguläre Wartungsjobs (Archivierung, Log-Rotation in Tabellen) auch legitime Größenschwankungen zeigen.

#!/usr/bin/env bash
# check-dump-size-trend.sh — flag a suspicious size drop across backups
set -euo pipefail

readonly BACKUP_DIR="/var/backups/mysql"
readonly THRESHOLD_PERCENT=20

# Collect sizes of the last 7 dumps, oldest first
mapfile -t sizes < <(
  find "$BACKUP_DIR" -name "*.sql" -mtime -7 -printf "%T@ %s\n" \
    | sort -n | awk '{print $2}'
)

if (( ${#sizes[@]} < 2 )); then
  echo "[INFO] Not enough history to compare, skipping trend check"
  exit 0
fi

current_size="${sizes[-1]}"
sum=0
for (( i = 0; i < ${#sizes[@]} - 1; i++ )); do
  sum=$(( sum + sizes[i] ))
done
average=$(( sum / (${#sizes[@]} - 1) ))

drop_percent=$(awk -v cur="$current_size" -v avg="$average" 'BEGIN { printf "%.1f", (1 - cur/avg) * 100 }')

if awk -v d="$drop_percent" -v t="$THRESHOLD_PERCENT" 'BEGIN { exit !(d > t) }'; then
  echo "[WARN] Dump size dropped by ${drop_percent}% vs. 7-day average" >&2
  echo "[WARN] Current: $current_size bytes, average: $average bytes" >&2
  exit 1
fi

echo "[OK] Dump size within expected range (${drop_percent}% vs. average)"

Diese Kennzahl allein reicht für eine erste Datenbank-Dump-Analyse bereits aus, um die gröbsten Backup-Katastrophen frühzeitig zu erkennen. Für eine differenziertere Diagnose, welche Tabelle konkret betroffen ist, braucht es die tabellenweise Analyse aus dem nächsten Abschnitt.

3. Zeilenzahlen pro Tabelle direkt aus dem Dump zählen

Ein mysqldump im Standardformat schreibt für jede Tabelle einen INSERT INTO-Block, oft mit mehreren Werten pro Zeile durch --extended-insert. Für eine genaue Datenbank-Dump-Analyse pro Tabelle reicht das reine Zählen von INSERT INTO-Zeilen nicht aus, weil eine einzige Zeile hunderte Datensätze enthalten kann. Stattdessen zählt man die Anzahl der Wertegruppen, erkennbar an öffnenden Klammern nach einem Komma oder direkt nach VALUES.

Eine zuverlässigere Alternative, die ohne fragile Klammer-Zählung auskommt, ist der Export mit --skip-extended-insert, wodurch mysqldump pro Zeile genau einen Datensatz schreibt. Für eine Datenbank-Dump-Analyse, die primär der Diagnose dient, ist diese Option beim Backup selbst zu setzen sinnvoll, weil sie die nachträgliche Auswertung erheblich vereinfacht, auf Kosten einer etwas größeren unkomprimierten Dump-Datei.

#!/usr/bin/env bash
# count-rows-per-table.sh — approximate row counts from a mysqldump file
set -euo pipefail

readonly DUMP_FILE="${1:?Usage: count-rows-per-table.sh <dump.sql>}"

# Works reliably when the dump was created with --skip-extended-insert
awk '
/^INSERT INTO/ {
  match($0, /INSERT INTO `?([a-zA-Z0-9_]+)`?/, arr)
  table = arr[1]
  count[table]++
}
END {
  for (t in count) printf "%-30s %d rows\n", t, count[t]
}' "$DUMP_FILE" | sort

Für Dumps mit --extended-insert, die in der Praxis häufiger vorkommen, weil sie deutlich kleiner sind, zählt man stattdessen die Anzahl der abgeschlossenen Wertegruppen pro INSERT INTO-Zeile mit einem regulären Ausdruck, der auf ),( als Trennzeichen zwischen Datensätzen matcht. Diese Zählung ist eine Annäherung, aber für eine Datenbank-Dump-Analyse, die Trends über Zeit statt exakte Zahlen liefern soll, ist diese Genauigkeit ausreichend.

4. Schema-Änderungen zwischen zwei Dumps erkennen

Ein mysqldump enthält standardmäßig auch die CREATE TABLE-Anweisungen für jede Tabelle. Das macht eine Datenbank-Dump-Analyse zu einem praktischen Werkzeug, um Schema-Drift zu erkennen: unbeabsichtigte Änderungen an der Datenbankstruktur, die ohne dokumentierte Migration passiert sind, etwa eine manuell hinzugefügte Spalte direkt in der Produktionsdatenbank, die im Versionskontrollsystem nirgends auftaucht.

Die Technik dafür ist simpel: Aus jedem Dump werden alle CREATE TABLE-Blöcke extrahiert und in eine kanonische, sortierte Form gebracht, dann werden zwei Versionen mit diff verglichen. Für eine aussagekräftige Datenbank-Dump-Analyse ist wichtig, variable Bestandteile wie AUTO_INCREMENT-Startwerte vorher herauszufiltern, weil diese sich bei jedem Insert ändern und sonst bei jedem Vergleich fälschlich als Schema-Änderung erscheinen würden.

#!/usr/bin/env bash
# schema-diff.sh — compare CREATE TABLE statements between two dumps
set -euo pipefail

readonly OLD_DUMP="${1:?Usage: schema-diff.sh <old.sql> <new.sql>}"
readonly NEW_DUMP="${2:?Usage: schema-diff.sh <old.sql> <new.sql>}"

extract_schema() {
  local dump_file="$1"
  # Extract CREATE TABLE blocks, strip AUTO_INCREMENT values (they always change)
  awk '/^CREATE TABLE/,/^\) ENGINE/' "$dump_file" \
    | sed -E 's/AUTO_INCREMENT=[0-9]+//g'
}

old_schema="$(mktemp)"
new_schema="$(mktemp)"
extract_schema "$OLD_DUMP" > "$old_schema"
extract_schema "$NEW_DUMP" > "$new_schema"

if diff -u "$old_schema" "$new_schema" > /tmp/schema.diff; then
  echo "[OK] No schema changes detected"
else
  echo "[WARN] Schema changes detected between dumps:" >&2
  cat /tmp/schema.diff >&2
fi

rm -f "$old_schema" "$new_schema"

Diese Art der Datenbank-Dump-Analyse ersetzt keine formale Migrationsverwaltung, ist aber ein wertvolles Sicherheitsnetz, um Schema-Drift zwischen dem tatsächlichen Zustand der Produktionsdatenbank und dem, was in Migrationsdateien dokumentiert ist, frühzeitig aufzudecken, bevor daraus ein größeres Problem bei der nächsten geplanten Migration entsteht.

5. Strukturelle Integrität des Dumps prüfen

Ein unvollständiger oder abgebrochener mysqldump kann trotzdem eine valide, wenn auch verkürzte, Textdatei erzeugen. Ohne explizite Prüfung fällt das oft erst beim tatsächlichen Restore-Versuch auf, im schlimmsten Fall erst dann, wenn das Backup gebraucht wird. Eine strukturelle Datenbank-Dump-Analyse prüft deshalb, ob der Dump mit den erwarteten Markern beginnt und endet, die mysqldump bei einem vollständigen Lauf immer schreibt.

Der zuverlässigste Marker für einen vollständigen Dump ist die Kommentarzeile -- Dump completed on, die mysqldump als letzte Zeile schreibt, sofern der Export nicht abgebrochen wurde. Fehlt diese Zeile, ist der Dump mit hoher Wahrscheinlichkeit unvollständig, selbst wenn mysqldump selbst mit Exit-Code null beendet wurde, etwa weil die Verbindung genau zwischen dem letzten Datensatz und dieser Abschlusszeile unterbrochen wurde.

#!/usr/bin/env bash
# verify-dump-completeness.sh — check structural markers of a mysqldump file
set -euo pipefail

readonly DUMP_FILE="${1:?Usage: verify-dump-completeness.sh <dump.sql>}"

errors=0

# A complete mysqldump always ends with this comment line
if ! tail -n 5 "$DUMP_FILE" | grep -q "^-- Dump completed on"; then
  echo "[ERROR] Missing 'Dump completed on' marker — dump may be truncated" >&2
  errors=$(( errors + 1 ))
fi

# Every dump should contain at least one CREATE TABLE statement
if ! grep -q "^CREATE TABLE" "$DUMP_FILE"; then
  echo "[ERROR] No CREATE TABLE statements found — dump looks empty" >&2
  errors=$(( errors + 1 ))
fi

# Check for unbalanced quotes, a common sign of a corrupted or truncated file
quote_count="$(grep -o "'" "$DUMP_FILE" | wc -l)"
if (( quote_count % 2 != 0 )); then
  echo "[WARN] Odd number of single quotes ($quote_count) — possible corruption" >&2
  errors=$(( errors + 1 ))
fi

if (( errors > 0 )); then
  echo "[FAIL] $errors integrity issue(s) found in $DUMP_FILE" >&2
  exit 1
fi

echo "[OK] Dump structurally complete: $DUMP_FILE"

Diese drei Prüfungen zusammen fangen die häufigsten Ausfallmuster ab: einen bei der Übertragung abgeschnittenen Dump, einen versehentlich leeren Export und eine durch Encoding-Probleme beschädigte Datei. Für eine produktionsreife Datenbank-Dump-Analyse gehört diese Prüfung direkt im Anschluss an den Backup-Lauf, nicht erst beim nächsten Restore-Versuch.

6. Anomalien erkennen: plötzlicher Datenverlust oder Explosion

Neben dem Größenverlauf auf Dateiebene lohnt sich eine Datenbank-Dump-Analyse, die einzelne Tabellen über Zeit verfolgt. Eine Tabelle, die normalerweise um wenige Prozent pro Tag wächst, aber plötzlich auf null Zeilen fällt, deutet auf eine versehentliche DELETE- oder TRUNCATE-Operation hin. Umgekehrt deutet eine Tabelle, die sich über Nacht verzehnfacht, oft auf einen fehlerhaften Import-Job oder eine Endlosschleife in der Anwendung hin, die Datensätze dupliziert.

Die Technik ist eine Erweiterung der Zeilenzählung aus Abschnitt drei: Statt nur den aktuellen Stand zu zeigen, wird die Zeilenzahl jeder Tabelle in einer Zeitreihen-Datei protokolliert, sodass jede Datenbank-Dump-Analyse gegen den Wert von vor 24 Stunden vergleichen kann. Ein Schwellenwert von plus/minus fünfzig Prozent ohne bekannten Grund ist ein guter Startpunkt für die meisten Anwendungen.

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

readonly HISTORY_FILE="/var/backups/table-row-history.tsv"
readonly TODAY="$(date +%Y-%m-%d)"

# Append today's counts, then compare against yesterday's for each table
while read -r table count; do
  echo -e "${TODAY}\t${table}\t${count}" >> "$HISTORY_FILE"

  yesterday_count="$(awk -F'\t' -v t="$table" -v d="$(date -d yesterday +%Y-%m-%d)" \
    '$1 == d && $2 == t { print $3 }' "$HISTORY_FILE")"

  [[ -z "$yesterday_count" ]] && continue

  change_percent=$(awk -v c="$count" -v y="$yesterday_count" \
    'BEGIN { if (y == 0) { print 0 } else { printf "%.1f", ((c - y) / y) * 100 } }')

  if awk -v p="$change_percent" 'BEGIN { exit !(p < -50 || p > 100) }'; then
    echo "[ANOMALY] Table '$table': ${change_percent}% change since yesterday ($yesterday_count -> $count)" >&2
  fi
done < <(./count-rows-per-table.sh latest-dump.sql)

7. Komprimierte Dumps analysieren, ohne sie zu entpacken

In der Praxis werden Dumps fast immer direkt mit gzip komprimiert, um Speicherplatz zu sparen, entweder durch eine Pipe während des Backups oder nachträglich. Eine Datenbank-Dump-Analyse, die zuerst den kompletten Dump entpackt, verschwendet Speicherplatz und Zeit, besonders bei mehreren Gigabyte großen Datenbanken. Die Lösung: zgrep, zcat und zless sind direkte Äquivalente zu grep, cat und less, die transparent mit gzip-komprimierten Dateien arbeiten, ohne eine temporäre entpackte Kopie zu erzeugen.

Alle in diesem Artikel gezeigten Analyseskripte lassen sich mit minimalen Anpassungen auf komprimierte Dumps übertragen, indem cat durch zcat und grep durch zgrep ersetzt wird. Für eine Datenbank-Dump-Analyse, die routinemäßig nach jedem Backup läuft, ist das der Unterschied zwischen einer Analyse, die in Sekunden läuft, und einer, die erst Minuten für das Entpacken eines mehrere Gigabyte großen Dumps braucht.

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

readonly DUMP_FILE="${1:?Usage: analyze-compressed.sh <dump.sql.gz>}"

# Check completeness marker directly on the compressed file
if zcat "$DUMP_FILE" | tail -n 5 | grep -q "^-- Dump completed on"; then
  echo "[OK] Compressed dump appears complete"
else
  echo "[ERROR] Compressed dump missing completeness marker" >&2
  exit 1
fi

# Count rows per table without ever writing an uncompressed copy to disk
zcat "$DUMP_FILE" | awk '
/^INSERT INTO/ {
  match($0, /INSERT INTO `?([a-zA-Z0-9_]+)`?/, arr)
  count[arr[1]]++
}
END {
  for (t in count) printf "%-30s %d rows\n", t, count[t]
}' | sort

8. Ergebnisse als Report zusammenfassen und versenden

Die einzelnen Prüfungen aus den vorigen Abschnitten liefern jeweils für sich genommen wertvolle Signale, aber der eigentliche operative Nutzen entsteht erst, wenn eine Datenbank-Dump-Analyse alle Ergebnisse in einem einzigen Report zusammenfasst, der nach jedem Backup automatisch verschickt wird. Nach dem in einem früheren Artikel dieser Reihe beschriebenen Muster mit Heredoc und inline CSS lässt sich ein solcher Report ohne zusätzliche Abhängigkeiten direkt aus Bash bauen.

Wichtig für einen brauchbaren Report ist, zwischen Warnungen und harten Fehlern zu unterscheiden: Ein Größenrückgang von fünf Prozent ist eine Beobachtung, ein fehlender Abschlussmarker ist ein Fehler, der eine sofortige Reaktion braucht. Eine Datenbank-Dump-Analyse, die alle Befunde gleich behandelt, führt entweder zu ignorierten kritischen Fehlern oder zu Alarmmüdigkeit durch zu viele belanglose Warnungen.

9. Dump-Analyse im Vergleich zu einem Test-Restore

Eine textbasierte Datenbank-Dump-Analyse ersetzt nicht jeden Anwendungsfall eines echten Restore-Tests, deckt aber einen großen Teil der relevanten Prüfungen mit erheblich weniger Aufwand ab.

Die pragmatische Lösung ist eine Kombination: Eine schnelle textbasierte Datenbank-Dump-Analyse läuft nach jedem einzelnen Backup, ein vollständiger Test-Restore mit anschließender Konsistenzprüfung läuft seltener, etwa wöchentlich, als tiefere Absicherung gegen Probleme, die eine reine Textanalyse nicht erfassen kann.

10. Zusammenfassung

Eine gründliche Datenbank-Dump-Analyse geht deutlich über die reine Prüfung des Exit-Codes von mysqldump hinaus. Der Größenverlauf über mehrere Backups hinweg entdeckt plötzliche Einbrüche, die auf gelöschte Tabellen oder abgebrochene Exporte hindeuten. Zeilenzahlen pro Tabelle, direkt aus dem Dump gezählt, zeigen, welche konkrete Tabelle betroffen ist. Schema-Diffs zwischen aufeinanderfolgenden Dumps decken unbeabsichtigte Strukturänderungen auf, die an der offiziellen Migrationsverwaltung vorbeigelaufen sind.

Strukturelle Integritätsprüfung anhand des Abschlussmarkers erkennt abgeschnittene Dumps, die trotz Exit-Code null unvollständig sind. All diese Prüfungen funktionieren auch auf gzip-komprimierten Dumps mit zgrep und zcat, ohne eine temporäre entpackte Kopie zu erzeugen. Eine Datenbank-Dump-Analyse ersetzt keinen vollständigen Restore-Test, ist aber als schnelle, ressourcenschonende Ergänzung nach jedem einzelnen Backup ein wesentlicher Baustein zuverlässiger Backup-Strategien.

11. FAQ: Datenbank-Dumps automatisiert analysieren