About binlog (2) (mysql5.7.13), binlogmysql5.7.13

About binlog (2) (mysql5.7.13), binlogmysql5.7.13

Last time, we only gave an overview of binlog and did not go into depth. (1) How is binlog file constructed? (2) How is the binlog events unit of binlog file made up? (3) Can we forge a binlog file identified by mysqlbinlog? Of course, the third question looks cool and quite challenging.

This time we will discuss binlogfile of version 4th. mysql of version 5.0.x or later uses binlogfile of this version.

First, answer the first question.

I. Composition of binlog file

(1) general composition of binlog file

I have answered this question before, but it is not in-depth. This time, I will explore it in depth. First, let's look at the official saying;

Http://dev.mysql.com/doc/internals/en/binlog-file.html

Here is a sentence: binlog file is composed of binlog file header and binlog events. As for binlog file header, it is actually 0xfe62696e. In other words, binlog file first writes 0xfe62696e, followed by many binlog events. The official documentation is still awesome. We can open any binlog file in hexadecimal format, starting with 0xfe62696e.

(2) binlog events Organization

Does many binlog events have their own organizational structure? The answer is yes. What follows the binlog file header is generallyBinlog: FORMAT_DESCRIPTION_EVENTThis is the beginning of binlog event 4th, which will be detailed in "II. Composition of binlog events" in this article.

FollowedTransactional binlog eventsIt should be emphasized that all the update, delete, and insert statements in binlog file exist in a group.A transactional binlog event.

The format of transactional binlog events is as follows (SQL indicates any SQL statement. Of course, only select, update, delete, and insert statements are included, excluding ddl statements.):

SQL statement form	Statement format	Row format
/	Anonymous_gtid_event	Anonymous_gtid_event
BEGIN	Query_event (BEGIN)	Query_event (BEGIN)
SQL	Query_event (SQL)	Table_map_event & Rows_event
SQL	Query_event (SQL)	Table_map_event & Rows_event
......	......	......
COMMIT	Xid_event	Xid_event

 

 

 

 

 

AsMixed formatYesStatement format andRow formatThis is where the differences between the three formats appear. As for the binlog events format shown above, we will detail it in "II. Composition of binlog events" in this article.

FinallySTOP_EVENTOrROTATE_EVENTAt the end of this article, the two are also described in detail in "II. Composition of binlog events.

Other binlog events are not very important. If you are interested, you can use

Http://dev.mysql.com/doc/internals/en/binlog-event.htmlto learn more

Ii. Composition of binlog events

(1) Composition of binlog events

Binlog events are divided into four parts: common header, post header, body, and footor. The translation capability is limited and I don't know how to translate them. But as long as I understand them, the common header and footor are common, the post header and body are unique to each event.

Common headers generally contain the following

Name	Format	Description
When	4-byte integer	The time when the event occurred, from January 1, 1970 to the current number of seconds
Type_code	1-byte integer	Binglog event Type
Unmasked_server_id	4-byte integer	Server id
Data_written	4-byte integer	The length of the binglog event, that is, the length of the common header + the length of the post header + the length of the body + 4
Log_pos	4-byte integer	Location of the next binglog event in the file
Flags	2-byte integer	Binglog version

 

 

 

 

 

Footor contains a crc32 verification code, which is a 4-byte integer.

(2) Composition of important binlog events

1)FORMAT_DESCRIPTION_EVENT

Reference self-http://dev.mysql.com/doc/internals/en/format-description-event.html

Body:

Name	Format	Description
Binlog-version	2-byte integer	Binlog version, generally 4
Mysql-server version	50-byte string	Mysql database version
Create timestamp	4-byte integer	Creation Time
Event header length	1-byte integer	The length of the common header, usually 19
Event type header lengths	EOF string	Length of the post header of various binlog events

 

 

 

 

2)Xid_event

Reference self-http://dev.mysql.com/doc/internals/en/xid-event.html

Body:

Name	Format	Description
XID	8-byte integer	Transaction id submitted

 

 

3)Anonymous_gtid_event

No websites for Reference

Post header:

Name	Format	Description
Commit flag	1-byte integer	Submitted or not. 1 indicates submitted, and 0 indicates not submitted.
ENCODED SID	16-byte integer	Generally 0
ENCODED GNO	8-byte integer	Generally 0
TS_TYPE	1-byte integer	Generally 2

 

 

 

 

Body:

Name	Format	Description
Last_committed	8-byte integer	Serial number of the last submission
Sequence_number	8-byte integer	This serial number

 

 

4)STOP_EVENT

Reference self-http://dev.mysql.com/doc/internals/en/stop-event.html

No post header or body

5)ROTATE_EVENT

Reference self-http://dev.mysql.com/doc/internals/en/rotate-event.html

Post header:

Name	Format	Description
Position	8-byte integer	Location of the next binlog event

 

 

Body:

Name	Format	Description
Name of the next binlog	String	File Name of the next binlog event

 

 

6)Table_map_event

Reference self-http://dev.mysql.com/doc/internals/en/table-map-event.html

Post header

Name	Format	Description
Table id	4-byte integer	Table id. Each table in the mysql database has a unique id.
Flgas	2-byte integer	Currently retained for future use

 

 

Body:

Name	Format	Description
Schema name length	1-byte integer	Database Name Length
Schema name	NULL String	Database Name
Table name length	1-byte integer	Table Name Length
Table name	NULL String	Table Name
Column-count	Variable integer	Number of Columns
Column-def	String	Description of the Data Type of each column
Column-meta-def	String	Describe the metadata of each column
NULL-bitmask	String	Can each column be empty?

 

 

 

 

 

 

7)ROWS_EVENT

Reference self-http://dev.mysql.com/doc/internals/en/rows-event.html

Post header

Name	Format	Description
Table id	4-byte integer	Table id. Each table in the mysql database has a unique id.
Flgas	2-byte integer	Currently retained for future use

 

 

Body

Name	Format	Description
Var_header_len	4-byte integer	Number of columns in the table
Columns_before_image	String	The bit value of the column to be used. For example, if the table has three columns and only 1st and 3rd columns are used, the value is 0xfa and only 1st and 2nd columns are used, the value is 0xfc.
Columns_after_image	String	It is only used for the update command, that is, the updated data is obtained here, and neither delete nor insert has this item.
Row	String	String. var_len nul-bitmap, length (bits set in 'columns-present-bitmap1 '+ 7)/8 String. var_len value of each field as defined in table-map Each row contains nul-bitmap, which indicates whether the used column is null, followed by the data value of each field as defined in table-map.

 

 

 

 

 

 

 

8)QUERY_EVENT

Reference self-http://dev.mysql.com/doc/internals/en/query-event.html

Post header

Name	Format	Description
Slave_proxy_id	4-byte integer	Slave proxy id
Execution time	4-byte integer	Execution time
Schema length	1-byte integer	Database Name Length
Error-code	2-byte integer	Error Code
Status-vars length	2-byte integer	Status Length

 

 

 

 

Body

Name	Format	Description
Status-vars	String	Mysql environment variables for executing SQL statements
Schema	NULL String	Database Name
Query	EOF string	SQL statement

 

 

 

3. Forged binlog files

Now, through the above introduction, we can forge our binlog file, but we can only forge the binlog in statement format, because the binlog in row mode requires real data during SQL Execution.