当前位置:首页 > MySQL > 利用 MySQL bin-log 恢复数据表

利用 MySQL bin-log 恢复数据表

今天公司一同事使用典型的“UPDATE 不带 WHERE 语句”误操作把数据库中一张极重要数据表 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 后做一次备份;这样的好处是一旦发生“意外”就可以从上一个备份点开始执行,而不是重头执行。请记住在恢复数据的过程中你永远无法预知接下会发生什么事故!

本文来自梦想影院 ! http://www.tosms.net

  • «
  • »
  • 作者:
    除非注明,本文原创:OpenFree,专注于IT互联网,欢迎转载!转载请以链接形式注明本文地址,谢谢。
    原文链接:http://www.it163.org/post/118fd1_a1be60

    发表评论

    电子邮件地址不会被公开。 必填项已用*标注


    您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>