windows下mysql中binlog日志分析和数据恢复

1.首先查看是否开启了binlog

show variables like '%log_bin%';
1

看到了是没有开启的。

2.开启binlog日志，并重启mysql服务

不能通过命令的方式去打开，因为会提示说这个参数是只读的。如下图：

所以，打开mysql的配置文件，找到mysql的配置文件(和Linux下的文件名是不一样的，Linux是my.cnf)，windows默认的在C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

在[mysqld]节点下添加

log-bin= mysqlbinlog
binlog-format=ROW
1
2
3
4

配置好之后，要进行重启mysql服务，没有重启之前该路径目录长这样子

重启服务
在任务管理器中找到mysql右键点击重新启动。

重启服务之后该文件的目录长这样子，新添加了如图所示的文件。

此时在命令行中再去查看是否开启了binlog，

3.查看有哪些binlog文件和正在使用的binlog文件

查看有哪些binlog文件

show binary logs;
或者
show master logs;
1
2
3

查看当前正在使用的是哪一个binlog文件

show master status;
1

4.binlog中的事件（show binlog events）数据恢复就会使用到该命令

我们执行命令都是以事件的形式操作的

 查看所有的事件
 show binlog events;
 
 当binlog文件比较多的时候可以加条件来查看具体的某一个文件中的所有事件
 show binlog events in 'mysqlbinlog.000001';
1
2
3
4
5

5.创建一个数据库和一张表并插入数据之后再次查看binlog中的事件（show binlog events）

create database binlogtest;
1

创建数据库后，再查看binlog日志文件，就看到刚刚执行的创建数据库的命令事件。

在刚才创建的数据库下面新建一张测试表并插入数据。

mysql> use binlogtest;
Database changed
mysql>
CREATE TABLE test (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(10) DEFAULT NULL,
  age int DEFAULT 0,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

mysql> insert into test values(1,'zhangsan',20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values(2,'lisi',30);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(3,'wangwu',40);
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

show binlog events in 'mysqlbinlog.000001';
1

已经将创建数据库、创建表、新增数据库日志都添加进去了。

6.查看binlog文件中的内容（mysqlbinlog）

由于binlog是二进制的文件，是不能直接查看的，可以使用mysqlbinlog命令就可以转换成人能够看懂的内容，进入到C:\ProgramData\MySQL\MySQL Server 5.7\Data下面打开cmd
结尾没有分号

mysqlbinlog "mysqlbinlog.000001"

或者将binlog文件换成人能够看懂的文件
mysqlbinlog "mysqlbinlog.000001">"test.sql"
1
2
3
4

转成文件

在控制台不好看，用记事本打开方便查看

7.数据恢复

现在做了一个伟大的壮举，，，删库了

已经删库了，现在要进行数据的恢复
查看有哪些binlog文件(这个binlog文件是重启一次mysql服务就会形成一个新的文件mysqlbinlog.000002 …后缀依次累加)

show binary logs;
1

查看删库之前执行过的操作事件,并找到要恢复数据的起点和终点（这里在控制到中看起来比较费劲，可以借助别的软件如：navicat）

show binlog events;
或者
show binlog events in 'mysqlbinlog.000001';
1
2
3

那就以创建数据库为起点，以删库之前为终点
为了方便查看我将上图show binlog events;
结果复制到excel中

binlog命令恢复数据

mysqlbinlog --start-position=154 --stop-position=1503 mysqlbinlog.000001 | mysql -uroot -proot 
1

提示信息不需要理会，要是有强迫症，那你就先

mysqlbinlog --start-position=154 --stop-position=1503 mysqlbinlog.000001 | mysql -uroot -p
回车
再次单独输入密码
root
1
2
3
4

8.总结

简单来说，要恢复数据
1.查看当前使用的binlog文件是那个

show binary logs; --文件列表
show master status; --当前使用的是哪个文件
1
2

2.针对当前使用的文件查看事件，以此来确认要恢复数据的终点和起点

show binlog events;
或者
show binlog events in 'mysqlbinlog.000001';
1
2
3

3.携带起点和终点，执行数据恢复命令

mysqlbinlog --start-position=154 --stop-position=1503 mysqlbinlog.000001 | mysql -uroot -proot 
1