For details about the event types in MySQL binlog, mysqlbinlog

For details about the event types in MySQL binlog, mysqlbinlog

All operations recorded by MySQL binlog actually have corresponding event types. For example, DML operations in STATEMENT format correspond to QUERY_EVENT types, while DML operations in ROW format correspond to ROWS_EVENT types.

First, let's look at the event types defined in the source code.

Source code location: 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 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 longersql_ex, allowing multibyte TERMINATED BY etc; both types share thesame 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 aretherefore 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 master's online status to slave*/HEARTBEAT_LOG_EVENT= 27,/**In some situations, it is necessary to send over ignorabledata to the slave: data that a slave can handle in case thereis 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 */};

In fact, there are quite a few things. Let's take a few key points.

QUERY_EVENT

QUERY_EVENT records transaction operations in the form of text.

QUERY_EVENT events are generally used in the following scenarios:

1. The BEGIN operation executed when the transaction starts.

2. DML operations in STATEMENT format

3. DDL operations in ROW format

For example:

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 is introduced in binlog version 4 to replace the START_EVENT_V3 event in previous versions. It is the first event in the binlog file, and this event only appears once in binlog. MySQL parses other events according to the definition of FORMAT_DESCRIPTION_EVENT.

It usually specifies the MySQL Server version, the binlog version, and the time when the binlog file was created.

For example:

# 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

For binlogs in ROW format, all DML statements are recorded in ROWS_EVENT.

ROWS_EVENT can be divided into three types: WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, and DELETE_ROWS_EVENT, which correspond to insert, update, and delete operations respectively.

For insert operations, WRITE_ROWS_EVENT contains the data to be inserted.

For the update operation, UPDATE_ROWS_EVENT not only contains the modified data, but also the pre-modification value.

For the delete operation, you only need to specify the primary key to be deleted (all columns are given without a primary key)

For QUERY_EVENT events, DML operations are recorded in text format. The ROWS_EVENT event is not in the text format, so when you view the binlog Based on the ROW format through mysqlbinlog, You need to specify-vv -- base64-output = decode-rows.

For example:

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

When a transaction is committed, an XID_EVENT event will be added at the end of either the STATEMENT or the ROW format binlog to indicate the end of the transaction. This event records the ID of the transaction. when MySQL recovers from a crash, it determines whether to submit a transaction in the storage engine in the prepared state based on the transaction commit status in the binlog.

ROTATE_EVENT

When the binlog file size reaches the value of max_binlog_size or the flush logs command is executed, the binlog will be switched. In this case, a ROTATE_EVENT event will be added to the current binlog, specifies the name and location of the next log.

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

After GTID mode is enabled, MySQL actually assigns a GTID to each transaction.

For example:

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

Previus_gtids_log_event

After GTID mode is enabled, each binlog starts with a PREVIOUS_GTIDS_LOG_EVENT event. Its value is previousus_gtids_log_event + GTID_LOG_EVENT of the previous binlog. In fact, when the database is restarted, you need to re-fill the value of gtid_executed, which is the latest binlog previus_gtids_log_event + GTID_LOG_EVENT.

For example:

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)

The Previous_gtids in the mysql-bin.000033 log is cad449f2-5d4f-11e6-b353-000c29c64704: 1, GTID is cad449f2-5d4f-11e6-b353-000c29c64704: 2 and cad449f2-5d4f-11e6-b353-000c29c64704: 3, so that in the next log, that is, the Previous_gtids in the mysql-bin.000034 is 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

When the MySQL database is stopped, a STOP_EVENT event is added at the end of the current binlog to indicate that the database is stopped.

For example:

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

The above is a detailed description of the event types in MySQL binlog. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!