服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - MySQL binlog中的事件类型详解

MySQL binlog中的事件类型详解

2020-06-19 15:28iVictor Mysql

这篇文章主要介绍了MySQL binlog中的事件类型详解,介绍的非常详细，具有参考借鉴价值，需要的朋友可以参考下

MySQL binlog记录的所有操作实际上都有对应的事件类型的，譬如STATEMENT格式中的DML操作对应的是QUERY_EVENT类型，ROW格式下的DML操作对应的是ROWS_EVENT类型。

首先，看看源码中定义的事件类型

源码位置：mysql-5.7.14/libbinlogevents/include/binlog_event.h

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
enum Log_event_type
{
/**
Every time you update this enum (when you add a type), you have to
fix Format_description_event::Format_description_event().
*/
UNKNOWN_EVENT= 0,
START_EVENT_V3= 1,
QUERY_EVENT= 2,
STOP_EVENT= 3,
ROTATE_EVENT= 4,
INTVAR_EVENT= 5,
LOAD_EVENT= 6,
SLAVE_EVENT= 7,
CREATE_FILE_EVENT= 8,
APPEND_BLOCK_EVENT= 9,
EXEC_LOAD_EVENT= 10,
DELETE_FILE_EVENT= 11,
/**
NEW_LOAD_EVENT is like LOAD_EVENT except that it has a longer
sql_ex, allowing multibyte TERMINATED BY etc; both types share the
same class (Load_event)
*/
NEW_LOAD_EVENT= 12,
RAND_EVENT= 13,
USER_VAR_EVENT= 14,
FORMAT_DESCRIPTION_EVENT= 15,
XID_EVENT= 16,
BEGIN_LOAD_QUERY_EVENT= 17,
EXECUTE_LOAD_QUERY_EVENT= 18,
TABLE_MAP_EVENT = 19,
/**
The PRE_GA event numbers were used for 5.1.0 to 5.1.15 and are
therefore obsolete.
*/
PRE_GA_WRITE_ROWS_EVENT = 20,
PRE_GA_UPDATE_ROWS_EVENT = 21,
PRE_GA_DELETE_ROWS_EVENT = 22,
/**
The V1 event numbers are used from 5.1.16 until mysql-trunk-xx
*/
WRITE_ROWS_EVENT_V1 = 23,
UPDATE_ROWS_EVENT_V1 = 24,
DELETE_ROWS_EVENT_V1 = 25,
/**
Something out of the ordinary happened on the master
*/
INCIDENT_EVENT= 26,
/**
Heartbeat event to be send by master at its idle time
to ensure master's online status to slave
*/
HEARTBEAT_LOG_EVENT= 27,
/**
In some situations, it is necessary to send over ignorable
data to the slave: data that a slave can handle in case there
is code for handling it, but which can be ignored if it is not
recognized.
*/
IGNORABLE_LOG_EVENT= 28,
ROWS_QUERY_LOG_EVENT= 29,
/** Version 2 of the Row events */
WRITE_ROWS_EVENT = 30,
UPDATE_ROWS_EVENT = 31,
DELETE_ROWS_EVENT = 32,
GTID_LOG_EVENT= 33,
ANONYMOUS_GTID_LOG_EVENT= 34,
PREVIOUS_GTIDS_LOG_EVENT= 35,
TRANSACTION_CONTEXT_EVENT= 36,
VIEW_CHANGE_EVENT= 37,
/* Prepared XA transaction terminal event similar to Xid */
XA_PREPARE_LOG_EVENT= 38,
/**
Add new events here - right above this comment!
Existing events (except ENUM_END_EVENT) should never change their numbers
*/
ENUM_END_EVENT /* end marker */
};

实际上还是蛮多的，下面就挑几个重点的说一下

QUERY_EVENT

QUERY_EVENT以文本的形式来记录事务的操作。

QUERY_EVENT类型的事件通常在以下几种情况下使用：

1. 事务开始时，执行的BEGIN操作。

2. STATEMENT格式中的DML操作

3. ROW格式中的DDL操作

譬如：

?
1
2
3
4
5
6
7
8
9
10
11
12
mysql> show binlog events in 'mysql-bin.000021';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000021 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000021 | 120 | Query | 1 | 195 | BEGIN |
| mysql-bin.000021 | 195 | Query | 1 | 298 | insert into test.t1 values(1,'a') |
| mysql-bin.000021 | 298 | Xid | 1 | 329 | COMMIT /* xid=25 */ |
| mysql-bin.000021 | 329 | Query | 1 | 408 | BEGIN |
| mysql-bin.000021 | 408 | Query | 1 | 515 | use `test`; insert into test.t1 values(2,'b') |
| mysql-bin.000021 | 515 | Xid | 1 | 546 | COMMIT /* xid=33 */ |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+

FORMAT_DESCRIPTION_EVENT

FORMAT_DESCRIPTION_EVENT是binlog version 4中为了取代之前版本中的START_EVENT_V3事件而引入的。它是binlog文件中的第一个事件，而且，该事件只会在binlog中出现一次。MySQL根据FORMAT_DESCRIPTION_EVENT的定义来解析其它事件。

它通常指定了MySQL Server的版本，binlog的版本，该binlog文件的创建时间。

譬如：

?
1
2
3
4
5
6
7
8
9
# at 4
#160817 11:00:10 server id 1 end_log_pos 120 CRC32 0x03010da1 Start: binlog v 4, server v 5.6.31-log created 160817 11:00:10
# Warning: this binlog is either in use or was not closed properly.
mysql> show binlog events in 'mysql-bin.000021';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000021 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.31-log, Binlog ver: 4 |
...

ROWS_EVENT

对于ROW格式的binlog，所有的DML语句都是记录在ROWS_EVENT中。

ROWS_EVENT分为三种：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT，分别对应insert，update和delete操作。

对于insert操作，WRITE_ROWS_EVENT包含了要插入的数据

对于update操作，UPDATE_ROWS_EVENT不仅包含了修改后的数据，还包含了修改前的值。

对于delete操作，仅仅需要指定删除的主键（在没有主键的情况下，会给定所有列）

对于QUERY_EVENT事件，是以文本形式记录DML操作的。而对于ROWS_EVENT事件，并不是文本形式，所以在通过mysqlbinlog查看基于ROW格式的binlog时，需要指定-vv --base64-output=decode-rows。

譬如：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show binlog events in 'mysql-bin.000027';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000027 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000027 | 120 | Query | 1 | 188 | BEGIN |
| mysql-bin.000027 | 188 | Table_map | 1 | 236 | table_id: 80 (test.t1) |
| mysql-bin.000027 | 236 | Write_rows | 1 | 278 | table_id: 80 flags: STMT_END_F |
| mysql-bin.000027 | 278 | Xid | 1 | 309 | COMMIT /* xid=198 */ |
| mysql-bin.000027 | 309 | Query | 1 | 377 | BEGIN |
| mysql-bin.000027 | 377 | Table_map | 1 | 425 | table_id: 80 (test.t1) |
| mysql-bin.000027 | 425 | Update_rows | 1 | 475 | table_id: 80 flags: STMT_END_F |
| mysql-bin.000027 | 475 | Xid | 1 | 506 | COMMIT /* xid=199 */ |
| mysql-bin.000027 | 506 | Query | 1 | 574 | BEGIN |
| mysql-bin.000027 | 574 | Table_map | 1 | 622 | table_id: 80 (test.t1) |
| mysql-bin.000027 | 622 | Delete_rows | 1 | 664 | table_id: 80 flags: STMT_END_F |
| mysql-bin.000027 | 664 | Xid | 1 | 695 | COMMIT /* xid=200 */ |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
13 rows in set (0.00 sec)

XID_EVENT

在事务提交时，不管是STATEMENT还是ROW格式的binlog，都会在末尾添加一个XID_EVENT事件代表事务的结束。该事件记录了该事务的ID，在MySQL进行崩溃恢复时，根据事务在binlog中的提交情况来决定是否提交存储引擎中状态为prepared的事务。

ROTATE_EVENT

当binlog文件的大小达到max_binlog_size的值或者执行flush logs命令时，binlog会发生切换，这个时候会在当前的binlog日志添加一个ROTATE_EVENT事件，用于指定下一个日志的名称和位置。

?
1
2
3
4
5
6
7
8
mysql> show binlog events in 'mysql-bin.000028';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000028 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000028 | 120 | Rotate | 1 | 167 | mysql-bin.000029;pos=4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
?
1
2
# at 120
#160817 12:34:26 server id 1 end_log_pos 167 CRC32 0xd965567c Rotate to mysql-bin.000029 pos: 4

GTID_LOG_EVENT

在启用GTID模式后，MySQL实际上为每个事务都分配了个GTID

譬如：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# at 448
#160818 5:37:32 server id 1 end_log_pos 496 CRC32 0xaeb24aac GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'cad449f2-5d4f-11e6-b353-000c29c64704:3'/*!*/;
# at 496
#160818 5:37:32 server id 1 end_log_pos 571 CRC32 0x042ca092 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1471469852/*!*/;
BEGIN
/*!*/;
# at 571
#160818 5:37:32 server id 1 end_log_pos 674 CRC32 0xa35beb37 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1471469852/*!*/;
insert into test.t1 values(2,'b')
/*!*/;
# at 674
#160818 5:37:32 server id 1 end_log_pos 705 CRC32 0x1905d8c6 Xid = 12
COMMIT/*!*/;
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> show binlog events in 'mysql-bin.000033';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000033 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000033 | 120 | Previous_gtids | 1 | 191 | cad449f2-5d4f-11e6-b353-000c29c64704:1 |
| mysql-bin.000033 | 191 | Gtid | 1 | 239 | SET @@SESSION.GTID_NEXT= 'cad449f2-5d4f-11e6-b353-000c29c64704:2' |
| mysql-bin.000033 | 239 | Query | 1 | 314 | BEGIN |
| mysql-bin.000033 | 314 | Query | 1 | 417 | insert into test.t1 values(1,'a') |
| mysql-bin.000033 | 417 | Xid | 1 | 448 | COMMIT /* xid=11 */ |
| mysql-bin.000033 | 448 | Gtid | 1 | 496 | SET @@SESSION.GTID_NEXT= 'cad449f2-5d4f-11e6-b353-000c29c64704:3' |
| mysql-bin.000033 | 496 | Query | 1 | 571 | BEGIN |
| mysql-bin.000033 | 571 | Query | 1 | 674 | insert into test.t1 values(2,'b') |
| mysql-bin.000033 | 674 | Xid | 1 | 705 | COMMIT /* xid=12 */ |
| mysql-bin.000033 | 705 | Rotate | 1 | 752 | mysql-bin.000034;pos=4 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
11 rows in set (0.00 sec)

PREVIOUS_GTIDS_LOG_EVENT

开启GTID模式后，每个binlog开头都会有一个PREVIOUS_GTIDS_LOG_EVENT事件，它的值是上一个binlog的PREVIOUS_GTIDS_LOG_EVENT+GTID_LOG_EVENT，实际上，在数据库重启的时候，需要重新填充gtid_executed的值，该值即是最新一个binlog的PREVIOUS_GTIDS_LOG_EVENT+GTID_LOG_EVENT。

譬如：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> show binlog events in 'mysql-bin.000033';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000033 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000033 | 120 | Previous_gtids | 1 | 191 | cad449f2-5d4f-11e6-b353-000c29c64704:1 |
| mysql-bin.000033 | 191 | Gtid | 1 | 239 | SET @@SESSION.GTID_NEXT= 'cad449f2-5d4f-11e6-b353-000c29c64704:2' |
| mysql-bin.000033 | 239 | Query | 1 | 314 | BEGIN |
| mysql-bin.000033 | 314 | Query | 1 | 417 | insert into test.t1 values(1,'a') |
| mysql-bin.000033 | 417 | Xid | 1 | 448 | COMMIT /* xid=11 */ |
| mysql-bin.000033 | 448 | Gtid | 1 | 496 | SET @@SESSION.GTID_NEXT= 'cad449f2-5d4f-11e6-b353-000c29c64704:3' |
| mysql-bin.000033 | 496 | Query | 1 | 571 | BEGIN |
| mysql-bin.000033 | 571 | Query | 1 | 674 | insert into test.t1 values(2,'b') |
| mysql-bin.000033 | 674 | Xid | 1 | 705 | COMMIT /* xid=12 */ |
| mysql-bin.000033 | 705 | Rotate | 1 | 752 | mysql-bin.000034;pos=4 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
11 rows in set (0.00 sec)
?
1
2
3
4
5
6
7
8
mysql> show binlog events in 'mysql-bin.000034';
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
| mysql-bin.000034 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000034 | 120 | Previous_gtids | 1 | 191 | cad449f2-5d4f-11e6-b353-000c29c64704:1-3 |
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
2 rows in set (0.00 sec)

mysql-bin.000033日志中的Previous_gtids是cad449f2-5d4f-11e6-b353-000c29c64704:1，GTID是cad449f2-5d4f-11e6-b353-000c29c64704:2和cad449f2-5d4f-11e6-b353-000c29c64704:3，这样，在下一个日志，即mysql-bin.000034中的Previous_gtids是cad449f2-5d4f-11e6-b353-000c29c64704:1-3。

?
1
2
3
# at 120
#160818 5:39:38 server id 1 end_log_pos 191 CRC32 0x4e84f3b5 Previous-GTIDs
# cad449f2-5d4f-11e6-b353-000c29c64704:1-3

STOP_EVENT

当MySQL数据库停止时，会在当前的binlog末尾添加一个STOP_EVENT事件表示数据库停止。

譬如：

?
1
2
3
4
5
6
7
8
mysql> show binlog events in 'mysql-bin.000030';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000030 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000030 | 120 | Stop | 1 | 143 | |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.04 sec)
?
1
2
# at 120
#160818 5:18:04 server id 1 end_log_pos 143 CRC32 0xf20ddc85 Stop

以上所述是小编给大家介绍的MySQL binlog中的事件类型详解，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持！

原文链接：http://www.cnblogs.com/ivictor/archive/2016/08/18/5780617.html

  • mysql
  • binlog
  • 事件类型

    • 延伸 · 阅读

    精彩推荐
    • MysqlMySQL锁的知识点总结

      MySQL锁的知识点总结

      在本篇文章里小编给大家整理了关于MySQL锁的知识点总结以及实例内容，需要的朋友们学习下。...

      别人放弃我坚持吖4362020-12-14
    • MysqlMySQL 数据备份与还原的示例代码

      MySQL 数据备份与还原的示例代码

      这篇文章主要介绍了MySQL 数据备份与还原的相关知识,本文通过示例代码给大家介绍的非常详细，具有一定的参考借鉴价值，需要的朋友可以参考下...

      逆心2962019-06-23
    • Mysqlmysql 不能插入中文问题

      mysql 不能插入中文问题

      当向mysql5.5插入中文时，会出现类似错误 ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column ...

      MYSQL教程网5722019-11-25
    • MysqlMySQL数据库varchar的限制规则说明

      MySQL数据库varchar的限制规则说明

      本文我们主要介绍了MySQL数据库中varchar的限制规则，并以一个实际的例子对限制规则进行了说明，希望能够对您有所帮助。 ...

      mysql技术网4192019-11-23
    • Mysql浅谈mysql 树形结构表设计与优化

      浅谈mysql 树形结构表设计与优化

      在诸多的管理类，办公类等系统中，树形结构展示随处可见，本文主要介绍了mysql 树形结构表设计与优化，具有一定的参考价值，感兴趣的小伙伴们可以参...

      小码农叔叔5242021-11-16
    • Mysql解决MySQl查询不区分大小写的方法讲解

      解决MySQl查询不区分大小写的方法讲解

      今天小编就为大家分享一篇关于解决MySQl查询不区分大小写的方法讲解，小编觉得内容挺不错的，现在分享给大家，具有很好的参考价值，需要的朋友一起...

      Veir_dev5592019-06-25
    • MysqlERROR: Error in Log_event::read_log_event()

      ERROR: Error in Log_event::read_log_event()

      ERROR: Error in Log_event::read_log_event(): read error, data_len: 438, event_type: 2 ...

      MYSQL教程网6402020-03-13
    • Mysql详解MySQL中的分组查询与连接查询语句

      详解MySQL中的分组查询与连接查询语句

      这篇文章主要介绍了MySQL中的分组查询与连接查询语句,同时还介绍了一些统计函数的用法,需要的朋友可以参考下 ...

      GALAXY_ZMY5432020-06-03
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1449 　 1053 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1862 　 1864 　 1146 　 2003 　 1175 　 1215 　 1615 　 1054 　 2006 　 1130 　 1366 　 10048 　 28000 　 1786 　 1135 　 1577 　 1665 　 1067 　 1093 　
    © 2019-2024 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图