The event type in the MySQL Binlog is detailed _mysql

All operations recorded by MySQL Binlog actually have corresponding event types, such as the DML operation in the statement format corresponds to the query_event type, and the DML operation in row format corresponds to the rows_event type.

First, look at the types of events defined in the source code

SOURCE Location: Mysql-5.7.14/libbinlogevents/include/binlog_event.h

Enum Log_event_type {/** Every time for the update this enum (where you add a type), and 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, SL  Ave_event= 7, create_file_event= 8, append_block_event= 9, exec_load_event=, delete_file_event=,/** NEW_LOAD_EVENT IS-like load_event except that it has a longer sql_ex, allowing multibyte terminated by etc; Both types share same class (load_event) * * new_load_event=, rand_event=, user_var_event=, format_description _event=, xid_event=, begin_load_query_event=, execute_load_query_event=, table_map_event =,/** the PRE_GA
Event numbers were used for 5.1.0 to 5.1.15 and are therefore. * * Pre_ga_write_rows_event = Pre_ga_update_rows_event =, Pre_ga_delete_rows_event =/** the V1 EVENT numbers ar E used from 5.1.16 until mysql-trunk-xx * * WRITE_ROWS_EVENT_V1 = UPDATE_ROWS_EVENT_V1 = DELETE_ROWS_EVENT_V1 =,/** something out of the ordinary on the master/happened nt=,/** Heartbeat event to is send by Master in its idle time to ensure Master's online status to slave */heartbeat_l Og_event=,/** in some situations, it are necessary to send over ignorable data to the slave:data that a slave can hand
Le in case there are code for handling it, but which can being ignored if it is not recognized. * * ignorable_log_event=, rows_query_log_event=,/** Version 2 of the Row events * * write_rows_event = Update_row S_event = Delete_rows_event = gtid_log_event=, anonymous_gtid_log_event=, previous_gtids_log_event=, TRAN saction_context_event=, view_change_event=, * Prepared XA transaction terminal EVENT similar to Xid * * xa_prepare_l
og_event=,/** Add new events here-right above this comment! Existing events (except enum_end_event) should never change their numbers * * enum_end_event/* END marker * *; 

Actually, it's quite a lot, so let's pick a few points to talk about.

Query_event

Query_event the operation of transactions as text.

Events of type query_event are usually used in the following situations:

1. Begin operation at the beginning of a transaction.

2. DML operations in the statement format

3. DDL operations in row format

Such as:

 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 was 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 the event will only appear once in Binlog. MySQL parses other events according to the format_description_event definition.

It typically specifies the version of MySQL server, the Binlog version, and the creation time of the Binlog file.

Such as:

# at 4
#160817 11:00:10 server ID 1 end_log_pos CRC32 0x03010da1 start:binlog v 4, Server v 5.6.31-log created 16 0817 11:00:10
# warning:this Binlog is either with 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 binlog in row format, all DML statements are recorded in Rows_event.

Rows_event is divided into three kinds: write_rows_event,update_rows_event,delete_rows_event, respectively corresponding to insert,update and DELETE operations.

For the insert operation, Write_rows_event contains the data to insert

For the UPDATE operation, update_rows_event not only contains the modified data, but also contains the value before the modification.

For a delete operation, you simply specify the primary key that was deleted (all columns are given without a primary key)

For query_event events, the DML operation is recorded as text. For rows_event events, it is not a text form, so you need to specify-VV--base64-output=decode-rows when viewing binlog based on row format through Mysqlbinlog.

Such as:

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 commits, a Xid_event event is added to the end of the transaction, either statement or row format Binlog. This event records the ID of the transaction and, in the case of a crash recovery of MySQL, determines whether to commit a transaction in the storage engine that has a status of prepared, based on the submission of the transaction in Binlog.

Rotate_event

When the size of the Binlog file reaches the Max_binlog_size value or executes the flush logs command, Binlog switches, which adds a Binlog event to the current rotate_event log. Use to specify 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

Gtid_log_event

When Gtid mode is enabled, MySQL actually assigns a gtid to each transaction

Such as:

 # at 448 #160818 5:37:32 server ID 1 end_log_pos 496 CRC32 0XAEB24AAC gtid [Commit=yes] S ET @ @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 Times
tamp=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=0 SET Times
tamp=1471469852/*!*/;
INSERT into TEST.T1 values (2, ' B ')/*!*/; 
# at 674 #160818 5:37:32 server ID 1 end_log_pos 705 CRC32 0x1905d8c6 = Xid; 

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 | +------------------+-----+----------------+-----------+-------------+------------------------------------------ -------------------------+ rows in Set (0.00 sec)

Previous_gtids_log_event

When Gtid mode is turned on, there is a previous_gtids_log_event event at the beginning of each binlog, and its value is the previous_gtids_log_event+gtid_log_event of the previous binlog, in fact, When the database restarts, you need to repopulate the gtid_executed value, which is the previous_gtids_log_event+gtid_log_event of the latest binlog.

Such as:

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 | +------------------+-----+----------------+-----------+-------------+------------------------------------------ -------------------------+ 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 mysql-bin.000034 is cad449f2-5d4f-11e6-b353-000c29c64704:1-3.

# at
#160818 5:39:38 server ID 1 end_log_pos 191 CRC32 0x4e84f3b5 previous-gtids

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.

Such as:

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
#160818 5:18:04 server ID 1 end_log_pos 143 CRC32 0xf20ddc85 Stop

The above is a small set up to introduce the MySQL Binlog in the event type of detailed, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!