今天公司一同事使用典型的“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 后做一次備份；這樣的好處是一旦發生“意外”就可以從上一個備份點開始執行，而不是重頭執行。請記住在恢復數據的過程中你永遠無法預知接下會發生什么事故！

?

轉載于:https://www.cnblogs.com/edwardlost/archive/2011/07/13/2105598.html

總結

以上是生活随笔為你收集整理的利用 MySQL bin-log 恢复数据表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。