Parsing MySQL binlog

1. Introduction to binlog

binlog is binary log, binary log file. It records all DDL and DML statements executed by the database (except data query statements such as select and show), records them in the form of events and saves them in binary files.

Binlog has two main application scenarios. One is for replication. The master passes its binary log to the slaves to achieve master-slave data consistency. The second is for data recovery. For example, after restoring a backup, you can re-execute the newly generated binlog after the backup to keep the database up to date. In addition to these two main uses, binlog can be used for data interaction between heterogeneous systems. Binlog completely saves the preceding and succeeding records of a record. DTS services can be used to extract MySQL data in a near-real-time manner to underlying data platforms, such as HBase, Hive, Spark, etc., to connect OLTP and OLAP.

There are three modes for binlog logs: STATEMENT , ROW , and MIXED . The following is a brief introduction to these three modes:

• STATEMENT : Based on SQL statement replication, each SQL statement that modifies data will be recorded in the binlog. The amount of binlog logs generated in this mode will be relatively small, but it may cause inconsistency between master and slave data.
• ROW : Row-based replication does not record every specific SQL statement executed, but only records which data is modified and its appearance before and after the modification. The amount of binlog logs generated in this mode will be relatively large, but the advantage is that it will clearly record the details of each row of data modification, and the master-slave replication will not go wrong.
• Mixed : Mixed mode replication, a combination of the above two modes. General replication uses the STATEMENT mode to save binlogs. For operations that cannot be replicated in the STATEMENT mode, the ROW mode is used to save binlogs. MySQL will select the log saving method based on the executed SQL statement.

The default binlog mode is STATEMENT before MySQL 5.7.7, and ROW in later versions. It is recommended to use ROW mode here because ROW mode is safer and can clearly record the details of each row of data modification.

2. Binlog related parameters

Binlog is not enabled by default, but in general, it is recommended to enable binlog by adding the log-bin parameter to the configuration file during initialization.

# Add log-bin configuration to the configuration file [mysqld]
log-bin = binlog

# If you do not specify a path, the default is in the data directory. You can also specify a path [mysqld]
log-bin = /data/mysql/logs/binlog

# Check whether binlog is enabled in the database
show variables like 'log_bin%';

After enabling binlog, you need to pay attention to some binlog-related parameters. The following is a brief introduction to the relevant parameters:

binlog_format
Set the binlog mode. It is recommended to set it to ROW.

binlog_do_db
This parameter means that only the binary log of the specified database is recorded. By default, all logs are recorded. It is generally not recommended to change it.

binlog_ignore_db
This parameter indicates that the binary log of the specified database is not recorded. As above, it is generally not explicitly specified.

expire_logs_days
This parameter controls the number of days to retain binary log files. The default value is 0, which means no automatic deletion. It can be set to 0~99. It can be set according to actual situation, such as retaining for 15 days or 30 days. In MySQL 8.0, the binlog_expire_logs_seconds parameter can be used instead.

max_binlog_size
Controls the size of a single binary log. When the current log file size exceeds this variable, a switch action is executed. The maximum and default value of this parameter is 1GB. This setting cannot strictly control the size of Binlog, especially when Binlog is close to the maximum value and encounters a relatively large transaction. In order to ensure the integrity of the transaction, it is impossible to switch the log. All SQL statements of the transaction can only be recorded in the current log until the transaction ends. The default value can usually be used.

log_bin_trust_function_creators
This parameter is enabled when binary logging is enabled. It controls whether stored function creators can be trusted not to create stored functions that write to the binary log and cause unsafe events. If set to 0 (the default), users may not create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. The recommended setting is 1.

sync_binlog
Controls how often the MySQL server synchronizes binary logs to disk. The default value is 1.
If set to 0, MySQL does not control the flushing of binlogs, and the file system itself controls the flushing of its cache.
If set to 1, MySQL will flush the binlog for each transaction submission. This is the safest setting, but it may have a negative impact on performance due to the increase in the number of disk writes.
Set to n, where n is a value other than 0 or 1. After n transactions are committed, MySQL will execute a disk synchronization instruction such as fsync to refresh the Binlog file cache to disk.
The recommended setting is 1, but it can be adjusted for performance reasons.

There are also many SQL statements related to binlog operations and management. The following are some commonly used statements:

3. Analyze binlog content

As mentioned earlier, all modifications to the database will be recorded in binglog. However, binlog is a binary file and cannot be viewed directly. If you want to observe it more intuitively, you must use the mysqlbinlog command tool. The following content mainly introduces how to use mysqlbinlog to parse binlog log content.

For the smooth development of the story, we first switch to binlog, then create a test library and test table, insert data, and update data. These pre-operations are not shown for now. Let's see how to parse and view the generated binlog content:

# This analysis is based on MySQL 8.0, the instance has gtid enabled, and the mode is ROW

[root@centos logs]# mysqlbinlog --no-defaults --base64-output=decode-rows -vv binlog.000013
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
...
...
#200708 16:52:09 server id 1003306 end_log_pos 1049 CRC32 0xbcf3de39 Query thread_id=85 exec_time=0 error_code=0 Xid = 1514
use `bindb`/*!*/;
SET TIMESTAMP=1594198329/*!*/;
SET @@session.explicit_defaults_for_timestamp=1/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `bin_tb` (
  `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
  `stu_id` int(11) NOT NULL COMMENT 'Student ID',
  `stu_name` varchar(20) DEFAULT NULL COMMENT 'Student name',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',
  PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Test binlog'
/*!*/;
# at 1049
#200708 16:52:45 server id 1003306 end_log_pos 1128 CRC32 0xf19ea0a9 GTID last_committed=2 sequence_number=3 rbr_only=yes original_committed_timestamp=1594198365741300 immediate_commit_timestamp=1594198365741300 transaction_length=468
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1594198365741300 (2020-07-08 16:52:45.741300 CST)
# immediate_commit_timestamp=1594198365741300 (2020-07-08 16:52:45.741300 CST)
/*!80001 SET @@session.original_commit_timestamp=1594198365741300*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= '0032d819-2d32-11ea-91b5-5254002ae61f:24883'/*!*/;
# at 1128
#200708 16:52:45 server id 1003306 end_log_pos 1204 CRC32 0x5b4b03db Query thread_id=85 exec_time=0 error_code=0
SET TIMESTAMP=1594198365/*!*/;
BEGIN
/*!*/;
# at 1204
#200708 16:52:45 server id 1003306 end_log_pos 1268 CRC32 0xd4755d50 Table_map: `bindb`.`bin_tb` mapped to number 139
# at 1268
#200708 16:52:45 server id 1003306 end_log_pos 1486 CRC32 0x274cf734 Write_rows: table id 139 flags: STMT_END_F
### INSERT INTO `bindb`.`bin_tb`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=1001 /* INT meta=0 nullable=0 is_null=0 */
### @3='from1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### INSERT INTO `bindb`.`bin_tb`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=1002 /* INT meta=0 nullable=0 is_null=0 */
### @3='dfsfd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
...
# at 1486
#200708 16:52:45 server id 1003306 end_log_pos 1517 CRC32 0x0437e777 Xid = 1515
COMMIT /*!*/;
...
# at 1596
#200708 16:54:35 server id 1003306 end_log_pos 1681 CRC32 0x111539b6 Query thread_id=85 exec_time=0 error_code=0
SET TIMESTAMP=1594198475/*!*/;
BEGIN
/*!*/;
# at 1681
#200708 16:54:35 server id 1003306 end_log_pos 1745 CRC32 0x6f0664ee Table_map: `bindb`.`bin_tb` mapped to number 139
# at 1745
#200708 16:54:35 server id 1003306 end_log_pos 1939 CRC32 0xfafe7ae8 Update_rows: table id 139 flags: STMT_END_F
### UPDATE `bindb`.`bin_tb`
###WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=1005 /* INT meta=0 nullable=0 is_null=0 */
### @3='dsfsdg' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=1005 /* INT meta=0 nullable=0 is_null=0 */
### @3=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198475 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### UPDATE `bindb`.`bin_tb`
###WHERE
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2=1006 /* INT meta=0 nullable=0 is_null=0 */
### @3='fgd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### SET
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2=1006 /* INT meta=0 nullable=0 is_null=0 */
### @3=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198475 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
...
# at 1939
#200708 16:54:35 server id 1003306 end_log_pos 1970 CRC32 0x632a82b7 Xid = 1516
COMMIT /*!*/;
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*/;

# It can be seen that the binlog records in detail the changes caused by the execution of each SQL statement.
And it includes system values ​​such as execution time, pos location, server_id, etc.

There are many more tips for using the mysqlbinlog tool, such as only parsing operations on a certain database or within a certain time period. Here are some common statements. For more information, please refer to the official documentation.

mysqlbinlog --no-defaults --base64-output=decode-rows -vv binlog.000013 > /tmp/bin13.sql
Import the parsed SQL into the file

mysqlbinlog --no-defaults --base64-output=decode-rows -vv --database=testdb binlog.000013
Only parse operations of a certain library

mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-datetime="2020-01-11 01:00:00" --stop-datetime="2020-01-11 23:59:00" binlog.000008
Analyze operations within a specified time period

mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-position=204136360 --stop-position=204136499 binlog.000008
Parse the operation within the specified pos position

mysqlbinlog --no-defaults --start-position=204136360 --stop-position=204136499 binlog.000008 | mysql -uroot -pxxxx testdb
Restore operations between specified sites in a specified library

IV. Conclusion

This article has been quite long without me noticing. It describes various binlog-related knowledge points. I hope you will have a deeper understanding of binlog after reading it. In fact, the most important thing is practice. Only by learning more and using more can you master it better. This is such hard-core knowledge, I hope you can read it when you need it. You are welcome to forward and share it so that more people can see it.

The above is the detailed content of parsing MySQL binlog. For more information about MySQL binlog, please pay attention to other related articles on 123WORDPRESS.COM!