mysqlbinlog工具分析binlog日志信息

MySQL中，任何时候对DB的修改均会记录到binlog中。对数据库表的CURD操作，都会忠实的记录在binlog中。同时，主从数据库部署架构中，从库从主库的复制操作也是基于binlog进行的。

如下命令为通过mysqlbinlog工具分析binlog日志信息。

# 查询mysqlbinlog位置
[root@test-001 binlog_analysis]# which mysqlbinlog
/usr/bin/mysqlbinlog

# 准备binlog文件list
[root@test-001 binlog_analysis]# pwd
/root/binlog_analysis
[root@test-001 binlog_analysis]# ll
total 6268
-rw-r--r-- 1 root root 1201742 Nov 25 21:00 mysql-bin.145520
-rw-r--r-- 1 root root  934904 Nov 25 21:05 mysql-bin.145521
-rw-r--r-- 1 root root 3470451 Nov 25 21:10 mysql-bin.145522
-rw-r--r-- 1 root root  801835 Nov 25 21:15 mysql-bin.145523


# mysqlbinlog工具分析binlog日志信息
[root@test-001 binlog_analysis]# /usr/bin/mysqlbinlog --no-defaults --database=user_db --base64-output=decode-rows -v --start-datetime='2021-11-25 21:09:59' --stop-datetime='2021-11-25 21:10:02' mysql-bin.145522 | more

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#211125 21:05:03 server id 150000000  end_log_pos 126 CRC32 0xef967696 	Start: binlog v 4, server v 5.7.33-2-log created 211125 21:05:03
# at 3467952
#211125 21:10:00 server id 150000000  end_log_pos 3468017 CRC32 0x5369f0cd 	GTID	last_committed=364	sequence_number=365	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '51020af5-ca3b-11e9-b9e2-fa163e874475:114542117'/*!*/;
# at 3468017
#211125 21:10:00 server id 150000000  end_log_pos 3468107 CRC32 0x0874425f 	Query	thread_id=515	exec_time=0	error_code=0
SET TIMESTAMP=1637845800/*!*/;
SET @@session.pseudo_thread_id=515/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1342177288/*!*/;
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.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 3468107
# at 3468343
# at 3468430
# at 3468545
#211125 21:10:00 server id 150000000  end_log_pos 3468576 CRC32 0x1e2b0532 	Xid = 95038700
COMMIT/*!*/;
# at 3468576
#211125 21:10:00 server id 150000000  end_log_pos 3468641 CRC32 0x99edb0d7 	GTID	last_committed=364	sequence_number=366	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '51020af5-ca3b-11e9-b9e2-fa163e874475:114542118'/*!*/;
# at 3468641
#211125 21:10:00 server id 150000000  end_log_pos 3468724 CRC32 0x6b89a9f3 	Query	thread_id=3164600	exec_time=0	error_code=0
SET TIMESTAMP=1637845800/*!*/;
BEGIN
/*!*/;
# at 3468724
# at 3468962
#211125 21:10:00 server id 150000000  end_log_pos 3469042 CRC32 0x506e22fa 	Table_map: `user_db`.`schedule_execute_result` mapped to number 116
# at 3469042
#211125 21:10:00 server id 150000000  end_log_pos 3469159 CRC32 0x1115df84 	Write_rows: table id 116 flags: STMT_END_F
### INSERT INTO `user_db`.`user_test`
### SET
###   @1=10001							--用户ID
###   @2='zhangsan'						--用户名
###   @3='贵州'							--行程信息
###   @4='2021-11-25 21:10:00'			--数据create_time
###   @5=NULL							--数据update_time
###   @6=0								--目的地是否更新（0:未更新；1:更新）
# at 3469159
#211125 21:10:00 server id 150000000  end_log_pos 3469190 CRC32 0x04086c6b 	Xid = 95038701
COMMIT/*!*/;
# at 3469190
#211125 21:10:00 server id 150000000  end_log_pos 3469255 CRC32 0x2ac7fd30 	GTID	last_committed=366	sequence_number=367	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '51020af5-ca3b-11e9-b9e2-fa163e874475:114542119'/*!*/;
# at 3469255
#211125 21:10:00 server id 150000000  end_log_pos 3469345 CRC32 0xfa1ec855 	Query	thread_id=1193	exec_time=0	error_code=0
SET TIMESTAMP=1637845800/*!*/;
BEGIN
/*!*/;
# at 3469345
# at 3469481
# at 3469568
# at 3469771
#211125 21:10:00 server id 150000000  end_log_pos 3469802 CRC32 0xc7d5fc61 	Xid = 95038705
COMMIT/*!*/;
# at 3469802
#211125 21:10:00 server id 150000000  end_log_pos 3469867 CRC32 0x08a23b29 	GTID	last_committed=366	sequence_number=368	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '51020af5-ca3b-11e9-b9e2-fa163e874475:114542120'/*!*/;
# at 3469867
#211125 21:10:00 server id 150000000  end_log_pos 3469950 CRC32 0x5a28195f 	Query	thread_id=3164534	exec_time=0	error_code=0
SET TIMESTAMP=1637845800/*!*/;
BEGIN
/*!*/;
# at 3469950
# at 3470086
#211125 21:10:00 server id 150000000  end_log_pos 3470166 CRC32 0x8d125e06 	Table_map: `user_db`.`user_test` mapped to number 116
# at 3470166
#211125 21:10:00 server id 150000000  end_log_pos 3470373 CRC32 0x881126c5 	Update_rows: table id 116 flags: STMT_END_F
### UPDATE `user_db`.`user_test`
### WHERE
###   @1=10002							--用户ID
###   @2='lisi'							--用户名
###   @3='江西'							--行程信息
###   @4='2021-11-24 21:10:00'			--数据create_time
###   @5=NULL							--数据update_time
###   @6=0								--目的地是否更新（0:未更新；1:更新）
### SET
###   @1=10002
###   @2='zhangsan'
###   @3='江西|广东'
###   @4='2021-11-24 21:10:00'
###   @5='2021-11-25 21:10:00'
###   @6=1
# at 3470373
#211125 21:10:00 server id 150000000  end_log_pos 3470404 CRC32 0xd1f98a2b 	Xid = 95038706
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*/;
[root@test-001 binlog_analysis]# 

参考：

• mysqlbinlog 工具分析binlog日志