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

Original: reprint please state the origin thank you!

Reference source:

1. Source code log_event.h log_event.cc pack.c

2 、 internals-en.epub

I. purpose

The main purpose of this series of documents is to illustrate

1. Why does row format take up more space than statement?

2. Why binlog in row format is more secure

3. INSERT/UPDATE/DELETE is the generated row binlog how to directly understand the binary format

4. What is the binlog generated by DDL?

5. How to generate row binlog from INSERT SELECT/CREATE TABLE

Second, use version and digital display

This series of articles mainly explains the binlog format of row format after MYSQL 5.6 and the event related to things, according to the official statement.

The format of binlog has gone through several stages

V1:mysql 3.23

V3:mysql 4.0.2 to 4.1

V4:mysql 5.0 +

The v2 version only exists briefly, of course we have to parse the v4 version of binlog

Because it depends on the binlog of 5.6 or above.

With regard to multi-byte digital display, Little-endian mode is generally used, which has nothing to do with the OS system, unless deliberately stated

About the Little-endian reference:

Http://blog.itpub.net/7728585/viewspace-2124159/

3. The magic number of binlog

There is not much parsing about the role of MYSQL BINLOG. What is stored in binlog is an entry called event.

They are stored in binary format, and the mysqlbinlog tool we use usually is for files in this binary format.

Parse it and get an intuitive output. Instead of mysqlbinlog, I will look directly at the binary files. Of course, I will compare them.

Output of MYSQLBINLOG and the process of binary parsing

Each binlog file has a 4-byte magic number, with a fixed value of

[root@testmy mysqld.1] # hexdump-Cv test.000005

You can see

Fe 62 69 6e .bin

IV. The overall framework of binlog event

An event includes

Event header

Event data

Among them, event data is divided into

Fixed data (posted header)

Variable data

Event header: all event is in a uniform and fixed format

Fixed data (posted header): each type of event is fixed

Variable data: you can change the actual value.

For more information about the types of event, please refer to the interception of the source code at the end.

5. Event to be discussed in this series of articles

Here we just need to discuss 5.6, 5.7 neutralizing row binlog format and innodb.

Several closely related event are as follows:

Query_log_event/QUERY_EVENT typecode=02

Format_description_log_event/FORMAT_DESCRIPTION_EVENT typecode=15

Xid_log_event/XID_EVENT typecode=16

Table_map_log_event/TABLE_MAP_EVENT typecode=19

Write_rows_log_event/WRITE_ROW_EVENT typecode=30

Update_rows_log_event/UPDATE_ROW_EVENT typecode=31

Delele_rows_log_event/DELETE_ROW_EVENT typecode=32

Because these statements are something that must be experienced, and Format_description_log_event is one of the most important

Illustrative event

VI. General header file (event header) parsing

Let's first explain the general 19 bytes.

Each event has a fixed header called event header:

Event header

Timestamp 0:4

Type_code 4:1

Server_id 5:4

Event_length 9:4

Next_position 13:4

Flags 17:2

Timestamp: fixed 4 byte display is the number of seconds since the new era (epoch time)

Type_code: fixed 1-byte event event encoding, in the source code is an enum type payload at the last source code

Server_id: fixed 4 bytes is the value from show variables like'% server_id';

Event_length: fixed 4-byte length of the entire event, including fixed and non-fixed length

Next_position: fix the start position of the next 4-byte event (2 ^ 32 is 4G)

Flags: fixed 2-byte event flags

The flags of LOG_EVENT_BINLOG_IN_USE_F 0x1 indicates whether the binlog is turned off correctly. This sign only appears in Format_description_log_event.

Whether LOG_EVENT_THREAD_SPECIFIC_F 0x4 query is based on temporary table. If MYSQLBINLOG is based on temporary table, @ @ PSEUDO_THREAD_ID=xx must be set.

LOG_EVENT_SUPPRESS_USE_F 0x8 has something to do with binlog-do-db and replicated-do-db.

There are many others.

LOG_EVENT_ARTIFICIAL_F 0x20 LOG_EVENT_RELAY_LOG_F 0x40 LOG_EVENT_IGNORABLE_F 0x80 LOG_EVENT_NO_FILTER_F 0x100 .

You can refer to the detailed explanation in the log_event.h source terminal file.

7. Packed interger

Some numbers are displayed in this way in binlog, which will be mentioned in later parsing

Follow the instructions in the documentation and source code

If the first byte is 0250 and 0X0-0XFA, then this byte is the actual displayed numeric value

Source code:

If (length < (ulonglong) LL)

{

* packet= (uchar) length

Return packet+1

}

If the first byte is 252 and 0XFC, then the value of the next 2 bytes is 0XFB-0XFFFF

Source code:

If (length < ulonglong) LL (65536))

{

* packet++=252

Int2store (packet, (uint) length)

Return packet+2

}

If the first byte is 253 and 0XFD, then the value of the next 3 bytes is 0XFFFF-0XFFFFFF

Source code:

If (length < ulonglong) LL (16777216))

{

* packet++=253

Int3store (packet, (ulong) length)

Return packet+3

}

If the first byte is 254and 0XFE, then the value of the next 8 bytes is 0XFFFFFF-0XFFFFFFFFFFFFFFFF

* packet++=254

Int8store (packet,length)

You can refer to the source code API by yourself. The returned value of the function is the pointer of the next location.

Uchar * net_store_length (uchar * packet, ulonglong length)

Click (here) to collapse or open

Enum Log_event_type

{

/ * *

Every time you update this enum (when you add a type), you 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

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 longer

Sql_ex, allowing multibyte TERMINATED BY etc; both types share the

Same 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 are

Therefore 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 time

To ensure master's online status to slave

, /

HEARTBEAT_LOG_EVENT= 27

/ * *

In some situations, it is necessary to send over ignorable

Data to the slave: data that a slave can handle in case there

Is code for handling it, but which can be ignored if it is not

Recognized.

, /

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

Existing events (except ENUM_END_EVENT) should never change their numbers

, /

ENUM_END_EVENT / * end marker * /

}

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.