利用 MySQL bin-log 恢复数据表

转载地址：http://www.cnblogs.com/edwardlost/archive/2011/07/13/2105598.html

如果误操作把数据库中一张极重要数据表 player 给“做掉了”，还算幸运的是该数据库每3个月会完整备份一次，最近一次的备份点为6月30日，再加上 bin-log 保留了30天的数据，可以根据这两份数据还原数据表的内容。方法看上去非常简单清晰，但是具体执行起来还是遇到了很多问题，下面整理了一些关键问题，以备以后灾难再发生时可供参考。

 

在处理 bin-log 前首先要把二进制的文件转换成文本文件，方法：

/data/mysql/bin/mysqlbinlog mysql-bin.001468 > mysql-bin.001468.txt

由于一开始我们想当然认为针对 player 表的更新 SQL 都是单行语句，所以就直接使用 grep 进行行级的提取，这种简单做法也为我们后面的恢复失败埋下了“地雷”。

 

先看一下文本格式 bin-log 的记录格式：

# at 7473
#110630 11:56:05 server id 1  end_log_pos 7612  Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1309406165/*!*/;
UPDATE ssmatch.young_league_match_7 SET status='playing' WHERE mid=699617
/*!*/;

 

显而易见，正确的提取方法应该是：
1. 读取一行数据，如果行首字符为'#'则跳过，否则执行第2步；
2. 检测行尾字符是否为';'，如果不是则继续读下一行直到行尾字符为';'，所有读取的行构成一条 SQL 语句，执行第3步；
3. 清空读取的行缓存，如果已到文件尾则结束，否则跳到第1步循环处理；

 

下面接着说使用 grep 过滤行数据，不能够使用 insert、update、delete、replace 关键词去做严格的匹配，这样很容易漏掉SQL，因为不同人写的 SQL语句格式差异很大。正确的做法是采用“排除法”，即：先使用表名作为关键词进行grep，然后再通过一些关键词滤掉可能误选的SQL。比如：出错的数据表名为 player，而 bin-log 记录的数据库中还有 player_position、young_player 等表，那么我们就需要过滤包含这些数据表名的SQL。只根据数据表名进行过滤还不行，还要根据数据表的字段名，比如：staff表中包含 judge_player_ability 字段，那么对staff表执行的更新操作也会被提取出来，所以需要检查数据库中所有数据表的字段，将包含 player 关键词的字段名全部过滤点。

 

下面是实际提取player表更新SQL语句的命令：

cat mysql-bin.001468.txt | grep -i \"player\" |grep -v \"player_position\|player_league\|young\|match_info\|tactics\|player_buddy\|player_champion\|player_cup\|player_friend\|staff\|match_report\|setpieces_nid\|old_player_ca\|new_player_ca\" > mysql-bin.001468.player

最后说一下上面提到的“地雷”问题，我们在数据重跑进度达到80%的时候遇到 bin-log 中下面格式的update语句：

UPDATE player SET /* 此粗省略更新字段内容 ... */
WHERE nid = 1111

“它换行了！！！WHERE语句在第2行！！！”，悲催啊，上面的行级提取方法将完整的UPDATE语句截断了，重跑执行的是“不带 WHERE 的 UPDATE”！！！一下回到解放前，这下只能重头开始再执行一遍！在重跑前还需要将这种“病毒”语句清理掉。

 

所以，除非你非常肯定确定一定不会出现多行SQL语句，否则都一定要使用上面的正确做法提取SQL。

 

一条宝贵的经验：在恢复数据的过程中一定要做阶段备份，比如在重跑 SQL 时发生错误导致中断，那么可以先把该时间点的数据表复制一份，然后再从中断点往后继续执行；更好的做法是一开始就按阶段进行数据恢复，比如有30个 bin-log 文件需要重跑，那么可以每跑5~10个 bin-log 后做一次备份；这样的好处是一旦发生“意外”就可以从上一个备份点开始执行，而不是重头执行。请记住在恢复数据的过程中你永远无法预知接下会发生什么事故！