mysqlbinlog 工具分析binlog日志

mysqlbinlog 工具分析binlog日志

MySQL的binlog 日志对于生产环境非常有用，任何时间对数据库的修改都会记录在binglog中；当数据发生增删改，创建数据库对象都会记录到binlog中，数据库的复制也是基于binlog进行同步数据；

下面介绍mysqlbinlog工具查看 二进制日志内容；

检查数据库配置

1、查看是否开启binlog

mysql> show variables like 'log_bin%';
+---------------------------------+----------------------------------------------------------------+
| Variable_name                   | Value                                                          |
+---------------------------------+----------------------------------------------------------------+
| log_bin                         | ON                                                             |
| log_bin_basename                | C:\ProgramData\MySQL\MySQL Server 8.0\Data\HURYER-PC-bin       |
| log_bin_index                   | C:\ProgramData\MySQL\MySQL Server 8.0\Data\HURYER-PC-bin.index |
| log_bin_trust_function_creators | OFF                                                            |
| log_bin_use_v1_row_events       | OFF                                                            |
+---------------------------------+----------------------------------------------------------------+

2、查看binlog

show binary logs
+----------------------+-----------+-----------+
| Log_name             | File_size | Encrypted |
+----------------------+-----------+-----------+
| HURYER-PC-bin.000038 |      1297 | No        |
| HURYER-PC-bin.000039 |    865508 | No        |
| HURYER-PC-bin.000040 |       155 | No        |
+----------------------+-----------+-----------+

示例

1、 创建数据库db

create database test;

2、 创建表t1

use test;
create table `t1` (
  `id` int(11) default null,
  `name` varchar(20) default null
);

3、增删改数据

insert into t1(id,name) values(1,'n1');
update t1 set name='m1' where id=1;
delete from t1 where id=1;

4、查看当前binlog

mysql> show binary logs;
+----------------------+-----------+-----------+
| Log_name             | File_size | Encrypted |
+----------------------+-----------+-----------+
| HURYER-PC-bin.000038 |      1297 | No        |
| HURYER-PC-bin.000039 |    865508 | No        |
| HURYER-PC-bin.000040 |      2285 | No        |
+----------------------+-----------+-----------+

把最后一个 binlog : 拷贝到 d:/tmp 目录下：

5、查看binlog 日志内容：

mysqlbinlog --no-defaults --database=test -v --start-datetime="2020-04-17-12:00:00" --stop-datetime="2020-04-17-14:02:54"  d:\tmp\HURYER-PC-bin.000040 | more
mysqlbinlog --no-defaults --database=test -v d:\tmp\HURYER-PC-bin.000040 | more

–start-datetime --stop-datetime 开始截止时间，windows中需要使用双引号，linux中使用单引号；

也可以根据精确位置 – start-position --stop-position 进行解析；