Point-in-Time Recovery mit Binlogs durchführen

1. Was Point-in-Time Recovery leistet

Point-in-Time Recovery löst ein Problem, das ein reines Voll-Backup nicht lösen kann: den Zustand einer Datenbank exakt zu einem beliebigen Zeitpunkt zwischen zwei Backups wiederherzustellen. Wenn ein fehlerhaftes DELETE-Statement um 14:37 Uhr versehentlich zehntausend Bestellungen löscht, hilft ein Backup von Mitternacht nur bedingt: alle Transaktionen zwischen Mitternacht und dem Fehler wären verloren. Point-in-Time Recovery schließt genau diese Lücke, indem ein Basis-Backup mit den seit dem Backup geschriebenen Binlogs kombiniert wird.

Der Mechanismus dahinter ist einfach beschrieben, aber in der Praxis anspruchsvoll: zuerst wird das letzte konsistente Backup vollständig eingespielt, danach werden alle Binlog-Events ab dem Backup-Zeitpunkt bis kurz vor den Fehler erneut angewendet. Diese Kombination aus Restore und Replay ist der Kern jeder Point-in-Time Recovery-Strategie und funktioniert unabhängig davon, ob die Datenbank Teil einer Magento-Installation, einer Symfony-Anwendung oder eines beliebigen anderen Systems ist. Wer diesen Ablauf nicht vorher geübt hat, verliert im Ernstfall wertvolle Zeit und riskiert zusätzliche Fehler unter Druck.

2. Binary Logging korrekt aktivieren

Ohne aktiviertes Binary Logging ist Point-in-Time Recovery von vornherein unmöglich, weil schlicht keine Aufzeichnung der Änderungen seit dem letzten Backup existiert. Binary Logging wird über die Konfigurationsdatei aktiviert und schreibt jede datenverändernde Operation als Event in eine Sequenz von Binlog-Dateien. Wichtig ist dabei die Wahl des Binlog-Formats: ROW zeichnet die tatsächlich geänderten Zeilen auf und ist für eine präzise Wiederherstellung deutlich zuverlässiger als STATEMENT, das nur die ausgeführte SQL-Anweisung speichert.

Ebenso entscheidend ist die Aufbewahrungsdauer der Binlogs, gesteuert über binlog_expire_logs_seconds. Wird dieser Wert zu niedrig gesetzt, werden alte Binlogs gelöscht, bevor das nächste Basis-Backup entstanden ist, und eine Point-in-Time Recovery über den gelöschten Zeitraum wird unmöglich. Als Faustregel gilt: die Aufbewahrung muss mindestens die doppelte Backup-Intervall-Dauer abdecken, damit auch bei einem verzögerten Backup-Lauf noch genug Historie vorhanden ist.

# my.cnf: Binary Logging für Point-in-Time Recovery aktivieren
[mysqld]
server-id               = 101
log_bin                 = /var/lib/mysql-binlogs/binlog
binlog_format            = ROW
binlog_row_image         = FULL
binlog_expire_logs_seconds = 604800   # 7 Tage Aufbewahrung
max_binlog_size          = 512M
sync_binlog               = 1
innodb_flush_log_at_trx_commit = 1

# GTID für robustere Point-in-Time Recovery (empfohlen)
gtid_mode                = ON
enforce_gtid_consistency = ON
log_slave_updates        = ON

3. Basis-Backup als Ausgangspunkt erstellen

Jede Point-in-Time Recovery beginnt mit einem konsistenten Basis-Backup, dessen exakte Binlog-Position bekannt sein muss. Ohne diese Startposition weiß der Recovery-Prozess nicht, ab welchem Event die Binlogs erneut angewendet werden sollen, und riskiert entweder doppelte Anwendung bereits gesicherter Änderungen oder eine Lücke im wiederhergestellten Datenbestand. Percona XtraBackup schreibt diese Position automatisch in die Datei xtrabackup_binlog_info, mysqldump mit der Option --master-data=2 in den Dump-Header als Kommentar.

In der Praxis empfiehlt sich ein tägliches physisches Backup mit XtraBackup, weil es ohne langes Sperren der Tabellen auskommt und bei großen Datenbanken deutlich schneller wiederhergestellt werden kann als ein logisches mysqldump-Backup. Die Kombination aus täglichem Basis-Backup und kontinuierlich geschriebenen Binlogs reduziert das maximale Datenverlustfenster auf die Zeit zwischen zwei Binlog-Flushes, in der Praxis oft nur wenige Sekunden.

# Basis-Backup mit Percona XtraBackup erstellen
xtrabackup --backup \
  --target-dir=/backup/base/$(date +%F) \
  --user=backup_user --password="$BACKUP_PW"

# Binlog-Position des Backups anzeigen (wichtig für Point-in-Time Recovery)
cat /backup/base/$(date +%F)/xtrabackup_binlog_info
# Ausgabe z. B.: binlog.000042    157      3E11FA47-...:1-928472

# Backup vorbereiten (Redo-Log anwenden)
xtrabackup --prepare --target-dir=/backup/base/$(date +%F)

4. Binlog-Formate und Events verstehen

Ein Binlog besteht aus einer Sequenz einzelner Events: Query-Events, Row-Events, Xid-Events für Transaktionsabschlüsse und Rotate-Events beim Wechsel in eine neue Binlog-Datei. Für Point-in-Time Recovery ist entscheidend, dass jedes Event entweder über eine Byte-Position innerhalb einer Datei oder, bei aktiviertem GTID, über eine global eindeutige Transaktions-ID adressierbar ist. Beide Adressierungsarten erlauben es, das Replay exakt vor einem bestimmten Event zu stoppen.

Mit SHOW BINARY LOGS und SHOW BINLOG EVENTS IN lassen sich die vorhandenen Dateien und deren Inhalt direkt in der MySQL-Shell inspizieren, bevor mysqlbinlog für die eigentliche Recovery eingesetzt wird. Das ist besonders hilfreich, um vor einem Recovery-Vorgang zu prüfen, in welcher Datei und an welcher Position sich das fehlerhafte Statement befindet, ohne bereits die vollständige Textausgabe von mysqlbinlog durchsuchen zu müssen.

-- Vorhandene Binlog-Dateien auflisten
SHOW BINARY LOGS;

-- Events einer bestimmten Binlog-Datei inspizieren
SHOW BINLOG EVENTS IN 'binlog.000042' LIMIT 20;

-- Aktuelle Schreibposition des Servers ermitteln
SHOW MASTER STATUS;
-- File: binlog.000045 | Position: 738291 | Executed_Gtid_Set: 3E11FA47-...:1-931205

5. mysqlbinlog: Inhalte inspizieren und filtern

Das Kommandozeilenwerkzeug mysqlbinlog übersetzt die binären Binlog-Dateien in lesbares SQL und ist das zentrale Werkzeug jeder Point-in-Time Recovery. Mit den Optionen --start-datetime und --stop-datetime lässt sich ein Zeitfenster eingrenzen, mit --start-position und --stop-position ein exakter Byte-Bereich. Für die eigentliche Fehlersuche vor einer Recovery ist die Kombination mit grep praktisch, um die genaue Position eines fehlerhaften Statements zu finden, bevor der Stop-Punkt final festgelegt wird.

Ein häufiger Fehler ist, das Zeitfenster zu grob zu wählen und dadurch entweder den fehlerhaften Befehl versehentlich mit zu replayen oder zu viele gutartige Transaktionen wegzulassen. Deshalb empfiehlt sich immer zuerst ein Trockenlauf mit reiner Textausgabe in eine Datei, die manuell durchsucht wird, bevor das Ergebnis tatsächlich gegen die Datenbank ausgeführt wird. Erst wenn die exakte Stop-Position bestätigt ist, folgt der produktive Replay-Schritt.

# Binlog-Inhalt als lesbares SQL ausgeben und nach dem Fehler suchen
mysqlbinlog --base64-output=decode-rows -v \
  /var/lib/mysql-binlogs/binlog.000045 > /tmp/binlog_dump.sql

grep -n -B5 "DELETE FROM \`sales_order\`" /tmp/binlog_dump.sql

# Gezielt bis kurz vor den fehlerhaften Befehl extrahieren
mysqlbinlog --start-datetime="2026-07-23 14:00:00" \
  --stop-datetime="2026-07-23 14:36:59" \
  /var/lib/mysql-binlogs/binlog.000045 > /tmp/replay_safe.sql

6. Der Recovery-Ablauf im Detail

Der vollständige Ablauf einer Point-in-Time Recovery gliedert sich in drei klar getrennte Phasen. In der ersten Phase wird das vorbereitete Basis-Backup auf einen separaten Datenverzeichnis-Pfad kopiert und der MySQL-Server mit diesem Datenbestand gestartet, idealerweise auf einer isolierten Instanz und nicht direkt auf dem Produktionsserver. In der zweiten Phase wird mit mysqlbinlog der Bereich von der im Backup vermerkten Startposition bis zur ermittelten Stop-Position extrahiert und gegen diese wiederhergestellte Instanz ausgeführt.

In der dritten Phase folgt die Verifikation: stichprobenartige Abfragen prüfen, ob die erwarteten Datensätze wieder vorhanden sind und die fehlerhafte Operation tatsächlich nicht mehr enthalten ist. Erst nach erfolgreicher Verifikation wird die wiederhergestellte Instanz zur neuen Produktionsdatenbank, entweder durch Umschalten der Anwendungskonfiguration oder durch selektiven Export der betroffenen Tabellen zurück in die laufende Produktionsdatenbank, falls nur ein Teilbereich betroffen war.

7. GTID- versus positionsbasierte Recovery

Vor MySQL 5.6 und ohne aktiviertes GTID musste jede Point-in-Time Recovery mit exakten Datei-Byte-Positionen arbeiten, was bei mehreren Binlog-Dateien und Rotationen fehleranfällig ist. Mit aktiviertem gtid_mode = ON erhält jede Transaktion eine global eindeutige ID, unabhängig von Dateigrenzen. mysqlbinlog unterstützt dann Optionen wie --exclude-gtids, um gezielt eine einzelne problematische Transaktion aus dem Replay auszuschließen, ohne das gesamte Zeitfenster manuell eingrenzen zu müssen.

GTID-basierte Recovery ist besonders in Replikationssetups mit mehreren Servern von Vorteil, weil die Transaktions-ID auf allen Knoten identisch bleibt und ein Replay auf einem anderen Server als dem ursprünglichen Master keine Positions-Umrechnung erfordert. In der Praxis empfiehlt sich GTID daher für jede neue MySQL-Installation, während positionsbasierte Recovery vor allem in Alt-Systemen ohne GTID noch relevant bleibt.

8. Recovery-Drill Schritt für Schritt

Ein Recovery-Plan, der nie geübt wurde, ist im Ernstfall nur eine Theorie. Ein regelmäßiger Point-in-Time Recovery-Drill auf einer Test-Instanz deckt Lücken auf, bevor sie im Ernstfall teuer werden: fehlende Berechtigungen für den Backup-User, zu kurze Binlog-Aufbewahrung, veraltete Runbooks oder schlicht ein Team, das den Ablauf noch nie unter Zeitdruck durchgespielt hat. Ein guter Drill simuliert bewusst einen fehlerhaften DELETE-Befehl, misst die Zeit bis zur vollständigen Wiederherstellung und dokumentiert jeden Schritt.

Die folgende Sequenz zeigt einen vollständigen Drill von der Fehlersimulation bis zur Verifikation, wie er sich in einer Staging-Umgebung monatlich wiederholen lässt. Wichtig ist, das Ergebnis jedes Drills schriftlich festzuhalten, inklusive der tatsächlich benötigten Zeit, damit sich die Recovery Time Objective realistisch einschätzen lässt und nicht auf Annahmen beruht.

# Kompletter Recovery-Drill auf einer isolierten Test-Instanz
set -euo pipefail

# 1. Fehler simulieren (nur auf der Test-Instanz!)
mysql -e "DELETE FROM sales_order WHERE created_at < '2026-07-20';"

# 2. Basis-Backup auf Test-Datenverzeichnis kopieren
rsync -a /backup/base/2026-07-23/ /var/lib/mysql-drill/
chown -R mysql:mysql /var/lib/mysql-drill/

# 3. Test-Instanz mit wiederhergestelltem Basis-Backup starten
mysqld --datadir=/var/lib/mysql-drill --port=3307 --socket=/tmp/drill.sock &

# 4. Binlogs bis kurz vor den simulierten Fehler replayen
mysqlbinlog --start-position=157 --stop-datetime="2026-07-23 14:36:59" \
  /var/lib/mysql-binlogs/binlog.000045 | \
  mysql --socket=/tmp/drill.sock -u root

# 5. Verifikation und Zeitmessung dokumentieren
mysql --socket=/tmp/drill.sock -e "SELECT COUNT(*) FROM sales_order;"

9. Fallstricke und Performance-Aspekte

Der häufigste Fallstrick bei Point-in-Time Recovery ist eine Binlog-Aufbewahrung, die kürzer ist als der Abstand zwischen zwei Basis-Backups. Wird ein Backup übersprungen, etwa wegen eines vollen Datenträgers, und die Binlogs sind bereits rotiert, entsteht eine unwiederbringliche Lücke. Ein zweiter Fallstrick ist das Fehlen von log_slave_updates auf Replikaten, wodurch auf einem Replika replizierte Änderungen nicht erneut in dessen eigene Binlogs geschrieben werden und dort für eine Recovery fehlen.

Bei sehr großen Binlog-Mengen kann das Replay selbst zur Engpassquelle werden, weil mysqlbinlog die Events sequenziell und einzeln an den Server sendet. Für sehr lange Zeitfenster empfiehlt sich, das Replay in mehreren kleineren Abschnitten durchzuführen und zwischendurch Checkpoints zu prüfen, statt einen einzigen mehrstündigen Lauf zu riskieren, der bei einem Abbruch von vorn beginnen müsste. Auch innodb_flush_log_at_trx_commit und sync_binlog sollten in Produktion auf sicheren Werten stehen, sonst kann selbst das Binlog Lücken gegenüber tatsächlich committeten Transaktionen aufweisen.

10. Zusammenfassung

Point-in-Time Recovery schließt die Lücke, die ein reines Backup offen lässt: die Wiederherstellung bis zur letzten korrekten Transaktion vor einem Fehler. Voraussetzung ist aktiviertes Binary Logging im ROW-Format mit ausreichender Aufbewahrungsdauer, ein regelmäßiges Basis-Backup mit dokumentierter Binlog-Startposition und die Fähigkeit, mysqlbinlog gezielt für ein bestimmtes Zeitfenster oder eine bestimmte GTID einzusetzen.

Der größte Hebel liegt nicht in der Technik allein, sondern in der Übung: ein Recovery-Ablauf, der monatlich als Drill durchgespielt wird, deckt fehlende Berechtigungen, zu kurze Aufbewahrungsfristen und veraltete Dokumentation auf, bevor ein echter Vorfall die Datenbank betrifft. GTID-basierte Recovery reduziert dabei zusätzlich die Fehleranfälligkeit gegenüber rein positionsbasierten Abläufen.

11. FAQ: Point-in-Time Recovery mit Binlogs