MySQL的二进制日志（binlog）使用分析（2）——使用mysqlbinlog命令查看日志信息

默认情况下binlog日志是二进制文件，无法直接查看。mysqlbinlog命令的功能是将Mysql的binlog日志转换成Mysql语句。mysqlbinlog命令的格式如下：

1、基本功能

mysqlbinlog 日志文件名;

说明：把日志文件的信息在屏幕上直接显示出来。

命令执行的结果如下：

[root@localhost wgx]# mysqlbinlog /var/lib/mysql/mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200703 11:57:47 server id 2 end_log_pos 123 CRC32 0x4f25334b Start: binlog v 4, server v 5.7.27-log created 200703 11:57:47 at startup ROLLBACK/*!*/; BINLOG ' u6z+Xg8CAAAAdwAAAHsAAAAAAAQANS43LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAC7rP5eEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AUszJU8= '/*!*/; ....... # at 692 #200703 12:05:38 server id 2 end_log_pos 692 CRC32 0x4b642e50 Intvar SET INSERT_ID=1/*!*/; #200703 12:05:38 server id 2 end_log_pos 801 CRC32 0x8929f76f Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1593749138/*!*/; insert into t2(name) values('Jack') /*!*/; # at 801 #200703 12:05:38 server id 2 end_log_pos 832 CRC32 0x6e8cf9f7 Xid = 12 COMMIT/*!*/; # at 832 #200703 12:06:15 server id 2 end_log_pos 879 CRC32 0x3497fb32 Rotate to mysql-bin.000002 pos: 4 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*/;

2、把日志文件的信息导出到文本文件中

使用【-r】参数或重定向符号（>），格式如下：

mysqlbinlog -r 日志文件名 文本文件名; mysqlbinlog 日志文件名 > 文本文件名;

例如：

[root@localhost wgx]# mysqlbinlog /var/lib/mysql/mysql-bin.000001 > /tmp/log_bak001.sql [root@localhost wgx]# mysqlbinlog -r /var/lib/mysql/mysql-bin.000001 /tmp/log_bak002.sql [root@localhost wgx]# ls /tmp/ log_bak001.sql log_bak002.sql

3、分库导出binlog

使用【-d】参数可以导出对某个数据库操作的日志信息，格式如下：

mysqlbinlog -d 数据库名称 日志文件名 > 文本文件名;

例如，导出和【hist】数据库相关的操作日志信息：

[root@localhost wgx]# mysqlbinlog -d hist /var/lib/mysql/mysql-bin.000001 > /tmp/log_bak004.sql [root@localhost wgx]#

4、输出指定位置的日志信息

使用【–start-position】和【–stop-position】参数可以输出【–start-position】和【–stop-position】之间的日志信息。 如果省略【–stop-position】选项，则输出从【–start-position】到结尾的日志信息； 如果省略【–start-position】选项，则输出从开头到【–stop-position】之间的日志信息；

例如： （1）输出1315到1451之间的日志信息：

[root@localhost wgx]# mysqlbinlog --start-position=1315 --stop-position=1451 /var/lib/mysql/mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200703 22:27:21 server id 2 end_log_pos 123 CRC32 0x41949ba4 Start: binlog v 4, server v 5.7.27-log created 200703 22:27:21 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' SUD/Xg8CAAAAdwAAAHsAAAABAAQANS43LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABJQP9eEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AaSblEE= '/*!*/; # at 1315 #200703 22:36:34 server id 2 end_log_pos 1451 CRC32 0x8fb104a1 Query thread_id=4 exec_time=0 error_code=0 use `wgx`/*!*/; SET TIMESTAMP=1593786994/*!*/; SET @@session.pseudo_thread_id=4/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create table t1(id int primary key auto_increment,name char(20)) /*!*/; 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*/;

（2）输出从文件开始到154之间的日志信息

[root@localhost wgx]# mysqlbinlog --stop-position=154 /var/lib/mysql/mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200703 22:27:21 server id 2 end_log_pos 123 CRC32 0x41949ba4 Start: binlog v 4, server v 5.7.27-log created 200703 22:27:21 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' SUD/Xg8CAAAAdwAAAHsAAAABAAQANS43LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABJQP9eEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AaSblEE= '/*!*/; # at 123 #200703 22:27:21 server id 2 end_log_pos 154 CRC32 0xa490108e Previous-GTIDs # [empty] 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*/;

（3）输出从1741到文件结尾的日志信息

[root@localhost wgx]# mysqlbinlog --start-position=1741 /var/lib/mysql/mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200703 22:27:21 server id 2 end_log_pos 123 CRC32 0x41949ba4 Start: binlog v 4, server v 5.7.27-log created 200703 22:27:21 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' SUD/Xg8CAAAAdwAAAHsAAAABAAQANS43LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABJQP9eEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AaSblEE= '/*!*/; # at 1741 #200703 22:36:51 server id 2 end_log_pos 1772 CRC32 0x1d33aa48 Xid = 44 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*/;

5、输出指定时间范围的日志信息

使用【–start-datetime】和【–stop-datetime】参数可以输出【–start-datetime】和【–stop-datetime】之间的日志信息。 如果省略【–stop-datetime】选项，则输出从【–start-datetime】到结尾的日志信息； 如果省略【–start-datetime】选项，则输出从开头到【–stop-datetime】之间的日志信息；

举例省略。

6、把日志格式为ROW的binlog转换成带注释的伪SQL语句

使用【-v】参数，可以把日志格式为ROW的binlog转换成带注释的伪SQL语句。

例如：直接输出ROW格式的日志文件信息如下：

[root@Mysql11 ~]# mysqlbinlog --start-position=509 --stop-position=845 /var/lib/mysql/mysql-bin.000001 .......... # at 509 #200703 22:24:26 server id 1 end_log_pos 581 CRC32 0xa68d8aed Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1593786266/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 581 #200703 22:24:26 server id 1 end_log_pos 629 CRC32 0x220d2e02 Table_map: `hist`.`t2` mapped to number 113 # at 629 #200703 22:24:26 server id 1 end_log_pos 845 CRC32 0x4cd0e798 Update_rows: table id 113 flags: STMT_END_F BINLOG ' mj//XhMBAAAAMAAAAHUCAAAAAHEAAAAAAAEABGhpc3QAAnQyAAID/gL+PAICLg0i mj//Xh8BAAAA2AAAAE0DAAAAAHEAAAAAAAEAAgAC///8AQAAAAVKb2huefxlAAAABUpvaG55/AIA AAADVG9t/GYAAAADVG9t/AMAAAAESmFja/xnAAAABEphY2v8BAAAAAVNZXJyefxoAAAABU1lcnJ5 /AUAAAAEUm9zZfxpAAAABFJvc2X8BgAAAAR3YW5n/GoAAAAEd2FuZ/wHAAAABXpoYW5n/GsAAAAF emhhbmf8CAAAAAJsafxsAAAAAmxp/AkAAAAEemhhb/xtAAAABHpoYW+Y59BM '/*!*/; ROLLBACK /* added by mysqlbinlog */ /*!*/; 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*/;

ROW格式的日志文件将一个事务以二进制形式写到日志中。ROW格式的日志信息是无法看懂的，即使使用mysqlbinlog工具也无法可读。使用【-v】参数可以将其中对事务的二进制描述转换为类似的SQL的形式展示出来。 使用【-v】参数输出日志信息：

[root@Mysql11 ~]# mysqlbinlog -v --start-position=509 --stop-postion=845 /var/lib/mysql/mysql-bin.000001 mysqlbinlog: [ERROR] unknown variable 'stop-postion=845' [root@Mysql11 ~]# mysqlbinlog --base64-output='decode-rows' -v --start-position=509 --stop-position=845 /var/lib/mysql/mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 509 #200703 22:24:26 server id 1 end_log_pos 581 CRC32 0xa68d8aed Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1593786266/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 581 #200703 22:24:26 server id 1 end_log_pos 629 CRC32 0x220d2e02 Table_map: `hist`.`t2` mapped to number 113 # at 629 #200703 22:24:26 server id 1 end_log_pos 845 CRC32 0x4cd0e798 Update_rows: table id 113 flags: STMT_END_F ### UPDATE `hist`.`t2` ### WHERE ### @1=1 ### @2='Johny' ### SET ### @1=101 ### @2='Johny' ### UPDATE `hist`.`t2` ### WHERE ### @1=2 ### @2='Tom' ### SET ### @1=102 ### @2='Tom' ### UPDATE `hist`.`t2` ### WHERE ### @1=3 ### @2='Jack' ### SET ### @1=103 ### @2='Jack' ### UPDATE `hist`.`t2` ### WHERE ### @1=4 ### @2='Merry' ### SET ### @1=104 ### @2='Merry' ### UPDATE `hist`.`t2` ### WHERE ### @1=5 ### @2='Rose' ### SET ### @1=105 ### @2='Rose' ### UPDATE `hist`.`t2` ### WHERE ### @1=6 ### @2='wang' ### SET ### @1=106 ### @2='wang' ### UPDATE `hist`.`t2` ### WHERE ### @1=7 ### @2='zhang' ### SET ### @1=107 ### @2='zhang' ### UPDATE `hist`.`t2` ### WHERE ### @1=8 ### @2='li' ### SET ### @1=108 ### @2='li' ### UPDATE `hist`.`t2` ### WHERE ### @1=9 ### @2='zhao' ### SET ### @1=109 ### @2='zhao' ROLLBACK /* added by mysqlbinlog */ /*!*/; 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*/;