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[=
gestartet wird, schreibt es eine Logdatei mit allen
SQL-Befehlen, die Daten aktualisieren. Wird kein
base_name
]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=
),
dann wird diese stillschweigend entfernt und ignoriert.
base_name.extension
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[=
ändern. Solange mysqld ausgeführt wird,
sollten Sie diese Datei nicht manuell bearbeiten, da dies
mysqld durcheinander bringen könnte.
file_name
]
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“.
--binlog-do-db=
db_name
Weist den Server an, das binäre Loggen auf Updates zu
beschränken, für die die Standarddatenbank
db_name
heißt (d. h. die mit
USE
gewählte Datenbank). Alle anderen
nicht ausdrücklich erwähnten Datenbanken werden ignoriert.
Wenn Sie diese Option verwenden, sollten Sie sicherstellen,
dass Sie Aktualisierungen nur in der Standarddatenbank
durchführen.
Hierzu gibt es eine Ausnahme für die Anweisungen
CREATE DATABASE
, ALTER
DATABASE
und DROP DATABASE
. Der
Server entscheidet auf der Basis der in der Anweisung
genannten Datenbank (nicht der Standarddatenbank), ob die
Anweisungen protokolliert werden sollen oder nicht.
Hier ein Beispiel für etwas, das nicht so funktioniert, wie
Sie es vielleicht erwarten: Wenn der Server mit
binlog-do-db=sales
gestartet wird und Sie
USE prices; UPDATE sales.january SET
amount=amount+1000;
ausführen, wird diese
Anweisung nicht in das Binärlog
geschrieben.
Um mehrere Datenbanken zu loggen, verwenden Sie mehrere Optionen, wobei die Option einmal für jede Datenbank angegeben werden muss.
--binlog-ignore-db=
db_name
Weist den Server an, das binäre Loggen von Updates zu
unterdrücken, für die die Standarddatenbank
db_name
heißt (d. h. die mit
USE
gewählte Datenbank). Wenn Sie diese
Option verwenden, sollten Sie sicherstellen, dass Sie
Aktualisierungen nur in der Standarddatenbank durchführen.
Wie bei der Option --binlog-do-db
gibt es
auch hier eine Ausnahme für die Anweisungen CREATE
DATABASE
, ALTER DATABASE
und
DROP DATABASE
. Der Server entscheidet auf
der Basis der in der Anweisung genannten Datenbank (nicht
der Standarddatenbank), ob die Anweisungen protokolliert
werden sollen oder nicht.
Hier ein Beispiel für etwas, das nicht so funktioniert, wie
Sie es vielleicht erwarten: Wenn der Server mit
binlog-ignore-db=sales
gestartet wird und
Sie USE prices; UPDATE sales.january SET
amount=amount+1000;
ausführen,
wird diese Anweisung in das Binärlog
geschrieben.
Um mehrere Datenbanken zu ignorieren, verwenden Sie mehrere Optionen, wobei die Option einmal für jede Datenbank angegeben werden muss.
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.
Sind --binlog-do-db
- oder
--binlog-ignore-db
-Regeln vorhanden?
Nein: Anweisung in Binärlog schreiben und beenden.
Ja: Mit nächstem Schritt fortfahren.
Es sind Regeln vorhanden (--binlog-do-db
,
--binlog-ignore-db
oder beide). Gibt es
eine Standarddatenbank (d. h. wurde eine Datenbank mit
USE
ausgewählt)?
Nein: Anweisung nicht schreiben, sondern beenden.
Ja: Mit nächstem Schritt fortfahren.
Es gibt eine Standarddatenbank. Gibt es
--binlog-do-db
-Regeln?
Ja: Liegt eine Übereinstimmung der Standarddatenbank
mit einer der --binlog-do-db
-Regeln
vor?
Ja: Anweisung schreiben und beenden.
Nein: Anweisung nicht schreiben, sondern beenden.
Nein: Mit nächstem Schritt fortfahren.
Es gibt --binlog-ignore-db
-Regeln. Liegt
eine Übereinstimmung der Standarddatenbank mit einer der
--binlog-ignore-db
-Regeln vor?
Ja: Anweisung nicht schreiben, sondern beenden.
Nein: Abfrage schreiben und beenden.
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.
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.