mysql binlog to get started

There are generally the following types of logs in MySQL:

Log typeInformation written to the log
Error logRecord the problems encountered when starting, running or stopping mysqld
General query logRecord established client connections and executed statements
Binary log (binlog)Record the statement that changes the data
Relay logData changes received from the replication master server
Slow query logLog all queries whose execution time exceeds long_query_time seconds or queries that do not use indexes
DDL log (metadata log)Metadata operations are performed by DDL statements

This article mainly introduces the binary log binlog.

MySQL's binary log binlog can be said to be the most important log of MySQL, it records all DDL and DML statements (except data query statements select, show, etc.), recorded in the form of events, and also contains the time consumed by statement execution. MySQL The binary log is transaction-safe. The main purpose of binlog is to copy and restore.

The two most important usage scenarios of Binlog logs

MySQL master-slave replication : MySQL Replication opens binlog on the master side, and the master passes its binary log to slaves to achieve the purpose of master-slave data consistency.
Data recovery : restore data by using the mysqlbinlog tool


MySQL version used in this article

Generally speaking, there will be a performance loss of 1% when binlog is turned on.

To enable binlog, configure the log-bin option of the /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf configuration file:

Adding the log-bin configuration to the configuration file means that binlog is enabled. If there is no given value, write log-bin=, 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 taken as the name)


The binlog log includes two types of files:

  • Binary log index file (file name suffix .index) is used to record all valid binary files
  • Binary log file (file name suffix .00000*) records all DDL and DML statement events in the database

When encountering the following 3 situations, MySQL will regenerate a new log file, and the file serial number will increase:

  • When the MySQL server is stopped or restarted;
  • Use flush logs command;
  • When the binlog file size exceeds the value of the max_binlog_size variable;

Binlog log format

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

  • STATEMENT: SQL statement-based replication (statement-based replication, SBR)
  • ROW: Row-based replication (RBR)
  • MIXED: Mixed-based replication (MBR)

Before MySQL 5.7.7, the default format was STATEMENT, and in MySQL 5.7.7 and later, the default value was ROW. The log format is specified by binlog-format, such as binlog-format=STATEMENT, binlog-format=ROW, binlog-format=MIXED.

  • Statement

Each sql that will modify the data will be recorded in the binlog

Advantages: There is no need to record the changes of each line, reducing the amount of binlog, saving IO, and improving performance.

Disadvantages: Since only the executed statements are recorded, in order for these statements to run correctly on the slave, it is necessary to record some relevant information when each statement is executed to ensure that all statements can be obtained on the slave and executed on the master side. The same result. In addition, mysql replication, like the functions of some specific functions, there are many related problems in keeping the slave and master consistent.

  • Row

MySQL version 5.1.5 only started to support row level replication. It does not record the context-related information of the sql statement, but only saves which record has been modified.

Advantages: Binlog does not need to record the context-related information of the executed sql statement, but only needs to record what the record has been modified into. Therefore, the log content of row will record the details of each row of data modification very clearly. And there will be no problems that stored procedures, or functions, and trigger calls and triggers cannot be copied correctly under certain circumstances.

Disadvantages: When all executed statements are recorded in the log, they will be recorded with the modification of each line record, which may generate a large amount of log content.

Note: When the binary log format is set 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 from version 5.1.8, MySQL provides a Mixed format, which is actually a combination of Statement and Row.
In Mixed mode, the general statement modification uses the statment format to save the binlog. For some functions, the statement cannot complete the master-slave replication operation, and the row format is used to save the binlog. MySQL will treat the records according to each specific SQL statement executed. The log format is to choose one between Statement and Row.


1. Check whether the binlog log is turned on

show global variables like "%log_bin%";

2. Configure mysql to enable binlog log

vi /etc/my.cnf

server_id=2 log_bin = mysql-bin binlog_format = ROW expire_logs_days = 30 

server_id: Randomly specify a string that cannot have the same name as other machines in the cluster. If there is only one machine, you can specify it at will.

log_bin: Binlog log rule name

binlog_format: Binlog log format

expire_logs_days: Set the binlog file save event, expired deletion, unit day.

3. Restart mysql

# CentOS 6service mysqld restart  # CentOS 7systemctl restart mysqld 

4. Check whether the opening is successful

show global variables like "%log_bin%";

5. Binlog log file storage path

/Var/lib/mysql of log_bin_basename as shown in the figure above

Then under this path will be generated

mysql-bin.000001

mysql-bin.000002

Such a file

6, the current MySQL binlog situation and view the binlog log

show master status;

As you can see from the above figure, there is currently only one binlog file, the file name is: mysql-bin.000001. Every time we restart, a binlog file will be automatically generated , and we will execute the same command again after the restart. The content is as follows:

There is also such a file in the directory where binlog is stored.

Of course, we can also refresh the binlog file manually. Through flush logs , a new binlog file will also be created. In fact, when the server is restarting, the flush logs operation will also be called.

If we want to clear all these files, we can use reset master to process

Let me look at the operations for a single file. First, we want to look at the contents of the file.

Find the binlog directory, for example, we need to look at mysql-bin.000001

vi mysql-bin.000001

We saw a bunch of garbled codes. We know that this is a bunch of binary files, so opening a binary file as text must be a problem, so how do we view the contents of this file?

mysql provides us with a tool for viewing binlog logs, called mysqlbinlog

mysqlbinlog mysql-bin.000001

This file is relatively long. What should I do if I can't finish it once I open it. You can use the linux pipeline here. I won't go into details here. You can find some knowledge about linux by yourself.

Note that there is a position field in the screenshot below. This field is similar to a pointer. The current value of this field is 154, which represents the current position of binlog. Every time we perform a dml operation , the position will change. For example, let's create a data test first

Before creating it, we can clear the binlog log for us to view, you can use reset master. In a production environment, this operation is very dangerous , so we can use flush logs to process and generate a new binlog file. No matter which method is adopted, we only need a new binlog file in the test environment. After generating the new binlog file, we can check the status through show master status.

Let's execute a dml statement, for example, we want to create a test database

create database test;

Then we look at the state after creation, as follows, we find that the position has changed from 154 to 418, which means that our operation is between 154 and 418, and then we look at the content of binlog.

[[email protected] mysql]# mysqlbinlog mysql-bin.000001

Here you can also see that the three annotated commands such as --show --select are because I executed them through Navicat.

7. Turn on binlog_rows_query_log_events

binlog is a binary log file recorded by the MySQL Server layer. It is used to record MySQL data updates or potential updates (for example, DELETE statement executes deletion but there is no data that meets the conditions). Operations that do not modify data such as select or show will not Recorded in binlog. Usually in  the environment of binlog_format = ROW , we can obtain historical SQL execution records through binlog, provided that the binlog_rows_query_log_events parameter must be turned on  (default is off, and it is recommended to turn on). This parameter can record the original SQL through the rows_query_event event. If it is not turned on, Only the row data corresponding to SQL can be obtained.

vi /etc/my.cnf

binlog_rows_query_log_events=1

8. show binlog events

The show binlog events method can parse the specified binlog log, but it is not suitable for extracting a large number of logs, the speed is very slow, and it is not recommended.

-- 查看最老的binlogshow binlog events -- 查看指定的binlogshow binlog events in 'mysql-bin.000002';  -- IN 'log_name' :指定要查询的binlog文件名(不指定就是第一个binlog文件)-- FROM pos :指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)-- LIMIT [offset,] :偏移量(不指定就是0)-- row_count :查询总条数(不指定就是所有行)show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

9. View the biglog file and size used by the current server

show binary logs

10. Set the binlog file save event, expired deletion, unit day

show global variables like "%expire_logs%"; set global expire_logs_days=3; 

11. max_binlog_size

# 查询max_binlog_size变量show global variables like "%max_binlog_size%";

The minimum value of max_binlog_size is 4096 bytes, and the maximum and default value is 1GB (1073741824 bytes). The transaction is written to a block of binlog, so it will not be split between several binary logs. Therefore, if you have a large transaction, in order to ensure the integrity of the transaction, it is impossible to switch the log. You can only record the transaction log in the current log file. Until the transaction ends, you may see the binlog The case where the file is larger than max_binlog_size.

12. Detailed explanation of binlog log

# 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/*!*/;

The above output includes information:

  • position: The position in the file, that is, the first line (# at 21019), indicating that the event record starts from the 21019th byte of the file
  • timestamp: The timestamp of the event, that is, the second line (#190308 10:10:09)
  • server id: server ID (1)
  • end_log_pos represents the start position of the next event (that is, the end position of the current event + 1)
  • thread_id: the id of the thread executing the event (thread_id=113)
  • exec_time: the time it takes for the event to execute
  • error_code: error code, 0 means no error occurred
  • type: event type Query

13. Binlog event type

There are three main versions of the structure of binlog events:

v1: Use
v3 in MySQL 3.23 : Use
v4 in MySQL 4.0.2 to 4.1: Use in MySQL 5.0 and above Versions below MySQL 5.0
are generally not used, so the following only introduces the binlog event types of the v4 version. There are many types of binlog events, this article will do some simple summary here

Event typeDescription
UNKNOWN_EVENTThis event will never be triggered, nor will it be written to the binlog; when it occurs when the binlog is read, any other event cannot be recognized, it is regarded as UNKNOWN_EVENT
START_EVENT_V3The event written at the beginning of each binlog file, this event is used in MySQL 3.23-4.1, MYSQL 5.0 has been replaced by FORMAT_DESCRIPTION_EVENT
QUERY_EVENTThis event is generated when the update statement is executed, including: create, insert, update, delete;
STOP_EVENTThis event is generated when mysqld is stopped
ROTATE_EVENTWhen mysqld switches to a new binlog file to generate this event, switch to the new binlog file by executing the flush logs command or the binlog file is larger than the size configured by the max_binlog_size parameter;
INTVAR_EVENTWhen the AUTO_INCREMENT field or LAST_INSERT_ID() function is used in the sql statement; this event is not used in the case of binlog_format as ROW mode
LOAD_EVENTThis event is generated when the LOAD DATA INFILE statement is executed. It is used in MySQL 3.23
SLAVE_EVENTUnused
CREATE_FILE_EVENTThis event is generated when the LOAD DATA INFILE statement is executed. It is used in MySQL 4.0 and 4.1
APPEND_BLOCK_EVENTThis event is generated when the LOAD DATA INFILE statement is executed. It is used in MySQL 4.0
EXEC_LOAD_EVENTThis event is generated when the LOAD DATA INFILE statement is executed. It is used in MySQL 4.0 and 4.1
DELETE_FILE_EVENTThis event is generated when the LOAD DATA INFILE statement is executed. It is used in MySQL 4.0
NEW_LOAD_EVENTThis event is generated when the LOAD DATA INFILE statement is executed. It is used in MySQL 4.0 and 4.1
RAND_EVENTThis event is generated when the statement containing the RAND() function is executed. This event is not used when binlog_format is in ROW mode.
USER_VAR_EVENTThis event is generated when a statement containing user variables is executed. This event is not used when binlog_format is in ROW mode.
FORMAT_DESCRIPTION_EVENTDescribe the event, written at the beginning of each binlog file, used in MySQL 5.0 and later versions, instead of START_EVENT_V3
XID_EVENTOnly storage engines that support XA are available. The database storage engine tested locally is innodb, and XID_EVENT appears on all of them; innodb transaction submission produces BEGIN statements of QUERY_EVENT, QUERY_EVENT and COMMIT statements, and if it is myIsam storage engine, there will also be BEGIN and COMMIT statements. , But the COMMIT type is not XID_EVENT
BEGIN_LOAD_QUERY_EVENTThis event is generated when the LOAD DATA INFILE statement is executed. It is used in MySQL 5.0
EXECUTE_LOAD_QUERY_EVENTThis event is generated when the LOAD DATA INFILE statement is executed. It is used in MySQL 5.0
TABLE_MAP_EVENTUsed in binlog_format as ROW mode, the definition of the table is mapped to a number, which is recorded before the row operation event (including: WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, DELETE_ROWS_EVENT)
PRE_GA_WRITE_ROWS_EVENTExpired, replaced by WRITE_ROWS_EVENT
PRE_GA_UPDATE_ROWS_EVENTExpired, replaced by UPDATE_ROWS_EVENT
PRE_GA_DELETE_ROWS_EVENTExpired, replaced by DELETE_ROWS_EVENT
WRITE_ROWS_EVENTUsed when binlog_format is ROW mode, corresponding to insert operation
UPDATE_ROWS_EVENTUsed when binlog_format is ROW mode, corresponding to update operation
DELETE_ROWS_EVENTUsed when binlog_format is ROW mode, corresponding to delete operation
INCIDENT_EVENTAn abnormal event occurred on the master server, notify the slave server and inform that it may cause the data to be in an inconsistent state
HEARTBEAT_LOG_EVENTThe master server tells the slave server that the master server is still alive and will not be written to the log file

14. The structure of Binlog events

+=====================================+| 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              |+=====================================+

If the length of the event header is  x bytes, then the length of the event body is  (event_length - x) bytes;  if  the length in fixed part the event body  is  ybytes, then  variable partthe length is  (event_length - (x + y)) bytes

15. Summary

1. The binlog file will create a new file with the start of the service

2. Through flush logs, you can manually refresh the log and generate a new binlog file

3. You can view the status of binlog through show master status

4. Pass reset master binlog log file can be cleared

5. The contents of the binlog log can be viewed through the mysqlbinlog tool

6. By executing dml, mysql will automatically record binlog

7. Format the binlog log

mysqlbinlog --no-defaults  --base64-output=decode-rows -v --start-datetime='2019-04-11 00:00:00' --stop-datetime='2021-06-11 15:00:00'  mysql-bin.000001 |more

8. Common format binlog commands

-- 个人常用的 Linux 下解析命令: mysqlbinlog /data/mysql_data/bin.000008 --database EpointFrame --base64-output=decode-rows -vv --skip-gtids=true |grep -C 1 -i "delete from Audit_Orga_Specialtype" > /opt/sql.log 

/data/mysql_data/bin.000008: The binlog log to be parsed.

database: Only the row data under the database is listed, but Rows_query_event cannot be filtered.

base64-output=decode-rows -vv: Display specific SQL statements.

skip-gtids=true: Ignore GTID display.

grep -C 1 -i "delete from dataex_trigger_record": Filter out the required SQL and execution time through pipeline commands.

/opt/sql.log: Import the results to the log file for easy viewing.

Tips:

1. If you are unsure of the SQL format or unable to filter the data, for example, because delete from is a little more than a space in the middle, you can use grep to filter and filter multiple times, such as **grep -C 1 -i "Rows_query" |grep -C 1 -i "Audit_Orga_Specialtype" |grep -C 1 -i "delete" **Filter the delete operation on the corresponding table.

2. The SQL executed by the trigger will not be recorded in Rows_query_event, only the corresponding row data will be recorded.

  1. --database is not filter Rows_query_event, just to filter rows of data.

3. Comparison of analytical methods

For common databases ( SQL Server, Oracle, MySQL ), they all have similar logs to record historical SQL. The only difference is the log recording method and parsing method:

16, mysql view the sql statement being executed and view the historical sql statement that has been executed

One, mysql view the SQL statement being executed

show processlist;

Two, mysql view the historical SQL statements that have been executed (method: open the log mode)

SET GLOBAL log_output = 'TABLE';SET GLOBAL general_log = 'ON';  //日志开启

You enter the statement you want to check, such as select * from iot.tb_iot;

After querying the sql statement, there is a corresponding log record in the corresponding /var/lib/mysql/mysql/general_log.CSV

SET GLOBAL log_output ='TABLE'; SET GLOBAL general_log ='OFF'; //Log off

Note: After querying the required records, you should close the log mode as soon as possible, which will take up a lot of disk space