admin

Mysql binlog 二进制日志恢复数据
起因昨晚匆匆忙忙给自己的网站发了一个新版本,由于代码中的一个UPDATE语句忘加WHERE条件,最终导致的结果就是...
扫描右侧二维码阅读全文
25
2019/12

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-positionstop-position的方式即可,当然这只是粗略恢复。

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

Last modification:December 25th, 2019 at 08:48 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment