Introduction détaillée aux fichiers journaux binlog dans MySQL

Cet article vous apporte une introduction détaillée au fichier journal binlog dans MySQL. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer. J'espère qu'il vous sera utile.

Le fichier journal binlog de MySQL enregistre toutes les opérations de modification de la table de la base de données. Cet article résume brièvement les connaissances liées au binlog MySQL et comment utiliser binlog pour restaurer ou flashback les données de la base de données.

binlog au format STATEMENT

Pour activer binlog, vous devez passer le paramètre --log-bin lors du démarrage de MySQL. Ou vous pouvez définir log_bin dans le fichier de configuration MySQL /etc/my.cnf pour activer binlog. À partir de MySQL 5.7, après avoir activé binlog, le paramètre --server-id doit également être spécifié, sinon le serveur MySQL ne démarrera pas.

binlog_format prend en charge trois formats : STATEMENT, ROW et MIXED. MySQL 5.5 et 5.6 sont par défaut STATEMENT, et MySQL 5.7.7 commence par défaut à ROW. comme SQL utilise UUID(), RAND(), VERSION() et d'autres fonctions, ou utilise des procédures stockées, des fonctions personnalisées, basées sur STATEMENT Il n'est pas sûr lorsque le maître-esclave est répliqué (beaucoup de gens peuvent penser que NOW(), CURRENT_TIMESTAMP et ces fonctions sont également dangereux, mais en fait ils sont sûrs) [doc1, doc2]. La réplication maître-esclave basée sur ROW est la méthode de réplication la plus sûre.

Regardons maintenant le binlog au format STATEMENT. Le contenu modifié du fichier /etc/my.cnf est le suivant :

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

Après avoir redémarré MySQL, sous les données. répertoire datadir, tel que /var /lib/mysql/, les fichiers binlog correspondants, mysql-bin.index et mysql-bin.000001, seront générés. Les fichiers avec le suffixe .index enregistrent tous les noms de fichiers binlog. Le fichier mysql-bin.000001 enregistre le contenu du binlog. Chaque fois que MySQL démarre ou vide le journal, un nouveau fichier journal sera créé en fonction du numéro de séquence. De plus, lorsque la taille du fichier journal dépasse max_binlog_size, un nouveau fichier journal est également créé.

Essayons maintenant la fonction binlog. Supposons qu'il y ait une table hello dans la bibliothèque testdb et qu'une opération de modification soit effectuée sur une ligne :

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 est un fichier binaire, et vous devez utiliser le mysqlbinlog (doc, man ) pour l'afficher :

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

Le contenu du fichier binlog nouvellement ajouté après l'exécution de la mise à jour :

# 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 au format ROW

Modifiez le format binlog_format de /etc/my.cnf en ROW, redémarrez MySQL. Une fois le format modifié, un nouveau fichier binlog mysql-bin.000002 sera généré.

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

Pour afficher le binlog au format ROW, vous devez utiliser la commande sudo mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000002. Le contenu du journal binaire correspondant nouvellement ajouté après l'exécution de la mise à jour :

# 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/*!*/;

Si le SQL suivant est exécuté :

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

Le contenu du journal binaire généré correspondant :

# 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/*!*/;

Si exécuté Le SQL suivant :

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

Le contenu du binlog généré correspondant :

# 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/*!*/;

Utiliser la récupération incrémentielle du binlog

La sauvegarde logique MySQL est généralement combinés Pour une sauvegarde complète et une sauvegarde incrémentielle, utilisez mysqldump pour sauvegarder régulièrement la base de données dans son intégralité, puis utilisez binlog pour enregistrer les données incrémentielles. Lors de la restauration des données, les données sauvegardées par mysqldump sont restaurées au moment de la sauvegarde. Si la base de données est modifiée de manière incrémentielle depuis le moment de la sauvegarde jusqu'à l'heure actuelle, les données incrémentielles du binlog seront restaurées dans la base de données via mysqlbinlog. Supposons maintenant que mysqldump a été utilisé pour restaurer la base de données vers :

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

Le SQL exécuté après :

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

Que ce soit en utilisant STATEMENT ou ROW, la commande mysqlbinlog peut restaurer progressivement le binlog à la base de données [doc].

En observant le binlog, nous pouvons voir que depuis la mise à jour initiale hello set name = 'David' où id = 3; jusqu'à la suppression finale de hello où id = 2;, l'heure est du "2018-06 -17 22:54:13" à "2018-06-17 22:56:44", donc en fonction de la récupération du point temporel, la commande est la suivante :

$ 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 La position de l'événement le nombre est de "154" à "956", mais il convient de noter qu'utilisez --start-position et --stop-position pour spécifier la plage de points de localisation, qui correspond logiquement à start <= position < stop, donc en fonction de la récupération du point temporel, la commande est comme suit :

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

Dans les deux cas, vous pouvez restaurer les données sur :

mysql> select * from hello;
+----+-------+
| id | name  |
+----+-------+
|  1 | Andy  |
|  3 | David |
|  4 | Frank |
+----+-------+
3 rows in set (0.00 sec)<p><strong>Utilisez binlog2sql pour revenir à </strong></p>
<p>binlog2sql. l'auteur est Cao Danfeng, DBA de Dianping.com. binlog2sql, analyse le SQL que vous souhaitez à partir du binlog MySQL. Selon les options, vous pouvez obtenir le SQL original, le SQL de restauration, INSERT SQL avec la clé primaire supprimée, etc. binlog2sql, l'implémentation sous-jacente s'appuie sur python-mysql-replication, qui complète l'analyse du protocole de réplication MySQL et du format binlog. </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

Générer un rollback sql :

$ 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

Le principe réel du flashback est très simple. Videz d'abord le binlog via la commande com-binlog-dump du protocole de réplication MySQL, et. puis suivez le binlog Analysez le binlog selon la spécification de format, convertissez le binlog en SQL, convertissez ces SQL en SQL logique inverse et enfin exécutez-le dans l'ordre inverse.

Binlog d'analyse Java

上文中的 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]
]}}

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!