Parsing MySQL Binlog--(3) query_event

First, Introduction

The Query_event event records information in the form of text. When the Binlog format is statement, the executed statements are stored in Query_event, as follows:

Mysql> Show Binlog events in "mysql-bin.000002"; +------------------+-----+-------------+-----------+-------------+--------------------------------------------- +  | Log_name | Pos | Event_type | server_id | End_log_pos |  Info | +------------------+-----+-------------+-----------+-------------+--------------------------------------------- +  |   mysql-bin.000002 | 4 |        Format_desc |         11 | 120 |  Server ver:5.6.26-debug-log, Binlog ver:4 | | mysql-bin.000002 | 120 |        Query |         11 | 191 |  BEGIN | | mysql-bin.000002 | 191 |        Table_map |         11 | 236 |  Table_id:70 (YZS.T1) | | mysql-bin.000002 | 236 |        Write_rows |         11 | 280 |  Table_id:70 Flags:stmt_end_f | | mysql-bin.000002 | 280 |        Xid |         11 | 311 |  COMMIT/* xid=9 */| +------------------+-----+-------------+-----------+-------------+---------------------------------------------+ 5 rows in Set (0.00 sec)   

Events of type query_event are usually used in a few cases:

1. At the beginning of a transaction, there is a query_event type of begin in Binlog.

2. In the statement format, the SQL statements that are executed are saved in the event.

3. For row format Binlog, all DDL operations are recorded as text in the event.

Second, the format of this event

Third, case explanation

Combined with Hexdump data and Mysqlbinlog parsed log analysis:

# at 120  #180310 21:53:38 server id 11  end_log_pos 191 CRC32 0xba8c8530     Query   thread_id=1 exec_time=0 error_code=0  SET TIMESTAMP=1520747618/*!*/;  SET @@session.pseudo_thread_id=1/*!*/;  SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;  SET @@session.sql_mode=1073741824/*!*/;  SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;  /*!\C utf8 *//*!*/;  SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;  SET @@session.lc_time_names=0/*!*/;  SET @@session.collation_database=DEFAULT/*!*/;  BEGIN  /*!*/;  # at 191  

Hexdump's log:

-------Public Event Header--------

1, timestamp:4 bytes, C4 A4 5a

2, even type:1 bytes, 02: The Type is query_event= 2,

3, Server-id:4 bytes, 0b 00 00 00, i.e. Server-id 11

4, event Size:4 Bytes, 47 00 00 00, size is 71

5, Next-log pos:4 bytes, BF 00 00 00. That is 191

6, Flag:2 bytes, 08 00

--------Private Event Header-----

7, Slave_proxy_id:4 bytes, 01 00 00 00, that is, the thread ID is 1. threads ID that stores different connections or sessions

8, execution Time:4 Bytes, 00 00 00 00, query from start execution to record to Binlog time spent, unit s

9, schema length:1 bytes, 03,schema character length. Yzs

10. Error Code:2 Bytes, 00 00, error code

11, Status-var length:2 bytes, 1a 00,status-var length, 26

---------Event Body--------

12, status var:26 bytes, in the form of KV to save some of the context information set by the setting command.

13, Schema:3 Byte, Yzs, currently selected databases

14, 00, default

15, Query:query text format, which may be stored in the begin, commit string or native SQL, etc.

Parsing MySQL Binlog--(3) query_event