MySQL 用 binlog 及备份进行回滚/恢复

MySQL 运用 binlog 及备份进行回滚/恢复

引言

如果碰到数据错误，需要进行回滚/恢复，可以利用 binlog 文件及备份进行操作。但是请注意，没有提前备份文件，或者没有开启 binlog 日志文件，不适用此方法。
如果数据库建立在云端，可以向相关服务提供商要求恢复；如果自建，建议找找其他办法，或者咨询专业的数据恢复服务。

回滚/恢复

1. 平复下情绪，立即终止相关应用/服务
为最坏情况做准备，所以需要避免磁盘的写操作，以免发生数据写入覆盖。

2. 找到并下载合适时间的全量备份
下载的前提是有提前备份，备份命令：
mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql

示例：
mysqldump -h 192.168.1.100 -p 3306 -uroot -ppassword --database cmdb > /data/backup/cmdb.sql

可以写一个定时脚本，进行日/周/月备份，以备不时之需。

3. 本地（第二环境）导入备份库
在本地（第二环境）安装MySQL，登录后进行导入：
mysql> source /home/backup.sql

4. 找到并下载 binlog 文件
同样，下载的前提是已提前打开 binlog。
打开需要开启 binlog 的MySQL，查看日志开启状态show variables like 'log_%';

查看所有binlog日志列表show master logs;

将全量备份时间节点耦合上的的所有日志文件下载到备份库所在的环境，比如备份时间为 1月31日，日志文件共有4个，创建时间为：1月1日、1月15日、1月30日、2月1日，那就需要1月30日、2月1日的日志文件。

4. 定位备份库起始位置
将全量备份时间点前后的 SQL 生成，如果备份时间点为2月1日13点整，生成命令：
mysqlbinlog --database=test --start-datetime='2020-02-01 13:00:00' --stop-datetime='2020-02-01 13:00:00' "/Users/***/Downloads/bin/mysql-bin.000020" > /Users/test.sql

打开生成的 SQL 文件：

确认全量备份时间点后的第一条 SQL，记录位置编号

5. 生成备份库备份时间后的所有 SQL
SQL 生成命令：
mysqlbinlog --start-position='164361' /Users/****/Downloads/bin/mysql-bin.000020> binlog_name000020.sql

如果备份文件有多个，例如还有一个mysql-bin.000021，SQL 生成命令不用带位置编号：
mysqlbinlog /Users/****/Downloads/bin/mysql-bin.000021> binlog_name000021.sql

6. 检查并剔除错误 SQL
打开文件，找到错误的 SQL，将其删除，不然还原出的数据仍然为错误数据

7. 本地（第二环境）依次导入SQL，还原完成
按照时间先后顺序依次导入 SQL：
mysql> source /home/binlog_name000020.sql
mysql> source /home/binlog_name000021.sql

现在本地（第二环境）的数据已经恢复正常了，可以进行数据回传，覆盖掉错误数据，最后重启应用/服务，还原完成。

一点总结

可以拓展学习下 binlog 的相关知识，另外，备份平日一定要做好。多饶一句，写这篇博文的时候，微盟的删库事件还在眼前，权限层层制约确实是好事，但也要想到"一抓就死,一放就乱"这句话，管的太严，一定会出现办事效率低下的问题，太松，风险又是可预期的。