mysqlbinlog 简单使用笔记（入门级）

最新推荐文章于 2025-05-15 16:30:06 发布

原创最新推荐文章于 2025-05-15 16:30:06 发布 · 400 阅读

1 ·

CC 4.0 BY-SA版权

Linux 同时被 2 个专栏收录

8 篇文章

订阅专栏

mysql

7 篇文章

订阅专栏

这篇博客介绍了如何简单使用mysqlbinlog命令来查看和导出MySQL的binlog文件。首先，通过登录MySQL并查看binlog日志路径找到所需文件。然后，利用mysqlbinlog命令，结合start-datetime和stop-datetime参数设定时间范围，再通过database参数指定数据库，可将binlog内容导出。不加-v参数时，内容难以理解；添加-v参数后，可以清晰看到对应的SQL执行语句。

查看mysql自己日志

1，首先是登录mysql，如下所示：

[root@ddi ~]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 323
Server version: 8.0.14 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2，查看mysqlbinlog 日志路径，如下图所示

mysql> show variables like '%datadir%';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| datadir       | /home/mysql/mysql/data/ |
+---------------+-------------------------+
1 row in set (0.03 sec)

3，退出mysql用户，cd 到mysqlbinlog路径`

[root@ddi ~]# cd /home/mysql/mysql/data/
[root@ddi data]# ll
total 1767364
drwxr-x---. 2 mysql mysql          6 May 12 15:34 activiti
drwxr-x---. 2 mysql mysql       4096 Apr 17 14:16 auction
-rw-r-----. 1 mysql mysql         56 Jan 25  2019 auto.cnf
-rw-r-----. 1 mysql mysql 1073791433 Apr 20 16:14 binlog.000035
-rw-r-----. 1 mysql mysql  271108936 May 11 16:04 binlog.000036
-rw-r-----. 1 mysql mysql     374535 May 11 16:46 binlog.000037
-rw-r-----. 1 mysql mysql   21937309 May 13 15:50 binlog.000038
-rw-r-----. 1 mysql mysql         64 May 11 16:59 binlog.index
-rw-------. 1 mysql mysql       1680 Jan 25  2019 ca-key.pem
-rw-r--r--. 1 mysql mysql       1112 Jan 25  2019 ca.pem
-rw-r--r--. 1 mysql mysql       1112 Jan 25  2019 client-cert.pem
-rw-------. 1 mysql mysql       1680 Jan 25  2019 client-key.pem

其中binlog开头的文件就是mysql的日志文件。

接下来就是将binlog文件导出

[root@ddi data]# mysqlbinlog --no-defaults  --start-datetime='2020-05-06 15:46:00' --stop-datetime='2020-05-11 00:00:00'  --database=project -v  binlog.000036 |more >result.txt

说明一下：
mysqlbinlog 是binlog日志文件的专用命令。
start-datetime 表示读取日志的开始时间点。
stop-datetime 表示读取日志的截止时间点。
也就是说，只查看指定开始时间到截止时间范围之内的日志。
database 表示指定数据库名
-v 表示显示sql执行语句
binlog.000036 表示日志文件名
more 表示直接在命令行显示
> result.txt 表示将日志输出到result.txt 文件

示例，不加-v参数情况

执行命令：mysqlbinlog --no-defaults --start-datetime=‘2020-05-06 15:46:00’ --stop-datetime=‘2020-05-11 00:00:00’ --database=project binlog.000036 |more

[root@ddi data]# mysqlbinlog --no-defaults  --start-datetime='2020-05-06 15:46:00' --stop-datetime='2020-05-11 00:00:00'  --database=project   binlog.000036 |more

截取部分显示如下：

BEGIN
/*!*/;
# at 221354940
#200506 15:46:12 server id 0  end_log_pos 221355019 CRC32 0x6097546d    Table_map: `project`.`QRTZ_SCHEDULER_STATE` mapped to number 95
# at 221355019
#200506 15:46:12 server id 0  end_log_pos 221355157 CRC32 0x76cb9460    Update_rows: table id 95 flags: STMT_END_F

BINLOG '
RGuyXhMAAAAATwAAAAucMQ0AAF8AAAAAAAEAB3Byb2plY3QAFFFSVFpfU0NIRURVTEVSX1NUQVRF
AAQPDwgIBGgBWAIAAQEAAgFTbVSXYA==
RGuyXh8AAAAAigAAAJWcMQ0AAF8AAAAAAAEAAgAE//8ADgBSdW95aVNjaGVkdWxlchAAZGRpMTU4
ODA2MjIwODAzN2XH8uhxAQAAmDoAAAAAAAAADgBSdW95aVNjaGVkdWxlchAAZGRpMTU4ODA2MjIw
ODAzN/4B8+hxAQAAmDoAAAAAAABglMt2
'/*!*/;
# at 221355157
#200506 15:46:12 server id 0  end_log_pos 221355188 CRC32 0x94ad3f08    Xid = 45905655
COMMIT/*!*/;
# at 221355188
#200506 15:46:13 server id 0  end_log_pos 221355267 CRC32 0xa6f12ec5    Anonymous_GTID  last_committed=367689   sequence_number=367690  rbr_only=yes    original_commit
ted_timestamp=1588751173203934  immediate_commit_timestamp=1588751173203934     transaction_length=416
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1588751173203934 (2020-05-06 15:46:13.203934 CST)
# immediate_commit_timestamp=1588751173203934 (2020-05-06 15:46:13.203934 CST)
/*!80001 SET @@session.original_commit_timestamp=1588751173203934*//*!*/;
/*!80014 SET @@session.original_server_version=80014*//*!*/;
/*!80014 SET @@session.immediate_server_version=80014*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 221355267
#200506 15:46:13 server id 0  end_log_pos 221355355 CRC32 0x9d23a92b    Query   thread_id=24507 exec_time=0     error_code=0
SET TIMESTAMP=1588751173/*!*/;

可以看出binlog的内容都是看不明白的。

现在加上-v参数

[root@ddi data]# mysqlbinlog --no-defaults  --start-datetime='2020-05-06 15:46:00' --stop-datetime='2020-05-11 00:00:00'  --database=project -v  binlog.000036 |more

执行结果如下显示：

BEGIN
/*!*/;
# at 221354940
#200506 15:46:12 server id 0  end_log_pos 221355019 CRC32 0x6097546d    Table_map: `project`.`QRTZ_SCHEDULER_STATE` mapped to number 95
# at 221355019
#200506 15:46:12 server id 0  end_log_pos 221355157 CRC32 0x76cb9460    Update_rows: table id 95 flags: STMT_END_F

BINLOG '
RGuyXhMAAAAATwAAAAucMQ0AAF8AAAAAAAEAB3Byb2plY3QAFFFSVFpfU0NIRURVTEVSX1NUQVRF
AAQPDwgIBGgBWAIAAQEAAgFTbVSXYA==
RGuyXh8AAAAAigAAAJWcMQ0AAF8AAAAAAAEAAgAE//8ADgBSdW95aVNjaGVkdWxlchAAZGRpMTU4
ODA2MjIwODAzN2XH8uhxAQAAmDoAAAAAAAAADgBSdW95aVNjaGVkdWxlchAAZGRpMTU4ODA2MjIw
ODAzN/4B8+hxAQAAmDoAAAAAAABglMt2
'/*!*/;
### UPDATE `project`.`QRTZ_SCHEDULER_STATE`
### WHERE
###   @1='RuoyiScheduler'
###   @2='ddi1588062208037'
###   @3=1588751157093
###   @4=15000
### SET
###   @1='RuoyiScheduler'
###   @2='ddi1588062208037'
###   @3=1588751172094
###   @4=15000
# at 221355157
#200506 15:46:12 server id 0  end_log_pos 221355188 CRC32 0x94ad3f08    Xid = 45905655
COMMIT/*!*/;
# at 221355188
#200506 15:46:13 server id 0  end_log_pos 221355267 CRC32 0xa6f12ec5    Anonymous_GTID  last_committed=367689   sequence_number=367690  rbr_only=yes    original_commit
ted_timestamp=1588751173203934  immediate_commit_timestamp=1588751173203934     transaction_length=416
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1588751173203934 (2020-05-06 15:46:13.203934 CST)
# immediate_commit_timestamp=1588751173203934 (2020-05-06 15:46:13.203934 CST)
/*!80001 SET @@session.original_commit_timestamp=1588751173203934*//*!*/;
/*!80014 SET @@session.original_server_version=80014*//*!*/;
/*!80014 SET @@session.immediate_server_version=80014*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 221355267
#200506 15:46:13 server id 0  end_log_pos 221355355 CRC32 0x9d23a92b    Query   thread_id=24507 exec_time=0     error_code=0
SET TIMESTAMP=1588751173/*!*/;