mysql binlog row 解析_解析MySQL binlog --（5）ROWS_EVENT

1、介绍

对于statement格式的binlog，所有增删改的SQL语句都记录在QUERY_EVENT中，而row格式的binlog则以ROWS_EVENT格式记录对数据库的修改。ROWS_EVENT分为3种：WRITE_ROWS_EVENT、UPDATE_ROWS_EVENT、DELETE_ROWS_EVENT，分别对应insert、update、delete。WRITE_ROWS_EVENT包含了要插入的数据；UPDATE_ROWS_EVENT不仅包含行修改后的值，也包括修改前的值；DELETE_ROWS_EVENT仅仅记录删除行的主键值。

2、定义格式

3、案例讲解

mysql> show binlog events in "mysql-bin.000002";

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

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

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

| mysql-bin.000002 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.26-debug-log, Binlog ver: 4 |

| mysql-bin.000002 | 120 | Query | 11 | 191 | BEGIN |

| mysql-bin.000002 | 191 | Table_map | 11 | 236 | table_id: 70 (yzs.t1) |

| mysql-bin.000002 | 236 | Write_rows | 11 | 280 | table_id: 70 flags: STMT_END_F |

| mysql-bin.000002 | 280 | Xid | 11 | 311 | COMMIT /* xid=9 */ |

| mysql-bin.000002 | 311 | Query | 11 | 382 | BEGIN |

| mysql-bin.000002 | 382 | Table_map | 11 | 427 | table_id: 70 (yzs.t1) |

| mysql-bin.000002 | 427 | Write_rows | 11 | 471 | table_id: 70 flags: STMT_END_F |

| mysql-bin.000002 | 471 | Xid | 11 | 502 | COMMIT /* xid=37 */ |

| mysql-bin.000002 | 502 | Query | 11 | 573 | BEGIN |

| mysql-bin.000002 | 573 | Table_map | 11 | 618 | table_id: 70 (yzs.t1) |

| mysql-bin.000002 | 618 | Write_rows | 11 | 662 | table_id: 70 flags: STMT_END_F |

| mysql-bin.000002 | 662 | Xid | 11 | 693 | COMMIT /* xid=46 */ |

| mysql-bin.000002 | 693 | Query | 11 | 764 | BEGIN |

| mysql-bin.000002 | 764 | Table_map | 11 | 809 | table_id: 70 (yzs.t1) |

| mysql-bin.000002 | 809 | Write_rows | 11 | 853 | table_id: 70 flags: STMT_END_F |

| mysql-bin.000002 | 853 | Xid | 11 | 884 | COMMIT /* xid=47 */ |

| mysql-bin.000002 | 884 | Query | 11 | 955 | BEGIN |

| mysql-bin.000002 | 955 | Table_map | 11 | 1000 | table_id: 70 (yzs.t1) |

| mysql-bin.000002 | 1000 | Write_rows | 11 | 1044 | table_id: 70 flags: STMT_END_F |

| mysql-bin.000002 | 1044 | Xid | 11 | 1075 | COMMIT /* xid=56 */ |

| mysql-bin.000002 | 1075 | Query | 11 | 1199 | use `yzs`; create table t2(id1 int primary key,id2 int,id3 int) |

| mysql-bin.000002 | 1199 | Query | 11 | 1270 | BEGIN |

| mysql-bin.000002 | 1270 | Table_map | 11 | 1316 | table_id: 71 (yzs.t2) |

| mysql-bin.000002 | 1316 | Write_rows | 11 | 1364 | table_id: 71 flags: STMT_END_F |

| mysql-bin.000002 | 1364 | Xid | 11 | 1395 | COMMIT /* xid=59 */ |

| mysql-bin.000002 | 1395 | Query | 11 | 1466 | BEGIN |

| mysql-bin.000002 | 1466 | Table_map | 11 | 1512 | table_id: 71 (yzs.t2) |

| mysql-bin.000002 | 1512 | Delete_rows | 11 | 1560 | table_id: 71 flags: STMT_END_F |

| mysql-bin.000002 | 1560 | Xid | 11 | 1591 | COMMIT /* xid=60 */ |

| mysql-bin.000002 | 1591 | Query | 11 | 1662 | BEGIN |

| mysql-bin.000002 | 1662 | Table_map | 11 | 1708 | table_id: 71 (yzs.t2) |

| mysql-bin.000002 | 1708 | Write_rows | 11 | 1756 | table_id: 71 flags: STMT_END_F |

| mysql-bin.000002 | 1756 | Xid | 11 | 1787 | COMMIT /* xid=63 */ |

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

34 rows in set (0.00 sec)

3.1 insert

mysqlbinlog工具看到的日志：

# at 1316

#180324 23:03:59 server id 11 end_log_pos 1364 CRC32 0xb23b2d4f Write_rows: table id 71 flags: STMT_END_F

BINLOG '

zzu3WhMLAAAALgAAACQFAAAAAEcAAAAAAAEAA3l6cwACdDIAAwMDAwAGU9yKKQ==

zzu3Wh4LAAAAMAAAAFQFAAAAAEcAAAAAAAEAAgAD//gBAAAAAQAAAAEAAABPLTuy

'/*!*/;

### INSERT INTO `yzs`.`t2`

### SET

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2=1 /* INT meta=0 nullable=1 is_null=0 */

### @3=1 /* INT meta=0 nullable=1 is_null=0 */

# at 1364

------公有事件头-----

1)timestamp：cf 3b b7 5a，4个字节

2)event_type：1e,1个字节，WRITE_ROWS_EVENT = 30

3)server-id：0b 00 00 00,4个字节即11

4)event_size：30 00 00 00，4个字节，即该event总大小是48字节

5)next-log pos：54 05 00 00，4个字节，即下一个log的位置是1364字节位置

6)flag：00 00

-------私有事件头------

7)table ID：47 00 00 00 00 00，6个字节，即71

8)flag：01 00,2个字节，可以包含以下信息。该事件是否是语句的最后一个事件，是否需要进行外键约束检查，针对innodb的二级索引是否需要进行唯一性检查，该事件是否包含了完整一行数据也就是说覆盖了所有列。

9)Var-size header len：02 00

10)RW_V_EXTRAINFO_TAG、m_extra_row_data：没有

---------事件体---------

11)列个数：03，即3个列

12)columns-present-bitmap：ff，包含了所有列的数据

13)null-bitmap：f8，即1111 1000，即3个列都不是NULL

14)列值：01 00 00 00 01 00 00 00 01 00 00 00，即3个列的值是1,1,1

15)checksum：4f 2d 3b b2

2.2 delete

# at 1512

#180325 0:19:45 server id 11 end_log_pos 1560 CRC32 0xc6b58a5e Delete_rows: table id 71 flags: STMT_END_F

BINLOG '

kU23WhMLAAAALgAAAOgFAAAAAEcAAAAAAAEAA3l6cwACdDIAAwMDAwAG0hGsLw==

kU23WiALAAAAMAAAABgGAAAAAEcAAAAAAAEAAgAD//gBAAAAAQAAAAEAAABeirXG

'/*!*/;

### DELETE FROM `yzs`.`t2`

### WHERE

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2=1 /* INT meta=0 nullable=1 is_null=0 */

### @3=1 /* INT meta=0 nullable=1 is_null=0 */

# at 1560

和insert的类型差不多，这里只看最后存的值：

f8 01 00 00 00 01 00 00 00 01 00 00 00

即存储的是delete的行值。

update类似，就不再使用案例讲解，感兴趣的可以按照格式解析。