MySQL Binary Log Recovery

Automatisch erzeugte Backups einer MySQL Datenbank helfen nicht in dem Zeitraum zwischen dem letzten und kommenden Backup. Es gibt einige Methoden um auch für diesen Zwischenraum (interim Daten) Datensicherheit zu gewährleisten, eine davon ist das Binary Log Recovery.

Das Binary Log schreibt zur Laufzeit alle auf dem Server ausgeführten SQL-Transaktionen mit, dies wiederum verbraucht Ressourcen (je nach Frequentierung >= 1% CPU), weshalb manche Administratoren die Aktivität des binären loggen eingrenzen oder gar ausschalten. Die Vorteile die das Binary Log mitbringt sind aber enorm, insbesondere wenn neue Applikationen fehlerhafte SQL-Anweisungen absetzen.

Binäres Loggen konfigurieren

Alle Einstellungen die das Binary Log betreffen werden in der /etc/mysql/my.cnf vorgenommen

log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M

Durch log_bin wird der Speicherort der Log-Dateien bestimmt. expire_logs_days bestimmt den Zeitraum, in welchen die Logs aufbewahrt werden sollen, in dem Beispiel werden alle Logs die älter sind als 10 Tage entfernt. max_binlog_size gibt die maximal zugelassene Dateigröße für ein Log-File an. Die Angabe von max_binlog_size sollte unter Debian Systemen nie ohne die Einstellung log_bin aufgeführt werden, da hier der Server abstürzen würde. Je nachdem wieviele SQL-Statements erzeugt werden ist abzuwägen welche Größe und welcher Zeitraum berücksichtigt bzw. zugelassen werden soll. Unbedacht erstellte Applikationen können manchmal riesige Mengen an SQL-Anweisungen innerhalb eines Tages erzeugen, wenn diese Anwendung auch noch von mehreren Benutzern gleichzeitig benutzt wird, kann man sich ausmalen, dass die Grenzen eines zu klein gewählten Log-Files schnell erreicht sind und dann wäre der Vorteil dahin. Um Änderungen an der my.cnf wirksam zu machen, muß der MySQL Server neugestartet werden.

Einfaches Wiederherstellen

Durch das binäre Loggen in Verbindung mit der Sicherung der binären Log-Dateien ist es möglich ein vollst. Backup-Konzept zu erstellen. Empfehlen würde ich in Hinsicht auf die mitgeführten redundanten Daten nicht. Praktikabler ist das normale tägliche Backup (bsp. per MySQL Dump oder ähnliches) in Kombination mit dem Binary Log.
Ein alltägliches Backup Szenario könnte folgendermaßen aussehen, vorrausgesetzt das tägliche Backup (bsp. immer um 00:00Uhr) und das Binary Log sind aktiv. Angenommen alle Daten gingen aus irgendeinem Grund verloren, dann ist der erste Schritt den Zugriff auf die Datenbank exklusiv für den Administrator zu setzen:

$ mysqld --socket=/tmp/mysql_restore.sock --skip-networking

skip-networking ermöglicht den Zugriff nur über SSH oder direkt an der Server Konsole. Nach der Einschränkung des Zugriffs von außen, kann das tägliche Backup per Konsole eingespielt werden um so den Datenstand vom Vortag zu erhalten:

$ mysql -u root -ppassword  --socket=/tmp/mysql_restore.sock \
    < /var/backup/20080325.sql

Mit diesem Befehl wird das SQL Backup direkt auf den mysql_restore.sock geleitet. Anschliessend müssen noch die interim Daten wiederhergestellt werden, also die Daten die nach 00:00Uhr entstanden sind. Dazu verwendet man das Werkzeug mysqlbinlog. Vor Benutzung des Tools muß herausgefunden werden welche Log Dateien den jetzigen Tag betreffen, ein einfacher ls -la auf der Konsole im Binary Log Verzeichnis wird alle nötigen Informationen liefern. Nun kann von der kleinsten laufenden Nummer auftsteigend zum neuesten Log folgendermaßen vorgegangen werden:

$ mysqlbinlog /var/log/mysql/mysql-bin.000xxx \ 
   | mysql -u root -ppassword \
           --socket=/tmp/mysql_restore.sock

Mit diesem Befehl werden alle Statements die durch das Binary Log in der Log Datei aufgezeichnet wurden direkt auf der MySQL Datenbank abgesetzt und erneut ausgeführt. Wenn man alle Log-Daten so abgearbeitet hat, hat man alle Daten bis zum DB-Crash wiederhergestellt. Ein abschliessender MySQL-DB Neustart wird die Datenbank wieder zur Verfügung stellen.

Manuelles Wiederherstellen

Es kann vorkommen, dass man nicht alle Daten wiederherstellen möchte, da nur eine fehlerhafte Anweisung Daten gelöscht hat. Dazu piped (umleiten) man die Ausgabe nicht direkt auf den MySQL Socket um, sondern in ein seperate Textdatei:

$ mysqlbinlog /var/log/mysql/mysql-bin.000xxx \
    > /tmp/tmp_mysql-bin-000xxx..sql

Danach kann mit vi oder vim oder einem anderen Texteditor die temporäre erstellte Datei bearbeitet werden, eventuell fehlerhafte Anweisungen entfernt und danach erneut in die DB eingespielt werden.

Punktgenaues Wiederherstellen (Zeitstempel)

Ab MySQL Version 4.1.4 sind die Parameter –start-date und –stop-date hinzugefügt wurden. Diese ermöglichen punktuelles Recovery. Beispielsweise löschte eine Anweisung die um 12:00Uhr am 23.03.2008 ausgeführt wurde einige Daten. Die Vorgehensweise wäre die gleiche wie die eben beschriebene, man spielt das tägliche Backup zurück und führt anschliessend folgendes Kommando aus:

$ mysqlbinlog --stop-date="2008-03-23 11:59:59"
      /var/log/mysql/bin.000xxx |
    mysql -u root -ppassword \
          --socket=/tmp/mysql_restore.sock

Hiermit werden alle SQL-Statements die bis 11:59:59 aufgelaufen sind neu auf der Datenbank ausgeführt. Wenn man die fehlerverursachende SQL-Anweisung nicht exakt ausfindig machen kann, dann möchte man vielleicht an dieser Stelle alle Anweisungen die nach dem Fehlerzeitraum abgesetzt wurden wiederherstellen:

$ mysqlbinlog --start-date="2008-03-23 12:00:01"
      /var/log/mysql/bin.000xxx |
    mysql -u root -ppassword \
          --socket=/tmp/mysql_restore.sock

Mit diesen beiden Parametern kann man sehr punktuell Daten wiederherstellen und fehlerhafte SQL-Anweisungen zu umgehen.

Punktgenaues Wiederherstellen (Positionsangabe)

Nach dem gleichen Prinzip wie start-date und stop-date funktioniert start-position und stop-position. Wenn man eine binäre Log-Datei in eine Textdatei ausgibt, dann erkennt man das jedes SQL-Statement eine eindeutige Log-Positionsangabe mitführt, auf dieser Basis kann man genauso punktuell Daten wiederherstellen. Wann wird diese Vorgehensweise notwendig? Ganz einfach, wenn man den ungefähren Zeitpunkt des Problemfalles nicht kennt, dann führt kein Weg an dieser Methode vorbei.

Fazit

Die Nachteile liegen in dem Verbrauch der Ressourcen, SQL-Statement lastige Anwendungen fluten die Logs, es gilt abzuwägen, wieviel Ressourcen man hierfür als notwendig ansieht. Die Vorteile heben sich aber deutlich von den Nachteilen ab. Ich kam selbst in den Genuß eine MySQL Datenbank in 5 Minuten wieder in ordnungsgemäßen Zustand versetzen zu dürfen, seitdem ist für mich ist das Binary Log Recovery unverzichtbar um eine bessere Datensicherheit bzw. geringere Ausfallzeiten zu gewährleisten.