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 进行解析;