Detaillierte Einführung in Binlog-Protokolldateien in MySQL

Dieser Artikel bietet Ihnen eine detaillierte Einführung in die Binlog-Protokolldatei in MySQL. Ich hoffe, dass er für Freunde hilfreich ist.

Die Binlog-Protokolldatei von MySQL zeichnet alle Änderungsvorgänge der Datenbanktabelle auf. Dieser Artikel fasst kurz das Wissen im Zusammenhang mit MySQL-Binlog und der Verwendung von Binlog zum Wiederherstellen oder Flashbacken von Datenbankdaten zusammen.

Binlog im STATEMENT-Format

Um Binlog zu aktivieren, müssen Sie beim Starten von MySQL den Parameter --log-bin übergeben. Oder Sie können log_bin in der MySQL-Konfigurationsdatei /etc/my.cnf festlegen, um Binlog zu aktivieren. Ab MySQL 5.7 muss nach der Aktivierung von Binlog auch der Parameter --server-id angegeben werden, da sonst der MySQL-Server nicht gestartet werden kann.

binlog_format unterstützt drei Formate: STATEMENT, ROW und MIXED. MySQL 5.5 und 5.6 verwenden standardmäßig STATEMENT, und MySQL 5.7.7 beginnt standardmäßig mit ROW. wie SQL verwendet UUID(), RAND(), VERSION() und andere Funktionen oder verwendet gespeicherte Prozeduren, benutzerdefinierte Funktionen, basierend auf STATEMENT Es ist unsicher, wenn Master-Slave repliziert wird (viele Leute denken vielleicht, dass NOW(), CURRENT_TIMESTAMP und diese Funktionen ebenfalls unsicher sind, aber tatsächlich sind sie sicher) [doc1, doc2]. Die auf ROW basierende Master-Slave-Replikation ist die sicherste Replikationsmethode.

Werfen wir nun einen Blick auf das Binlog im STATEMENT-Format. Der geänderte Inhalt der Datei /etc/my.cnf lautet wie folgt:

server_id = 1
log_bin = mysql-bin
binlog_format = STATEMENT
binlog_row_image=FULL

Nach dem Neustart von MySQL im Datenverzeichnis datadir B. /var/lib /mysql/, werden die entsprechenden Binlog-Dateien mysql-bin.index und mysql-bin.000001 generiert. Dateien mit dem Suffix .index speichern alle Binlog-Dateinamen. Die Datei mysql-bin.000001 zeichnet Binlog-Inhalte auf. Jedes Mal, wenn MySQL das Protokoll startet oder leert, wird entsprechend der Sequenznummer eine neue Protokolldatei erstellt. Wenn die Größe der Protokolldatei außerdem max_binlog_size überschreitet, wird auch eine neue Protokolldatei erstellt.

Jetzt probieren wir die Binlog-Funktion aus. Angenommen, es gibt eine Hello-Tabelle in der testdb-Bibliothek und eine Änderungsoperation wird an einer Zeile darin durchgeführt:

mysql> select * from hello;
+----+-------+
| id | name  |
+----+-------+
|  1 | Andy  |
|  2 | Bill  |
|  3 | Candy |
+----+-------+
4 rows in set (0.00 sec)

mysql> update hello set name = 'Will' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

binlog ist eine Binärdatei, die mit mysqlbinlog (doc, man) angezeigt werden muss. Befehl:

$ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000001  # 直接在 mysql 服务器上读取 binlog 文件
$ mysqlbinlog -R -h192.168.2.107 -uroot -p123456 mysql-bin.000001  # 或者，远程读取 binlog 文件

update ausführen Der Inhalt der neu hinzugefügten Binlog-Datei ist:

# at 154
#180617 22:47:49 server id 1  end_log_pos 219 CRC32 0x4bd9d69b     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180617 22:47:49 server id 1  end_log_pos 302 CRC32 0x476fafc9     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529246869/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 302
#180617 22:47:49 server id 1  end_log_pos 423 CRC32 0x7f2c2c7a     Query    thread_id=2    exec_time=0    error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1529246869/*!*/;
update hello set name = 'Will' where id = 3
/*!*/;
# at 423
#180617 22:47:49 server id 1  end_log_pos 454 CRC32 0x68da744a     Xid = 12
COMMIT/*!*/;

binlog im ROW-Format

Ändern Sie das binlog_format von /etc /my.cnf in ROW und starten Sie dann MySQL neu. Nachdem das Format geändert wurde, wird eine neue Binlog-Datei mysql-bin.000002 generiert.

mysql> show create table hello;
+-------+-------------------------------------------------------------------------+
| Table | Create Table
+-------+-------------------------------------------------------------------------+
| hello | CREATE TABLE `hello` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from hello where id;
+----+------+
| id | name |
+----+------+
|  1 | Andy |
|  2 | Lily |
|  3 | Will |
+----+------+
1 row in set (0.00 sec)

mysql> update hello set name = 'David' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Um Binlog im ROW-Format anzuzeigen, müssen Sie den Befehl sudo mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000002 verwenden. Der entsprechende neu hinzugefügte Binlog-Inhalt nach der Ausführung des Updates:

# at 154
#180617 22:54:13 server id 1  end_log_pos 219 CRC32 0x2ce70d4d     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180617 22:54:13 server id 1  end_log_pos 293 CRC32 0x8183fddf     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247253/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#180617 22:54:13 server id 1  end_log_pos 346 CRC32 0x0fc7e1a4     Table_map: `testdb`.`hello` mapped to number 110
# at 346
#180617 22:54:13 server id 1  end_log_pos 411 CRC32 0xb58e729d     Update_rows: table id 110 flags: STMT_END_F
### UPDATE `testdb`.`hello`
### WHERE
###   @1=3
###   @2='Will'
### SET
###   @1=3
###   @2='David'
# at 411
#180617 22:54:13 server id 1  end_log_pos 442 CRC32 0xef964db8     Xid = 13
COMMIT/*!*/;

Wenn die folgende SQL ausgeführt wird:

mysql> insert hello (name) values ('Frank');
Query OK, 1 row affected (0.02 sec)

Der entsprechende generierte Binlog-Inhalt:

# at 442
#180617 22:55:47 server id 1  end_log_pos 507 CRC32 0x79de08a7     Anonymous_GTID    last_committed=1    sequence_number=2    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 507
#180617 22:55:47 server id 1  end_log_pos 581 CRC32 0x56f9eb6a     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247347/*!*/;
BEGIN
/*!*/;
# at 581
#180617 22:55:47 server id 1  end_log_pos 634 CRC32 0xedb73620     Table_map: `testdb`.`hello` mapped to number 110
# at 634
#180617 22:55:47 server id 1  end_log_pos 684 CRC32 0x525a6a70     Write_rows: table id 110 flags: STMT_END_F
### INSERT INTO `testdb`.`hello`
### SET
###   @1=4
###   @2='Frank'
# at 684
#180617 22:55:47 server id 1  end_log_pos 715 CRC32 0x09a0d4de     Xid = 14
COMMIT/*!*/;

Wenn die folgende SQL ausgeführt wird :

mysql> delete from hello where id = 2;
Query OK, 1 row affected (0.02 sec)

Der entsprechende generierte Binlog-Inhalt:

# at 715
#180617 22:56:44 server id 1  end_log_pos 780 CRC32 0x9f52450e     Anonymous_GTID    last_committed=2    sequence_number=3    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 780
#180617 22:56:44 server id 1  end_log_pos 854 CRC32 0x0959bc8d     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247404/*!*/;
BEGIN
/*!*/;
# at 854
#180617 22:56:44 server id 1  end_log_pos 907 CRC32 0x2945260f     Table_map: `testdb`.`hello` mapped to number 110
# at 907
#180617 22:56:44 server id 1  end_log_pos 956 CRC32 0xc70df255     Delete_rows: table id 110 flags: STMT_END_F
### DELETE FROM `testdb`.`hello`
### WHERE
###   @1=2
###   @2='Bill'
# at 956
#180617 22:56:44 server id 1  end_log_pos 987 CRC32 0x0c98f18e     Xid = 15
COMMIT/*!*/;

Inkrementelle Binlog-Wiederherstellung verwenden

Die logische MySQL-Sicherung kombiniert normalerweise eine vollständige Sicherung und eine inkrementelle Sicherung und verwendet sie mysqldump regelmäßig Sichern Sie die Datenbank vollständig und verwenden Sie dann binlog, um inkrementelle Daten zu speichern. Beim Wiederherstellen von Daten werden die von mysqldump gesicherten Daten zum Zeitpunkt der Sicherung wiederhergestellt. Wenn die Datenbank vom Sicherungszeitpunkt bis zum aktuellen Zeitpunkt inkrementell geändert wird, werden die inkrementellen Daten im Binlog über mysqlbinlog in der Datenbank wiederhergestellt. Nehmen Sie nun an, dass mysqldump verwendet wurde, um die Datenbank wiederherzustellen:

mysql> select * from hello;
+----+------+
| id | name |
+----+------+
|  1 | Andy |
|  2 | Lily |
|  3 | Will |
+----+------+
3 rows in set (0.00 sec)

Das anschließend ausgeführte SQL:

update hello set name = 'David' where id = 3;
insert hello (name) values ('Frank');
delete from hello where id = 2;

Unabhängig davon, ob STATEMENT oder ROW verwendet wird, kann der Befehl mysqlbinlog binlog inkrementell in der Datenbank wiederherstellen [doc ] .

Wenn wir uns das Binlog ansehen, können wir sehen, dass vom ersten Update „Hello Set Name = ‚David‘ mit der ID = 3;“ bis zum endgültigen Löschen von „Hallo mit der ID = 2;“ die Zeit von „2018-06“ stammt -17 22:54:13“ bis „2018-06-17 22:56:44“, also lautet der Befehl basierend auf der Zeitpunktwiederherstellung wie folgt: Die Ereignispositionsnummer von

$ sudo mysqlbinlog --start-datetime="2018-06-17 22:54:13" --stop-datetime="2018-06-17 22:56:44" mysql-bin.000002 | mysql -uroot -p123456

binlog ist von „154“ bis „956“, aber es sollte beachtet werden, dass --start-position und --stop-position verwendet werden, um den Standortpunktbereich anzugeben, der logischerweise start <= position < stop entspricht. Basierend auf der Wiederherstellung des Zeitpunkts lautet der Befehl also wie folgt folgt:

$ sudo mysqlbinlog --start-position=154 --stop-position=957 mysql-bin.000002 | mysql -uroot -p123456

So oder so Ausführen, die Daten können wiederhergestellt werden in:

mysql> select * from hello;
+----+-------+
| id | name  |
+----+-------+
|  1 | Andy  |
|  3 | David |
|  4 | Frank |
+----+-------+
3 rows in set (0.00 sec)<p><strong>Verwenden Sie binlog2sql, um einen Flashback durchzuführen</strong></p>
<p>binlog2sql, der Autor ist Cao Danfeng, Dianping DBA. binlog2sql, analysiert das gewünschte SQL aus dem MySQL-Binlog. Abhängig von den Optionen können Sie Original-SQL, Rollback-SQL, INSERT-SQL mit entferntem Primärschlüssel usw. erhalten. Binlog2sql, die zugrunde liegende Implementierung, basiert auf der Python-MySQL-Replikation, die das Parsen des MySQL-Replikationsprotokolls und des Binlog-Formats abschließt. </p>
<pre class="brush:php;toolbar:false">$ python binlog2sql/binlog2sql.py -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=957 --start-file='mysql-bin.000002'
UPDATE `testdb`.`hello` SET `id`=3, `name`='David' WHERE `id`=3 AND `name`='Will' LIMIT 1; #start 4 end 411 time 2018-06-17 22:54:13
INSERT INTO `testdb`.`hello`(`id`, `name`) VALUES (4, 'Frank'); #start 442 end 684 time 2018-06-17 22:55:47
DELETE FROM `testdb`.`hello` WHERE `id`=2 AND `name`='Bill' LIMIT 1; #start 715 end 956 time 2018-06-17 22:56:44

Rollback-SQL generieren:

$ python binlog2sql/binlog2sql.py --flashback -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=956 --start-file='mysql-bin.000002'
INSERT INTO `testdb`.`hello`(`id`, `name`) VALUES (2, 'Bill'); #start 715 end 956 time 2018-06-17 22:56:44
DELETE FROM `testdb`.`hello` WHERE `id`=4 AND `name`='Frank' LIMIT 1; #start 442 end 684 time 2018-06-17 22:55:47
UPDATE `testdb`.`hello` SET `id`=3, `name`='Will' WHERE `id`=3 AND `name`='David' LIMIT 1; #start 154 end 411 time 2018-06-17 22:54:13

Das eigentliche Prinzip des Flashbacks ist sehr einfach. Geben Sie zuerst das Binlog über den Befehl com-binlog-dump des MySQL-Replikationsprotokolls aus und folgen Sie dann dem Binlog Analysieren Sie das Binlog gemäß der Formatspezifikation, konvertieren Sie das Binlog in SQL, konvertieren Sie diese SQL in umgekehrtes logisches SQL und führen Sie es schließlich in umgekehrter Reihenfolge aus.

Java-Parsing-Binlog

上文中的 binlog2sql 其实底层依赖 python-mysql-replication 库，这是 Python 库。如果想使用 Java 解析 binlog 可以使用 mysql-binlog-connector-java（github）库。目前开源的 CDC 工具，如 Zendesk maxwell、Redhat debezium、LinkedIn Databus 等都底层依赖 mysql-binlog-connector-java 或者其前身 open-replicator。使用 mysql-binlog-connector-java 的示例代码如下：

BinaryLogClient client = new BinaryLogClient("192.168.2.107", 3306, "root", "123456");
client.setBinlogFilename("mysql-bin.000001");
client.setBinlogPosition(4);
client.setBlocking(false);
client.registerEventListener(event -> {
    System.out.println(event);
});
client.connect();

输出（省略部分内容）：

...
Event{header=EventHeaderV4{timestamp=1529247253000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=346, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247253000, eventType=EXT_UPDATE_ROWS, serverId=1, headerLength=19, dataLength=46, nextPosition=411, flags=0}, data=UpdateRowsEventData{tableId=110, includedColumnsBeforeUpdate={0, 1}, includedColumns={0, 1}, rows=[
    {before=[3, Will], after=[3, David]}
]}}
...
Event{header=EventHeaderV4{timestamp=1529247347000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=634, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247347000, eventType=EXT_WRITE_ROWS, serverId=1, headerLength=19, dataLength=31, nextPosition=684, flags=0}, data=WriteRowsEventData{tableId=110, includedColumns={0, 1}, rows=[
    [4, Frank]
]}}
...
Event{header=EventHeaderV4{timestamp=1529247404000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=907, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247404000, eventType=EXT_DELETE_ROWS, serverId=1, headerLength=19, dataLength=30, nextPosition=956, flags=0}, data=DeleteRowsEventData{tableId=110, includedColumns={0, 1}, rows=[
    [2, Bill]
]}}

Das obige ist der detaillierte Inhalt vonDetaillierte Einführung in Binlog-Protokolldateien in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!