Analysieren des MySQL-Binärprotokolls

1. Einführung in Binlog

binlog ist ein Binärprotokoll, eine binäre Protokolldatei. Es zeichnet alle von der Datenbank ausgeführten DDL- und DML-Anweisungen (außer Datenabfrageanweisungen wie „Select“ und „Show“) auf, zeichnet sie in Form von Ereignissen auf und speichert sie in Binärdateien.

Binlog hat zwei Hauptanwendungsszenarien. Eines ist die Replikation. Der Master übergibt sein Binärprotokoll an die Slaves, um Master-Slave-Datenkonsistenz zu erreichen. Die zweite Möglichkeit ist die Datenwiederherstellung. Beispielsweise können Sie nach dem Wiederherstellen eines Backups das neu generierte Binlog nach dem Backup erneut ausführen, um die Datenbank auf dem neuesten Stand zu halten. Neben diesen beiden Hauptanwendungen kann Binlog auch für die Dateninteraktion zwischen heterogenen Systemen verwendet werden. Binlog speichert die vorhergehenden und nachfolgenden Datensätze eines Datensatzes vollständig. DTS-Dienste können verwendet werden, um MySQL-Daten nahezu in Echtzeit auf zugrunde liegende Datenplattformen wie HBase, Hive, Spark usw. zu extrahieren und OLTP und OLAP zu verbinden.

Es gibt drei Modi für Binlog-Protokolle: STATEMENT , ROW und MIXED . Im Folgenden finden Sie eine kurze Einführung in diese drei Modi:

• ANWEISUNG : Basierend auf der Replikation von SQL-Anweisungen wird jede SQL-Anweisung, die Daten ändert, im Binärprotokoll aufgezeichnet. Die Menge der in diesem Modus generierten Binlog-Protokolle ist relativ gering, es kann jedoch zu Inkonsistenzen zwischen Master- und Slave-Daten kommen.
• ROW : Die zeilenbasierte Replikation zeichnet nicht jede einzelne ausgeführte SQL-Anweisung auf, sondern zeichnet nur auf, welche Daten geändert werden und wie sie vor und nach der Änderung aussahen. Die Menge der in diesem Modus generierten Binlog-Protokolle ist relativ groß, der Vorteil besteht jedoch darin, dass die Details jeder Datenänderungszeile klar aufgezeichnet werden und bei der Master-Slave-Replikation keine Fehler auftreten.
• Gemischt : Replikation im gemischten Modus, eine Kombination der beiden oben genannten Modi. Bei der allgemeinen Replikation wird der STATEMENT-Modus zum Speichern von Binärprotokollen verwendet. Für Vorgänge, die im STATEMENT-Modus nicht repliziert werden können, wird der ROW-Modus zum Speichern von Binärprotokollen verwendet. MySQL wählt die Methode zum Speichern von Protokollen basierend auf der ausgeführten SQL-Anweisung aus.

Der Standard-Binlog-Modus ist STATEMENT vor MySQL 5.7.7 und ROW in späteren Versionen. Es wird empfohlen, hier den ROW-Modus zu verwenden, da der ROW-Modus sicherer ist und die Details jeder Zeile der Datenänderung klar aufzeichnen kann.

2. Binlog-bezogene Parameter

Binlog ist standardmäßig nicht aktiviert, im Allgemeinen wird jedoch empfohlen, Binlog zu aktivieren, indem während der Initialisierung der Parameter log-bin zur Konfigurationsdatei hinzugefügt wird.

# Log-Bin-Konfiguration zur Konfigurationsdatei [mysqld] hinzufügen
log-bin = Binlog

# Wenn Sie keinen Pfad angeben, wird standardmäßig das Datenverzeichnis verwendet. Sie können auch einen Pfad angeben [mysqld]
log-bin = /Daten/mysql/logs/binlog

# Prüfen Sie, ob Binlog in der Datenbank aktiviert ist
Variablen wie „log_bin%“ anzeigen;

Nach dem Aktivieren von Binlog müssen Sie auf einige Binlog-bezogene Parameter achten. Im Folgenden finden Sie eine kurze Einführung in die relevanten Parameter:

binlog_format
Legen Sie den Binlog-Modus fest. Es wird empfohlen, ihn auf ROW einzustellen.

binlog_do_db
Dieser Parameter bedeutet, dass nur das Binärprotokoll der angegebenen Datenbank aufgezeichnet wird. Standardmäßig werden alle Protokolle aufgezeichnet. Es wird im Allgemeinen nicht empfohlen, dies zu ändern.

binlog_ignore_db
Dieser Parameter gibt an, dass das Binärprotokoll der angegebenen Datenbank nicht aufgezeichnet wird. Wie oben wird es im Allgemeinen nicht explizit angegeben.

Protokolltage ablaufen lassen
Dieser Parameter steuert die Anzahl der Tage, die Binärprotokolldateien aufbewahrt werden sollen. Der Standardwert ist 0, was bedeutet, dass keine automatische Löschung erfolgt. Der Wert kann auf 0 bis 99 eingestellt werden. Die Aufbewahrungsdauer kann je nach tatsächlicher Situation beispielsweise auf 15 oder 30 Tage festgelegt werden. In MySQL 8.0 kann stattdessen der Parameter binlog_expire_logs_seconds verwendet werden.

max_binlog_size
Steuert die Größe eines einzelnen Binärprotokolls. Wenn die aktuelle Protokolldateigröße diese Variable überschreitet, wird eine Switch-Aktion ausgeführt. Der Maximal- und Standardwert dieses Parameters beträgt 1 GB. Diese Einstellung kann die Größe des Binlogs nicht streng steuern, insbesondere wenn das Binlog nahe am Maximalwert liegt und auf eine relativ große Transaktion stößt. Um die Integrität der Transaktion sicherzustellen, ist es nicht möglich, das Protokoll zu wechseln. Alle SQL-Anweisungen der Transaktion können nur im aktuellen Protokoll aufgezeichnet werden, bis die Transaktion endet. Normalerweise kann der Standardwert verwendet werden.

log_bin_trust_function_creators
Dieser Parameter wird aktiviert, wenn die binäre Protokollierung aktiviert ist. Es steuert, ob den Erstellern gespeicherter Funktionen vertraut werden kann, dass sie keine gespeicherten Funktionen erstellen, die in das Binärprotokoll schreiben und unsichere Ereignisse verursachen. Wenn der Wert auf 0 (Standard) gesetzt ist, dürfen Benutzer keine gespeicherten Funktionen erstellen oder ändern, es sei denn, sie verfügen zusätzlich zum Privileg CREATE ROUTINE oder ALTER ROUTINE über das Privileg SUPER. Die empfohlene Einstellung ist 1.

sync_binlog
Steuert, wie oft der MySQL-Server Binärprotokolle mit der Festplatte synchronisiert. Der Standardwert ist 1.
Wenn der Wert auf 0 gesetzt ist, steuert MySQL das Leeren der Binärprotokolle nicht und das Dateisystem steuert das Leeren seines Caches selbst.
Wenn der Wert auf 1 gesetzt ist, leert MySQL das Binärprotokoll bei jeder Transaktionsübermittlung. Dies ist die sicherste Einstellung, kann sich jedoch aufgrund der erhöhten Anzahl von Festplattenschreibvorgängen negativ auf die Leistung auswirken.
Auf n setzen, wobei n ein anderer Wert als 0 oder 1 ist. Nachdem n Transaktionen festgeschrieben wurden, führt MySQL eine Festplattensynchronisierungsanweisung wie z. B. fsync aus, um den Binlog-Dateicache auf der Festplatte zu aktualisieren.
Die empfohlene Einstellung ist 1, sie kann jedoch aus Leistungsgründen angepasst werden.

Es gibt auch viele SQL-Anweisungen im Zusammenhang mit Binärprotokollvorgängen und -verwaltung. Im Folgenden sind einige häufig verwendete Anweisungen aufgeführt:

3. Analysieren Sie den Binlog-Inhalt

Wie bereits erwähnt, werden alle Änderungen an der Datenbank im Binglog aufgezeichnet. Binlog ist jedoch eine Binärdatei und kann nicht direkt angezeigt werden. Wenn Sie es intuitiver betrachten möchten, müssen Sie das Befehlstool mysqlbinlog verwenden. Der folgende Inhalt stellt hauptsächlich die Verwendung von mysqlbinlog zum Analysieren des Binlog-Protokollinhalts vor.

Für die reibungslose Entwicklung der Story wechseln wir zuerst zu Binlog, erstellen dann eine Testbibliothek und eine Testtabelle, fügen Daten ein und aktualisieren Daten. Diese Voroperationen werden derzeit nicht angezeigt. Sehen wir uns an, wie der generierte Binärprotokollinhalt analysiert und angezeigt wird:

# Diese Analyse basiert auf MySQL 8.0, die Instanz hat gtid aktiviert und der Modus ist ROW

[root@centos-Protokolle]# mysqlbinlog --no-defaults --base64-output=decode-rows -vv binlog.000013
/*!50530 SETZEN @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SETZEN @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
...
...
#200708 16:52:09 Server-ID 1003306 End-Log-Pos 1049 CRC32 0xbcf3de39 Abfrage Thread-ID = 85 Exec-Time = 0 Fehlercode = 0 Xid = 1514
verwende „bindb“/*!*/;
ZEITSTEMPEL EINSTELLEN=1594198329/*!*/;
SETZEN Sie @@session.explicit_defaults_for_timestamp=1/*!*/;
/*!80013 SETZEN @@session.sql_require_primary_key=0*//*!*/;
Tabelle „bin_tb“ erstellen (
  `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel automatisch inkrementieren',
  `stu_id` int(11) NOT NULL KOMMENTAR 'Studenten-ID',
  `stu_name` varchar(20) DEFAULT NULL COMMENT 'Studentenname',
  `create_time` Zeitstempel NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',
  `update_time` Zeitstempel NICHT NULL STANDARD CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Änderungszeit',
  PRIMÄRSCHLÜSSEL (`increment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Binlog testen'
/*!*/;
# bei 1049
#200708 16:52:45 Server-ID 1003306 End-Log-Position 1128 CRC32 0xf19ea0a9 GTID letztes Committed = 2 Sequenznummer = 3 rbr_only = ja Original-Committed-Zeitstempel = 1594198365741300 Zeitstempel für sofortiges Commit = 1594198365741300 Transaktionslänge = 468
/*!50718 FESTLEGEN DER TRANSAKTIONSISOLIERSTUFE - LESEN COMMITTED*//*!*/;
# original_commit_timestamp=1594198365741300 (08.07.2020 16:52:45.741300 CST)
# immediate_commit_timestamp=1594198365741300 (08.07.2020 16:52:45.741300 CST)
/*!80001 FESTLEGEN @@session.original_commit_timestamp=1594198365741300*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SETZEN @@session.immediate_server_version=80019*//*!*/;
SETZEN @@SESSION.GTID_NEXT= '0032d819-2d32-11ea-91b5-5254002ae61f:24883'/*!*/;
# bei 1128
#200708 16:52:45 Server-ID 1003306 End-Log-Pos 1204 CRC32 0x5b4b03db Abfrage Thread-ID = 85 Exec-Zeit = 0 Fehlercode = 0
ZEITSTEMPEL EINSTELLEN=1594198365/*!*/;
BEGINNEN
/*!*/;
# bei 1204
#200708 16:52:45 Server-ID 1003306 end_log_pos 1268 CRC32 0xd4755d50 Table_map: `bindb`.`bin_tb` zugeordnet zu Nummer 139
# bei 1268
#200708 16:52:45 Server-ID 1003306 end_log_pos 1486 CRC32 0x274cf734 Write_rows: Tabellen-ID 139 Flags: STMT_END_F
### INSERT INTO `bindb`.`bin_tb`
### SATZ
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=1001 /* INT meta=0 nullable=0 is_null=0 */
### @3='from1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### INSERT INTO `bindb`.`bin_tb`
### SATZ
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=1002 /* INT meta=0 nullable=0 is_null=0 */
### @3='dfsfd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
...
# bei 1486
#200708 16:52:45 Server-ID 1003306 end_log_pos 1517 CRC32 0x0437e777 Xid = 1515
BEGEHEN /*!*/;
...
# bei 1596
#200708 16:54:35 Server-ID 1003306 End-Log-Pos 1681 CRC32 0x111539b6 Abfrage Thread-ID = 85 Exec-Zeit = 0 Fehlercode = 0
ZEITSTEMPEL EINSTELLEN=1594198475/*!*/;
BEGINNEN
/*!*/;
# bei 1681
#200708 16:54:35 Server-ID 1003306 end_log_pos 1745 CRC32 0x6f0664ee Table_map: `bindb`.`bin_tb` zugeordnet zu Nummer 139
# um 1745
#200708 16:54:35 Server-ID 1003306 end_log_pos 1939 CRC32 0xfafe7ae8 Update_rows: Tabellen-ID 139 Flags: STMT_END_F
### UPDATE `bindb`.`bin_tb`
###WO
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=1005 /* INT meta=0 nullable=0 is_null=0 */
### @3='dsfsdg' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### SATZ
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=1005 /* INT meta=0 nullable=0 is_null=0 */
### @3=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198475 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### UPDATE `bindb`.`bin_tb`
###WO
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2=1006 /* INT meta=0 nullable=0 is_null=0 */
### @3='fgd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### SATZ
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2=1006 /* INT meta=0 nullable=0 is_null=0 */
### @3=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198475 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
...
# im Jahr 1939
#200708 16:54:35 Server-ID 1003306 end_log_pos 1970 CRC32 0x632a82b7 Xid = 1516
BEGEHEN /*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* hinzugefügt von mysqlbinlog */ /*!*/;
TRENNUNGSZEICHEN ;
# Ende der Protokolldatei
/*!50003 SETZEN SIE COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SETZEN @@SESSION.PSEUDO_SLAVE_MODE=0*/;

# Es ist ersichtlich, dass das Binärprotokoll die durch die Ausführung jeder SQL-Anweisung verursachten Änderungen detailliert aufzeichnet.
Und es enthält Systemwerte wie Ausführungszeit, POS-Standort, Server-ID usw.

Es gibt noch viele weitere Tipps zur Verwendung des Tools mysqlbinlog, z. B. das Ausführen von Analysevorgängen nur für eine bestimmte Datenbank oder innerhalb eines bestimmten Zeitraums. Hier sind einige allgemeine Anweisungen. Weitere Informationen finden Sie in der offiziellen Dokumentation.

mysqlbinlog --no-defaults --base64-output=decode-rows -vv binlog.000013 > /tmp/bin13.sql
Importieren Sie das analysierte SQL in die Datei

mysqlbinlog --no-defaults --base64-output=decode-rows -vv --database=testdb binlog.000013
Nur Parsevorgänge einer bestimmten Bibliothek

mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-datetime="2020-01-11 01:00:00" --stop-datetime="2020-01-11 23:59:00" binlog.000008
Analysieren Sie Vorgänge innerhalb eines bestimmten Zeitraums

mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-position=204136360 --stop-position=204136499 binlog.000008
Analysieren Sie den Vorgang innerhalb der angegebenen POS-Position.

mysqlbinlog --no-defaults --start-position=204136360 --stop-position=204136499 binlog.000008 | mysql -uroot -pxxxx testdb
Wiederherstellungsvorgänge zwischen angegebenen Sites in einer angegebenen Bibliothek

IV. Fazit

Dieser Artikel ist ziemlich lang geworden, ohne dass ich es bemerkt habe. Er beschreibt verschiedene binlog-bezogene Wissenspunkte. Ich hoffe, dass Sie nach der Lektüre ein tieferes Verständnis von Binlog haben werden. Tatsächlich ist Übung das Wichtigste. Nur wenn Sie mehr lernen und mehr anwenden, können Sie es besser beherrschen. Das ist so hartes Wissen, ich hoffe, Sie können es lesen, wenn Sie es brauchen. Sie können es gerne weiterleiten und teilen, damit mehr Leute es sehen können.

Oben finden Sie den detaillierten Inhalt zum Parsen des MySQL-Binlogs. Weitere Informationen zum MySQL-Binlog finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!