Use of mysqlbinlog command tool

Use syntax
mysqlbinlog [options] log-files

Description of common parameters

parameterdescribe
-?, --helpHelp information
--base64-output=nameOutput format: 'auto': Auto by default‘ never ': output in binlog event format‘ Decode rows': output pseudo SQL in annotation mode
-d, --database=nameList only entries for this database (only local logs)
--bind-addressBound IP address
-h, --host=nameGet the binary log of mysql server of the given host
-p, --password[=name]Password for connection
-u, --userUser connecting to remote server
-P, --port=port
-R, --read-from-remote-serverRead binary log from MySQL server
--rawUsed with - R to output the original binlog log data
--start-datetime=nameStart time of parsing log
--stop-datetime=nameEnd time of parsing log
-j, --start-positionStarting point of parsing log
--stop-position=Parse log end point
-v, --verbosedetailed information
-f, --force-readForce to continue reading. If an unrecognized event occurs, a warning message will be output
-o, --offset=NSkip the first N entries
-s, --short-formOnly the statements contained in the log are displayed, and no other information is displayed
--character-sets-dir=nameCharacter set path information
-S, --socket=namesocket
-D, --disable-log-binDisable binary If you use the - to last logs option to send the output to the same mysql server, you can avoid infinite loops. This option is also useful in crash recovery to avoid copying recorded statements. super requires this option.
-t, --to-last-logIn the mysql server, the end of the requested binary log does not stop, but continues to print until the end of the last binary log. If the output is sent to the same mysql server, it will cause an infinite loop. This option should be -- read from remote server
-F, --force-if-openIt is enabled by default. If binlog is not closed correctly, it is forced to be closed
--connection-server-idServer ID of the slave Library
-H, --hexdumpDisplays hexadecimal and ASCII event dumps in comments
-l, --local-load=nameThe directory specified in the file to prepare the local temporary file for LOAD DATA INFILE
--skip-gtidsSkip GTID
--include-gtids=nameGTID provided by print time
--exclude-gtids=namePrint the GTID provided for everything
--print-table-metadataPrint stored in table_ map_ log_ metadata information of event

Example test

## binlog log file parsing
mysqlbinlog --base64-output=decode-row /data/mysql_log/mysql-bin.000008 -v

## Resolve the specified time
mysqlbinlog --base64-output=decode-row /data/mysql_log/mysql-bin.000008 -v --start-datetime='2022-03-02 14:30:30'

## Analytic point
mysqlbinlog --base64-output=decode-row /data/mysql_log/mysql-bin.000008 -v --start-position=6199 --stop-position=6319
## Parsed file information format
...
....
SET TIMESTAMP=1646202630/*!*/;
BEGIN
/*!*/;
# at 6199
#220302 14:30:30 server id 1033306  end_log_pos 6248 CRC32 0x56411c87   Table_map: `test`.`bb` mapped to number 114
# at 6248
#220302 14:30:30 server id 1033306  end_log_pos 6319 CRC32 0xeb502b34   Write_rows: table id 114 flags: STMT_END_F
### INSERT INTO `test`.`bb`
### SET
###   @1=1
###   @2=16
### INSERT INTO `test`.`bb`
### SET
###   @1=2
###   @2=17
### INSERT INTO `test`.`bb`
### SET
###   @1=3
###   @2=18
### INSERT INTO `test`.`bb`
### SET
###   @1=4
###   @2=20
# at 6319
#220302 14:30:30 server id 1033306  end_log_pos 6350 CRC32 0x9fd4e6c5   Xid = 831
COMMIT/*!*/;

Keywords: Database MySQL

Added by VirtuosiMedia on Tue, 08 Mar 2022 13:54:03 +0200