In-depth explanation of binlog in MySQL 8.0

1 Introduction

Binary log records SQL statements that have or may change data and saves them in binary form on disk.

2. The role of Binlog

Main functions: replication, recovery and auditing.

3 Enable Binlog

3.1 Check whether the current MySQL supports binlog

The following OFF means not supported

3.2 Modify the my.cnf file to support binlog

View my.cnf path

mysql --help --verbose | grep my.cnf

Create a new file my.cnf in /etc and add the following content. Note: add the mysqld group

Restart MySQL

Check again whether binlog is supported

3 binlog management commands

show master logs

View the list of all Binlog logs.

show master status View the binlog status. View the number name of the last Binlog log and the position (pos) where the last event ends

flush logs

Refresh the binlog log file. After refreshing, a new Binlog log file will be created.

reset master

Clear all binlog log files

View binlog log files

mysqlbinlog mysql-bin.000002

4 Binlog related variables

log_bin

Binlog switch.

show variables like 'log_bin';

View the variable:

show variables like 'log_bin'; 

binlog_format

Binlog log format.

View variables:

show variables like 'binlog_format'; 

5 Binlog log format

ROW

Only the modified record details are saved, and the SQL statement context related information is not recorded.

advantage

The binlog does not need to record the context-related information of the executed SQL statement. It only needs to record what the record has been modified to. Therefore, the rowlevel log content will clearly record the details of each row of data modification. And there will be no problem that the stored procedure, function, or trigger call and trigger cannot be correctly copied in certain situations

shortcoming

When all executed statements are recorded in the log, they will be recorded as changes to each row of records. This may generate a large amount of log content. For example, if an update statement modifies multiple records, each modification will be recorded in the binlog, which will cause a large amount of binlog logs. Especially when executing statements such as alter table, each record will be changed due to the modification of the table structure, so each record of the table will be recorded in the log.

STATEMENT

Every SQL statement that modifies data will be recorded in Binlog.

advantage

No need to record every row change, which reduces the amount of binlog logs, saves IO, and improves performance. How much performance and log volume can be saved compared to row depends on the SQL situation of the application. Normally, the amount of log generated by modifying or inserting the same record in row format is less than the amount of log generated by statement. However, considering the update operation with conditions, the deletion of the entire table, the alter table and other operations, the ROW format will generate a large amount of logs. Therefore, when considering whether to use ROW format logs, you should consider the actual situation of the application, how much the amount of logs generated will increase, and the IO performance issues it will bring.

shortcoming

Since only the executed statements are recorded, in order for these statements to run correctly on the slave, some relevant information of each statement during execution must also be recorded to ensure that all statements can obtain the same results on the slave as when executed on the master. In addition, MySQL replication, such as some specific functions, the slave can be consistent with the master, which will cause many related problems (such as sleep() function, last_insert_id(), and user-defined functions (udf)).

MIXED

A combination of the above two levels. General statement modifications use the statement format to save binlogs. For example, some functions and statements cannot complete the master-slave replication operation, so the row format is used to save binlogs. MySQL will distinguish the log format to be recorded according to each specific SQL statement executed, that is, choose between Statement and Row. The row level mode of the new version of MySQL has also been optimized. Not all modifications will be recorded at the row level. For example, when the table structure changes, it will be recorded in statement mode. As for statements that modify data, such as update or delete, changes to all rows will still be recorded.

Binlog log format selection

Mysql uses the Statement log format by default, and MIXED is recommended.

Due to some special uses, you can consider using ROWED, such as synchronizing data modifications through binlog logs, which will save a lot of related operations. Binlog data processing becomes very easy, and parsing is also very easy compared to mixed data (of course, the premise is that the IO overhead caused by the increased log volume is within the tolerable range).

mysqlbinlog format selection

MySQL's selection principle for log format: If you use INSERT, UPDATE, DELETE and other direct table operations, the log format is recorded according to the setting of binlog_format. If you use management statements such as GRANT, REVOKE, SET PASSWORD, then SBR mode is used for recording anyway.

6 View Binlog-related SQL

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

View the first Binlog log

show binlog events; 

View the specified Binlog log

show binlog events in 'mysql-bin.000001'; 

Start from the specified location and view the specified Binlog log

show binlog events in 'mysql-bin.000001' from 666;

Start from the specified location, view the specified Binlog log, and limit the number of queries

show binlog events in 'mysql-bin.000001' from 666 limit 2;

Start from the specified position, with an offset, view the specified Binlog log, and limit the number of entries to be queried

show binlog events in 'mysql-bin.000001' from 666 limit 1, 2;

7 Binlog Column Description

Event_type

• QUERY_EVENT Operations not related to data, such as begin, drop table, truncate table, etc.
• TABLE MAP EVENT records the table information corresponding to the next operation, storing the database name and table name
• XID_EVENT marks transaction commit
• WRITE ROWS EVENT inserts data, i.e., insert operation
• UPDATE ROWS EVENT updates data, that is, update operation
• DELETE ROWS EVENT deletes data, that is, delete operation

refer to

https://www.jb51.net/article/197048.htm

Summarize

This is the end of this article about the in-depth explanation of binlog in MySQL 8.0. For more relevant MySQL 8.0 binlog explanations, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!