Mysql binlog 二进制日志恢复数据

####起因
昨晚匆匆忙忙给自己的网站发了一个新版本，由于代码中的一个`UPDATE`语句忘加`WHERE`条件，最终导致的结果就是毁灭性的全表更新了~！

好在十几天前数据库备份过，所以当时也是没有太过的担心，由于是个人网站，所以最多也就丢失这十几天的数据。如果是在公司，老板会被你气吐血的，所以大家在写`UPDATE`语句的时候千万要仔细认真。

但是，能恢复还是尽量地恢复吧。

####开干
首先查看数据库有没有开启`binlog`日志：
```
mysql> SHOW BINARY LOGS;
+---------------+------------+-----------+
| Log_name      | File_size  | Encrypted |
+---------------+------------+-----------+
| binlog.000030 | 1073742505 | No        |
| binlog.000031 | 1074133741 | No        |
| binlog.000032 |  241644089 | No        |
| binlog.000033 |    2223970 | No        |
+---------------+------------+-----------+
4 rows in set (0.01 sec)
```
如果有如上结果列表那就说明开启了二进制日志，如果没有的话，那就自求多福吧，除了用以前备份的数据来恢复没有其他办法了。

网上也搜了一些文章，很多都没有说明整个恢复机制是怎么样的，在那一顿乱讲，害我搞了个通宵才把数据恢复，下面简单说下整个恢复机制：
1、 首先，你得有操作未出错之前的备份数据（这一点很重要，后面讲为什么）
2、 确认备份数据的时间，找出备份数据最后时间点是在哪个`binlog`二进制文件中
比如数据库备份时间为`2019-12-20 01:00:00`，执行如下命令：
```
mysqlbinlog --no-defaults --database=xxxx --start-datetime="2019-12-20 00:00:00" --stop-datetime="2019-12-20 02:00:00" -v binlog.000030 | more
```
如果有结果输出，那么表示最后备份的数据操作在`binlog.000030`日志中，由于数据库备份时间为`2019-12-20 01:00:00`，所以可以知道在`2019-12-20 01:00:00`之前的数据都是正确的，我们要做的就是恢复`2019-12-20 01:00:00`之后的数据，既然要恢复之后的，那么必须找到`2019-12-20 01:00:00`这个时间点在哪个日志文件中。
3、 还是上面例子，假如找到是在`binlog.000031`文件中，那么接下来就是确认数据出错的时间点，如果是公司项目，这个时间点必须精确到`第一次`失误操作的语句所在位置，一般这个失误操作的大致时间是知道的，所以用上面的时间范围查询的方式再加上`grep`操作也不难定位到首次出错的时间点。
4、 进行恢复。

说明：由于数据恢复工作量巨大，涉及到数据丢失问题，所以我这里也是大致恢复思路，其他细节恢复问题请自行研究。
由于我这个人网站除了评论，其他内容只有自己才有发布权限，所以这里除了丢失部分评论，对我没有多大的影响。

整体流程：
1. 备份当前数据库（即使是错误的，也备份一下以防万一），清空所有数据，导入十几天前正确的数据，因为 UPDATE 将全表数据都损坏了，而一般情况下数据库的 binog 可能并不包含你从建表开始到最终当时的所有日志，所以这就是为什么之前说必须要有备份数据，如果你从建表到出错之时的 binlog 都有的话，那么没有备份数据文件也是照样能够恢复的。
2. 还是用上面例子，我们用备份数据恢复了`2019-12-20 01:00:00`之前的所有数据，并且找到`2019-12-20 01:00:00`时间点是在`binlog.000031`文件中，出错时间点在`binlog.000032`文件中，那么就要执行两次恢复：
首先恢复`binlog.000031` `2019-12-20 01:00:00`时间点之后的所有数据：
> mysqlbinlog binlog.000031 start-datetime='2019-12-20 01:00:00' | mysql -uroot -p123456 -f (加上-f 忽略主键冲突及其他错误)

然后恢复`binlog.000032`文件中从起始位置到出错时间点的位置。
> mysqlbinlog binlog.000032 stop-datetime='2019-12-20 01:00:00' | mysql -uroot -p123456 -f

当然，要想精确恢复，可以不用时间点的方式恢复，使用`start-position`与`stop-position`的方式即可，当然这只是粗略恢复。

如果是比如银行项目，那么必须是精确恢复：即只回滚操作失误的语句。这就不能简简单单地使用上面的方式恢复了，因为在操作失误的时间点位置之后可能有其他的`增删改查`操作，而这些操作都是用户的正常操作，用上面的方式恢复就会出现部分数据丢失。

Mysql binlog 二进制日志恢复数据

发表评论：