Das Binärlog enthält alle Anweisungen, die Daten aktualisieren oder hätten aktualisieren können (beispielsweise eine DELETE-Anweisung ohne zutreffenden Datensatz). Die Anweisungen werden in Form von „Ereignissen“ gespeichert, die die Änderungen beschreiben. Ferner enthält das Binärlog auch Informationen dazu, wie lange die Ausführung datenverändernder Anweisungen jeweils gedauert hat.

Hinweis: Das Binärlog hat beginnend mit MySQL 5.0 das alte Update-Log ersetzt, welches nun nicht mehr vorhanden ist. Das Binärlog enthält alle Informationen, die bereits im Update-Log vorhanden waren, in einem effizienteren Format und ist zudem transaktionssicher. Wenn Sie Transaktionen verwenden, müssen Sie für Backups das MySQL-Binärlog statt des alten Update-Logs verwenden.

Das Binärlog enthält keine Anweisungen, die keine Daten ändern. Wenn Sie alle Anweisungen aufzeichnen wollen (um etwa eine problematische Abfrage zu ermitteln), dann verwenden Sie das allgemeine Abfragelog. Siehe auch Abschnitt 5.12.2, „Die allgemeine Anfragen-Logdatei“.

Der primäre Zweck des Binärlogs besteht darin, eine möglichst vollständige Aktualisierung von Datenbanken im Zuge eines Wiederherstellungsvorgangs zu ermöglichen, denn das Binärlog enthält alle Updates, die nach Erstellung einer Sicherung erfolgten. Außerdem wird das Binärlog auf Master-Replikationsservern zur Aufzeichnung von Anweisungen verwendet, die an Slave-Server gesendet werden. Siehe auch Kapitel 6, Replikation bei MySQL.

Die Ausführung des Servers mit aktiviertem Binärlog verringert die Leistungsfähigkeit um ca. 1 Prozent. Die vom Binärlog in Zusammenhang mit der Datenwiederherstellung und der Replikationskonfiguration gebotenen Vorteile machen diese vernachlässigbare Leistungseinbuße jedoch mehr als wett.

Wenn mysqld mit der Option --log-bin[=base_name] gestartet wird, schreibt es eine Logdatei mit allen SQL-Befehlen, die Daten aktualisieren. Wird kein base_name-Wert angegeben, dann wird als Name der Datei standardmäßig der Name des Hostcomputers gefolgt von -bin gewählt. Wenn der Basisname angegeben wird, jedoch nicht als absoluter Pfadname, dann schreibt der Server die Datei in das Datenverzeichnis. Die Angabe eines Basisnamens wird empfohlen (siehe Abschnitt A.8.1, „Offene Probleme in MySQL“ zu den Gründen).

Wenn Sie eine Erweiterung im Lognamen angeben (z. B. --log-bin=base_name.extension), dann wird diese stillschweigend entfernt und ignoriert.

mysqld hängt eine numerische Erweiterung an den Basisnamen des Binärlogs an. Diese Zahl wird jedes Mal erhöht, wenn der Server eine neue Logdatei erstellt. Hierdurch entsteht eine geordnete Abfolge von Dateien. Der Server erstellt bei jedem Neustart und beim Schreiben der Logs jedes Mal eine neue Logdatei. Ferner wird ein neues Binärlog automatisch erstellt, wenn die Größe der aktuellen Logdatei den Wert max_binlog_size erreicht. Ein Binärlog kann, wenn Sie große Transaktionen verwenden, unter Umständen auch größer werden als durch max_binlog_size angegeben. Das liegt daran, dass eine Transaktion vollständig in eine Datei geschrieben und niemals auf mehrere Dateien verteilt wird.

Um im Überblick zu behalten, welche Binärlogdateien bereits verwendet wurden, erstellt mysqld außerdem eine Indexdatei, die die Namen aller verwendeten Binärlogdateien enthält. Standardmäßig hat diese denselben Basisnamen wie die Binärlogdatei zuzüglich der Erweiterung '.index'. Sie können den Namen der Indexdatei für Binärlogs mit der Option --log-bin-index[=file_name] ändern. Solange mysqld ausgeführt wird, sollten Sie diese Datei nicht manuell bearbeiten, da dies mysqld durcheinander bringen könnte.

Schreibvorgänge in die Binärlogdatei und die Indexdatei für Binärlogs werden auf identische Weise verarbeitet, nämlich als Schreiboperationen in MyISAM-Tabellen. Siehe auch Abschnitt A.4.3, „Wie MySQL mit vollen Festplatten umgeht“.

Mit der Anweisung RESET MASTER können Sie alle Binärlogdateien löschen, mit der Anweisung PURGE MASTER LOGS einen Teil davon. Siehe auch Abschnitt 13.5.5.5, „RESET“, und Abschnitt 13.6.1, „SQL-Anweisungen für die Steuerung von Master-Servern“.

Das Binärlogformat hat einige bekannte Beschränkungen, die sich auf die Wiederherstellung aus Backups auswirken können. Siehe auch Abschnitt 6.8, „Replikation: Features und bekannte Probleme“.

Das binäre Loggen für gespeicherte Routinen und Trigger erfolgt wie in Abschnitt 19.4, „Binärloggen gespeicherter Routinen und Trigger“, beschrieben.

Sie können die folgenden Optionen für mysqld verwenden, um zu beeinflussen, was in der Binärlogdatei aufgezeichnet wird. Beachten Sie auch die auf die Liste folgenden Erläuterungen.

Wenn Sie die Replikation verwenden, beeinflussen die hier beschriebenen Optionen, welche Anweisungen vom Master-Server an die Slaves gesendet werden. Es gibt auch Optionen für Slave-Server, mit denen gesteuert wird, welche vom Master empfangenen Anweisungen ausgeführt und welche ignoriert werden. Detaillierte Informationen finden Sie in Abschnitt 6.9, „Replikationsoptionen in my.cnf“.

Der Server wertet die Optionen zum Loggen von Updates im Binärlog oder zum Ignorieren der Updates entsprechend den folgenden Regeln aus. Wie bereits erwähnt, gibt es eine Ausnahme für die Anweisungen CREATE DATABASE, ALTER DATABASE und DROP DATABASE. In solchen Fällen ersetzt die Datenbank, die erstellt, geändert oder gelöscht wird, in den folgenden Regeln die Standarddatenbank.

So schreibt beispielsweise ein Slave, der nur mit --binlog-do-db=sales ausgeführt wird, keine Anweisungen in das Binärlog, bei der sich die Standarddatenbank von sales unterscheidet (anders gesagt kann --binlog-do-db auch manchmal „andere Datenbanken ignorieren“ bedeuten).

Wenn Sie die Replikation verwenden, sollten Sie alte Binärlogdateien erst dann löschen, wenn Sie ganz sicher sind, dass kein Slave sie mehr benötigt. Laufen Ihre Slaves also etwa nie länger als drei Tage am Stück, dann können Sie einmal täglich mysqladmin flush-logs auf dem Master ausführen und dann alle Logs entfernen, die älter als drei Tage sind. Sie können die Dateien dabei zwar manuell entfernen, aber die Verwendung von PURGE MASTER LOGS ist vorzuziehen, weil diese Anweisung gleichzeitig auch die Indexdatei für die Binärlogs sicher aktualisiert (und weil sie auch ein Datumsargument entgegennehmen kann). Siehe auch Abschnitt 13.6.1, „SQL-Anweisungen für die Steuerung von Master-Servern“.

Ein Client, der die Berechtigung SUPER hat, kann das binäre Loggen seiner eigenen Anweisungen mithilfe einer SET SQL_LOG_BIN=0-Anweisung deaktivieren. Siehe auch Abschnitt 13.5.3, „SET“.

Sie können den Inhalt von Binärlogdateien mit dem Hilfsprogramm mysqlbinlog anzeigen. Die kann praktisch sein, wenn Sie die Anweisungen im Log neu verarbeiten wollen. So können Sie einen MySQL-Server beispielsweise aus dem Binärlog heraus wie folgt aktualisieren:

shell> mysqlbinlog log_file | mysql -h server_name

Weitere Informationen zum Hilfsprogramm mysqlbinlog und seiner Verwendung finden Sie in Abschnitt 8.8, „mysqlbinlog — Hilfsprogramm für die Verarbeitung binärer Logdateien“. mysqlbinlog kann auch mit Relay-Logs verwendet werden, da diese im selben Format wie Binärlogdateien geschrieben sind.

Das binäre Loggen erfolgt unmittelbar nach Abschluss einer Anweisung, aber vor dem Aufheben ggf. vorhandener Sperren und dem Schreiben in die Datenbank. Auf diese Weise ist sichergestellt, dass die Anweisungen in der Reihenfolge ihrer Ausführung geloggt werden.

Aktualisierungen nicht transaktionssicherer Tabellen werden im Binärlog unmittelbar nach ihrer Ausführung gespeichert. Im Verlauf einer noch nicht geschriebenen Transaktion werden alle Änderungen (UPDATE, DELETE oder INSERT), die transaktionssichere Tabellen wie etwa BDB- oder InnoDB-Tabellen betreffen, zwischengespeichert, bis vom Server eine COMMIT-Anweisung empfangen wird. Dann schreibt mysqld die gesamte Transaktion in das Binärlog, bevor die COMMIT-Anweisung ausgeführt wird. Wenn der Thread, der die Transaktion verarbeitet, startet, reserviert er zur Zwischenspeicherung von Anweisungen einen Puffer der mit binlog_cache_size angegebenen Größe. Ist eine Anweisung größer, dann öffnet der Thread eine Temporärdatei, um die Transaktion zu speichern. Endet der Thread, dann wird auch die Temporärdatei wieder gelöscht.

Für Modifikationen an nicht transaktionssicheren Tabellen kann kein Rollback durchgeführt werden. Wenn eine Transaktion, für die ein Rollback erfolgt, Änderungen an nicht transaktionssicheren Tabellen enthält, dann wird die gesamte Transaktionen am Ende mit einer ROLLBACK-Anweisung geloggt, um sicherzustellen, dass die Modifikationen an diesen Tabellen repliziert werden.

Die Statusvariable Binlog_cache_use zeigt die Anzahl der Transaktionen an, die diesen Puffer (und möglicherweise auch eine Temporärdatei) zur Speicherung von Anweisungen verwendet haben. Die Statusvariable Binlog_cache_disk_use gibt an, wie viele dieser Transaktionen tatsächlich eine Temporärdatei erstellen mussten. Mithilfe dieser beiden Variablen kann die Größe von binlog_cache_size optimiert werden: Wählen Sie einen ausreichend hohen Wert, damit die Verwendung von Temporärdateien möglichst vermieden wird.

Die Systemvariable max_binlog_cache_size kann benutzt werden, um die Gesamtspeicherkapazität zu begrenzen, die zur Zwischenspeicherung einer Transaktion mit mehreren Anweisungen verwendet wird (Standardwert: 4 Gbyte). Ist eine Transaktion größer als dieser Wert, dann schlägt sie fehl, und es wird ein Rollback durchgeführt.

Wenn Sie das Binärlog verwenden, werden nebenläufige in normale Einfügeoperationen für die Anweisungen CREATE ... SELECT oder INSERT ... SELECT umgewandelt. Hierdurch wird gewährleistet, dass Sie eine exakte Kopie Ihrer Tabellen neu erstellen können, indem Sie das Log während eines Sicherungsvorgangs anwenden.

Beachten Sie, dass sich das Binärlogformat in MySQL 5.1 von dem in vorherigen MySQL-Versionen unterscheidet. Grund hierfür sind Erweiterungen bei der Replikation. Siehe auch Abschnitt 6.6, „Replikation: Kompatibilität zwischen MySQL-Versionen“.

Standardmäßig wird das Binärlog nicht bei jeder Schreiboperation auf Festplatte synchronisiert. Wenn also das Betriebssystem oder der Computer selbst (d. h. nicht nur der MySQL-Server) abstürzen, dann sind die letzten Änderungen im Binärlog unter Umständen verloren gegangen. Um dies zu verhindern, können Sie mithilfe der Systemvariablen sync_binlog die Synchronisierung des Binärlogs nach jeder N-Schreiboperation erzwingen. Siehe auch Abschnitt 5.2.2, „Server-Systemvariablen“. Der sicherste – aber auch langsamste – Wert für sync_binlog ist 1. Aber auch dann, wenn sync_binlog auf 1 steht, besteht die Möglichkeit von Inkonsistenzen zwischen dem Tabelleninhalt und dem Inhalt des Binärlogs im Falle eines Absturzes. Wenn Sie beispielsweise InnoDB-Tabellen benutzen und der MySQL-Server eine COMMIT-Anweisung verarbeitet, dann schreibt er die gesamte Transaktion in das Binärlog und übergibt sie dann an InnoDB. Stürzt der Server zwischen diesen beiden Vorgängen ab, dann erfolgt beim Neustart ein Rollback der Transaktion durch InnoDB; sie ist aber weiterhin im Binärlog vorhanden. Dieses Problem lässt sich mit der Option --innodb-safe-binlog beheben, die die Konsistenz zwischen InnoDB-Tabellen und dem Binärlog herstellt. (Hinweis: --innodb-safe-binlog wird ab MySQL 5.0 nicht mehr benötigt, weil die XA-Transaktionsunterstützung implementiert wurde.)

Damit diese Option ein höheres Maß an Sicherheit gewähren kann, sollte der MySQL-Server auch so konfiguriert werden, dass das Binär- und die InnoDB-Logs bei jeder Transaktion synchronisiert werden. Die InnoDB-Logs werden standardmäßig synchronisiert, und das Binärlog kann mit der Option sync_binlog=1 synchronisiert werden. Diese Option bewirkt, dass der MySQL-Server nach einem Absturz und dem beim folgenden Neustart ausgeführten Rollback die betreffenden Transaktionen aus dem Binärlog ausschneidet. Hierdurch ist sichergestellt, dass das Binärlog die exakten Daten der InnoDB-Tabellen widerspiegelt und der Slave insofern immer synchron zum Master bleibt (d. h. keine Anweisung empfängt, für die ein Rollback durchgeführt wurde).

Beachten Sie, dass die Option --innodb-safe-binlog auch dann verwendet werden kann, wenn der MySQL-Server andere Speicher-Engines als InnoDB aktualisiert. Aus dem Binärlog entfernt werden durch die Wiederherstellungsfunktionen von InnoDB nur Anweisungen und Transaktionen, die InnoDB-Tabellen betreffen. Wenn der MySQL-Server bei der Wiederherstellung nach einem Absturz feststellt, dass das Binärlog kürzer als erwartet ist, dann fehlt ihm mindestens eine erfolgreich übermittelte InnoDB-Transaktion. Dies sollte normalerweise nicht passieren, wenn die Synchronisierung gezielt über sync_binlog=1 und das Festplatten-/Dateisystem geregelt wurde (was nicht immer klappt); der Server gibt also die Fehlermeldung The binary log <name> is shorter than its expected size. aus. In diesem Fall ist das Binärlog nicht korrekt, und die Replikation sollte mit einem neuen Schnappschuss der Master-Daten neu gestartet werden.