mysql--Binlog Event Explanation

Mysqlbinlog  -VVV log.000001

# at154#160517  the: the: -Server ID -End_log_pos219CRC320x7439a004GTID last_committed=0Sequence_number=1SET @ @SESSION. Gtid_next='191f7a9f-ffa2-11e5-a825-00163e00242a:1'/*!*/; # at219#160517  the: the: -Server ID -End_log_pos291CRC320X0839A8DFQuery thread_id= AExec_time=0Error_code=0SET TIMESTAMP=1463468953/*!*/; SET @ @session. pseudo_thread_id= A/*!*/; SET @ @session. foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. unique_checks=1, @ @session. autocommit=1/*!*/; SET @ @session. Sql_mode=1436549152/*!*/; SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;/*!\c UTF8*//*!*/; SET @ @session. character_set_client= -, @ @session. collation_connection= -, @ @session. collation_server=8/*!*/; SET @ @session. Lc_time_names=0/*!*/; SET @ @session. collation_database=default/*!*/; BEGIN/*!*/; # at291#160517  the: the: -Server ID -End_log_pos336CRC320x738c7c74Table_map: ' Test '. ' TT ' mapped to number113# at336#160517  the: the: -Server ID -End_log_pos376CRC320x1949e971Write_rows:table ID113Flags:stmt_end_fbinlog'Mcm6vxnkaaaalqaaafabaaaaaheaaaaaaaeabhrlc3qaanr0aaedaaf0fixzmcm6vx5kaaaakaaaahgbaaaaaheaaaaaaaeaagab//4kaaaaceljgq=='/*!*/;# # # INSERT into ' test '. ' TT ' # # # set### @1=Ten /*INT meta=0 nullable=1 is_null=0*/# at376#160517  the: the: -Server ID -End_log_pos407CRC320x63b03feeXid = theCOMMIT/*!*/;

| Log.000001|154| Gtid | -|219| SET @ @SESSION. gtid_next='191f7a9f-ffa2-11e5-a825-00163e00242a:1'|| Log.000001|219| Query | -|291| BEGIN | | Log.000001|291| Table_map | -|336| TABLE_ID:113(TEST.TT) | | Log.000001|336| Write_rows | -|376| TABLE_ID:113Flags:stmt_end_f | | Log.000001|376| Xid | -|407| COMMIT/*xid=1

mysqlbinlog--BASE64-OUTPUT=DECODE-ROWS-VVV log.000001


# at154#160517  the: the: -Server ID -End_log_pos219CRC320x7439a004GTID last_committed=0Sequence_number=1SET @ @SESSION. Gtid_next='191f7a9f-ffa2-11e5-a825-00163e00242a:1'/*!*/; # at219#160517  the: the: -Server ID -End_log_pos291CRC320X0839A8DFQuery thread_id= AExec_time=0Error_code=0SET TIMESTAMP=1463468953/*!*/; SET @ @session. pseudo_thread_id= A/*!*/; SET @ @session. foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. unique_checks=1, @ @session. autocommit=1/*!*/; SET @ @session. Sql_mode=1436549152/*!*/; SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;/*!\c UTF8*//*!*/; SET @ @session. character_set_client= -, @ @session. collation_connection= -, @ @session. collation_server=8/*!*/; SET @ @session. Lc_time_names=0/*!*/; SET @ @session. collation_database=default/*!*/; BEGIN/*!*/; # at291#160517  the: the: -Server ID -End_log_pos336CRC320x738c7c74Table_map: ' Test '. ' TT ' mapped to number113# at336#160517  the: the: -Server ID -End_log_pos376CRC320x1949e971Write_rows:table ID113flags:stmt_end_f### INSERT into ' test '. ' TT ' # # # set### @1=Ten /*INT meta=0 nullable=1 is_null=0*/# at376#160517  the: the: -Server ID -End_log_pos407CRC320x63b03feeXid = theCOMMIT/*!*/;

Binlog event: Consists of the < public event header >+[Private Event Header]+[event Body] three parts

           All Binlog events contain the public event header, and the other two sections are optional depending on the event type

Public Event Header:

Http://dev.mysql.com/doc/internals/en/event-structure.html


V4Eventstructure:+=====================================+|Event| Timestamp0:4||        Header +----------------------------+| | Type_code4:1||        +----------------------------+| | server_id5:4||        +----------------------------+| | Event_length9:4||        +----------------------------+| | Next_position -:4||        +----------------------------+| | Flags -:2||        +----------------------------+| | Extra_headers +: X- +|+=====================================+|Event|fixedPart X:y | |        Data +----------------------------+| | Variable part |+=====================================+Header Length=x bytesdata Length= (event_length-x) bytes


Timestamp: Contains the start time of the event
Event Type: Refers to the type of the event
SERER_ID: Server-id of the server that generated the event
Event_length:(includes the public event header, the private event header, the length of the event body three parts)

-------------------------------------------------------------------------

Binlog Event Type:

Format_description_event
mysql resolves other events according to its definition


Query_event:
Things begin the beginning of events
binlog_format= ' STATEMENT ', specifically executed statements are saved in the Query_event event
For row format Binlog, all DDL is recorded in text format in Query_event


Row_event divided into three categories: (binlog_format= ' ROW ')
1.write_rows_event->insert
Contains the data to be inserted
2.update_rows_event->update
Contains the value before the row was modified, and also contains the modified value
3.delete_rows_event->delete
Contains the primary key value/line number of the row to be deleted


Table_map_event
There is a table_map_event before each rows_event event to describe the internal ID and structure definition

Xid_event:
MySQL crashes recovery time, deciding whether to commit to prepared in the storage engine based on the transaction's commit in Binlog

Binlog_checkpoint_event
When MySQL performs a crash, the Binlog files are determined to be free of scanning based on the binlog_checkpoint_event read.

Rotate_event
When the Binlog file size reaches the Max_binlog_size parameter setting value or the flush Logs command, the Binlog switches and the Rotate_event event is added at the end of the file

Stop_event
When MySQL stops, add stop_event at the end of the file

mysql--Binlog Event Explanation