解析MySQL binlog --（1）大致结构及event type

最新推荐文章于 2022-12-06 07:00:03 发布

weixin_33998125

最新推荐文章于 2022-12-06 07:00:03 发布

阅读量1k

点赞数 1

CC 4.0 BY-SA版权

文章标签：数据库 c#

原文链接：http://blog.51cto.com/yanzongshuai/2085203

本文介绍了MySQL中Binlog的基本概念及其工作原理。通过示例展示了如何使用show binlog events命令查看不同类型的事件，如格式描述事件、查询事件等，并解释了事件头部包含的信息，如时间戳、事件类型、服务器ID等。

1、简介

binlog以事件的形式记录数据库变更情况。通过执行show binlog events in "binlog file"命令可以查看事件

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 */                          |  
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+  
5 rows in set (0.00 sec)

mysql> show binlog events in "mysql-bin.000001";  
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+  
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                        |  
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+  
| mysql-bin.000001 |   4 | Format_desc |        11 |         120 | Server ver: 5.6.26-debug-log, Binlog ver: 4 |  
| mysql-bin.000001 | 120 | Query       |        11 |         197 | BEGIN                                       |  
| mysql-bin.000001 | 197 | Query       |        11 |         294 | use `yzs`; insert into t1 select 2,2        |  
| mysql-bin.000001 | 294 | Xid         |        11 |         325 | COMMIT /* xid=9 */                          |  
| mysql-bin.000001 | 325 | Stop        |        11 |         348 |                                             |  
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+  
5 rows in set (0.00 sec)

2、binlog事件格式及类型
解析MySQL binlog --（1）大致结构及event type
分为2部分，事件头和事件体。事件头包括：