＜通俗易懂版＞ mysql通过binlog恢复数据（不能回滚）

目录

1、前言

2、创建一个dbtest数据库，准备一张空表t_user，包含两个字段id、username、age，如下：

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

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

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

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

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

8、mysqlbinlog恢复数据

小结：

---

1、前言

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

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

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

2、创建一个dbtest数据库，准备一张空表t_user，包含两个字段id、username、age，如下：

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

show variables like 'log_bin';

值为OFF，需开启，值为ON，已开启。

如果没有开启binlog，也没有预先生成回滚SQL，那可能真的无法快速回滚了。对存放重要业务数据的MySQL，强烈建议开启binlog。  

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

flush logs //产生一个新的日志文件
 
show variables like 'log_bin_basename' //查看日志存储地址
 
show master status; //查看最新日志文件名称

 

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

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

show binlog events in 'mysql-bin.000013';  # mysql-bin.000013 为通过 show master status 查询到的file名

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

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

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

8、mysqlbinlog恢复数据

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

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

start-position 代表指定具体开始位置  start-datetime 代表指定具体开始日期

mysqlbinlog --no-defaults /www/server/data/mysql-bin.000013 --start-position=2696 --stop-position=2968 | mysql -uroot -p dbtest

如果mysqlbinlog报错(-bash: mysqlbinlog: command not found)请看http://t.csdn.cn/1YIozhttp://t.csdn.cn/1YIoz

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

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

小结：

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

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

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