MySQL binlog event 详解

实验环境：

CentOS7.3.1611 + MySQL社区版 5.7.19

参考：

    小菜鸟DBA的微信公众号推送

官方文档：

https://dev.mysql.com/doc/internals/en/binary-log-versions.html

https://dev.mysql.com/doc/internals/en/row-based-binary-logging.html

https://dev.mysql.com/doc/internals/en/event-classes-and-types.html

https://dev.mysql.com/doc/internals/en/event-header-fields.html

https://dev.mysql.com/doc/internals/en/event-meanings.html

https://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html

3个在线工具：

http://tool.oschina.net/hexconvert/  在线进制转换

http://tool.chinaz.com/Tools/unixtime.aspx   Unix时间戳

https://www.bejson.com/convert/ox2str/   16进制转字符串

binlog实际上由一个个不同类型的binlog event组成，每个binlog event还包含了event header部分和event data部分(可选)。

【注意：每个event最后还有4bytes的校验位，官方文档并没有提到这个地方，不然分析event物理格式时候会发现event长度对不上号】

常见的一个binlog物理文件有如下组成部分：

1、4字节的magic number作为binlog文件的开头

2、N个不同类型的binlog event

3、rotate event 作为binlog文件的结尾（正在使用的binlog里面是没有rotate event的）

此外，还有一个索引文件记录当前有哪些binlog文件，及当前正在使用的binlog文件。（文件名类似：mysql-bin.index）

下表就是的binlog event的一般格式：

+=====================================+

| event  | timestamp         0 : 4    |

| header +----------------------------+

|        | type_code         4 : 1    | = FORMAT_DESCRIPTION_EVENT = 15（binlog v4）

|        +----------------------------+

|        | 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_timestamp 71 : 4    |

|        +----------------------------+

|        | header_length    75 : 1    |

|        +----------------------------+

|        | post-header      76 : n    | = array of n bytes, one byte per event

|        | lengths for all            |   type that the server knows about

|        | event types                |

+=====================================+

常用的EVENT如下：

    FORMAT_DESCRIPTION_EVENT：binlog文件的第一个event，记录版本号等元数据信息

    QUERY_EVENT: 存储statement类的信息，基于statement的binlog格式记录sql语句，在row模式下记录事务begin标签

    XID_EVENT: 二阶段提交xid记录

    TABLE_MAP_EVENT: row模式下记录表源数据，对读取行记录提供规则参考，后面会详细介绍

    WRITE_ROWS_EVENT/DELETE_ROWS_EVENT/UPDATE_ROWS_EVENT: row模式下记录对应行数据变化的记录

    GTID_LOG_EVENT: 这个就是记录GTID事务号了，用于5.6版本之后基于GTID同步的方式

    ROTATE_EVENT: 连接下一个binlog文件

需要了解更全面的Event类型详见： https://dev.mysql.com/doc/internals/en/event-classes-and-types.html

下面是我截取的一个完整的binlog文件，具体的events如下：

目前，我们一般都是使用row格式的binlog，其他的mixed和statement格式的binlog这里不去关注了。

对于row格式的DML操作而言，实际上在binlog里面记录的是：TABLE_MAP_EVENT+ ROW_LOG_EVENT（ROW_LOG_EVENT还可以细分为WRITE_ROWS_EVENT、UPDATE_ROWS_EVENT、DELETE_ROWS_EVENT）

为什么一个update在ROW模式下需要分解成两个event：一个Table_map，一个Update_rows?

我们想象一下，一个update如果更新了10000条数据，那么对应的表结构信息是否需要记录10000次?其实是对同一个表的操作，所以这里binlog只是记录了一个Table_map用于记录表结构相关信息,而后面的Update_rows记录了更新数据的行信息。他们之间是通过table_id来联系的。【table_id不是固定的，是一个变量，占用的是table_definition_cache和table_open_cache空间（因此flush tables会造成table_id的增长）】

如下是一个insert插入1条记录的binlog，可以看到有table_map+ write_rows 这2个event组成。

table_map记录的是表的元数据信息，例如库名、表名、字段类型等信息。

补充：

关于table_id的几篇干货：

http://blog.itpub.net/22664653/viewspace-1158547/ 【杨奇龙】

http://agapple.iteye.com/blog/1797061

http://www.cnblogs.com/yuyue2014/p/3721172.html

http://www.sohu.com/a/130698375_610509   【宋利兵】

http://www.cnblogs.com/cenalulu/archive/2012/09/24/2699907.html  【卢钧轶】

其他的几个EVENT类型：

官方文档：https://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html

FORMAT_DESCRIPTION_EVENT

这个是最基础的event，每个新的binlog头部就带有这个event。每一个binlog文件只能存在一个FORMAT_DESCRIPTION_EVENT。

Fixed data part:

• 2 bytes. The binary log format version. This is 4 in MySQL 5.0 and up.

• 50 bytes. The MySQL server's version (example: 5.0.14-debug-log), padded with 0x00 bytes on the right.

• 4 bytes. Timestamp in seconds when this event was created (this is the moment when the binary log was created). This value is redundant; the same value occurs in the timestamp header field.

• 1 byte. The header length. This length - 19 gives the size of the extra headers field at the end of the header for other events.

• Variable-sized. An array that indicates the post-header lengths for all event types. There is one byte per event type that the server knows about.

FORMAT_DESCRIPTION_EVENT 实例：

flush logs; 产生一个全新的binlog文件，导出后如下：

master [localhost] {root} ((none)) > show binlog events in 'mysql-bin.000002';

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

| mysql-bin.000002 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.37-log, Binlog ver: 4 |

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

[root@test_mysql26 /root/sandboxes/rsandbox_5_6_37/master/data ]# hexdump -C mysql-bin.000002

00000000  fe 62 69 6e f6 e3 fe 59  0f 01 00 00 00 74 00 00  |.bin...Y.....t..|

00000010  00 78 00 00 00 01 00 04  00 35 2e 36 2e 33 37 2d  |.x.......5.6.37-|

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 5c 00  |8.............\.|

00000060  04 1a 08 00 00 00 08 08  08 02 00 00 00 0a 0a 0a  |................|

00000070  19 19 00 01 08 4c 67 48                           |.....LgH|

00000078

magic number （4bytes）

fe 62 69 6e   

event header （19bytes）

f6 e3 fe 59       timestamp

0f                type_code   表示binlog采用v4版本的

01 00 00 00       server_id

74 00 00 00       event_length   116bytes

78 00 00 00       next_position  下一个event从120开始

01 00             flags

event data：

04 00     binlog version  ，表示v4版的binlog格式

35 2e 36 2e 33 37 2d 6c 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00     表示的是server_version ，转换成字符串就是5.6.37-log

00 00 00 00     create_timestamp ，用的是相对时间

13            表示的是event header的长度，十进制表示就是19bytes     

38 0d 00 08 00 12 00 04  04 04 04 12 00 00 5c 00 04 1a 08 00 00 00 08 08  08 02 00 00 00 0a 0a 0a 19 19 00 01        36种event 类型

08 4c 67 48       4bytes校验位。

36种event类型：https://dev.mysql.com/doc/internals/en/event-classes-and-types.html

STOP_EVENT:

当正常关闭mysqld时候，或者是从库上执行了reset slave 都 产生这个stop_event

A Stop_log_event is written under these circumstances:

• A master writes the event to the binary log when it shuts down

• A slave writes the event to the relay log when it shuts down or when a RESET SLAVE statement is executed

STOP_EVENT实例：

/etc/init.d/mysqld restart 

master [localhost] {root} ((none)) > show binlog events in 'mysql-bin.000002';

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

| mysql-bin.000002 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.37-log, Binlog ver: 4 |

| mysql-bin.000002 | 120 | Stop        |         1 |         143 |                                       |

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

2 rows in set (0.00 sec)

[root@test_mysql26 /root/sandboxes/rsandbox_5_6_37/master/data ]# hexdump -C mysql-bin.000002 -s 120

00000078  f9 f0 fe 59 03 01 00 00  00 17 00 00 00 8f 00 00  |...Y............|

00000088  00 00 00 39 d3 4f ad                              |...9.O.|

0000008f

含义未知。官方没有说。

QYERY_EVENT:

使用begin命令开启一个事务的时候，会产生QUERY_EVENT

固定部分：

    4bytes    thread_id 可以用于审计

    4bytes    该语句的执行时长，单位秒

    1byte    执行命令时候所在的库名的字节长度

    2bytes    错误代码

    2bytes    记录data part部分variable status的长度

可变部分：

    0或者更多状态变量

    默认的库名

    SQL_Statement

master [localhost] {root} (test) > begin;

master [localhost] {root} (test) > insert into tttt2 select 'AAAA';

master [localhost] {root} (test) > commit;

master [localhost] {root} (test) > show binlog events in 'mysql-bin.000001';

+------------------+-----+-------------+-----------+-------------+---------------------------------------------+

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                        |

+------------------+-----+-------------+-----------+-------------+---------------------------------------------+

| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.37-log, Binlog ver: 4       |

| mysql-bin.000001 | 120 | Query       |         1 |         199 | BEGIN                                       |

| mysql-bin.000001 | 199 | Query       |         1 |         304 | use `test`; insert into tttt2 select 'AAAA' |

| mysql-bin.000001 | 304 | Xid         |         1 |         335 | COMMIT /* xid=40 */                         |

+------------------+-----+-------------+-----------+-------------+---------------------------------------------+

4 rows in set (0.00 sec)

[root@test_mysql26 /root/sandboxes/rsandbox_5_6_37/master/data ]# hexdump -C mysql-bin.000001  -s 120

00000078  de f3 fe 59 02 01 00 00  00 4f 00 00 00 c7 00 00  |...Y.....O......|

00000088  00 08 00 01 00 00 00 00  00 00 00 04 00 00 21 00  |..............!.|

00000098  00 00 00 00 00 01 00 00  00 40 00 00 00 00 06 03  |.........@......|

000000a8  73 74 64 04 21 00 21 00  08 00 0c 01 74 65 73 74  |std.!.!.....test|

000000b8  00 74 65 73 74 00 42 45  47 49 4e 37 1f 09 57 de  |.test.BEGIN7..W.|

000000c8  f3 fe 59 02 01 00 00 00  69 00 00 00 30 01 00 00  |..Y.....i...0...|

000000d8  00 00 01 00 00 00 00 00  00 00 04 00 00 21 00 00  |.............!..|

000000e8  00 00 00 00 01 00 00 00  40 00 00 00 00 06 03 73  |........@......s|

000000f8  74 64 04 21 00 21 00 08  00 0c 01 74 65 73 74 00  |td.!.!.....test.|

00000108  74 65 73 74 00 69 6e 73  65 72 74 20 69 6e 74 6f  |test.insert into|

00000118  20 74 74 74 74 32 20 73  65 6c 65 63 74 20 27 41  | tttt2 select 'A|

00000128  41 41 41 27 73 f4 e2 90  e0 f3 fe 59 10 01 00 00  |AAA's......Y....|

00000138  00 1f 00 00 00 4f 01 00  00 00 00 28 00 00 00 00  |.....O.....(....|

00000148  00 00 00 17 4f 16 46                              |....O.F|

0000014f

.... 这个类型的event分析卡住了，谁来帮我下。。。。。。。。。  【参考http://www.jianshu.com/p/c16686b35807】

ROTATE_EVENT：

当flush logs或者正常的切割binlog时候，会产生ROTATE_EVENT。

When a binary log file exceeds a size limit, a ROTATE_EVENT is written at the end of the file that points to the next file in the squence. This event is information for the slave to know the name of the next binary log it is going to receive.

Fixed data part:

• 8 bytes. The position of the first event in the next log file. Always contains the number 4 (meaning the next event starts at position 4 in the next binary log). This field is not present in v1; presumably the value is assumed to be 4.

Variable data part:

• The name of the next binary log. The filename is not null-terminated. Its length is the event size minus the size of the fixed parts.

XID_EVENT：

为了事务的一致性，写binlog的时候，先写事务的语句，然后写xid标志，最后才是提交COMMIT命令。

Fixed part为空

variable part 8bytes，记录的是xid编号

关于rotate event的例子：

master [localhost] {root} ((none)) > show binlog events in 'mysql-bin.000001';

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.37-log, Binlog ver: 4 |

| mysql-bin.000001 | 120 | Rotate      |         1 |         167 | mysql-bin.000002;pos=4                |

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

2 rows in set (0.00 sec)

[root@test_mysql26 /root/sandboxes/rsandbox_5_6_37/master/data ]# hexdump -C mysql-bin.000001  -s 4 -n 19  导出format desc event的内容

00000004  e2 e3 fe 59 0f 01 00 00  00 74 00 00 00 78 00 00  |...Y.....t...x..|

00000014  00 00 00                                          |...|

00000017

• < 上一篇

  SQL Server 中master..spt_values的应用

• 下一篇 >

  MySQL更改root密码、连接MySQL、MySQL常用命令介绍