MySQL binlog中的事件类型详解

2024-03-14 02:54:03 33

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

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

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

enumLog_event_type
{
/**
Everytimeyouupdatethisenum(whenyouaddatype),youhaveto
fixFormat_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_EVENTislikeLOAD_EVENTexceptthatithasalonger
sql_ex,allowingmultibyteTERMINATEDBYetc;bothtypessharethe
sameclass(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,
/**
ThePRE_GAeventnumberswereusedfor5.1.0to5.1.15andare
thereforeobsolete.
*/
PRE_GA_WRITE_ROWS_EVENT=20,
PRE_GA_UPDATE_ROWS_EVENT=21,
PRE_GA_DELETE_ROWS_EVENT=22,
/**
TheV1eventnumbersareusedfrom5.1.16untilmysql-trunk-xx
*/
WRITE_ROWS_EVENT_V1=23,
UPDATE_ROWS_EVENT_V1=24,
DELETE_ROWS_EVENT_V1=25,
/**
Somethingoutoftheordinaryhappenedonthemaster
*/
INCIDENT_EVENT=26,
/**
Heartbeateventtobesendbymasteratitsidletime
toensuremaster'sonlinestatustoslave
*/
HEARTBEAT_LOG_EVENT=27,
/**
Insomesituations,itisnecessarytosendoverignorable
datatotheslave:datathataslavecanhandleincasethere
iscodeforhandlingit,butwhichcanbeignoredifitisnot
recognized.
*/
IGNORABLE_LOG_EVENT=28,
ROWS_QUERY_LOG_EVENT=29,
/**Version2oftheRowevents*/
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,
/*PreparedXAtransactionterminaleventsimilartoXid*/
XA_PREPARE_LOG_EVENT=38,
/**
Addneweventshere-rightabovethiscomment!
Existingevents(exceptENUM_END_EVENT)shouldneverchangetheirnumbers
*/
ENUM_END_EVENT/*endmarker*/
};

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

QUERY_EVENT

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

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

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

2.STATEMENT格式中的DML操作

3.ROW格式中的DDL操作

譬如：

mysql>showbinlogeventsin'mysql-bin.000021';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
|mysql-bin.000021|4|Format_desc|1|120|Serverver:5.6.31-log,Binlogver:4|
|mysql-bin.000021|120|Query|1|195|BEGIN|
|mysql-bin.000021|195|Query|1|298|insertintotest.t1values(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`;insertintotest.t1values(2,'b')|
|mysql-bin.000021|515|Xid|1|546|COMMIT/*xid=33*/|
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+

FORMAT_DESCRIPTION_EVENT

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

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

譬如：

#at4
#16081711:00:10serverid1end_log_pos120CRC320x03010da1Start:binlogv4,serverv5.6.31-logcreated16081711:00:10
#Warning:thisbinlogiseitherinuseorwasnotclosedproperly.
mysql>showbinlogeventsin'mysql-bin.000021';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
|mysql-bin.000021|4|Format_desc|1|120|Serverver:5.6.31-log,Binlogver: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>showbinlogeventsin'mysql-bin.000027';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
|mysql-bin.000027|4|Format_desc|1|120|Serverver:5.6.31-log,Binlogver: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:80flags: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:80flags: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:80flags:STMT_END_F|
|mysql-bin.000027|664|Xid|1|695|COMMIT/*xid=200*/|
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
13rowsinset(0.00sec)

XID_EVENT

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

ROTATE_EVENT

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

mysql>showbinlogeventsin'mysql-bin.000028';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
|mysql-bin.000028|4|Format_desc|1|120|Serverver:5.6.31-log,Binlogver:4|
|mysql-bin.000028|120|Rotate|1|167|mysql-bin.000029;pos=4|
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2rowsinset(0.00sec)

#at120
#16081712:34:26serverid1end_log_pos167CRC320xd965567cRotatetomysql-bin.000029pos:4

GTID_LOG_EVENT

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

譬如：

#at448
#1608185:37:32serverid1end_log_pos496CRC320xaeb24aacGTID[commit=yes]
SET@@SESSION.GTID_NEXT='cad449f2-5d4f-11e6-b353-000c29c64704:3'/*!*/;
#at496
#1608185:37:32serverid1end_log_pos571CRC320x042ca092Querythread_id=2exec_time=0error_code=0
SETTIMESTAMP=1471469852/*!*/;
BEGIN
/*!*/;
#at571
#1608185:37:32serverid1end_log_pos674CRC320xa35beb37Querythread_id=2exec_time=0error_code=0
SETTIMESTAMP=1471469852/*!*/;
insertintotest.t1values(2,'b')
/*!*/;
#at674
#1608185:37:32serverid1end_log_pos705CRC320x1905d8c6Xid=12
COMMIT/*!*/;

mysql>showbinlogeventsin'mysql-bin.000033';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
|mysql-bin.000033|4|Format_desc|1|120|Serverver:5.6.31-log,Binlogver: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|insertintotest.t1values(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|insertintotest.t1values(2,'b')|
|mysql-bin.000033|674|Xid|1|705|COMMIT/*xid=12*/|
|mysql-bin.000033|705|Rotate|1|752|mysql-bin.000034;pos=4|
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
11rowsinset(0.00sec)

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>showbinlogeventsin'mysql-bin.000033';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
|mysql-bin.000033|4|Format_desc|1|120|Serverver:5.6.31-log,Binlogver: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|insertintotest.t1values(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|insertintotest.t1values(2,'b')|
|mysql-bin.000033|674|Xid|1|705|COMMIT/*xid=12*/|
|mysql-bin.000033|705|Rotate|1|752|mysql-bin.000034;pos=4|
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
11rowsinset(0.00sec)

mysql>showbinlogeventsin'mysql-bin.000034';
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
|mysql-bin.000034|4|Format_desc|1|120|Serverver:5.6.31-log,Binlogver:4|
|mysql-bin.000034|120|Previous_gtids|1|191|cad449f2-5d4f-11e6-b353-000c29c64704:1-3|
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
2rowsinset(0.00sec)

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。

#at120
#1608185:39:38serverid1end_log_pos191CRC320x4e84f3b5Previous-GTIDs
#cad449f2-5d4f-11e6-b353-000c29c64704:1-3

STOP_EVENT

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

譬如：

mysql>showbinlogeventsin'mysql-bin.000030';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
|mysql-bin.000030|4|Format_desc|1|120|Serverver:5.6.31-log,Binlogver:4|
|mysql-bin.000030|120|Stop|1|143||
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2rowsinset(0.04sec)

#at120
#1608185:18:04serverid1end_log_pos143CRC320xf20ddc85Stop

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

MySQL binlog中的事件类型详解

热门推荐

随机推荐