Mysql binlog 格式

最新推荐文章于 2024-05-25 17:06:40 发布

justsomebody126

最新推荐文章于 2024-05-25 17:06:40 发布

阅读量228

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/justsomebody126/article/details/110580143

版权

本文详细解析了MySQL binlog的格式，从format_desc event开始，包括header和data部分的解析，接着介绍了rotate event、query event、table_map event、Write_rows、Xid、Delete_rows和Update_rows等关键事件。此外，还探讨了如何查看和设置binlog的CRC32校验以及相关参数。

摘要由CSDN通过智能技术生成

参考：https://www.jianshu.com/p/c16686b35807

本文基于：
binlog_format=Row
binlog_row_image=FULL

从一个空的 binlog 开始

mysql> flush logs;  // 产生一个全新的 binlog 文件 mysql_bin.000009
Query OK, 0 rows affected (0.01 sec)

format_desc event

mysql> show binlog events in 'mysql_bin.000009';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000009 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.18-log, Binlog ver: 4 |
| mysql_bin.000009 | 123 | Previous_gtids |         1 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

$hexdump -C /home/shicai.xsc/mysql/data/mysql1/mysql_bin.000009
00000000  fe 62 69 6e b3 ef c8 5f  0f 01 00 00 00 77 00 00  |.bin..._.....w..|
00000010  00 7b 00 00 00 01 00 04  00 35 2e 37 2e 31 38 2d  |.{.......5.7.18-|
00000020  6c 6f 67 00 00 00 00 00  00 00 00 00 00 00 00 00  |log.............|
00000030  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 13  |................|
00000050  38 0d 00 08 00 12 00 04  04 04 04 12 00 00 5f 00  |8............._.|
00000060  04 1a 08 00 00 00 08 08  08 02 00 00 00 0a 0a 0a  |................|
00000070  2a 2a 00 12 34 00 01 45  e0 a6 fb b3 ef c8 5f 23  |**..4..E......_#|
00000080  01 00 00 00 1f 00 00 00  9a 00 00 00 80 00 00 00  |................|
00000090  00 00 00 00 00 00 56 85  cc ab                    |......V...|
0000009a

前面 4 个字节是固定的 magic number，值为 fe 62 69 6e。
binlog 第一个 event 也就是 format_desc event（mysql日志是小端字节序）。

format_desc event 格式

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    | = FORMAT_DESCRIPTION_EVENT = 15
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    | >= 91
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
+=====================================+
| event  | binlog_version   19 : 2    | = 4
| data   +----------------------------+
|        | server_version   21 : 50   |
|        +----------------------------+
|        | create_timesta