Shulou(Shulou.com)06/01 Report--

The following is mainly about parsing MySQL binlog-- taking QUERY_EVENT events as an example, hoping to parse MySQL binlog-- taking QUERY_EVENT events as examples can bring you practical use, which is the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

I. introduction

The QUERY_EVENT event records the information as text. When statement is in binlog format, 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 | Query | 11 | 191 | BEGIN | | mysql-bin.000002 | 191 | Table_map | 11 | 236 | table_id: 70 (yzs.t1) | | mysql-bin.000002 | 236 | Write_rows | 11 | | 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 typically used in the following situations:

1. At the beginning of the transaction, there is a BEGIN of type QUERY_EVENT in the binlog.

2. In statement format, the executed SQL statement is saved in this event.

3. For binlog in ROW format, all DDL operations are recorded in the event as text.

Second, the format of this event

III. Case explanation

Combine the data from hexdump and the log parsed by mysqlbinlog for analysis:

# at 12 # 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 pedigree; SET @ @ session.pseudothread readreadable license 1pxxx; SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.autocycle @ @ session.session .sqltobacco mode1073741824 SET @ @ session.auto_increment_increment=1, @ @ session.autoincrement increment offsets off the set of strings; / *!\ C utf8 * / / *! /; SET @ session.session setsetclientholders 331 copies of sessions. Collationalization connections 3 copies of sessions. Collationalization serverals 33 cannibales; SET @ @ session.lcbooks timestamps namespace zero-coordinate strings; SET @ @ session.collationalization databaseFAULTGULGULGULGRAPHY; BEGIN / *! * /; # 191 session

Hexdump's log:

-Public event head-

1. Timestamp:4 bytes, 62 c4 a4 5a

2. Even type:1 bytes, 02: this type is QUERY_EVENT= 2

3. Server-id:4 bytes, 0b 00 00, that is, 11 at server-id

4. Event size:4 bytes, 47000000, size 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 000 00, that is, thread ID is 1. 0. Thread ID that stores different connections or sessions

8. Execution time:4 bytes, 00 000 00 00, the time it takes from the beginning of the query to the record to the binlog (in s)

9. Schema length:1 byte, 03m schema character length. Yzs

10. Error code:2 byte, 00 00, error code

11. Status-var length:2 bytes. Length of 1a 00th statusMurvar, which is 26

-event body-

12. Status var:26 bytes, saved in the form of KV pairs, some context information set by the SET command.

13. Schema:3 bytes, yzs, the currently selected databases

14,00, default

15. Query:query text format, which may store BEGIN, COMMIT strings, native SQL, etc.

For the above about parsing MySQL binlog-take the QUERY_EVENT event as an example, do you find it very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.