MySQL數據恢複的九把瑞士軍刀

作者介紹

李輝，新浪愛彩票運維負責人，常用網名：門牙沒了。主導新浪愛彩票的MySQL運維工作。培訓合夥人、資深講師，中國科學院大學在讀研究生（大數據方向），擅長大型項目的關係型數據庫運維和管理，現在在數據庫運維自動化方向研究。

 

做DBA的朋友可能都遇到過MySQL數據損壞或丟失的問題，比如忘加where條件的update、delete語句，或者MySQL服務器異常宕機導致數據文件損壞等。本文針對在日常運維中由於誤操作、數據文件損壞、硬盤損壞、備份失效等情況導致的各種數據丟失或損壞的場景，提供了九種恢複方案，供大家參考。

 

注：高危操作請勿在沒有測試的情況下，直接在生產環境使用。

 

工具一：完全備份+binlog

 

恢複數據最常見的做法，隻要有這兩樣東西，無論是誤操作還是數據庫損壞等，都能恢複數據到指定的時間節點，能覆蓋大多數的恢複場景，也是DBA手中最重要的資產。恢複方法比較簡單這裏就不過多贅述了。

 

工具二：業務邏輯反推恢複update誤操作

 

這種方法適合做了誤操作但停機會造成更大影響的場景，通過邏輯反推可以迅速恢複數據到正常狀態。下麵我們以用戶充值表為例，來看看如何恢複誤操作。

 

充值狀態說明：0未充值，1已充值，2充值失敗，3充值異常。

 

示例1：

某開發在處理用戶充值故障時漏掉了用戶id，導致大麵積的用戶充值狀態被篡改。由於此表中有last_update_time字段，所以我們可以根據最後修改時間恢複這次的誤操作。

 

• 正確的語句update t1 set status=1 where member_id=10001 and status=0;

• 誤操作語句update t1 set status=1 where status=0;

• 反向執行即可恢複誤操作update t1 set status=0 where status=1 and last_update_time=’2017-03-20 11:30:27’;

 

示例2：

某開發在處理用戶充值狀態時，漏掉了where條件，導致全表被更新。

 

• 正確的語句update t1 set status=1 where member_id=10001 and status=0;

• 誤操作語句update t set status=1;

 

執行時丟失了where條件，此時就要根據其它表中記錄的用戶最後的充值status來進行恢複了，比如用戶充值曆史表，先從用戶充值曆史表中取得用戶最後一次充值的記錄，分析此次充值的status，恢複到用戶充值表即可。這種恢複方法和業務邏輯密切相關。

 

從這裏我們也可以看出此方法並不是很嚴謹，比較適合小規模的恢複。

 

工具三：MySQL flashback

 

最早的相關資料是在彭立勳的博客上，隨後他提交給了MariaDB，網易等大廠在自己的分支中也實現了該功能。對於仍然在使用官方主流版本的同學來說，業內開源的mysqlbinlog_flashback和binlog2sql這兩個閃回工具是個不錯的選擇，作者已經在Github上開源。

 

其原理主要是由於binlog中會記錄Update和Delete語句在更改前後的所有狀態（如下圖），對binlog進行解析和處理即可得到原始SQL、回滾SQL、INSERT語句等，可以恢複Update和Delete誤操作。

 

 

工具四：innodb_force_recovery

 

MySQL非正常重啟或者磁盤故障等原因可能導致MySQL數據文件損壞，損壞後會導致MySQL server無法啟動。如果也沒有備份文件，可以使用這個選項強製InnoDB啟動，阻止一些後台操作的運行，從而dump出數據庫中的數據。

 

innodb_force_recovery可選的值為0-6，默認情況下的值為0，大的數字包含前麵所有數字的影響。當設置參數值大於0後，可以對表進行select，create，drop操作，但insert，update或者delete這類操作是不允許的。

 

1. SRV_FORCE_IGNORE_CORRUPT:忽略檢查到的corrupt頁

2. SRV_FORCE_NO_BACKGROUND:阻止主線程的運行，如主線程需要執行full purge操作，會導致crash

3. SRV_FORCE_NO_TRX_UNDO:不執行事務回滾操作

4. SRV_FORCE_NO_IBUF_MERGE:不執行插入緩衝的合並操作

5. SRV_FORCE_NO_UNDO_LOG_SCAN:不查看重做日誌，InnoDB存儲引擎會將未提交的事務視為已提交

6. SRV_FORCE_NO_LOG_REDO:不執行前滾的操作。

 

[mysqld]中加入此參數，嚐試啟動MySQL，如果啟動失敗就逐步增加參數的值，直到啟動為止，當然其數據一致性也會越來越差。數據庫啟動後，InnoDB類型的表隻能讀不能寫，此時把表中的數據dump出來，或導入MyISAM表裏麵，即可恢複損壞的數據。

 

工具五：DISCARD、IMPORT TABLESPACE

 

這種方法適用於修複frm文件損壞，或者誤操作、ibd損壞但是有物理備份的情況。修複數據要分兩種情況討論：

 

• 有物理備份，數據損壞後table沒有recreate過

 

這種情況下恢複是比較簡單的，物理備份中的ibd、數據庫中ibd的space id和index id，都是和ibdata文件中的space id和index id一致的，所以可以直接拿物理備份中的ibd覆蓋數據庫中的ibd。

 

操作過程：

1. 應用物理備份的log：innobackupex --apply-log

2. 備份數據庫中的ibd：cp test.ibd test.bak

3. 丟棄數據庫中的ibd：alter table test discard tablespace;

4. 複製物理備份中的ibd到數據庫目錄：cp /bak/test.ibd /data/test/; chown mysql:mysql /data/test/test.ibd

5. 導入ibd：alter table test import tablespace;

 

• 有物理備份，但是數據庫中表結構已經被drop。

 

這種情況有點複雜，因為表被drop後元數據中的space id和index id已經被刪除。但space id和index id會留空，不會被新創建的table占用，給我們留下了恢複的機會。隻需要重建表結構，然後在ibdata中還原該表的space id即可，還原過程需要percona recovery tool的協助。

 

操作過程：

1. 應用物理備份的log：innobackupex --apply-log

2. 數據庫中重建表：create table test(id int);

3. 關閉數據庫

4. 用物理備份中的ibd覆蓋數據庫中的ibd

5. 使用percona recovery tool修改ibdata：~/percona-data-recovery-tool-for-innodb-0.5/ibdconnect -o /data/ibdata1 -f /data/test/test.ibd -d test -t test

6. 使用percona recovery tool對ibdata做checksum：~/percona-data-recovery-tool-for-innodb-0.5/innochecksum -f /data/ibdata1

7. 重複執行執行步驟6，直到沒有任何輸出為止

8. 啟動MySQL

 

工具六：手工修改ibd

 

這種方法適用於隻有ibd文件和表結構了，frm和ibdata全部損壞的情況。其原理是在新數據庫上創建表，然後修改待恢複的ibd的文件頭，使之適應新表的space id和index id，從而讀取出ibd中的數據。

 

操作過程：

1、新建數據庫，創建需要恢複的數據庫的表結構。

2、使用vim打開此表的ibd文件，16進製查看。

 [root@localhost test]# vim -b tmp.ibd

                          :%!xxd 

 

3、使用vim打開要恢複的ibd文件，16進製查看

 

4、修改要恢複的ibd文件，將紅方框中的值修改的和剛剛創建的新表的ibd文件一致。看到後麵大段的0000沒，我們隻需要修改文件頭就可以了。00000c0偏移量以後的不用修改。

 [root@localhost test]# vim -b tmp.ibd

         :%!xxd -r     #一定要先執行這一步

         :wq

5、把待恢複的ibd文件覆蓋剛剛創建的新表的ibd文件。修改文件權限為MySQL用戶。

6、重啟MySQL，重啟時加上參數innodb_force_recovery。

7、將數據dump出來，找回數據成功。

 

工具七：extundelete

 

這個工具是基於Linux的文件恢複工具，可以用來恢複誤刪除的表，對於DML和truncate操作無能為力。其主要原理是在Linux文件係統中，刪除文件隻是刪除了文件係統的inode信息，物理文件仍然在磁盤上，通過此工具即可將誤刪除的文件恢複正常。當然前提是物理文件沒有被覆蓋。類似的工具還有ext3grep、debugfs等，不再贅述。

 

工具八：Percona Data Recovery Tool for InnoDB

 

這個工具是Percona公司開發的一款InnoDB數據恢複工具，目前已經停止開發，但是仍然可用。它通過在原始數據文件（ibd） 中直接提取表的行記錄，實現我們從損壞的表恢複數據的目的。要完成這類恢複，前提是要知道待恢複的表結構。Percona Data Recovery Tool for InnoDB直接讀取InnoDB的物理頁，按照我們給出的表定義，把數據恢複成類csv文件。恢複後的數據可能包含正確的行記錄，也可能包含不正確的行記錄，並且拿到的數據比較亂，需要做進一步的處理才能導入到數據庫中。這個辦法是沒有辦法中的辦法了，不得已而為之，希望大家都不會用到這個工具。

 

原文發布時間為：2017-04-26

本文來自雲棲社區合作夥伴DBAplus