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

I am also a vegetable chicken. I would like to ask everyone to point out where blog is wrong or not rigorous. I will correct it in time so as not to mislead the children.

Experimental environment:

CentOS7.3.1611 + MySQL Community Edition 5.7.19

Reference:

The official Wechat account push of rookie DBA.

Official documents:

Https://dev.mysql.com/doc/internals/en/binary-log-versions.html

Https://dev.mysql.com/doc/internals/en/row-based-binary-logging.html

Https://dev.mysql.com/doc/internals/en/event-classes-and-types.html

Https://dev.mysql.com/doc/internals/en/event-header-fields.html

Https://dev.mysql.com/doc/internals/en/event-meanings.html

Https://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html

3 online tools:

Http://tool.oschina.net/hexconvert/ online binary conversion

Http://tool.chinaz.com/Tools/unixtime.aspx Unix timestamp

Https://www.bejson.com/convert/ox2str/ hexadecimal transfer string

The binlog actually consists of different types of binlog event, and each binlog event also contains an event header part and an event data part (optional).

[note: there is a check bit of 4bytes at the end of each event, which is not mentioned in the official document, otherwise when analyzing the physical format of event, it will be found that the length of event does not match]

A common binlog physical file has the following components:

1, 4-byte magic number as the beginning of the binlog file

2. N different types of binlog event

3. Rotate event is used as the end of the binlog file (there is no rotate event in the binlog being used)

In addition, there is an index file that records which binlog files are currently available and which binlog files are currently in use. (the file name is similar to: mysql-bin.index)

The following table shows the general format of binlog event:

+ = +

| | event | timestamp 0: 4 |

| | header +-+ |

| | type_code 4: 1 | = FORMAT_DESCRIPTION_EVENT = 15 (binlog v4) |

| | +-+

| | server_id 5: 4 |

| | +-+

| | event_length 9: 4 | > = 91 |

| | +-+

| | next_position 13: 4 |

| | +-+

| | flags 17: 2 |

+ = +

| | event | binlog_version 19: 2 | = 4 |

| | data +-+ |

| | server_version 21: 50 |

| | +-+

| | create_timestamp 71: 4 |

| | +-+

| | header_length 75: 1 |

| | +-+

| | post-header 76: n | = array of n bytes, one byte per event |

| | lengths for all | type that the server knows about |

| | event types |

+ = +

The commonly used EVENT is as follows:

The first event of the FORMAT_DESCRIPTION_EVENT:binlog file, recording metadata information such as version number

QUERY_EVENT: stores the information of the statement class, records sql statements based on statement's binlog format, and records transaction begin tags in row mode

XID_EVENT: two-phase submission of xid records

TABLE_MAP_EVENT: record table source data in row mode, which provides rule reference for reading row records, which will be described in more detail later.

WRITE_ROWS_EVENT/DELETE_ROWS_EVENT/UPDATE_ROWS_EVENT: records of corresponding row data changes in row mode

GTID_LOG_EVENT: this is the way to record GTID transaction numbers, which is based on GTID synchronization after version 5.6.

ROTATE_EVENT: connect to the next binlog file

For more comprehensive Event types, see: https://dev.mysql.com/doc/internals/en/event-classes-and-types.html (all defined in the binlog_event.h of the source code, see the following code 5.7 and several more event types compared to 5.6)

The following is a complete binlog file I intercepted. The specific events is as follows:

At present, we generally use binlog in row format, while other binlog in mixed and statement format will not be noticed here.

For DML operations in row format, what is actually recorded in binlog is: TABLE_MAP_EVENT+ ROW_LOG_EVENT (ROW_LOG_EVENT can also be subdivided into WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, DELETE_ROWS_EVENT)

Why does a update need to be decomposed into two event in ROW mode: one Table_map and one Update_rows?

Let's imagine that if an update updates 10000 pieces of data, does the corresponding table structure information need to be recorded 10000 times? It is actually an operation on the same table, so here binlog only records a Table_map to record information about the table structure, while the subsequent Update_rows records the row information of the updated data. They communicate with each other through table_id. [table_id is not fixed, it is a variable and takes up table_definition_cache and table_open_cache space (so flush tables causes table_id growth)]

The following is a binlog with 1 record inserted by insert. You can see that it is composed of two event of table_map+ write_rows.

Table_map records the metadata information of the table, such as library name, table name, field type, and so on.

Add:

Several pieces of practical information about table_id:

Http://blog.itpub.net/22664653/viewspace-1158547/ [Yang Qilong]

Http://agapple.iteye.com/blog/1797061

Http://www.cnblogs.com/yuyue2014/p/3721172.html

Http://www.sohu.com/a/130698375_610509 [Song Libing]

Http://www.cnblogs.com/cenalulu/archive/2012/09/24/2699907.html [Lu Junyi]

Several other EVENT types:

Official document: https://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html

FORMAT_DESCRIPTION_EVENT

This is the most basic event, and every new binlog header comes with this event. Only one FORMAT_DESCRIPTION_EVENT can exist per binlog file.

Fixed data part:

2 bytes. The binary log format version. This is 4 in MySQL 5.0 and up.

50 bytes. The MySQL server's version (example: 5.0.14-debug-log), padded with 0x00 bytes on the right.

4 bytes. Timestamp in seconds when this event was created (this is the moment when the binary log was created). This value is redundant; the same value occurs in the timestamp header field.

1 byte. The header length. This length-19 gives the size of the extra headers field at the end of the header for other events.

Variable-sized. An array that indicates the post-header lengths for all event types. There is one byte per event type that the server knows about.

FORMAT_DESCRIPTION_EVENT instance:

Flush logs; produces a new binlog file, which is exported as follows:

Master [localhost] {root} ((none)) > show binlog events in 'mysql-bin.000002'

+-- +

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-- +

| | mysql-bin.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.37-log, Binlog ver: 4 |

+-- +

[root@test_mysql26 / root/sandboxes/rsandbox_5_6_37/master/data] # hexdump-C mysql-bin.000002

00000000 fe 62 69 6e f6 e3 fe 59 0f 01 000000 74 0000 | .bin.Y.t.. |

00000010 00 78 000000 01 00 04 00 35 2e 36 2e 33 37 2d | .x.5.6.37-|

00000020 6c 6f 67 000000 000000 000000 00 | log. |

00000030 000000 000000 000000 00000000 |. |

00000040 000000 000000 000000 13 |. |

00000050 38 0d 00 08 00 12 00 04 04 12 0000 5c 00 | 8.\. |

00000060 04 1a 08 000000 08 08 02 000000 0a 0a 0a |. |

00000070 19 19 00 01 08 4c 67 48 | .LGH |

00000078

Magic number (4bytes)

Fe 62 69 6e

Event header (19bytes)

F6 e3 fe 59 timestamp

0f type_code indicates that binlog adopts v4 version of

01 00 00 00 server_id

74 00 00 00 event_length 116bytes

78 00 00 00 next_position the next event starts at 120

01 00 flags

Event data:

04 00 binlog version, indicating the v4 version of the binlog format

35 2e 36 2e 33 37 2d 6c 6f 67 00 00 00 is 5.6.37-log

00 00 00 create_timestamp, in relative time

13 represents the length of event header, and the decimal representation is 19bytes.

38 0d 00 08 00 12 00 04 04 04 12 00 5c 00 04 1a 08 00 00 08 08 02 00 00 0a 0a 0a 19 00 01 36 event types

08 4c 67 48 4bytes check bit.

36 event types: https://dev.mysql.com/doc/internals/en/event-classes-and-types.html

STOP_EVENT:

This stop_event is generated when mysqld is normally closed, or when reset slave is executed from the library.

A Stop_log_event is written under these circumstances:

A master writes the event to the binary log when it shuts down

A slave writes the event to the relay log when it shuts down or when a RESET SLAVE statement is executed

STOP_EVENT instance:

/ etc/init.d/mysqld restart

Master [localhost] {root} ((none)) > show binlog events in 'mysql-bin.000002'

+-- +

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-- +

| | mysql-bin.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.37-log, Binlog ver: 4 |

| | mysql-bin.000002 | 120 | Stop | 1 | 143 |

+-- +

2 rows in set (0.00 sec)

[root@test_mysql26 / root/sandboxes/rsandbox_5_6_37/master/data] # hexdump-C mysql-bin.000002-s 120

00000078 f9 f0 fe 59 03 01 000000 17 000000 8f 0000 |. Y. |

00000088 000000 39 d 3 4f ad |. 9.O. |

0000008f

The meaning is unknown. Officials didn't say.

QYERY_EVENT:

QUERY_EVENT is generated when you start a transaction with the begin command

Fixed part:

4bytes thread_id can be used for auditing

4bytes the execution time (in seconds) of the statement

The byte length of the library name where the 1byte executes the command

2bytes error code

2bytes records the length of the data part part of the variable status

Variable part:

0 or more state variables

Default library name

SQL_Statement

Master [localhost] {root} (test) > begin

Master [localhost] {root} (test) > insert into tttt2 select 'AAAA'

Master [localhost] {root} (test) > commit

Master [localhost] {root} (test) > show binlog events in 'mysql-bin.000001'

+-+ +

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-+ +

| | mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.37-log, Binlog ver: 4 |

| | mysql-bin.000001 | 120 | Query | 1 | 199 | BEGIN | |

| | mysql-bin.000001 | 199 | Query | 1 | 304 | use `test`; insert into tttt2 select 'AAAA' |

| | mysql-bin.000001 | 304 | Xid | 1 | 335 | COMMIT / * xid=40 * / | |

+-+ +

4 rows in set (0.00 sec)

[root@test_mysql26 / root/sandboxes/rsandbox_5_6_37/master/data] # hexdump-C mysql-bin.000001-s 120

00000078 de f3 fe 59 02 01 000000 4f 000000 c7 0000 |. Y.O. |

00000088 0008 00 01 000000 000000 00 04 0000 21 00 |. |

00000098 000000 0000 01 000000 40 000000 00 06 03 |. @. |

000000a8 73 74 64 04 21 00 21 00 08 00 0C 01 74 65 73 74 | std.room.test |

000000b8 00 74 65 73 74 00 42 45 47 49 4e 37 1f 09 57 de | .test.BEGIN7. W. |

000000c8 f3 fe 59 02 01 000000 69 000000 30 01 0000 |.. Y.I.. 0. |

000000d8 0000 01 000000 000000 00 04 0000 21 0000 |... |

000000e8 000000 00 01 000000 40 000000 00 06 03 73 | .@ .s |

000000f8 74 64 04 21 00 21 00 08 00 0C 01 74 65 73 74 00 | td.room.test. |

00000108 74 65 73 74 00 69 6e 73 65 72 74 20 69 6e 74 6f | test.insert into |

00000118 20 74 74 74 32 20 73 65 6c 65 63 74 20 27 41 | tttt2 select'a |

00000128 41 41 41 27 73 f4 e2 90 e0 f3 fe 59 10 01 0000 | AAA's.Y.... |

00000138 00 1f 0000 00 4f 01 0000 0000 28 0000 0000 | .o. (. |

00000148 0000 00 17 4f 16 46 |. O.F |

0000014f

.... This type of event analysis is stuck, who will help me? [reference http://www.jianshu.com/p/c16686b35807]

ROTATE_EVENT:

ROTATE_EVENT is generated when flush logs or normal binlog is cut.

When a binary log file exceeds a size limit, a ROTATE_EVENT is written at the end of the file that points to the next file in the squence. This event is information for the slave to know the name of the next binary log it is going to receive.

Fixed data part:

8 bytes. The position of the first event in the next log file. Always contains the number 4 (meaning the next event starts at position 4 in the next binary log). This field is not present in v1; presumably the value is assumed to be 4.

Variable data part:

The name of the next binary log. The filename is not null-terminated. Its length is the event size minus the size of the fixed parts.

XID_EVENT:

For the sake of transaction consistency, when writing binlog, first write the statement of the transaction, then write the xid flag, and finally commit the COMMIT command.

Fixed part is empty

Variable part 8bytes, recording the xid number

An example of rotate event:

Master [localhost] {root} ((none)) > show binlog events in 'mysql-bin.000001'

+-- +

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-- +

| | mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.37-log, Binlog ver: 4 |

| | mysql-bin.000001 | 120 | Rotate | 1 | 167 | mysql-bin.000002;pos=4 | |

+-- +

2 rows in set (0.00 sec)

[root@test_mysql26 / root/sandboxes/rsandbox_5_6_37/master/data] # hexdump-C mysql-bin.000001-s 4-n 19 derive the contents of format desc event

00000004 e2 e3 fe 59 0f 01 000000 74 000000 78 0000 |. Y.t.x.. |

00000014 000000 |. |

00000017

The last two digits of 0000 indicate that the binlog is closed, and if it is 0000, the binlog is still in use.

[root@test_mysql26 / root/sandboxes/rsandbox_5_6_37/master/data] # hexdump-C mysql-bin.000001-s 120 exports the contents of rotate event

00000078 f6 e3 fe 59 04 01 000000 2f 000000 a7 0000 |. Y.Univer. |

00000088 000000 04 000000 000000 00 6d 79 73 71 6c | .MySQL |

00000098 2d 62 69 6e 2e 30 30 30 32 ce 7f 95 b8 |-bin.000002.... |

000000a7

Event_header (19bytes) is as follows:

F6 e3 fe 59 timestamp

04 type_code whose event type is 0x04, indicating that it is a rotate event

01 00 00 00 server_id means serverid is 1

2f 00 00 event_length length is 2f, which means 47bytes in decimal system

A700000 next_position the next event starting position is 0xa7, and the decimal representation is 167,

0000 flags 0000 indicates that the binlog has been shut down normally

Then there is the event data section, as follows:

04 00 00 00 Fixed data part, 8bytes, records the position offset of the next binlog 4

6d 79 73 71 6c 2d 62 69 6e 2e 30 30 30 32 Variable data, recording the file name of the next binlog mysql-bin.000002

Ce 7f 95 b8 meaning unknown

TABLE_MAP_EVENT:

Used for row-based binary logging beginning with MySQL 5.1.5.

Fixed data part:

6 bytes. The table ID.

2 bytes. Reserved for future use.

Variable data part:

1 byte. The length of the database name. The length of the library name

Variable-sized. The database name (null-terminated). Library name

1 byte. The length of the table name. The length of the table name

Variable-sized. The table name (null-terminated). Table name

Packed integer. The number of columns in the table. Number of columns

Variable-sized. An array of column types, one byte per column. To find the meanings of these values, look atenum_field_types in the mysql_com.h header file. The data type of each column

Packed integer. The length of the metadata block. Length of metadata block

Variable-sized. The metadata block; see log_event.h for contents and format.

Variable-sized. Bit-field indicating whether each column can be NULL, one bit per column. For this field, the amount of storage required for N columns is INT ((Numb7) / 8) bytes. Whether this part of the record field is allowed to be empty, one bit represents a field, and the occupying word int ((Numb7ap8)) bytes,N is the number of fields.

WRITE_ROWS_EVENT, DELETE_ROW_EVENTS, and UPDATE_ROW_EVENTS all refer to the following explanation:

Used for row-based binary logging beginning with MySQL 5.1.18.

[TODO: following needs verification; it's guesswork]

Fixed data part:

6 bytes. The table ID.

2 bytes. Reserved for future use.

Variable data part:

Packed integer. The number of columns in the table. Number of columns

Variable-sized. Bit-field indicating whether each column is used, one bit per column. For this field, the amount of storage required for N columns is INT ((Numb7) / 8) bytes.

Variable-sized (for UPDATE_ROWS_LOG_EVENT only). Bit-field indicating whether each column is used in theUPDATE_ROWS_LOG_EVENT after-image; one bit per column. For this field, the amount of storage required for N columns is INT ((Numb7) / 8) bytes.

Variable-sized. A sequence of zero or more rows. The end is determined by the size of the event. Each row has the following format:

Variable-sized. Bit-field indicating whether each field in the row is NULL. Only columns that are "used" according to the second field in the variable data part are listed here. If the second field in the variable data part has N one-bits, the amount of storage required for this field is INT ((Numb7) / 8) bytes.

Variable-sized. The row-image, containing values of all table fields. This only lists table fields that are used (according to the second field of the variable data part) and non-NULL (according to the previous field). In other words, the number of values listed here is equal to the number of zero bits in the previous field (not counting padding bits in the last byte).

The format of each value is described in the log_event_print_value () function in log_event.cc.

(for UPDATE_ROWS_EVENT only) the previous two fields are repeated, representing a second table row.

For each row, the following is done:

For WRITE_ROWS_LOG_EVENT, the row described by the row-image is inserted.

For DELETE_ROWS_LOG_EVENT, a row matching the given row-image is deleted.

For UPDATE_ROWS_LOG_EVENT, a row matching the first row-image is removed, and the row described by the second row-image is inserted.

HEARTBEAT_LOG_EVENT:

A Heartbeat_log_event is sent by a master to a slave to let the slave know that the master is still alive. Events of this type do not appear in the binary or relay logs. They are generated on a master server by the thread that dumps events and sent straight to the slave without ever being written to the binary log. The slave examines the event contents and then discards it without writing it to the relay log.

The heartbeat event of master-slave heartbeat is not written into binlog. Sent by a master to a slave to let the slave know that the master is still alive. Not written to log files.

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.