11.3 Backup und Point-In-Time-Recovery
Sicherlich legen Sie in bestimmten Abständen ein vollständiges Backup Ihrer Datenbanken an. Sollte es zu einem Datenverlust kommen, können Sie das Backup hoffentlich wieder einspielen (Restore). Änderungen, die seit dem Anlegen des Backups vorgenommen wurden, sind aber leider verloren – es sei denn, Sie nutzen Point-In-Time-Recovery.
Point-In-Time-Recovery bedeutet, dass Sie die Änderungen am Datenbestand, die seit einem bestimmten Zeitpunkt in der Vergangenheit vorgenommen worden sind, ebenfalls restaurieren können. Das Mittel, das Ihnen diesen Kunstgriff ermöglicht, ist das binäre Logging. Dabei werden die Änderungen inkrementell in Binärdateien geschrieben, aus denen sie im Bedarfsfall wiederhergestellt werden können.
Damit Sie Point-In-Time-Recovery nutzen können, müssen Sie zunächst dafür sorgen, dass Ihr Server die binären Logfiles schreibt. Fügen Sie der MariaDB-Konfiguration dazu folgende Zeilen hinzu (bitte denken Sie unter CentOS daran, das Verzeichnis zu erstellen und zu berechtigen, sehen Sie sich dazu auch Listing 11.10 an):
log_bin = /var/log/mysql/mariadb-bin.log
max_binlog_size = 100M
innodb_flush_log_at_trx_commit=1
sync_binlog=1
Listing 11.57 Binäres Logging aktivieren
Nach einem Neustart des Datenbankservers werden die gesamten binären Log-Dateien unter /var/log/mysql/ angelegt. Für die folgenden Beispiele nutzen wir eine Datenbank mit dem Namen sonnensystem.
11.3.1 Restore zum letztmöglichen Zeitpunkt
Erstellen Sie ein vollständiges Backup der Datenbank mit diesem Kommando:
mysqldump --flush-logs --single-transaction sonnensystem -u root -p > backup.sql
Listing 11.58 Vollständiges Backup einer Datenbank
Wenn nun zu einem späteren Zeitpunkt – das heißt, nachdem seit dem Backup weitere Daten in der Datenbank geändert wurden – eine Katastrophe passiert und ein Restore notwendig wird, spielen Sie zunächst das Backup wieder ein:
mysql -u root -p < backup.sql
Listing 11.59 Restore der gesicherten Datenbank
Nun müssen noch die Änderungen restauriert werden, die seit dem Erstellen des Backups vorgenommen wurden. Diese Änderungen finden sich in den binären Logfiles.
Finden Sie zunächst heraus, wie viele binäre Logfiles seit dem Erstellen des Backups angelegt wurden. Sollten es mehrere sein, müssen Sie zum Wiederherstellen alle Logfiles angeben. Listing 11.60 zeigt ein Restore aus zwei binären Logfiles (es handelt sich um eine einzige Zeile, die nur aufgrund der Seitenbreite umbrochen wurde):
mysqlbinlog --database sonnensystem /var/log/mysql/mariadb-bin.000001 \
/var/log/mysql/mariadb-bin.000002 | mysql -u root -p
Listing 11.60 Restore aus den binären Logfiles
11.3.2 Restore zu einem bestimmten Zeitpunkt
Wenn Sie einen falschen Befehl eingegeben haben, wie beispielsweise das versehentliche Löschen einer Tabelle, können Sie per Point-In-Time-Recovery auch ein Restore genau bis zu dem Zeitpunkt durchführen, bevor Sie die Tabelle gelöscht haben.
Nehmen wir zum Beispiel an, die Tabelle planeten in der Datenbank sonnensystem ist mit dem Befehl drop table planeten gelöscht worden. Der Fehler ist Ihnen glücklicherweise sofort aufgefallen. Suchen Sie im Verzeichnis /var/log/mysql/ das aktuellste der dort abgelegten binären Logfiles (Sie benötigen das aktuellste, weil der Fehler ja gerade erst passiert ist).
In unserer Musterdatenbank hat das Verzeichnis beispielsweise den in Listing 11.61 gezeigten Inhalt:
# ls -l /var/log/mysql
total 60
-rw-rw---- 1 mysql mysql 353 28. Aug 17:17 mariadb-bin.000001
-rw-rw---- 1 mysql mysql 353 28. Aug 17:20 mariadb-bin.000002
-rw-rw---- 1 mysql mysql 353 6. Okt 09:29 mariadb-bin.000003
-rw-rw---- 1 mysql mysql 1855 6. Okt 11:48 mariadb-bin.000004
-rw-rw---- 1 mysql mysql 136 6. Okt 10:22 mariadb-bin.index
-rw-rw---- 1 mysql mysql 33085 6. Okt 11:44 mysqld.log
Listing 11.61 Inhalt des Verzeichnisses »/var/log/mysql/«
Das aktuellste binäre Logfile ist hier die Datei mariadb-bin.000004. Führen Sie auf der Kommandozeile den folgenden Befehl aus:
mysqlbinlog ---database sonnensystem /var/log/mysql/mariadb-bin.000004
Listing 11.62 Den Inhalt des binären Logfiles anschauen
Sie erhalten unter anderem die Ausgabe aus Listing 11.63. Dort sehen Sie, dass an Position 1897 das Löschen der Tabelle gestartet wurde (at 1897) und dass es an Position 2019 beendet wurde (end_log_pos 2019):
# at 1855
#181006 12:06:40 server id 105 end_log_pos 1897 CRC32 0xac3f2172 \
GTID 0-105-11 ddl
/*!100001 SET @@session.gtid_seq_no=11*//*!*/;
# at 1897
#181006 12:06:40 server id 105 end_log_pos 2019 CRC32 0x60355708 \
Query thread_id=30 exec_time=0 error_code=0
SET TIMESTAMP=1538820400/*!*/;
DROP TABLE `planeten` /* generated by server */
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Listing 11.63 Ergebnis der Suche im binären Logfile (Auszug)
Nun wird zunächst das letzte vollständige Backup wieder eingespielt:
mysql -u root -p < backup.sql
Listing 11.64 Restore der gesicherten Datenbank
Als Nächstes werden alle Änderungen bis zum fatalen DROP TABLE restauriert:
mysqlbinlog --database=sonnensystem /var/log/mysql/mariadb-bin.000004 \
--stop-position=1897 | mysql -u root -p
Listing 11.65 Restore der Daten bis zum »Drop Table«
Falls Sie nach dem DROP TABLE noch weitergearbeitet haben, können Sie auch diese Änderungen noch zurückholen, indem Sie auch die Daten nach dem Zeitstempel 1897 zurückspielen:
mysqlbinlog --database=sonnensystem /var/log/mysql/mariadb-bin.000007 \
--start-position=1897 | mysql -u root -p
Listing 11.66 Restore der Daten ab dem Zeitstempel »1897«