34.3Backups

Auch wenn Sie nicht vorhaben, sich auf die Datenbankadministration zu spezialisieren, sollten Sie wissen, wie Sie ein fehlerfreies Backup einer MySQL- oder MariaDB-Datenbank durchführen. Dabei gibt es mehr Varianten und Spielarten, als man es für möglich halten möchte. Dieser Abschnitt stellt die Kommandos mysqldump und mylvmbackup vor. Wenn Sie außer den zu bestimmten Zeiten durchgeführten Backups auch kontinuierliche Backups benötigen, aktivieren Sie das (binäre) Logging. Damit wird jede Änderung an der Datenbank in einer Logging-Datei festgehalten. Die Logging-Dateien können auch als Basis für die Replikation der Datenbank auf einen zweiten Server verwendet werden.

Die Vielfalt der MySQL-Backup-Verfahren hat unter anderem damit zu tun, dass MySQL verschiedene Tabellentypen kennt: Beim Anlegen einer Tabelle kann der Datenbankentwickler bzw. das Programm zwischen verschiedenen Tabellentypen wählen. Es ist sogar möglich, innerhalb einer Datenbank Tabellen mit unterschiedlichen Typen zu verwenden. Die beiden wichtigsten Typen sind momentan MyISAM und InnoDB, außerdem Aria bei MariaDB. Je nach Tabellentyp stehen nicht nur unterschiedliche Zusatzfunktionen zur Verfügung (Transaktionen, Foreign-Key-Regeln, Volltextsuche), auch das zugrunde liegende Dateiformat und das optimale Backup-Verfahren variieren.

Wenn Sie nicht wissen, welche Datenbanken Ihr MySQL-Server verwaltet und welchen Typ die darin enthaltenen Tabellen aufweisen, führen Sie im Kommandozeilen-Client mysql das folgende SQL-Kommando aus. Das resultierende Ergebnis listet alle Tabellen auf. Die Spalte table_schema gibt dabei den Datenbanknamen an, engine den Tabellentyp.

mysql> SELECT table_schema, table_name, engine FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='dbname' ORDER BY table_schema, table_name; table_schema table_name engine ----------------------------------- mylibrary authors InnoDB mylibrary categories InnoDB mylibrary counters MyISAM mylibrary fulltitles MyISAM mylibrary languages InnoDB ...

mysqldump

Das zum Lieferumfang von MySQL zählende Kommando mysqldump erstellt ein Backup einer MySQL-Datenbank in Form von SQL-Anweisungen. Die resultierende Datei kann später mit mysql wieder in eine bereits vorhandene Datenbank eingespielt werden. Die prinzipielle Syntax sieht so aus:

user$ mysqldump -u root -p [optionen] datenbankname > backup.sql

Details des Backups können Sie durch zahllose Optionen steuern (siehe auch mysqldump --help). Die optimale Kombination von Optionen hängt unter anderem davon ab, in welchem Format die Tabellen Ihrer MySQL-Datenbank vorliegen, also im MyISAM- oder im InnoDB-Format.

Bei MyISAM-Tabellen reicht die Option --lock-all-tables. Sie bewirkt, dass mysqldump am Beginn des Backups alle Tabellen durch ein LOCK-Kommando blockiert und erst am Ende des Backups wieder freigibt. Standardmäßig führt mysqldump das Locking hingegen Tabelle für Tabelle aus, also immer nur für die Tabelle, die gerade bearbeitet wird. Das kann aber zur Folge haben, dass sich einzelne Tabellen während des Backups verändern und dass die Verknüpfungen zwischen den Tabellen letztlich nicht mehr stimmen.

user$ mysqldump -u root -p --lock-all-tables dbname > backup.sql

Wesentlich mehr Optionen brauchen Sie für ein Backup einer Datenbank mit InnoDB-Tabellen: Mit --skip-opt deaktivieren Sie einige, speziell für MyISAM gedachte Standardoptionen. --single-transaction bewirkt, dass das gesamte Backup im Rahmen einer Transaktion durchgeführt wird. Damit ist ausgeschlossen, dass sich während des Backups Daten ändern, was zu inkonsistenten Verknüpfungen zwischen den Tabellen führen kann. --disable-keys bewirkt, dass beim späteren Einlesen der Daten vorübergehend die Indexaktualisierung deaktiviert wird. Der Index wird erst zum Schluss vollständig neu erzeugt, was wesentlich schneller ist. Dank --create-options verwendet mysqldump bei der Ausgabe alle MySQL-spezifischen Optionen des CREATE-TABLE-Kommandos.

--quick bewirkt, dass mysqldump die Tabellen Datensatz für Datensatz vom Server abholt, anstatt sie alle auf einmal zu lesen. Das ist bei großen Tabellen effizienter. Aufgrund von --extended-insert erzeugt mysqldump INSERT-Kommandos, die mehrere Datensätze auf einmal einfügen, was einerseits die Größe der Backup-Datei ein wenig reduziert und später das Wiedereinspielen der Daten beschleunigt. --add-drop-table bewirkt, dass mysqldump jedem CREATE-TABLE-Kommando ein DROP-TABLE-Kommando voranstellt. Das vermeidet Fehler, wenn – z.B. aufgrund eines unvollständig eingespielten Backups – einzelne Tabellen bereits in der Datenbank existieren.

user$ mysqldump -u root -p --skip-opt --single-transaction \ --disable-keys --create-options --quick \ --extended-insert --add-drop-table dbname > backup.sql

Bleibt noch die naheliegende Frage, wie Sie am besten vorgehen, wenn eine Datenbank sowohl MyISAM- als auch InnoDB-Tabellen enthält. In diesem Fall verwenden Sie mysqldump einfach ohne weitere Optionen. Falls die Datenbank während des Backups genutzt wird, kann es nun aber passieren, dass sich während des Backups Tabellen verändern. Wenn Sie Pech haben, ist das Backup dann inkonsistent, d.h., miteinander verknüpfte Tabellen enthalten Verweise auf nicht mehr existente Datensätze. Dieses Problem lässt sich mit mysqldump nicht umgehen. Am besten verwenden Sie stattdessen ein anderes Backup-Verfahren (siehe den folgenden Abschnitt zu mylvmbackup).

Wenn Sie ein Backup aller Datenbanken erstellen (nicht ein Backup einer bestimmten Datenbank), geben Sie die Option --all-databases an.

Beachten Sie, dass mysqldump standardmäßig weder Stored Procedures (SPs) noch Trigger speichert. Wenn Sie das wünschen, geben Sie zusätzlich die Optionen --routines und --triggers an.

Um eine Datenbank aus einem Backup wiederherzustellen, erzeugen Sie zuerst die betreffende Datenbank (falls es sie noch nicht gibt). Anschließend übergeben Sie die Backup-Datei an mysql. Dabei stellt die Option --default-character-set sicher, dass die im UTF8-Format gespeicherten Zeichenketten auch tatsächlich in diesem Format gelesen werden.

user$ mysqladmin create dbname user$ mysql -u root -p --default-character-set=utf8 dbname < backup.sql

Mit mysqldump erzeugte Backup-Dateien sind aufgrund des Textformats sehr groß. Dieses Problem umgehen Sie am einfachsten dadurch, dass Sie die Backups sofort komprimieren bzw. beim Wiedereinspielen direkt dekomprimieren. Die entsprechenden Kommandos sehen so aus:

user$ mysqldump [optionen] dbname | gzip -c > backup.sql.gz user$ gunzip -c backup.sql.gz | mysql [optionen] dbname

Die Komprimierung durch gzip kostet allerdings eine Menge CPU-Ressourcen. Wenn Sie Rechenzeit sparen möchten und sich dafür mit etwas größeren Backup-Dateien abfinden können, empfiehlt sich der Einsatz des Komprimierkommandos lzop aus dem gleichnamigen Paket:

user$ mysqldump [optionen] dbname | lzip -c > backup.sql.lzo user$ lzop -c -d backup.sql.lzo | mysql [optionen] dbname

mylvmbackup

Das Kommando mysqldump funktioniert für kleine Datenbanken ausgezeichnet. Allerdings sind die betroffenen Tabellen während des Backups nur eingeschränkt verwendbar – bei MyISAM-Tabellen aufgrund des Lockings, bei InnoDB-Tabellen aufgrund einer lange andauernden Transaktion. Für Datenbanksysteme, die im 24-Stunden-Betrieb unterbrechungsfrei laufen sollen, wird das mit zunehmender Datenbankgröße zu einem echten Problem.

Die optimale Lösung für dieses Problem wäre ein Hot-Backup-Verfahren, das störungsfrei im laufenden Betrieb funktioniert. MySQL bietet ein entsprechendes Backup-Programm an, dieses ist aber nur im Rahmen einer kostenpflichtigen MySQL-Enterprise-Lizenz verfügbar. Eine gute Alternative ist das Open-Source-Programm XtraBackup:

https://www.percona.com/software/mysql-database/percona-xtrabackup

Eine andere Möglichkeit bietet das hier vorgestellte Kommando mylvmbackup: Es minimiert die Zeit, während der die Datenbank blockiert ist, auf wenige Sekunden. Es setzt allerdings voraus, dass sich sämtliche Datenbankdateien in einem Logical Volume befinden.

Das Perl-Script mylvmbackup kann bei vielen Distributionen als Paket installiert werden. Wenn Ihre Distribution kein entsprechendes Paket anbietet, laden Sie das Script von dieser Seite herunter:

http://www.lenzg.net/mylvmbackup

Ich setze im Folgenden voraus, dass Sie mit LVM bereits vertraut sind (siehe Abschnitt 2.7, »RAID, LVM und Verschlüsselung«, und Abschnitt 23.18, »LVM«). Worauf es hier ankommt, ist die Möglichkeit, sogenannte LVM-Snapshots zu erstellen: Damit können Sie den Inhalt eines Logical Volumes (LV) gewissermaßen einfrieren und in diesem Zustand als neues LV in das Dateisystem einbinden.

Das ursprüngliche LV kann weiter verändert werden. Allerdings bleibt das Snapshot-LV nur verwendbar, solange es genug Platz für Kopien aller geänderten Datenblöcke gibt. Diese Datenblöcke werden in einen Snapshot-Puffer kopiert, dessen Größe Sie beim Erzeugen des Snapshots angeben. LVM-intern wird jeder Datenblock vor seiner Veränderung kopiert. Der Snapshot sieht so den alten Zustand, während das ursprüngliche LV den geänderten Datenblock verwendet.

mylvmbackup nutzt einen LVM-Snapshot, um alle Datenbankdateien aus dem Verzeichnis /var/lib/mysql zu sichern. Im Detail sieht die Vorgehensweise des Scripts so aus:

Vielleicht wundern Sie sich darüber, dass es keinerlei Maßnahmen gibt, um die InnoDB-Dateien zu synchronisieren. Das ist nicht notwendig, weil Transaktionen für InnoDB-Tabellen ACID-konform ausgeführt werden. Aus den sogenannten Masterspace-Dateien, die alle Tabellen enthalten, und den dazugehörigen InnoDB-Logging-Dateien kann der InnoDB-Treiber zu jedem Zeitpunkt alle bereits abgeschlossenen Transaktionen vollständig wiederherstellen, auch wenn die Änderungen im Masterspace noch gar nicht gespeichert wurden. Diese Sicherheitsmaßnahme schützt primär gegen Datenverluste bei einem Absturz oder Stromausfall, vereinfacht aber auch das Backup. Wichtig ist nur, dass der InnoDB-Masterspace und die Logging-Dateien exakt zum gleichen Zeitpunkt für das Backup eingefroren werden – und das ist durch den LVM-Snapshot sichergestellt.

Das so erstellte Backup unterscheidet sich in zwei Punkten von mysqldump-Resultaten: Erstens erfasst es grundsätzlich alle Datenbanken inklusive aller Zusatzdaten wie Stored Procedures, Trigger, Zugriffsrechte etc. Und zweitens liegt es in binärer Form vor. Das hat zur Folge, dass nur alle Datenbanken zusammen wiederhergestellt werden können und dass zum Wiedereinspielen des Backups ein MySQL-Server in derselben Konfiguration und möglichst auch mit derselben MySQL-Versionsnummer erforderlich ist.

Die Konfiguration erfolgt in der Datei /etc/mylvmbackup.conf. Die folgenden Zeilen zeigen eine Beispielkonfiguration. Dabei setze ich voraus, dass es für das Verzeichnis /var/lib/mysql eine eigene LVM-Partition mit dem Namen /dev/vg1/mysql gibt.

# /etc/mylvmbackup.conf [mysql] user = root password = ******* host = localhost port = 3306 socket = mycnf = /etc/mysql/my.cnf [lvm] vgname = vg1 lvname = mysql backuplv = lvsize = 5G [fs] xfs=0 mountdir = /var/cache/mylvmbackup/mnt/ backupdir = /var/cache/mylvmbackup/backup/ relpath = [tools] ... (normalerweise keine Änderungen) [misc] backuptype = tar prefix = backup tararg = cvzf tarsuffixarg = rsyncarg = -avWP datefmt = %Y%m%d_%H%M%S innodb_recover = 1 pidfile = /var/tmp/mylvmbackup_recoverserver.pid

Dazu einige Anmerkungen: vgname und lvname geben den VG- und LV-Namen an. Daraus wird der LVM-Device-Name /dev/vgname/lvname zusammengesetzt. Mit backuplv können Sie der Snapshot-Partition einen bestimmten Namen geben. Standardmäßig verwendet das Backup-Script mysql_snapshot. lvsize gibt die Größe des Snapshot-Puffers für geänderte Datenblöcke an. Der Puffer muss so groß sein, dass darin alle Datenblöcke der MySQL-Partition Platz finden, die sich während des Backups ändern. 5 GByte ist schon recht großzügig bemessen. mylvmbackup verrät zum Schluss, wie viel Speicher während des Backups tatsächlich verwendet wurde. Mit dieser Information können Sie die Einstellung optimieren.

backupdir gibt an, wo mylvmbackup die Backup-Dateien speichern soll. Das Verzeichnis sollte sich möglichst in einem anderen LV befinden als /var/lib/mysql. relpath gibt an, wo sich das Verzeichnis /var/lib/mysql relativ zum LV-Mount-Punkt befindet. Wenn es ein eigenes LV für /var/lib/mysql gibt, ist relpath leer. Wenn das LV dagegen das gesamte /var-Verzeichnis erfasst, müssen Sie relpath=lib/mysql/ verwenden.

innodb_recover gibt an, ob schon während des Backups getestet werden soll, ob der InnoDB-Masterspace und die dazugehörenden Logging-Dateien synchron sind. Ist das nicht der Fall, erfolgt im Rahmen des Backups ein InnoDB-Recovery-Durchlauf, um in der Logging-Datei aufgezeichnete Transaktionen auch im Masterspace auszuführen. Das verlängert die Zeit, die für das Backup erforderlich ist, verkürzt aber die Zeit, um später ein neues System auf der Basis des Backups einzurichten.

Das eigentliche Backup führen Sie nun so durch:

root# mylvmbackup

Anschließend finden Sie im Verzeichnis /var/cache/mylvmbackup/backup ein mit dem aktuellen Datum versehenes *.tar.gz-Archiv, das außer den eigentlichen Datenbankdateien auch eine Kopie von my.cnf sowie Logging- und Replikationsinformationen enthält (Datei backup-pos/*.pos).

Die folgenden Kommandos zeigen, wie Sie das Backup wieder einspielen. Das erste tar-Kommando ist ein wenig unübersichtlich: Es extrahiert nur jene Dateien, die sich innerhalb des Archivs im Verzeichnis backup befinden, und schreibt sie – ohne vorangestelltes backup-Verzeichnis – in das Verzeichnis /var/lib/mysql. Das zweite tar-Kommando extrahiert analog die Archivdateien backup-pos/* direkt in das Verzeichnis /etc/mysql.

root# service mysql/mariadb stop root# rm -rf /var/lib/mysql/* oder mv /var/lib/mysql/* /bak/ root# mv /etc/mysql/my.cnf /etc/mysql/my.cnf.bak root# tar -x -f backup.tar.gz -C /var/lib/mysql --strip 1 backup root# tar -x -f backup.tar.gz -C /etc/mysql --strip 1 backup-pos root# mv /etc/mysql/backup-*_my.cnf /etc/mysql/my.cnf root# service mysql/mariadb start

Inkrementelle Backups durch binäres Logging

Über ein Script können Sie Backups mit mysqldump automatisieren und so täglich oder wöchentlich ein Backup erstellen. Um einen möglichen Datenverlust im Katastrophenfall weiter zu minimieren, können Sie außerdem inkrementelle Backups aktivieren. Dazu fügen Sie in /etc/mysql/my.cnf die folgende Zeile ein und starten den MySQL-Server dann neu:

# Änderung in /etc/mysql/my.cnf log_bin = /var/log/mysql/mysql-bin.log

Der MySQL-Server protokolliert nun alle SQL-Kommandos, die Daten verändern. Die Logging-Dateien werden automatisch durchnummeriert (mysql-bin.000001, .000002 etc.). Da sich immer nur die letzte Datei ändert, ist es relativ einfach, diese Dateien in kurzen Abständen in ein Backup-Verzeichnis zu übertragen (z.B. mit rsync).

Wenn Sie Ihre Datenbanken wiederherstellen müssen, führen Sie zuerst die oben beschriebenen Restore-Schritte für das letzte Komplett-Backup aus. Anschließend spielen Sie mit mysqlbinlog alle Änderungen ein, die seither aufgetreten sind. Die Kommandoabfolge sieht so aus:

root# mysqlbinlog --start-position=<p> mysql-bin.<n> | mysql -u root -p root# mysqlbinlog mysql-bin.<n+1> | mysql -u root -p root# mysqlbinlog mysql-bin.<n+2> | mysql -u root -p ..

Bevor Sie loslegen können, brauchen Sie noch zwei Informationen: Welche Nummer hat die erste Logging-Datei, die Sie berücksichtigen müssen (<n>)? Und mit welcher Position innerhalb der ersten Datei beginnen Sie (<p>)? mylvmbackup hat diese Informationen zum Glück im Backup-Archiv gespeichert. Wenn Sie die Daten wie oben beschrieben extrahiert haben, finden Sie die erforderlichen Informationen in den ersten zwei Zeilen der Datei /etc/mysql/*_msql.pos:

root# less /etc/mysql/backup-20101128_145023_mysql.pos Master:File=mysql-bin.000016 Master:Position=98 ...

Ist das binäre Logging einmal aktiviert, ist es nur noch ein kleiner Schritt zur Replikation: Damit synchronisieren Sie einen zweiten MySQL-Server mit dem ersten und haben so jederzeit ein aktives zweites Datenbanksystem, das im Notfall das Hauptsystem ersetzen kann. Eine Einführung in die Replikation von MySQL-Datenbanken gibt das MySQL-Handbuch:

http://dev.mysql.com/doc/refman/5.6/en/replication.html