MySQL Backup- und Recovery-Strategien

MySQL Backup- und Recovery-Strategien
Matthias Jung
ORDIX AG
Wiesbaden
Schlüsselworte:
MySQL, Backup, Recovery, Restore, Snapshot
Einleitung
In den letzten Wochen und Monaten hat das freie Datenbankprodukt MySQL zunehmend an
Bedeutung gewonnen. Immer mehr Kunden entscheiden sich dafür, MySQL als Alternative zu
den im professionellen Bereich etablierten Datenbankprodukten einzusetzen.
Ein ausgereiftes Sicherungs- und Wiederherstellungskonzept ist in diesem Umfeld von
existentieller Bedeutung. Der folgende Vortrag soll einen grundlegenden Überblick über die
Möglichkeiten der Sicherung und Wiederherstellung von MySQL Datenbanken geben.
Einführung
Die Erstellung eines Sicherungskonzeptes wirft im Vorfeld viele Fragen auf.
Reicht es aus, die Datenbank offline zu sichern oder benötige ich ein Verfahren zur OnlineSicherung? Welcher Art sollen meine Backups sein? Benötige ich logische Abzüge (Dumps)
oder ist es vorteilhaft, physikalische Sicherungen (datei- und / oder blockbasiert) zu erstellen?
Kann ich neben Vollsicherungen auch inkrementelle oder gar transaktionsorientierte
Sicherungen erstellen? Welche Werkzeuge bietet mir MySQL für mein Vorhaben an? Kann
ich meine vorhandene Infrastruktur (Betriebssystem, Storage, …) gewinnbringend in mein
Konzept einbringen? Auf welche Besonderheiten muss ich seitens MySQL achten (Storage
Engine)?
Abb. 1: Offene Fragestellungen zum Thema MySQL & Backups.
MySQL unterscheidet sich in einem Punkt eklatant von den meisten anderen
Datenbankprodukten: Durch die Vielzahl der vorhandenen Storage Engines
(Speichersubsysteme). Diese, auch Tabellen-Typen genannten, Plugins kümmern sich um die
physikalische Verwaltung der eigentlichen Daten und sind mit bestimmen Funktionen
verknüpft. So gibt es Engines, die beispielsweise Daten nur im Speicher halten. Andere
Engines unterstützen keine Transaktionen und / oder referentielle Integrität.
Die Wahl einer Engine kann demnach auch Einfluss auf das gewählte Backup-Verfahren
haben. So eignen sich zum Beispiel manche Werkzeuge nicht für die Sicherung der einen oder
anderen Storage Engine.
Abb. 2: Die Verwaltung der Daten obliegt den gewählten Storage Engines.
Mögliche Backup-Strategien
Die Anforderungen an ein Backup-Konzept sind von Fall zu Fall höchst unterschiedlich.
Während die eine Firma höchsten Wert auf eine einfache Handhabung (Usabillity) legt, um
auch in Extremsituationen möglichst keine Fehler zu begehen, benötigt die andere Firma ein
Konzept, welches möglichst keinen Einfluss auf den laufenden Betrieb der Datenbank hat.
Generell sollte man sich jedoch folgende Fragen stellen:
„Wie oft möchte ich Backups erstellen und welche Wiederaufsatzpunkte ermöglichen mir
meine Sicherungen (RPO; Recovery Point Objektiv)? Kann ich nur den Moment der
Sicherung wieder herstellen oder ermöglicht mir mein Sicherungskonzept ein Point-in-timeRecovery?“
Zusätzlich muss geklärt werden, wie lange die Laufzeiten von Sicherungen – und viel
wichtiger – die Laufzeiten von Restore- und Recovery-Prozessen dauern dürfen (RTO,
Recovery Time Objective). Daneben sind natürlich auch Fragen bzgl. der Handhabung, der
Kosten und eines möglicherweise gegebenen Performance-Einflusses auf den Betrieb der
Datenbank zu klären.
Abb. 3: Mögliche Entscheidungskriterien für eine Backup-Strategie.
Aufgrund der Tatsache, dass die verschiedenen Storage Engines nicht nach einem
einheitlichen Algorithmus arbeiten, gestaltet sich die Sicherstellung eines konsistenten
Abbildes einer oder mehrerer MySQL-Datenbanken eines Servers leider schwierig.
In letzter Konsequenz kann diese Aufgabe nicht den Storage Engines überlassen werden, da
der Datenbestand einer Datenbank über alle Tabellen – egal in welcher Engine sie sich zum
Zeitpunkt des Backups befinden – logisch richtig und zeitlich einheitlich gesichert werden
muss.
Daher sperren alle Backup-Tools (die Storage Engine übergreifend arbeiten) den Zugriff von
DML und DDL Statements während der Backup-Phase. Wahlweise und je nach Anforderung
können diese Sperren auf einzelne Tabellen, die gesamte Datenbank und auf Wunsch über den
gesamten Server gelegt werden. Dies stellt natürlich eine erhebliche Einschränkung der
Verfügbarkeit (Nutzbarkeit) des Servers da.
Einige wenige Storage Engine spezifische Tools bieten allerdings auch Oracle RMAN
ähnliche Sicherungsmöglichkeiten (z. B. InnoDB Hot Backup) an. Ziel sollte es natürlich sein,
die Sperrprozesse zeitlich extrem zu minimieren oder im Idealfall ganz zu vermeiden.
Abb. 4: Konsistente Backups haben fast immer einen negativen Einfluss auf die Verfügbarkeit des
Servers bzw. der Datenbank.
Exakt der Zwang der Standardisierung, der bei der Erstellung von Backups negative
Auswirkungen (Sperren) nach sich zieht, hilft bei der Zielerreichung eines Point-in-timeRecovery. Da nicht davon auszugehen ist, dass alle Engines Transaktionsprotokolle
mitschreiben (und in der Tat tun dies die wenigsten Engines), haben sich die MySQL
Entwickler dazu entschieden, ein generisches Transaktionsprotokoll einzuführen: das Binary
Log. In diesem binären Logfile werden alle relevanten Aktionen (DML, DDL, DCL und TCL
Statements) sequentiell protokolliert.
Abb. 5: Voraussetzung für ein Point-in-time-Recovery ist die Nutzung des MySQL Binary Logs.
Eigentlich stammt dieses Logfile aus dem MySQL-Replikationsumfeld, kann aber im Rahmen
eines Backup-Konzeptes zum Recovern der erstellten Vollsicherungen (egal, ob diese logisch
oder physikalisch, online oder offline erstellt wurden) verwendet werden. Es muss lediglich
die Log-Position des Sicherungszeitpunktes (Sperrmoment) bekannt sein. Mit entsprechenden
Bordmitteln können die binären Logfiles nach einzelnen Datenbanken und/oder Tabellen
durchsucht und die Transaktion in ASCII SQL-Statements zurückgerechnet werden.
Backup-Werkzeuge und Tools
Es gibt eine Vielzahl vom Backup-Werkzeugen, GUIs, Frameworks und Skripten, die Ihnen
helfen können, Ihre Datenbanken zu sichern. In letzter Konsequenz nutzen Sie aber die
gleichen Verfahren wie die MySQL Standardwerkzeuge mysqldump und mysqlhotcopy.
mysqldump erstellt logische Backups. Im Prinzip werden DDL- und DML-Statements
generiert, mit deren Hilfe Sie in der Lage sind, exakt den Datenbestand wieder aufzubauen,
der im Moment der Sicherung aktiv in der Datenbank bestand.
Das Backup Medium entspricht damit einer – in Abhängigkeit zur Datenbank – relativ großen
ASCII-Datei. Die Erstellung einer solchen Datei kann dementsprechend lange dauern und viel
Platz einnehmen.
Auch das Wiederaufsetzen der Datenbank kann zeitintensiv sein, da alle Transaktionen,
welche zum Datenbestand geführt haben, wiederholt werden müssen.
Das Werkzeug mysqlhotcopy sichert die Datenbank physikalisch per CopyBetriebssystemkommandos. Auch hier wird die Datenbank/der Server gesperrt und alle Daten
aus den entsprechenden Hauptspeicherbereichen der Storage Engines werden auf Platte
geschrieben (Flush Tables). Sobald keine weiteren Transaktionen mehr durchgeführt werden
können (Sperre), werden die entsprechenden Datendateien kopiert. Allerdings gibt es
Restriktionen bzgl. der unterstützten Tabellen-Typen: MyISAM & Archive.
Selbstverständlich kann ein ähnliches Verfahren – durch eigene Skripte – analog auch für
andere Engines verwendet werden. Im Vorfeld sollte nur zweifelsfrei geklärt werden, welche
physikalischen Strukturen die entsprechende Engine verwendet. Darüber hinaus sollten die
Engines natürlich Sperrkonzepte (Locks) implementiert haben.
Abb. 6: MySQL liefert zwei Werkzeuge zur Erstellung von Backups mit aus: mysqldump und
mysqlhotcopy.
Abb. 7: Zwei Wege, ein Ziel: Die Sicherung der Datenbanken kann logisch oder physikalisch realisiert
werden.
Beide Verfahren scheinen für einen Oracle Administrator zunächst ungewohnt, führen aber
zum Ziel. Beide Vollsicherungen können durch entsprechend ausgewertete Binary Logs
angereichert werden. Damit lässt sich eine Datenbank mit Hilfe der Vollsicherungen auf einen
beliebigen Zeitpunkt nach vorne „recovern“.
Die Anzahl der Kommandos und Werkzeuge ist übersichtlich und leicht zu erlernen. Lediglich
hinsichtlich der Verfügbarkeit während des Backups und der Dauer des Restores – eine
entsprechende Datenmenge vorausgesetzt – lassen sich Kritikpunkte anbringen.
Hinsichtlich der Beschleunigung der Wiederherstellung eines Systems entscheiden sich die
meisten Betreiber (gerade von größeren Datenbanken) für physikalische Backups
(mysqlhotcopy oder Copy-Kommandos). Das Wiederholen der entsprechenden
Transaktionen zur erneuten Generierung eines aktuellen Datenbestandes dauert in vielen
Fällen zu lange (SLAs).
Praxislösungen
Um die verbleibenden Probleme zu minimieren gibt es in der Praxis zwei mögliche Verfahren:
•
•
Minimierung der Sperrzeit durch optimale „Kopiervorgänge“ und / oder
Verlagerung der Backup-Last auf ein nicht-produktives System.
Relativ häufig sind in der Praxis sogenannte Slave/Backup-Systeme zu finden. Bei einem
Slave handelt es sich um eine Art Schattendatenbank, welche zeitnah exakt dieselben
Transaktionen wie das produktive System (Master) erhält (am ehesten vergleichbar mit dem
Oracle Konstrukt „Logical Dataguard“).
Abb.8: MySQL unterstützt schon seit langer Zeit Replikationen.
Das MySQL-Replikationsverfahren basiert auf der Grundlage des Binary Logs, welches
bereits im Rahmen der möglichen Backup-Verfahren vorgestellt wurde. Das Master-System
loggt alle relevanten Transaktionen in diesem binären Protokoll. Das Slave- oder BackupSystem liest sequentiell diese Daten, kopiert sie lokal und spielt den SQL-Strom
(Transaktionen) in den eigenen Server ein (SQL Apply).
Auf beiden Seiten (Master und Slave) können umfangreiche Einstellungen vorgenommen
werden, um z. B. festzulegen, welche Datenbanken auf dem Master protokolliert und welche
Inhalte auf dem Slave repliziert werden sollen (es existiert eine Vielzahl an Filterkriterien).
Darüber hinaus lassen sich ohne Probleme unterschiedliche Replikationstopologien, wie z. B.
Ketten, Ringe, bidirektionale Replikationen, realisieren.
Abb.9: MySQL Replikationen werden oft im Rahmen von Backup-Konzepten eingesetzt.
Die beiden an der Replikation beteiligten Prozesse (I/O Thread und SQL Thread) lassen sich
auf Seiten des Slaves getrennt steuern. Ein Stoppen des SQL Threads sorgt dafür, dass die
Transaktionsinformationen weiterhin vom Master zum Slave fließen (und damit auch im
Fehlerfall dort zur Verfügung stehen), jedoch werden dieses Daten temporär (bis zum
erneuten Start des SQL Threads) hier nicht eingespielt.
Sofern keine direkten Transaktionen auf dem Slave durchgeführt werden, entspricht ein
angehaltener SQL Thread einer Art Sperre auf dem replizierten Server, bzw. der replizierten
Datenbank.
Abb.10: Sobald der Server ruht, kann mit dem Backup begonnen werden.
Sobald keine Aktionen mehr auf dem Slave ausgeführt werden (zur Sicherheit können auch
hier die Tabellen zusätzlich gesperrt werden), kann mit dem eigentlichen Backup
(mysqldump oder mysqlhotcopy) begonnen werden. Das Produktionssystem arbeitet in
dieser Zeit weiter.
Alternativ bzw. als Ergänzung kann man zusätzlich versuchen, die Backup-Zeiten durch
schnellere Sicherungskommandos (Ersatz für Copy-Befehle) zu optimieren. Logische
Sicherungen (mysqldump) scheiden als Strategie meist aus Zeitgründen aus.
Eine gute Möglichkeit die physikalischen Strukturen (Datendateien) des Servers und/oder von
einzelnen Datenbanken zu sichern bieten moderne Filesysteme und/oder Storagesysteme: z. B.
Solaris ZFS, NetApp Snapshot-Technologie.
Oftmals lassen sich die durch Sperren eingefrorenen Datenbanken innerhalb weniger
Sekunden logisch sichern (z. B. durch einen Snapshot), während die eigentliche Trennung von
aktiven und Backup-Filesystemen (z. B. durch Kopieren auf entfernte Platten und/oder
Bänder) erst nach dem Snapshot angegangen wird.
Dadurch wird die Sperrzeit des Servers/der Datenbank auf wenige Sekunden verkürzt.
Zusätzlich bieten diese Technologien – sofern die Backup-Informationen auch lokal gehalten
werden – im Fehlerfall extrem schnelle Restore-Zeiten. Die Laufzeit des Recoveries (SQLApply) kann natürlich durch den Einsatz einer solchen Technologie nicht verkürzt werden.
Selbstverständlich können auch replizierte Datenbanken über diese Mechanismen gesichert
werden.
Schlussbemerkungen
MySQL Datenbanken lassen sich relativ unproblematisch sichern und wiederherstellen.
Allerdings sollte ein Wiederherstellungskonzept sehr sorgsame geplant und auf die
vorhandene Infrastruktur abgestimmt werden.
Die Verantwortung für die erfolgreiche Erstellung von Backups liegt hier – noch stärker als
bei anderen Datenbankprodukten – in der Hand des Administrators. Er trägt nicht nur die
Verantwortung für das Konzept, sondern ist auch für die eingesetzten technischen
Komponenten (Storage Engine, Backup-Werkzeug, Binray Log-Pflege) und deren
Zusammenspiel verantwortlich.
Kontaktadresse:
Matthias Jung
ORDIX AG
Kreuzberger Ring 13
D-65205 Wiesbaden
Telefon:
Fax:
E-Mail
Internet:
+49 (0) 611 – 778 4000
+49 (0) 180 – 167349 0
[email protected]
www.ordix.de