In row based replication, the row data generated by DML is logged into binary log with some metadata. For example column type, type length etc. In the new release MySQL-8.0.1, more table metadata are written into binary log. All metadata together brings users and us below benefits:
- Allows us to build robuster replication and convert row data between types smoothly.
- Makes replication easier to debug and audit, since mysqlbinlog can print more details about the data types, as well as column names.
- It also provides necessary information when replicating to other database systems
What Metadata Is Logged
The new logged metadata includes:
- Signedness of numeric columns
- Collation number of character columns(Including BINARY columns)
- Types of Geometry columns
- Column names
- String values of SET/ENUM columns
- Primary Key information
Some sorts of the metadata are useful only in some special use cases which are rarely happen. It is not worth to log them always. So the sorts of metadata above are divided into two categories according to if they are required by slave. The first three sorts of metadata are required by slave. Slave needs them to check the column compatibility(It is not included in this worklog). The other three are not required by slave, so they are not logged by default. This worklog introduced a new system variable to control the behavior.
- binlog_row_metadata
1234567Scope: GLOBALType: ENUMDynamic: YesValues:MINIMAL Only metadata actually required by slave is logged.FULL Cause all metadata to be logged.Default: MINIMAL
Print Table Metadata Through mysqlbinlog
mysqlbinlog program is also extended to print the metadata. The new BOOL option –print-table-metadata is for it. It is disabled by default. The metadata is printed as a comment. It has two parts, one part is column metadata and another part is primary key. They are printed in separate lines.
Column metadata is printed in the the CREATE TABLE format. When binlog_row_metadata is MINIMAL, column names, string values of SET/ENUM are not logged, then it will printed without column name and string values. Below are a few examples:
|
1
2
3
4
|
# Columns( YEAR, DATE, TIME, TIME(3), SET, ENUM) # Columns(`c_smallint` SMALLINT, `c_int` INT UNSIGNED) # Columns(`c_set_1` SET('set1_v1' ,'set1_v2' ,'set1_v3')) # Columns(`c_char` CHAR(10), `c_longtext` LONGTEXT CHARSET utf8 COLLATE utf8_general_ci) DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci |
Primary key is printed only if primary key information is logged(binlog_row_metadata is set FULL). The format looks like:
|
1 |
# Primary Key(c_key1, c_key2(10), c_key3) |
Implementation Detail
The new metadata is appended at the end of original Table_map_log_event. Each sorts of the metadata is organized into a field called TLV field.
|
1
2
3
|
+-----------------+-----+-----+-----+ | Original Fields | TLV | TLV | ... | +-----------------+-----+-----+-----+ |
TLV means type, length and value. The first byte is the type of the value, following bytes(1-4) stores the the length of the value. Value is stored following length.
|
1
2
3
|
+------+--------+-------+ | Type | Length | VALUE | +------+--------+-------+ |
The value of each sort of metadata has its own format, for the detail please check the comment in libbinlogevents/include/rows_event.h.
For the users who use the libbinlogevents API, you can use the Table_map_event::Optional_metadata_fields class to parse the metadata. Optional_metadata_fields can read the fields from memory buffer and organize the metadata into vectors. Below is an example:
|
1
2
|
Optional_metadata_fields fields(table_map_event->m_optional_metadata, table_map_event->m_optional_metadata_len); |
If you have any questions or comments about this new metadata and related work, please let us know!
THANK YOU for using MySQL!