What you can learn from this article

  • binlogWhat is the
  • binlogThe role of
  • binlogThree types of, and their advantages and disadvantages
  • binlogThe structure and content of the file

binlogWhat is the

A Binary Log, as its name implies, is a Log in Binary format. Specifically, binlog is a set of logs containing data changes to MySQL Server instances (update/delete/insert/…). Information files.

Binlog role

  • Used for replication, such as master-slave replication
  • Data recovery

Binlog type

  1. Based on thestatements (Statement-based) logging (SBL): Events contain SQL statements that produce data changes.
  2. Based on theline (Row-based) logging (RBL): Describes changes to a single row
  3. hybrid (Mixed) : The above two are used in combination toSBLIn special cases, switch toRBL

Why are there three types of binlogs?

At the beginning, there was only statement-based, but there were many problems with statement-based, and row and mixed were added later.

SBLadvantages

  • A small number of log files are generated and the I/O count is small

SBLdisadvantages

  • In some insecure statements, master/slave replication cannot be consistent, for example

    1. Statements containing system functions that may return different values on replicas, such as RAND(), USER(),UUID(), SYSDATE()….

    2. Alter TABLE AUTO_INCREMENT alter table AUTO_INCREMENT

    3. Updates using LIMIT

    4. .

  • Slow SQL is executed again in the copy

RBLadvantages

  • This is the safest form of replication
  • inINSERT/UPDATE/DELETEStatement, the copy has a smaller scope than the primary row lock.

RBL shortcomings

  • RBLLog files are larger

conclusion

RBL is recommended. The advantages outweigh the disadvantages. The latest MySQL version also uses RBL by default.

binlogstructure

Binlog file structure

Binlog.index: Text file, as shown in the following example, which lists the current binary log files (there are now six).

Binlog.xxxxxx: log binary file,

binlog.000001
binlog.000002
binlog.000003
binlog.000004
binlog.000005
binlog.000006
binlog.index 
Copy the code

Each log file begins with a 4-byte magic number (0xFe b I n), followed by a set of events that describe the modification of the data, in the following format:

+===================+ | Magic Number | +===================+ | Start Event | +===================+ | Event 1 | + = = = = = = = = = = = = = = = = = = = + |... | + = = = = = = = = = = = = = = = = = = = +Copy the code

A concrete example:

 show BINLOG EVENTS in 'binlog.000001'+---------------+---------+----------------+-----------+-------------+-------------------------------------------------- -----------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+---------+----------------+-----------+-------------+-------------------------------------------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | binlog. 000001 | | Format_desc | 1 | 125 | Server ver: 8.0.26, binlog ver: 4 | | binlog.000001 | 125 | Previous_gtids | 1 | 156 | | | binlog.000001 | 156 | Anonymous_Gtid | 1 | 233 | SET @@SESSION.GTID_NEXT='ANONYMOUS'                              |
| binlog.000001 | 233     | Query          | 1         | 337         | use `mysql`; TRUNCATE TABLE time_zone /* xid=3 */                 |
| binlog.000001 | 337     | Anonymous_Gtid | 1         | 414         | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
| binlog.000001 | 414     | Query          | 1         | 523         | use `mysql`; TRUNCATE TABLE time_zone_name /* xid=4 */            |
Copy the code

Event Common categories

  • START_EVENT_V3: The first event of each log file. This event is used in MySQL 3.23 to 4.1 and is used in MySQL 5.0FORMAT_DESCRIPTION_EVENTReplaced by the fifth byte (after magic number)
  • QUERY_EVENT : SBL 的 binlogRecord the SQL statement, record the transaction BEGIN in ROW mode
  • STOP_EVENT: records when the mysqld process stops
  • ROTATE_EVENT: log file size reaches the set maximum, switch to a new log file when written, switch event
  • TABLE_MAP_EVENTBinlog is used only if the binlog file is in ROW format.TABLE_MAP_EVENTTable definitions (including Database name, table name, and field definitions) are recorded. Each change in the record is preceded by a corresponding table to be operated onTABLE_MAP_EVENT
  • WRITE_ROWS_EVENT: Insert records, ROW format records will be used
  • UPDATE_ROWS_EVENT: Updates records. Records in ROW format are used only
  • DELETE_ROWS_EVENT: Deletes records that are used in ROW format
  • .

The Event structure

The evolution of the binlog version is essentially the evolution of the Event

  • V1: used in MySQL 3.23

  • V3: used in MySQL 4.0.2 and 4.1

  • V4: used in MySQL 5.0 and later versions

V2 has been used in 4.0 and is deprecated

Event is divided into two parts: Event header and Event Data. The length of the header part is determined by the binlog version, and the length of the data part is determined by the header

+===================+
| event header      |
+===================+
| event data        |
+===================+
Copy the code

Here’s how events are structured and how they differ from version to version

The number represents the limit and offset of the field in the Event. For example, 0:4 represents the position of the field in the 0-4 bytes of the Event.

+=====================================+ | event | timestamp 0 : 4 | | header +----------------------------+ | | type_code 4 : 1 | | +----------------------------+ | | server_id 5 : 4 | | +----------------------------+ | | event_length 9 : 4 | v1 headers, a total of 13 byte | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | next_position 13: | v3 version 4. | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | flags 17:2 | v3 version. V3 headers, a total of 19 byte | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | extra_headers 19: x - 19 | v4 version. V4 byte header at least 19 + = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = + | event | fixed part x: y | | data +----------------------------+ | | variable part | +=====================================+Copy the code

Header field description

  • timestamp: The time when something starts executing, in seconds
  • type_code: Event type, see above [common category of events](# Event common category), which determines what data is written to the data section
  • server_id: server-id option from the server configuration file set for replication purposes, breaking possible endless loops in replication.
  • event_length: The total length of the event
  • next_positionIn V3, it represents the position where the event started and in V4, it represents the position where the event ends
  • flags: v3 new, some flag bitsDev.mysql.com/doc/interna…
  • extra_header: v4 new plus, currently 0, that is null

The data section

Depending on the event type, such as v1 and v3 query events, the data part is composed as follows:

+======================================+ | fixed | issue_thread 0 : 4 | thread id of the statement | part + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | timestamp 4: 4 | statement execution time | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | db_name_len 8: 1 | | to use the database name length + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | error_code 9: 2 | error code + = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = + | variable | db_name | db name | part + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | Sql_statment | SQL statement + = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = +Copy the code

conclusion

  • binlogThe file consists of magic number and event list. The event list is divided into three parts: start event + data update-related event + switch event
  • The type of event determines the format of the event, because the information required for each event is inconsistent

Q&A

  1. Master/slave replication, in push mode or pull mode

  2. MySQL binlog does not use Mixed mode by default

  3. How do DDL (CREATE/ALTER) statements record when using RBL

  4. Are statements using NOW() unsafe (SBL master-slave replication)

  5. If you were to design the structure of the start event type, what data would be required for the data section

  6. Binlog is different from redo log

reference

  1. Dev.mysql.com/doc/interna…

  2. Mysql.taobao.org/monthly/201…

  3. zhuanlan.zhihu.com/p/33504555