简介:mysqlbinlog工具用于处理binlog日志文件,binlog日志文件是一个二进制日志文件,直接用cat、vi等无法查看,需要使用mysqlbinlog工具查看binlog日志文件内容。
以下主要描述mysqlbinlog工具如果进行日志解析:
1、mysqlbinlog解析日志文件
为了能更清楚的查看解析日志文件的效果,执行步骤如下:
1> 登录数据库,重新切一个日志文件,然后再创建一个数据库与表,并向表中插入数据,执行如下:
2> 查看产生的日志文件,并使用mysqlbinlog工具对新产生的日志文件做解析。
解析的内容如下,其中可以查看到SQL执行的语句、SQL执行后产生的一些变化,例如server_id(数据库主机服务号)、end_log_pos(SQL结束时的pos节点)、thread_id(线程号)等信息
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240904 15:06:22 server id 1 end_log_pos 125 CRC32 0x14763f51 Start: binlog v 4, server v 8.0.25 created 240904 15:06:22
# Warning: this binlog is either in use or was not closed properly.
# at 125
#240904 15:06:22 server id 1 end_log_pos 156 CRC32 0x43ea0320 Previous-GTIDs
# [empty]
# at 156
#240904 15:07:36 server id 1 end_log_pos 233 CRC32 0xc83b9403 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1725433656826488 immediate_commit_timestamp=1725433656826488 transaction_length=188
# original_commit_timestamp=1725433656826488 (2024-09-04 15:07:36.826488 CST)
# immediate_commit_timestamp=1725433656826488 (2024-09-04 15:07:36.826488 CST)
/*!80001 SET @@session.original_commit_timestamp=1725433656826488*//*!*/;
/*!80014 SET @@session.original_server_version=80025*//*!*/;
/*!80014 SET @@session.immediate_server_version=80025*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 233
#240904 15:07:36 server id 1 end_log_pos 344 CRC32 0x13f71580 Query thread_id=293 exec_time=0 error_code=0 Xid = 10872
SET TIMESTAMP=1725433656/*!*/;
SET @@session.pseudo_thread_id=293/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database test6
/*!*/;
# at 344
#240904 15:07:53 server id 1 end_log_pos 421 CRC32 0xac1b4774 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1725433673349236 immediate_commit_timestamp=1725433673349236 transaction_length=211
# original_commit_timestamp=1725433673349236 (2024-09-04 15:07:53.349236 CST)
# immediate_commit_timestamp=1725433673349236 (2024-09-04 15:07:53.349236 CST)
/*!80001 SET @@session.original_commit_timestamp=1725433673349236*//*!*/;
/*!80014 SET @@session.original_server_version=80025*//*!*/;
/*!80014 SET @@session.immediate_server_version=80025*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 421
#240904 15:07:53 server id 1 end_log_pos 555 CRC32 0x27c160c5 Query thread_id=293 exec_time=0 error_code=0
use `test6`/*!*/;
SET TIMESTAMP=1725433673/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table aa1(id int, name char(10)) ENGINE=MyISAM
/*!*/;
# at 555
#240904 15:07:58 server id 1 end_log_pos 634 CRC32 0xafd4e6eb Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes original_committed_timestamp=1725433678237263 immediate_commit_timestamp=1725433678237263 transaction_length=334
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1725433678237263 (2024-09-04 15:07:58.237263 CST)
# immediate_commit_timestamp=1725433678237263 (2024-09-04 15:07:58.237263 CST)
/*!80001 SET @@session.original_commit_timestamp=1725433678237263*//*!*/;
/*!80014 SET @@session.original_server_version=80025*//*!*/;
/*!80014 SET @@session.immediate_server_version=80025*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 634
#240904 15:07:58 server id 1 end_log_pos 710 CRC32 0x2367b450 Query thread_id=293 exec_time=0 error_code=0
SET TIMESTAMP=1725433678/*!*/;
BEGIN
/*!*/;
# at 710
#240904 15:07:58 server id 1 end_log_pos 768 CRC32 0x3c6089ed Table_map: `test6`.`aa1` mapped to number 2056
# at 768
#240904 15:07:58 server id 1 end_log_pos 812 CRC32 0x9e35099d Write_rows: table id 2056 flags: STMT_END_F
### INSERT INTO `test6`.`aa1`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='aaa' /* STRING(40) meta=65064 nullable=1 is_null=0 */
# at 812
#240904 15:07:58 server id 1 end_log_pos 889 CRC32 0x98d648f9 Query thread_id=293 exec_time=0 error_code=0
SET TIMESTAMP=1725433678/*!*/;
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*/;
2、导出日志文件,使用如下命令导出。
3、解析日志使用起始与结束时间
4、解析日志使用起始结束pos点
5、应用binlog
如果数据库恢复完成后,没有应用binlog来把数据回滚到最新状态,可以通过应用binlog来实现恢复数据。
或者
其他命令待研究,也可以使用–help查看具体内容。