MySQL binlog中事件类型详解.doc

MySQL binlog中的事件类型详解MySQL binlog记录的所有操作实际上都有对应的事件类型的，譬如STATEMENT格式中的DML操作对应的是QUERY_EVENT类型，ROW格式下的DML操作对应的是ROWS_EVENT类型。首先，看看源码中定义的事件类型源码位置：mysql-5.7.14/libbinlogevents/include/binlog_event.h?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778 enum Log_event_type /* Every time you update this enum (when you add a type), you have tofix 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 timeto ensure masters 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 notrecognized. */ 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_EVENTQUERY_EVENT以文本的形式来记录事务的操作。QUERY_EVENT类型的事件通常在以下几种情况下使用：1. 事务开始时，执行的BEGIN操作。2. STATEMENT格式中的DML操作3. ROW格式中的DDL操作譬如：?123456789101112 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_EVENTFORMAT_DESCRIPTION_EVENT是binlog version 4中为了取代之前版本中的START_EVENT_V3事件而引入的。它是binlog文件中的第一个事件，而且，该事件只会在binlog中出现一次。MySQL根据FORMAT_DESCRIPTION_EVENT的定义来解析其它事件。它通常指定了MySQL Server的版本，binlog的版本，该binlog文件的创建时间。譬如：?123456789 # 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。譬如：?12345678910111213141516171819 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事件，用于指定下一个日志的名称与位置。?12345678 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) ?12 # 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譬如：?12345678910111213141516 # 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 d_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/*!*/; ?1234567891011121314151617 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。譬如：?1234567891011121314151617 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) ?12345678 mysql show binlog events in mysql-bin.000034; +-+-+-+-+-+-+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-+-+-+-+-+-+ | mysql-bin.000034 | 4 | Format_dewww.sm136.c