利用binlog恢复MySQL数据

问题描述:eHR测试环境的组织架构(organization)表被整体删除,操作者删除前没有备份数据,并且使用线上数据覆盖了原有数据。

整个测试环境的正常运行受到影响,需要恢复数据。

恢复过程:

1、检查MySQL的定时备份

先联系了OP,得知测试环境的数据库没有弄定时备份,此路不通。

2、检查MySQL的二进制日志(binlog)

登录数据库,查询是否开启了binlog

mysql > show variables like 'log_%’;

执行后,发现测试环境在 /var/lib/mysql 目录下记录了binlog。

3、尝试根据binlog恢复数据。

思路:使用mysql的binlog工具来解析binlog,得到ehr数据库的sql脚本,再建个新库来执行这个脚本。

mysqlbinlog --database ehr /var/lib/mysql/mysql-bin.000001 > dump.sql

执行的时候发现 ehr 的测试数据库默认使用了 utf8mb4 字符集,而 mysqlbinlog 不支持这种字符集。

解决方法:

(1) 把mysql默认字符集换成 utf8,然后重启服务。【这种方式行不通,因为会影响测试环境其他系统的使用。】

(2) 禁止 mysqlbinlog 读取mysql的默认字符集,加上 --no-defaults参数即可。

mysqlbinlog --no-defaults --database ehr /var/lib/mysql/mysql-bin.000001 > dump.sql

现在根据binlog得到了 sql 脚本,但是这个数据库运行的时间有点久,目前已经产生了 16 个 binlog。实际生成的 sql 文件有好几个G,直接去读取不太现实。

于是决定利用linux的管道特性,直接把sql脚本送入mysql来执行。

mysqlbinlog --no-defaults --database ehr /var/lib/mysql/mysql-bin.000001 | mysql

执行后立即报错。提示找不到表、找不到字段等异常。怀疑 binlog 不完整,于是查看binlog文件的信息。

ll /var/lib/mysql | grep mysql-bin

查看发现,最早一个 binlog 文件的更新时间是 2018年7月5日,而ehr的数据库在2018年初就已经创建了。这说明binlog并不是一开始就创建的,而是在系统运行了几个月之后才开启。

问题陷入了僵局,有点绝望。

4、寻找备份数据

冷静一下。

binlog是2018年7月5日后的,假如能有一份这个时间点附近的备份数据,就可以通过 --start-datetime 参数来制定binlog的时间,从而恢复数据。

但我们没有备份组织架构全表数据。

这就很难受了。

这时候,北京大数据部的一个兄弟(linyg)弹了我一个消息:“哈罗,咱们这边**.174和**.228两个ehr库的组织架构表的数据貌似不一样,麻烦有时间帮忙看一下呗?”

228 是 dev 环境的IP,174 是 test 环境的IP。ehr只有test环境和prod环境,没有dev环境。dev环境上怎么会有一份ehr数据的?

这不重要。重要的是dev环境上真的有一份ehr的数据,而且据日志来看,从2019年1月23日后就再也没有人写过这个库了。

而且!这个数据是从 test 环境拷贝过去的!

赶紧从 dev 环境的 ehr 库中导出数据,弄到用于修复数据的 mysql 中。【不直接弄到 test 环境,免得没修复反而把其他数据弄坏了。】然后找到2019-01-23那天之后的binlog,尝试导出脚本执行看看有没有问题。

mysqlbinlog --no-defaults --database ehr -vv --start-datetime=‘2019-01-23 14:22:50' /var/lib/mysql/mysql-bin.000012 > dump.sql

这次居然没问题了。

于是,在用于修复的的库中得到了完好的组织架构数据,然后用它来恢复测试环境的数据。至此ehr测试环境的组织架构数据终于修复了。

5、后话

关于dev环境数据的来历,后来我想起来了,dev 上的数据我年初的时候找OP弄的。

原因是我们在库中存了一些json格式的数据,而大数据部门的一些查询需要用到mysql的json函数。test环境的mysql版本有点低,不支持json函数。dev 环境的 mysql 版本较高,于是就把test环境的数据复制了一份过去。

当时给大数据部门提供的是只读账号,于是这份数据就相当于一个标本,没有任何人会去修改它,最终成了这次恢复数据的救命稻草。

6、教训

(1) 直接改数据库的数据之前,应该先做好备份。

(2) 给数据库做定时备份。

(3) 在做任何决定之前,想象一下开发、测试、运维的同学知道你家住哪。