mysql通过binlog恢复或回滚数据

1、前言

近期项目开发中出现了一次误删的操作，于是在网上查找了mysql的回滚操作，这里记录一下。

简单说下binlog，binlog是mysql中的二进制日志，其记录了数据库发生更改的各种变化。所以通过binlog可以回滚或者恢复失误的操作。

恢复一般使用mysqlbinlog命令，该命令是mysql自带的，使用简单。其运行的本质是将日志记录中的事件再次执行一遍。回滚一般要借助第三方工具binlog2sql，其回滚的本质是解析日志文件生成要回滚的sql，我们拷贝执行该sql即可。

2、创建一个test数据库，准备一张空表t1，包含两个字段id、name，如下：

 3、通过如下命令查看是否开启binlog记录功能（如果没开则上网搜一下开启教程，这里不过多介绍）：

show variables like 'log_bin';

4、为了便于观察测试，使用如下一系列命令产生一个新的binlog日志文件，使得我们后续的操作都记录在新的binlog中。

flush logs //产生一个新的日志文件

show variables like 'log_bin_basename' //查看日志存储地址

show master status; //查看最新日志文件名称

5、插入三条数据，如下：

 6、查看此时的binlog日志记录

show binlog events in 'binlog.000068';

 这里可以看到我们的三次插入事件

7、删除一条数据，此时的表数据和binlog如下：

 

 可以看到多了一个delete事件，这里注意下binlog中每个事件都有一个begin和commit，我们后面进行恢复或回滚的时候开始和结束的pos都是取的事件整体的开始点和结束点。比如上面删除事件的开始点其实是1105，结束点则是1316。

8、mysqlbinlog恢复数据

注意这里是恢复数据，不是回滚数据，恢复的本质是将原有的插入语句再执行一遍，而回滚则是回退到删除之前的状态。
mysqlbinlog是mysql自带的命令，一般是在mysql安装目录下的bin目录里。因为我们是恢复数据，所以要找到已经删除语句的对应写入事件，将该事件再重新执行一遍即可。
先通过show binlog events in 'binlog.000068'确认下插入语句的事件位置

可以看到开始位置为815，结束位置为1026，接着用mysqlbinlog命令恢复：

mysqlbinlog --no-defaults   ..\data\binlog.000068 --start-position=815 --stop-position=1026  | mysql -uroot -p123456 test

 下面来看一下表数据和binlog信息：

 

可以看到表中数据已经恢复，binlog中也多了一次写入事件。

小结：
mysqlbinlog命令只是用于恢复，不能用于回滚。如果数据进行update操作，则很难通过该命令恢复。所以该命令比较适用一些数据迁移，数据同步的场景。

mysqlbinlog 运行过程中如果出现unknown variable 'default-character-set=utf8mb4'异常，可以再该命令后加--no-defaults参数解决：mysqlbinlog --no-defaults

mysqlbinlog命令的详细用法这里没有介绍，需要的可以上官网或者百度搜索。

9、binlog2sql数据回滚

binlog2sql是一个第三方的工具，其安装过程中调试了很久，为了不让该文章的逻辑变的杂乱，这里我新开了一篇文章讲解（binlog2sql 工具安装使用及问题汇总_Interest1_wyt的博客-CSDN博客），不了解该工具的可以参考下。

binlog2sql回滚的原理是生成要回滚事件对应的sql语句，我们最后只需要拷贝该语句实现即可。这里为了便于观察，我们先清空t1表，然后再重新开启一个binlog记录，开启和查看新binlog命令前面介绍过，这里仅截图展示下：

 向t1表再次插入三条数据：

 修改最后一条数据的name字段，此时表和binlog的记录情况如下：

 通过binlog2sql生成回滚sql：

E:\Program Files\PYTHON3.9.5\lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 1")
  result = self._query(query)
UPDATE `test`.`t1` SET `id`=3, `name`='回滚3' WHERE `id`=3 AND `name`='更新' LIMIT 1; #start 1105 end 1312 time 2022-04-16 11:20:00

将回滚语句拷贝出来执行后再观察t1表和binlog日志：

 可以看到日志中多了一条更新事件，表中数据也被还原。