More Metadata Is Written Into Binary Log

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:

  1. Signedness of numeric columns
  2. Collation number of character columns(Including BINARY columns)
  3. Types of Geometry columns
  4. Column names
  5. String values of SET/ENUM columns
  6. 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

 

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:

Primary key  is printed only if primary key information is logged(binlog_row_metadata is set FULL). The format looks like:

 

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.

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.

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:

 

If you have any questions or comments about this new metadata and related work, please let us know!

THANK YOU for using MySQL!

One thought on “More Metadata Is Written Into Binary Log

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter. * Time limit is exhausted. Please reload CAPTCHA.