MySQL binlog commonly used combat commands

MySQL is a write-ahead log method that provides atomicity and durability. For the service layer binlog, it is often used in daily troubleshooting and analysis.
Sorted out practical related commands. Hope it helps in work.

There are two types of mysqlbinlog related commands, mysql client commands and execution files.

Client commands: binlog related SHOW and PURGE commands
are used on the RDS cloud server. Now it is restricted to log in to the server.

SHOW command introduction:

SHOW BINARY LOGS;SHOW MASTER LOGS;#A user with the SUPER or REPLICATION CLIENT privilege may execute this statementmysql> SHOW BINARY LOGS;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |       177 || mysql-bin.000002 |       201 |+------------------+-----------+mysql> SHOW MASTER LOGS;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |       177 || mysql-bin.000002 |       201 |+------------------+-----------+

BINLOG + RELAYLOG EVENT related commands:

##binlog eventSHOW BINLOG EVENTS   [IN 'log_name']   [FROM pos]   [LIMIT [offset,] row_count] ##Relay eventSHOW RELAYLOG EVENTS    [IN 'log_name']    [FROM pos]    [LIMIT [offset,] row_count]    [channel_option]channel_option:    FOR CHANNEL channel

mysql> SHOW BINLOG EVENTS IN'mysql-bin.000087';

image.png

mysql> SHOW BINLOG EVENTS IN'mysql-bin.000087' FROM 1008;

image.png

mysql> SHOW BINLOG EVENTS IN'mysql-bin.000087' FROM 1008 limit 2,5;

image.png
image.png

mysql> SHOW RELAYLOG EVENTS IN'relay-log.000004' FROM 1062 LIMIT 5,2;

image.png

Clear log:

PURGE { BINARY | MASTER } LOGS {    TO 'log_name'  | BEFORE datetime_expr} mysql> PURGE BINARY LOGS TO 'mysql-bin.000080';+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000080 |       194 || mysql-bin.000081 |       194 || mysql-bin.000082 |       194 || mysql-bin.000083 |       217 || mysql-bin.000084 |       408 || mysql-bin.000085 |       517 || mysql-bin.000086 |      7474 |+------------------+-----------+mysql> PURGE BINARY LOGS BEFORE '2020-04-27 10:00:00';Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> SHOW BINARY LOGS;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000086 |      7474 |+------------------+-----------+1 row in set (0.00 sec)

mysqlbinlog command: When executed, it must be in the full path of mysqlbinlog or in the global configuration. Because part of the mysqlbinlog corresponding to each mysql version has been rewritten, there will be differences when different clients resolve it.

1) Parse the command
#mysqlbinlog mysql-bin.000001

image.png

Note: The command will show all changes in the database on the system. But the executed SQL statement is garbled. Shield business data and provide third-party reference.

2) Display the content of the sql statement executed by binlog
#mysqlbinlog --no-defaults --base64-output=decode-row -vv mysql-bin.000001

image.png

Remarks: Full display of SQL statements and related content of this transaction.

3) Use the -d, --database=name option, you can specify a database name, and only display the specified database operation. (Local log only)
#mysqlbinlog --no-defaults -d db1 --base64-output=decode-row -v -v mysql-bin.000001

image.png

Note: Only local log only can be used. Some people think that mysqlbinlog should extract these unused content from the printed results.

4) -D, --disable-log-bin prohibit the recovery process from generating logs
#mysqlbinlog --no-defaults -d db1 --base64-output=decode-row -v -v mysql-bin.000001

image.png

Remarks: The main function is SQL_LOG_BIN=0, no log is recorded, especially when recovering because of poor io performance.

5) -r or then> output file
#mysqlbinlog --no-defaults --base64-output=decode-row -vv mysql-bin.000001> /opt/bin001.sql
#mysqlbinlog --no-defaults --base64- output=decode-row -vv mysql-bin.000001 -r /opt/bin002.sql

6) Specify server-id
#mysqlbinlog --no-defaults --base64-output=decode-row --server-id=1301 -vv mysql-bin.000001
Note: Especially in the case of master-slave replication, by specifying serverid Ways to distinguish binlog providers.

7) Specify the start time, end time or start positon and end position methods, which are frequently used parameters.

-j = --start-position has the same effect:

#mysqlbinlog --no-defaults --base64-output=decode-row -vv --start-position=123 --stop-position=290 mysql-bin.000001
#mysqlbinlog --no-defaults --base64-output= decode-row -vv -j 123 --stop-position=290 mysql-bin.000001

time method:

#mysqlbinlog --no-defaults --base64-output=decode-row -vv --start-datetime='2020-05-02 22:36:46 '--stop-datetime='2020-05-02 23: 25:46' mysql-bin.000001

8) Read from a remote server: The R option is the same as -read-from-remote-server, and the specified server mysql service must exist.

  • The -R option instructs the mysqlbinlog command to read log files from the remote server
  • -h specifies the ip address of the remote server
  • -p will prompt for a password. By default, it will use "root" as the username. You can also use the -u option to specify the user name.
  • -P uppercase P shows the mysql service port.
  • mysqld-bin.000001 This is the name of the binary log file of the remote server read here.

#mysqlbinlog --no-defaults -R -h 192.168.244.130 -uroot -p123456 -P3410 --base64-output=decode-row -vv mysql-bin.000001

9) --skip-gtids do not show any GTID in the output
#mysqlbinlog --no-defaults --base64-output=decode-row -vv --skip-gtids mysql-bin.000001

Remarks: There is no gtid information in this statement, so it is convenient to repeat the log on any server.

10) include-gtids and exclude-gtids

  • include-gtids
    specifies the gtid to be rolled back, and supports both single and range gtid forms.
  • exclude-gtids
    specifies the gtids that do not need to be rolled back, and the usage is the same as include-gtids

#mysqlbinlog --no-defaults --base64-output=decode-row -vv -t mysql-bin.000001 --exclude-gtids='39d0a7f2-702c-11ea-92a0-000c29b9a76d:1-2'

image.png

#mysqlbinlog --no-defaults --base64-output=decode-row -vv -t mysql-bin.000001 --include-gtids='39d0a7f2-702c-11ea-92a0-000c29b9a76d:1-2'

image.png

11) –rewrite-db='from_name->to_name' Rewrite all the corresponding names of the database from_name to to_name.
#mysqlbinlog --no-defaults --base64-output=decode-row -vv -f --rewrite-db='db1->db11' mysql-bin.000001

image.png

Remarks: In multi-source replication scenarios, use binlog to restore

12) --force-read, -f If mysqlbinlog reads an unrecognized binary log event, it will print a warning, ignore the event and continue. Without this option, mysqlbinlog will stop when reading such events
#mysqlbinlog --no-defaults --base64-output=decode-row -vv -f mysql-bin.000001

Remarks: Occasionally, failures may cause the log writing stage, and mysqlbinlog cannot analyze the log.