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

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

This time we are going to explore more detailed binlog content. The Query_event and Rows_event discussed last time certainly have some confusing questions. What are the status-vars environment variables in Query_event, what is the data type of Rows_event, and what is metadata? Let's answer them one by one today.

1. puzzling status-vars in Query_event

Status-vars is said to be added to the environment variable settings to be compatible with mysql servers of earlier versions. It tells the environment in which the SQL statement of the mysql server of earlier versions is executed.

The specific format is state enumeration value + state parameter

The following lists the important statuses with State enumeration values:

(1) 0x00 Q_FLAGS2_CODE 4 bytes

This is mainly for the settings of keys, FOREIGN_KEY_CHECKS, UNIQUE_CHECKS, and AUTOCOMMIT. SQL _AUTO_IS_NULL is automatically filled with NULL, FOREIGN_KEY_CHECKS is a foreign key check, and UNIQUE_CHECKS is a unique key index check, in this example, SQL _AUTO_IS_NULL = 1, and the other values are 0.

(2) 0x01 Q_ SQL _MODE_CODE 8 bytes

The configuration here should referSET sql_mode: Http://dev.mysql.com/doc/refman/5.7/en/set-variable.html, which contains a large amount of SQL Environment Variables

The corresponding status list can refer to the http://dev.mysql.com/doc/internals/en/query-event.html#q-sql-mode-code

You can refer to the following Chinese Translation:

1) Single Mode:

2) hybrid mode:

1. ANSI: If this item is set, it is equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, and IGNORE_SPACE.

2. DB2: If this option is set, it is equivalent to setting PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS.

3. MAXDB: If this item is set, it is equivalent to setting it together with PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_AUTO_CREATE_USER.

4. MSSQL: If this item is set, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS are set to equal values.

5. TRADITIONA: If this item is set, it corresponds to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, expiration, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

6. POSTGRESQL: If this option is set, it is equivalent to setting PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS.

7. ORACLE: If this option is set, it is equivalent to setting PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS.

8. MYSQL323: it only affects show create table.

9. MYSQL40: Same as above.

(3) 0x02 Q_CATALOG

The default record is "\ x3std \ x0", which is the directory used by the mysql server. One-Byte Length + NULL String

(4) 0x03 Q_AUTO_INCREMENT

It is related to AUTO_INCREMENT, 4 bytes. The first two bytes represent AUTO_INCREMENT, and the last two bytes represent AUTO_INCREMENT_OFFSET.

(5) 0x04 Q_CHARSET_CODE

Character Set set by the client during connection (2 bytes) + character set for connection verification (2 bytes) + character set for server verification (2 bytes)

(6) 0x05 Q_TIME_ZONE_CODE

The time zone is a byte length + String. For example, "\ x6 +" indicates Beijing time. This is to be confirmed.

(7) 0x07 Q_LC_TIME_NAMES_CODE

The name of the week and month. For example, Monday is MON and Jan is Jan on January 1, January.

Ii. Data Type and metadata of Rows_event

Here we will only introduce common data types

(1) Date and Time Type

1) Metadata represents a value that can be accurate to microseconds. n is usually 0-3.

Corresponding definitions for this type in my_time.h.

MYSQL_TYPE_DATETIME2, which is stored in longlong data, is fixed to 4-bit + n, and metadata represents the length of additional data n

1 // from mysql5.7.13 source code my_time.c 2 // convert longlong time to string 3 // longlong time format: 4 // calculate by bit 5 // 1 bit sign (used when on disk) 6 // 17 bits year * 13 + month (year 0-9999, month 0-12) 7 // 5 bits day (0-31) 8 // 5 bits hour (0-23) 9 // 6 bits minute (0-59) 10 // 6 bits second (0-59) 11 // 24 bits microseconds (0-999999) 12 // 13 // Total: 64 bits = 8 bytes14 // 15 // SYYYYYYY. YYYYYYYY. YYdddddh. hhhhmmmm. mmssssss. ffffffff. ffffffff. ffffffff

View Code

MYSQL_TYPE_TIME2 is also stored in longlong data. It is fixed to 3-bit + n, and metadata represents the length of additional data n.

MYSQL_TYPE_TIMESTAMP2 is a timestamp, fixed to 4 bits, metadata represents the length of additional data n

MYSQL_TYPE_NEWDATE is also stored in this way, but there is no metadata

2) Metadata does not exist. It is stored in the decimal format of yyyymmddhhmmss.

MYSQL_TYPE_TIMESTAMP and MYSQL_TYPE_DATETIME are stored in this way.

MYSQL_TYPE_TIME is the timestamp.

3) MYSQL_TYPE_YEAR. Metadata does not exist.

This is the year from 1900.

(2) integer. Metadata does not exist.

MYSQL_TYPE_TINY, MYSQL_TYPE_SHORT, MYSQL_TYPE_INT24, MYSQL_TYPE_LONG, MYSQL_TYPE_LONGLONG, the same method as we previously understood, but only small-end data

(3) Real Number

MYSQL_TYPE_FLOAT and MYSQL_TYPE_DOUBLE are also stored through small-end data. The difference is that they have a byte of metadata, whose metadata represents the length of their valid data.

The decimal storage method of MYSQL_TYPE_NEWDECIMAL is not very clear, only in decimal. h is defined, but the first byte of his metadata represents the length of his valid data, and the second represents his precision, that is, the length of the decimal point.

(4) string type

MYSQL_TYPE_VARCHAR its two-byte metadata is his longest length.

MYSQL_TYPE_STRING, MYSQL_TYPE_VAR_STRING their metadata the first byte represents the real type, and the second byte represents the size of the bytes that store the string length

(5) bit type

MYSQL_TYPE_BLOB its metadata stores the bit-length bytes.

MYSQL_TYPE_BIT: the first byte in metadata storage represents the number of BITs, and the second byte represents the size of BITs in storage.

Therefore, we can conclude that the metadata stores more information about the length of this type.

For more details, see the mysql5.7.13 source code log_event.cc: 1953: log_event_print_value () for more information.