你的位置:首页 > 数据库

[数据库]MySQL binlog中的事件类型


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

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

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

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操作

譬如:

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文件的创建时间。

譬如:

# 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。

譬如:

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事件,用于指定下一个日志的名称和位置。

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)

# 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

譬如:

# 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=0SET 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=0SET 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 = 12COMMIT/*!*/;

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。

譬如:

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)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。

# 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事件表示数据库停止。

譬如:

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)

# at 120#160818 5:18:04 server id 1 end_log_pos 143 CRC32 0xf20ddc85   Stop

 

参考

1. MariaDB原理与实现

2. MySQL官方文档