Wir alle wissen, dass die regelmäßige Durchführung von Sicherungen geplant werden muss. Ein vollständiges Backup (d. h. ein Schnappschuss der Daten zu einem gegebenen Zeitpunkt) kann in MySQL mit verschiedenen Tools erfolgen. So ermöglicht etwa InnoDB Hot Backup eine online durchgeführte physische Sicherung der InnoDB-Datendateien ohne Sperre, und mysqldump erlaubt ein online erstelltes logisches Backup. Wir werden an dieser Stelle mysqldump beschreiben.

Nehmen wir an, Sie erstellen am Sonntag um 13:00 Uhr ein Backup. Dies ist ein Zeitpunkt, an dem die Serverlast niedrig ist. Der folgende Befehl erstellt ein vollständiges Backup all Ihrer InnoDB-Tabellen in allen Datenbanken:

shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

Dies ist eine online ausgeführte, nicht sperrende Sicherung, die Lese- und Schreibvorgänge in den Tabellen nicht beeinträchtigt. Wir haben bereits weiter oben gesagt, dass unsere Tabellen InnoDB-Tabellen sind, d. h. --single-transaction verwendet eine konsistente Leseoperation und gewährleistet, dass die von mysqldump erkannten Daten nicht geändert werden. (Änderungen, die von anderen Clients an den InnoDB-Tabellen durchgeführt werden, werden vom mysqldump-Prozess nicht erkannt.) Wenn auch andere Tabellentypen vorhanden sind, müssen wir davon ausgehen, dass diese während des Sicherungsvorgangs nicht geändert werden. So müssen wir beispielsweise für die MyISAM-Tabellen in der mysql-Datenbank voraussetzen, dass während der Sicherung keine administrativen Änderungen an den MySQL-Konten vorgenommen werden.

Am Ende steht eine .sql-Datei, die von mysqldump erzeugt wird. Sie enthält einen Satz von INSERT-SQL-Anweisungen, die zum Neuladen der gespeicherten Tabellen zu einem späteren Zeitpunkt verwendet werden können.

Vollständige Backups sind erforderlich, aber manchmal unpraktisch. Sie erzeugen große Sicherungsdateien, und die Erstellung dauert recht lange. Außerdem sind sie nicht optimal in dem Sinn, dass alle aufeinanderfolgenden Sicherungen alle Daten enthalten – d. h. auch solche, die seit dem letzten vollständigen Backup nicht geändert wurden. Wenn wir also ein vollständiges Backup als Ausgangspunkt erstellt haben, ist es effektiver, nachfolgend inkrementelle Sicherungen zu erstellen. Diese sind kleiner, und ihre Erstellung verläuft schneller. Der Nachteil besteht darin, dass Sie im Fehlerfall nicht einfach ein einziges vollständiges Backup aufspielen können, um Ihre Daten wiederherzustellen. Sie müssen vielmehr auch die inkrementellen Sicherungen aufspielen, um die dort gespeicherten Änderungen wiederherzustellen.

Um inkrementelle Sicherungen zu erstellen, müssen wir die inkrementellen Änderungen speichern. Der MySQL-Server sollte immer mit der Option --log-bin gestartet werden, damit er diese Änderungen beim Aktualisieren von Daten in einer Datei speichert. Diese Option aktiviert das binäre Loggen, d. h. der Server schreibt jede SQL-Anweisung, die Daten ändert, in eine Datei: das MySQL-Binärlog. Wenn man einen Blick in das Datenverzeichnis eines MySQL-Servers wirft, der mit der Option --log-bin gestartet wurde und schon ein paar Tage läuft, dann findet man dort die folgenden MySQL-Binärlogdateien:

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

Bei jedem Neustart erstellt der MySQL-Server eine neue Binärlogdatei, für deren Benennung er die nächste Nummer in der Nummernfolge verwendet. Solange der Server ausgeführt wird, können Sie ihn auch manuell anweisen, die aktuelle Binärlogdatei zu schließen und eine neue zu erstellen. Hierzu verwenden Sie die SQL-Anweisung FLUSH LOGS oder geben den Befehl mysqladmin flush-logs ein. Auch mysqldump bietet eine Option zum Schreiben der Logs auf die Festplatte. Die .index-Datei im Datenverzeichnis enthält eine Liste aller MySQL-Binärlogs in diesem Verzeichnis. Die Datei wird zur Replikation verwendet.

Die MySQL-Binärlogs sind für die Wiederherstellung wichtig, denn sie bilden den Satz inkrementeller Backups. Wenn Sie die Logs beim Erstellen einer vollständigen Sicherung auf Festplatte schreiben, dann enthält ein ggf. nachfolgend erstelltes Backup alle Änderungen seit dem Backup. Wir wollen den obigen mysqldump-Befehl ein wenig abändern, damit er die MySQL-Binärlogs zum Zeitpunkt einer vollständigen Sicherung auf die Festplatte schreibt und die Speicherauszugsdatei den Namen des neuen aktuellen Binärlogs enthält:

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases > backup_sunday_1_PM.sql

Nach Ausführung dieses Befehls enthält das Datenverzeichnis eine neue Binärlogdatei namens gbichot2-bin.000007. Die am Ende erstellte .sql-Datei enthält die folgenden Zeilen:

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

Weil der mysqldump-Befehl ein vollständiges Backup erstellt hat, bedeuten diese Zeilen zweierlei:

Am Montag um 13:00 Uhr erstellen wir eine inkrementelle Sicherungsdatei, indem wir die Logdateien auf die Festplatte schreiben, um so eine neue Binärlogdatei zu erstellen. So erstellt beispielsweise der Befehl mysqladmin flush-logs die Datei gbichot2-bin.000008. Alle Änderungen, die zwischen Sonntag, 13:00 Uhr (Erstellung des vollständigen Backups), und Montag, 13:00 Uhr, vorgenommen wurden, sind in der Datei gbichot2-bin.000007 enthalten. Diese inkrementelle Sicherung ist wichtig, weswegen sie an einen sicheren Ort kopiert werden sollte. (Sie können sie etwa auf Band oder DVD sichern oder auf einen anderen Computer kopieren.) Am Dienstag um 13:00 Uhr wird der Befehl mysqladmin flush-logs erneut ausgeführt. Alle Änderungen, die zwischen Montag, 13:00 Uhr, und Dienstag, 13:00 Uhr, angefallen sind, sind in der Datei gbichot2-bin.000008 vermerkt (auch diese sollten Sie an einen sicheren Ort kopieren).

Die MySQL-Binärlogs benötigen Festplattenspeicher. Um Speicher freizugeben, sollten Sie sie von Zeit zu Zeit bereinigen. Eine Möglichkeit, dies zu tun, besteht im Löschen von Binärlogs, die nicht mehr benötigt werden (beispielsweise nachdem ein vollständiges Backup erstellt wurde):

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases --delete-master-logs > backup_sunday_1_PM.sql

Hinweis: Das Löschen von MySQL-Binärlogs mit mysqldump --delete-master-logs kann gefährlich sein, wenn Ihr Server ein Replikations-Master ist, denn unter Umständen haben die Slave-Server den Inhalt des Binärlogs noch nicht vollständig verarbeitet. Die Beschreibung der PURGE MASTER LOGS-Anweisung erläutert, was zu prüfen ist, bevor die MySQL-Binärlogs gelöscht werden. Siehe auch Abschnitt 13.6.1.1, „PURGE MASTER LOGS“.