Author: Hu Chengqing

Member of aikesheng DBA team, good at fault analysis and performance optimization, personal blog: https://www.jianshu.com/u/a95... , welcome to the discussion.

Source: original contribution

*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.

Common commands

1. Parse binlog to troubleshoot problems

If you just parse it and view it, you can add -- Base64 output = decode rows to not display the contents in line format: mysqlbinlog -- no defaults - VV -- Base64 output = decode rows MySQL bin 000201

2. Resolve the transaction with the specified GTID

Used to analyze what a transaction does: mysqlbinlog -- no defaults - VV -- Base64 output = decode rows -- include gtids ='b0ca6715-7554-11ea-a684-02000aba3dad: 614037 'MySQL bin 000199

3. Parse binlog in the specified range

a. Time range

--Start datetime and -- stop datetime parse the binlog within the specified time range. This is only suitable for rough parsing and is not accurate. Therefore, it should not be used to replay the binlog. A tip: if you can only determine the approximate time range and are not sure which binlog is in, you can directly parse multiple binlogs. For example, a table deletion operation is performed between 11:20 and 12:00, but there are multiple binlogs at this time. You can do the following:

mysqlbinlog --no-defaults -vv --base64-output=decode-rows --start-datetime='2020-08-18 11:20:00' --stop-datetime='2020-08-18 12:00:00' mysql-bin.000203 mysql-bin.000204 mysql-bin.000205

b. Offset range

--Start position, -- stop position resolves binlog within the specified offset range. If -- start position and -- stop position are specified at the same time, and multiple binlogs are resolved, then -- start position is only effective for the first binlog and - stop position is only effective for the last binlog.

This common scenario is: after the binlog has been parsed once and the starting position of the target transaction is obtained, this section of binlog is parsed accurately:

mysqlbinlog --no-defaults -vv --base64-output=decode-rows  --start-position='537' --stop-position='945' mysql-bin.000204
# at 537 "the starting position is the position before GTID event"
#200818 11:29:03 server id 3 end_log_pos 602 CRC32 0x7f07dd8c GTID last_committed=1 sequence_number=2 rbr_only=yes 
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; 
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614061'/*!*/; 
...
... 
#200818 11:29:03 server id 3 end_log_pos 945 CRC32 0xedf2b011 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1597721343/*!*/; 
COMMIT /*!*/; 
# at 945 "the end position is the position after the COMMIT event"

c. GTID range

--For include gtids and -- exclude gtids, please refer to the parameter explanation for details.

4. Playback binlog

• The -- Base64 output = decode rows parameter must not be added for playback, because the travel format will not be parsed (this is the real effective part of binlog);
• Playback can also use the parameters within the range specified above;
• When parsing binlog and playing back to this instance, you do not need to modify the server id, but pay attention to whether the GTID already exists;
• Gtids already exist. Playback will not report errors, but these transactions will not be played back. You can skip the GTID limit through the -- skip gtids parameter;

mysqlbinlog --no-defaults --skip-gtids mysql-bin.000203 | mysql -S /data/mysql/data/3306/mysqld.sock -proot

Parameter interpretation

1. --no-defaults

Can avoid my CNF is configured with [client] some parameters that mysqlbinlog does not have, resulting in the failure of mysqlbinlog

2. -v

No, only the line format (that is, the string) is displayed, and the pseudo SQL cannot be obtained:

Add - v to reconstruct the pseudo SQL from the row format (with comments), and binlog is not displayed_ rows_ query_ log_ Effects of events parameter:

Add - vv to reconstruct the pseudo SQL from the row format and add comments on the field data type to display binlog_ rows_ query_ log_ Effects of events parameter:

3. Add -- Base64 output = decode rows

The line format is not displayed. If the - v parameter is added at the same time, it can be decoded from the line format into annotated pseudo SQL:

4. --skip-gtids

The GTID event information is not reserved. In this way, when playing back binlog, a new GTID will be generated as if a new transaction was executed. The comparison is as follows:

5. --include-gtids

Resolve only transactions with the specified GTID:

[root@localhost 3306]# mysqlbinlog --no-defaults -vv --base64-output=decode-rows \
> --include-gtids='b0ca6715-7554-11ea-a684-02000aba3dad:614037-614040' mysql-bin.000199 |grep GTID
#200807 17:32:17 server id 2  end_log_pos 194 CRC32 0xc840be04  Previous-GTIDs
#200807 17:32:17 server id 2  end_log_pos 3818435 CRC32 0x9fdea913  GTID    last_committed=3    sequence_number=5   rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614037'/*!*/;
#200807 17:32:17 server id 2  end_log_pos 5726909 CRC32 0x51b51cc1  GTID    last_committed=4    sequence_number=6   rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614038'/*!*/;
#200807 17:32:17 server id 2  end_log_pos 5727523 CRC32 0x758852f1  GTID    last_committed=6    sequence_number=7   rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614039'/*!*/;
#200807 17:32:17 server id 2  end_log_pos 7635997 CRC32 0x47c43f83  GTID    last_committed=6    sequence_number=8   rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614040'/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

1. --exclude-gtids

The transaction for the specified GTID is not resolved