MySQLbinlog中的事件类型详解
MySQLbinlog记录的所有操作实际上都有对应的事件类型的，譬如STATEMENT格式中的DML操作对应的是QUERY_EVENT类型，ROW格式下的DML操作对应的是ROWS_EVENT类型。
首先，看看源码中定义的事件类型
源码位置：mysql-5.7.14/libbinlogevents/include/binlog_event.h
?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778enumLog_event_type{/Everytimeyouupdatethisenum(whenyouaddatype),youhavetofixFormat_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_EVENTexceptthatithasalongersql_ex,allowingmultibyteTERMINATEDBYetc;bothtypessharethesameclass(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.15andarethereforeobsolete./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,/Heartbeateventtobesendbymasteratitsidletimetoensuremaster''sonlinestatustoslave/HEARTBEAT_LOG_EVENT=27,/Insomesituations,itisnecessarytosendoverignorabledatatotheslave:datathataslavecanhandleincasethereiscodeforhandlingit,butwhichcanbeignoredifitisnotrecognized./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操作
譬如：
?123456789101112mysql>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文件的创建时间。
譬如：
?123456789#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。
譬如：
?12345678910111213141516171819mysql>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事件，用于指定下一个日志的名称和位置。
?12345678mysql>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)
?12#at120#16081712:34:26serverid1end_log_pos167CRC320xd965567cRotatetomysql-bin.000029pos:4
GTID_LOG_EVENT
在启用GTID模式后，MySQL实际上为每个事务都分配了个GTID
譬如：
?12345678910111213141516#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=0SETTIMESTAMP=1471469852/!/;BEGIN/!/;#at571#1608185:37:32serverid1end_log_pos674CRC320xa35beb37Querythreawww.shanxiwang.netd_id=2exec_time=0error_code=0SETTIMESTAMP=1471469852/!/;insertintotest.t1values(2,''b'')/!/;#at674#1608185:37:32serverid1end_log_pos705CRC320x1905d8c6Xid=12COMMIT/!/;
?1234567891011121314151617mysql>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。
譬如：
?1234567891011121314151617mysql>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)
?12345678mysql>showbinlogeventsin''mysql-bin.000034'';+------------------+-----+----------------+-----------+-------------+------------------------------------------+|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|+------------------+-----+----------------+-----------+-------------+------------------------------------------+|mysql-bin.000034|4|Format_dewww.sm136.comsc|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。
?123#at120#1608185:39:38serverid1end_log_pos191CRC320x4e84f3b5Previous-GTIDs#cad449f2-5d4f-11e6-b353-000c29c64704:1-3
STOP_EVENT
当MySQL数据库停止时，会在当前的binlog末尾添加一个STOP_EVENT事件表示数据库停止。
譬如：
?12345678mysql>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)
?12#at120#1608185:18:04serverid1end_log_pos143CRC320xf20ddc85Stop
|
|
