Binlog profile

MySQL Binlog is introduced

MySQL generally has the following types of logs:

Log type Information written to logs
The error log Log problems encountered when starting, running, or stopping mysqld
General query log Records client connections established and statements executed
Binary log A statement that records change data
Relay log Data changes received from the replication master server
Slow Query logs Record all execution times exceededlong_query_timeSeconds for all queries or queries that do not use indexes
DDL logs (metadata logs) Metadata operations are performed by DDL statements

This article introduces the binary log, binlog.

The binary log records all DDL and DML statements (except select, show, etc.) in the form of events. It also contains the time consumed by the execution of the statement. MySQL binary logs are transaction safe. The main purpose of binlog is to copy and restore.

The two most important use scenarios for Binlog logging

  • MySQL master-slave Replication: MySQL Replication enables binlog on the Master side and the Master passes its binary log to slaves to achieve master-slave data consistency
  • Data recovery: Restore data by using the mysqlbinlog tool

Enable the Binlog

Note: the MySQL version of the author’s experiment is 5.7.22

Generally speaking, enabling binlog has a performance penalty of about 1%.

Enable the binlog, by configuring the/etc/my CNF or/etc/mysql/mysql. Conf. D/mysqld. CNF profile log – bin options:

If log-bin is added to the configuration file, binlog is enabled. If no specified value is specified, log-bin= is used, and the default name is the host name. (Note: If the name has a decimal point, only the part before the first decimal point is used as the name.)

[mysqld]
log-bin=my-binlog-name
Copy the code

You can also run the SET SQL_LOG_BIN=1 command to enable binlog, and run the SET SQL_LOG_BIN=0 command to disable binlog. After binlog is enabled, you must restart MySQL for it to take effect.

Common Binlog operation command

# Whether to enable binlog
show variables like 'log_bin';

View detailed log configuration information
show global variables like '%log%';

Mysql data store directory
show variables like '%dir%';

Check the directory of binlog
show global variables like "%log_bin%";

# check the size of the biglog file used by the current server
show binary logs;

# check the size of the biglog file used by the primary server

Check the name and Position of the last binlog file
show master status;


Event query command
# IN 'log_name' : specifies the name of the binlog file to be queried.
# FROM pos: specify which pos point to start FROM (if not specified, start FROM the first POS point in the entire file)
# LIMIT [offset,] : offset
# row_count: query total number of rows (if not specified)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

View the contents of binlog
show binlog events;

# check the contents of a specific binlog file.
show binlog events in 'master.000003';

# Set binlog file save event, expiration delete, unit days
set global expire_log_days=3; 

Delete the current binlog file
reset master; 

Delete the trunk logs of the slave
reset slave;

Delete the binlog file from the log index before the specified date
purge master logs before '2019-03-09 14:00:00';

Delete the specified log file
purge master logs to 'master.000003';
Copy the code

When to write the Binlog

For transactions-enabled engines such as InnoDB, binlog is recorded only after a transaction has been committed. The time when binlog is flushed to disk depends on the sync_binlog parameter.

  • If the value is set to 0, MySQL does not control the refresh of binlog. The file system controls the refresh of its cache.
  • If set to a value other than 0, eachsync_binlogSecond transaction, MySQL calls the file system refresh operation to flush binlog to disk.
  • Setting it to 1 is the most secure and will lose at most one transaction update in the event of a system failure, but it can have an impact on performance.

If sync_binlog=0 or sync_binlog is greater than 1, in the event of a power failure or an operating system crash, a portion of committed transactions whose binlog has not been synchronized to disk may be lost and the recovery program will not be able to recover those transactions.

Before MySQL 5.7.7, the default value sync_binlog was 0. MySQL 5.7.7 and later use the default value 1, which is the safest choice. It is usually set to 100 or 0, sacrificing consistency for better performance.

Binlog files and extensions

Binlog contains two types of files:

  • Binary log index files (the file name suffix is. Index) record all valid binary files
  • Binary log files (filename extension:.00000*) record all DDL and DML statement events of the database

A binlog is a collection of binary files. Each binlog file begins with a magic number of 4 bytes, followed by a set of Events:

  • Magic number: 0xFE62696e corresponds to 0xfebin;
  • Event: Each Event contains header and data. Header provides the creation time of the Event and the server. The Data part provides specific information about the Event, such as the modification of specific data.
  • The first Event describes the format version of the binlog file. This format is the format in which events are written to the binlog file.
  • The remaining events are written as formatted versions of the first Event;
  • The last Event indicates the next binlog file;
  • The binlog index file is a text file containing the current list of binlog files

MySQL generates a new log file with an increasing sequence number in the following three cases:

  • The MySQL server stops or restarts
  • useflush logsCommand;
  • When the binlog file size exceedsmax_binlog_sizeThe value of a variable;

The minimum value of max_binlog_size is 4096 bytes, and the maximum value and default value are 1GB (1073741824 bytes). Transactions are written to a block in the binlog, so it is not split between several binary logs. Therefore, if you have a large transaction, it is not possible to switch the log to ensure the integrity of the transaction, and only log the transaction to the current log file until the end of the transaction, you may see the binlog file larger than max_binlog_size.

Binlog Indicates the log format

The format of events recorded in a binary log depends on the binary record format. Three format types are supported:

  • STATEMENT: STATement-based replication (SBR)
  • ROW: Row-based replication (RBR)
  • MIXED: Mixed-mode replication (MBR)

Before MySQL 5.7.7, the default format was STATEMENT. In MySQL 5.7.7 and later, the default is ROW. The log format can be specified by binlog-format, for example, binlog-format=STATEMENT, binlog-format=ROW, or binlog-format=MIXED.

Statement

Every SQL that modifies data is recorded in a binlog

Advantages: You do not need to record the changes of each row, reducing the amount of binlog logs, saving I/O, and improving performance.

Disadvantages: Since only executing statements are recorded, in order for the statements to run correctly on the slave, it is necessary to record information about the execution of each statement to ensure that all statements get the same results on the slave side as they did on the master side. In addition, mysql replication, like some specific functions, has a lot of problems with keeping the slave and master consistent.

Row

MySQL 5.1.5 just started to support row level replication. It does not record information about the context of SQL statements, only which records have been modified.

Advantage: Binlog does not record context-specific information about the SQL statement being executed, only record which record has been modified. So the log content of the row clearly records the details of each row. And there are no specific cases where stored procedures, or functions, or trigger calls and triggers cannot be copied correctly.

Disadvantages: All executed statements, when logged, are recorded as changes per line, which can generate a large amount of log content.

Note: When you set the binary log format to ROW, some changes still use the statement-based format, including all DDL statements, such as CREATE TABLE, ALTER TABLE, or DROP TABLE.

Mixed

Starting with version 5.1.8, MySQL offers a Mixed format, which is essentially a combination of Statement and Row. In Mixed mode, general statement modification uses statment format to store binlogs. For example, for some functions, statement cannot complete the operation of master/slave copy, so row format is used to store binlogs. MySQL will use different log forms according to each specific SQL statement executed. That is, choose between Statement and Row.

Use of the mysqlbinlog command

The server writes binlog logs in binary format to a binlog file. To display the contents in text format, use the mysqlbinlog command.

#The mysqlbinlog execution format
mysqlbinlog [options] log_file ...

#Viewing bin-log binaries (shell mode)
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003

#Viewing bin-log binaries (with query criteria)
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003 \
    --start-datetime="2019-03-01 00:00:00"  \
    --stop-datetime="2019-03-10 00:00:00"   \
    --start-position="5000"    \
    --stop-position="20000"
Copy the code

When I set the log format to ROW, I output the following information on my machine

/ *! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; / *! 50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*! * /.# at 4
#190308 10:05:03 server id 1 end_log_pos 123 CRC32 0xff02e23d Start: Binlog V 4, Server V 5.7.22-log Created 190308 10:05:03
# Warning: this binlog is either in use or was not closed properly.
# at 123
#190308 10:05:03 server id 1 end_log_pos 154 CRC32 0xb81da4c5 Previous-GTIDs
# [empty]
# at 154
#190308 10:05:09 server id 1 end_log_pos 219 CRC32 0xfb30d42c Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes/ *! 50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*! * /. SET @@SESSION.GTID_NEXT='ANONYMOUS'/ *! * /.# at 219. .# at 21019
#190308 10:10:09 server id 1 end_log_pos 21094 CRC32 0x7a405abc Query thread_id=113 exec_time=0 error_code=0SET TIMESTAMP=1552011009/*! * /. BEGIN /*! * /.# at 21094
#190308 10:10:09 server id 1 end_log_pos 21161 CRC32 0xdb7a2b35 Table_map: `maxwell`.`positions` mapped to number 110
# at 21161
#190308 10:10:09 server id 1 end_log_pos 21275 CRC32 0xec3be372 Update_rows: table id 110 flags: STMT_END_F
### UPDATE `maxwell`.`positions`
### WHERE
# # # @ 1 = 1
### @2='master.000003'
# # # @ 3 = 20262
### @4=NULL
### @5='maxwell'
### @6=NULL
# # # @ 7 = 1552011005707
### SET
# # # @ 1 = 1
### @2='master.000003'
# # # @ 3 = 20923
### @4=NULL
### @5='maxwell'
### @6=NULL
# # # @ 7 = 1552011009790
# at 21275
#190308 10:10:09 server id 1 end_log_pos 21306 CRC32 0xe6c4346d Xid = 13088COMMIT/*! * /. SET @@SESSION.GTID_NEXT='AUTOMATIC'/* added by mysqlbinlog */ /*! * /. DELIMITER ;# End of log file/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; / *! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;Copy the code

An excerpt for analysis:

# at 21019
#190308 10:10:09 server id 1 end_log_pos 21094 CRC32 0x7a405abc Query thread_id=113 exec_time=0 error_code=0SET TIMESTAMP=1552011009/*! * /. BEGIN /*! * /.Copy the code

The output above includes information:

  • Position: position in the file, on the first line (# at 21019), indicating that the event record starts at byte 21019 of the file
  • Timestamp: timestamp at which the event occurred, the second line (#190308 10:10:09)
  • Server ID: Server ID (1)
  • End_log_pos indicates where the next event starts (that is, where the current event ends +1)
  • Thread_id: id of the thread that executes the event (thread_id=113)
  • Exec_time: Time for event execution
  • Error_code: indicates the error code. 0 indicates that no error occurred
  • Type: indicates the event type Query

Binlog Indicates the type of the event

There are three main versions of the binlog event structure:

  • V1: used in MySQL 3.23
  • V3: used in MySQL 4.0.2 to 4.1
  • V4: used in MySQL 5.0 and later versions

Versions below MySQL5.0 are not generally used today, so the binlog event types for VERSION V4 are only described below. There are many types of binlog events, so this article will summarize them briefly

The event type instructions
UNKNOWN_EVENT This event is never triggered or written to the binlog; When a binlog is read and no other event is recognized, it is called UNKNOWN_EVENT
START_EVENT_V3 The event written at the beginning of each binlog file. This event is used in mysql3.23-4.1 and has been replaced by FORMAT_DESCRIPTION_EVENT since MYSQL5.0
QUERY_EVENT This event is generated when an update statement is executed, including: create, INSERT, update, delete;
STOP_EVENT This event is generated when mysqld stops
ROTATE_EVENT This event is generated when mysqld switches to a new binlog file. Switching to a new binlog file can be done by executing flush logs or the binlog file is greater thanmax_binlog_sizeThe size of the parameter configuration;
INTVAR_EVENT When the SQL statement uses an AUTO_INCREMENT column or a 1436753 function; This event is not used when binlog_format is ROW mode
LOAD_EVENT This event is generated when the LOAD DATA INFILE statement is executed. Used in MySQL 3.23
SLAVE_EVENT Don’t use
CREATE_FILE_EVENT This event is generated when the LOAD DATA INFILE statement is executed, used in MySQL4.0 and 4.1
APPEND_BLOCK_EVENT This event is generated when the LOAD DATA INFILE statement is executed, used in MySQL4.0
EXEC_LOAD_EVENT This event is generated when the LOAD DATA INFILE statement is executed, used in MySQL4.0 and 4.1
DELETE_FILE_EVENT This event is generated when the LOAD DATA INFILE statement is executed, used in MySQL4.0
NEW_LOAD_EVENT This event is generated when the LOAD DATA INFILE statement is executed, used in MySQL4.0 and 4.1
RAND_EVENT This event is generated by executing a statement containing the RAND() function and is not used when binlog_format is ROW mode
USER_VAR_EVENT This event is generated when the statement containing the user variable is executed. This event is not used when binlog_format is ROW mode
FORMAT_DESCRIPTION_EVENT Description events, written at the beginning of each binlog file, are used in versions after MySQL5.0 instead of START_EVENT_V3
XID_EVENT The storage engine that supports XA is only available. The database storage engine tested locally is InnoDB, so XID_EVENT appears on it. Innodb transaction commits generate QUERY_EVENT BEGIN statements, QUERY_EVENT statements, and COMMIT statements. MyIsam storage engine also has BEGIN and COMMIT statements, but the COMMIT type is not XID_EVENT
BEGIN_LOAD_QUERY_EVENT This event is generated when the LOAD DATA INFILE statement is executed, used in MySQL5.0
EXECUTE_LOAD_QUERY_EVENT This event is generated when the LOAD DATA INFILE statement is executed, used in MySQL5.0
TABLE_MAP_EVENT Used in binlog_format ROW mode to map the table definition to a number recorded before the ROW action event (including: WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, DELETE_ROWS_EVENT)
PRE_GA_WRITE_ROWS_EVENT Expired and replaced by WRITE_ROWS_EVENT
PRE_GA_UPDATE_ROWS_EVENT Expired and replaced by UPDATE_ROWS_EVENT
PRE_GA_DELETE_ROWS_EVENT Expired and replaced by DELETE_ROWS_EVENT
WRITE_ROWS_EVENT When binlog_format is ROW, it corresponds to insert operations
UPDATE_ROWS_EVENT This parameter is used when binlog_format is ROW, corresponding to the update operation
DELETE_ROWS_EVENT This parameter is used when binlog_format is ROW, corresponding to the delete operation
INCIDENT_EVENT The master server notifies the slave server of an abnormal event that may cause data to be in an inconsistent state
HEARTBEAT_LOG_EVENT The master server tells the slave server that the master is alive and not written to the log file

The structure of the Binlog event

An event object is divided into an event header and an event body. The event structure is as follows:

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    |
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    |
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
|        +----------------------------+
|        | extra_headers    19 : x-19 |
+=====================================+
| event  | fixed part        x : y    |
| data   +----------------------------+
|        | variable part              |
+=====================================+
Copy the code

If the length of the event header is x bytes, the length of the event body is (event_length-x) bytes; Fixed part (y); variable Part (event_length – (x + y))

Binlog Event Analysis

Analyze events from a simplest instance, including creating tables, inserting data, updating data, and deleting data;

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL.`name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
insert into test values(1.22."Little whirl");
update test set name='whirly' where id=1;
delete from test where id=1;
Copy the code

The log format is STATEMENT. View all events

When the log format is ROW, it looks like this, and you’ll notice some differences

As for the analysis of events, you can refer to the reference documents for calculation if necessary.

Reference documentation

  • MySQL 5.7 Reference Manual. Binary log
  • MySQL Internals Manual.The Binary Log
  • MySQL Binlog MySQL Binlog
  • MySQL binlog format
  • Mysql > restore data using binlog
  • MySql Binlog met
  • MySQL5.7 killer new feature: GTID principle and actual combat
  • MySQL 5.7 master/slave replication practices based on GTID