做DBA的朋友可能都遇到過(guò)MySQL數(shù)據(jù)損壞或丟失的問(wèn)題，比如忘加where條件的update、delete語(yǔ)句，或者M(jìn)ySQL服務(wù)器異常宕機(jī)導(dǎo)致數(shù)據(jù)文件損壞等。本文針對(duì)在日常運(yùn)維中由于誤操作、數(shù)據(jù)文件損壞、硬盤(pán)損壞、備份失效等情況導(dǎo)致的各種數(shù)據(jù)丟失或損壞的場(chǎng)景，提供了九種恢復(fù)方案，供大家參考。

注：高危操作請(qǐng)勿在沒(méi)有測(cè)試的情況下，直接在生產(chǎn)環(huán)境使用。

工具一：完全備份+binlog

恢復(fù)數(shù)據(jù)最常見(jiàn)的做法，只要有這兩樣?xùn)|西，無(wú)論是誤操作還是數(shù)據(jù)庫(kù)損壞等，都能恢復(fù)數(shù)據(jù)到指定的時(shí)間節(jié)點(diǎn)，能覆蓋大多數(shù)的恢復(fù)場(chǎng)景，也是DBA手中最重要的資產(chǎn)。恢復(fù)方法比較簡(jiǎn)單這里就不過(guò)多贅述了。

工具二：業(yè)務(wù)邏輯反推恢復(fù)update誤操作

這種方法適合做了誤操作但停機(jī)會(huì)造成更大影響的場(chǎng)景，通過(guò)邏輯反推可以迅速恢復(fù)數(shù)據(jù)到正常狀態(tài)。下面我們以用戶(hù)充值表為例，來(lái)看看如何恢復(fù)誤操作。

充值狀態(tài)說(shuō)明：0未充值，1已充值，2充值失敗，3充值異常。

示例1：

某開(kāi)發(fā)在處理用戶(hù)充值故障時(shí)漏掉了用戶(hù)id，導(dǎo)致大面積的用戶(hù)充值狀態(tài)被篡改。由于此表中有l(wèi)ast_update_time字段，所以我們可以根據(jù)最后修改時(shí)間恢復(fù)這次的誤操作。

正確的語(yǔ)句update t1 set status=1 where member_id=10001 and status=0;  
誤操作語(yǔ)句update t1 set status=1 where status=0;  
反向執(zhí)行即可恢復(fù)誤操作update t1 set status=0 where status=1 and last_update_time=’2017-03-20 11:30:27’;  

示例2：

某開(kāi)發(fā)在處理用戶(hù)充值狀態(tài)時(shí)，漏掉了where條件，導(dǎo)致全表被更新。

正確的語(yǔ)句update t1 set status=1 where member_id=10001 and status=0;  
誤操作語(yǔ)句update t set status=1;  

執(zhí)行時(shí)丟失了where條件，此時(shí)就要根據(jù)其它表中記錄的用戶(hù)最后的充值status來(lái)進(jìn)行恢復(fù)了，比如用戶(hù)充值歷史表，先從用戶(hù)充值歷史表中取得用戶(hù)最后一次充值的記錄，分析此次充值的status，恢復(fù)到用戶(hù)充值表即可。這種恢復(fù)方法和業(yè)務(wù)邏輯密切相關(guān)。

從這里我們也可以看出此方法并不是很?chē)?yán)謹(jǐn)，比較適合小規(guī)模的恢復(fù)。

工具三：MySQL flashback

最早的相關(guān)資料是在彭立勛的博客上，隨后他提交給了MariaDB，網(wǎng)易等大廠(chǎng)在自己的分支中也實(shí)現(xiàn)了該功能。對(duì)于仍然在使用官方主流版本的同學(xué)來(lái)說(shuō)，業(yè)內(nèi)開(kāi)源的mysqlbinlog_flashback和binlog2sql這兩個(gè)閃回工具是個(gè)不錯(cuò)的選擇，作者已經(jīng)在Github上開(kāi)源。

其原理主要是由于binlog中會(huì)記錄Update和Delete語(yǔ)句在更改前后的所有狀態(tài)(如下圖)，對(duì)binlog進(jìn)行解析和處理即可得到原始SQL、回滾SQL、INSERT語(yǔ)句等，可以恢復(fù)Update和Delete誤操作。

工具四：innodb_force_recovery

MySQL非正常重啟或者磁盤(pán)故障等原因可能導(dǎo)致MySQL數(shù)據(jù)文件損壞，損壞后會(huì)導(dǎo)致MySQL server無(wú)法啟動(dòng)。如果也沒(méi)有備份文件，可以使用這個(gè)選項(xiàng)強(qiáng)制InnoDB啟動(dòng)，阻止一些后臺(tái)操作的運(yùn)行，從而dump出數(shù)據(jù)庫(kù)中的數(shù)據(jù)。

innodb_force_recovery可選的值為0-6，默認(rèn)情況下的值為0，大的數(shù)字包含前面所有數(shù)字的影響。當(dāng)設(shè)置參數(shù)值大于0后，可以對(duì)表進(jìn)行select，create，drop操作，但insert，update或者delete這類(lèi)操作是不允許的。

SRV_FORCE_IGNORE_CORRUPT:忽略檢查到的corrupt頁(yè)  
SRV_FORCE_NO_BACKGROUND:阻止主線(xiàn)程的運(yùn)行，如主線(xiàn)程需要執(zhí)行full purge操作，會(huì)導(dǎo)致crash  
SRV_FORCE_NO_TRX_UNDO:不執(zhí)行事務(wù)回滾操作  
SRV_FORCE_NO_IBUF_MERGE:不執(zhí)行插入緩沖的合并操作  
SRV_FORCE_NO_UNDO_LOG_SCAN:不查看重做日志，InnoDB存儲(chǔ)引擎會(huì)將未提交的事務(wù)視為已提交  
SRV_FORCE_NO_LOG_REDO:不執(zhí)行前滾的操作。  

[mysqld]中加入此參數(shù)，嘗試啟動(dòng)MySQL，如果啟動(dòng)失敗就逐步增加參數(shù)的值，直到啟動(dòng)為止，當(dāng)然其數(shù)據(jù)一致性也會(huì)越來(lái)越差。數(shù)據(jù)庫(kù)啟動(dòng)后，InnoDB類(lèi)型的表只能讀不能寫(xiě)，此時(shí)把表中的數(shù)據(jù)dump出來(lái)，或?qū)隡yISAM表里面，即可恢復(fù)損壞的數(shù)據(jù)。

工具五：DISCARD、IMPORT TABLESPACE

這種方法適用于修復(fù)frm文件損壞，或者誤操作、ibd損壞但是有物理備份的情況。修復(fù)數(shù)據(jù)要分兩種情況討論：

有物理備份，數(shù)據(jù)損壞后table沒(méi)有recreate過(guò)

這種情況下恢復(fù)是比較簡(jiǎn)單的，物理備份中的ibd、數(shù)據(jù)庫(kù)中ibd的space id和index id，都是和ibdata文件中的space id和index id一致的，所以可以直接拿物理備份中的ibd覆蓋數(shù)據(jù)庫(kù)中的ibd。

操作過(guò)程：

應(yīng)用物理備份的log：innobackupex --apply-log  
備份數(shù)據(jù)庫(kù)中的ibd：cp test.ibd test.bak  
丟棄數(shù)據(jù)庫(kù)中的ibd：alter table test discard tablespace;  
復(fù)制物理備份中的ibd到數(shù)據(jù)庫(kù)目錄：cp /bak/test.ibd /data/test/; chown mysql:mysql /data/test/test.ibd  
導(dǎo)入ibd：alter table test import tablespace;  

有物理備份，但是數(shù)據(jù)庫(kù)中表結(jié)構(gòu)已經(jīng)被drop。

這種情況有點(diǎn)復(fù)雜，因?yàn)楸肀籨rop后元數(shù)據(jù)中的space id和index id已經(jīng)被刪除。但space id和index id會(huì)留空，不會(huì)被新創(chuàng)建的table占用，給我們留下了恢復(fù)的機(jī)會(huì)。只需要重建表結(jié)構(gòu)，然后在ibdata中還原該表的space id即可，還原過(guò)程需要percona recovery tool的協(xié)助。

操作過(guò)程：

應(yīng)用物理備份的log：innobackupex --apply-log  
數(shù)據(jù)庫(kù)中重建表：create table test(id int); 

關(guān)閉數(shù)據(jù)庫(kù)

用物理備份中的ibd覆蓋數(shù)據(jù)庫(kù)中的ibd

使用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 

使用percona recovery tool對(duì)ibdata做checksum：~/percona-data-recovery-tool-for-innodb-0.5/innochecksum -f /data/ibdata1 

重復(fù)執(zhí)行執(zhí)行步驟6，直到?jīng)]有任何輸出為止

啟動(dòng)MySQL

工具六：手工修改ibd

這種方法適用于只有ibd文件和表結(jié)構(gòu)了，frm和ibdata全部損壞的情況。其原理是在新數(shù)據(jù)庫(kù)上創(chuàng)建表，然后修改待恢復(fù)的ibd的文件頭，使之適應(yīng)新表的space id和index id，從而讀取出ibd中的數(shù)據(jù)。

操作過(guò)程：

1、新建數(shù)據(jù)庫(kù)，創(chuàng)建需要恢復(fù)的數(shù)據(jù)庫(kù)的表結(jié)構(gòu)。

2、使用vim打開(kāi)此表的ibd文件，16進(jìn)制查看。

[root@localhost test]# vim -b tmp.ibd  
:%!xxd   

3、使用vim打開(kāi)要恢復(fù)的ibd文件，16進(jìn)制查看

4、修改要恢復(fù)的ibd文件，將紅方框中的值修改的和剛剛創(chuàng)建的新表的ibd文件一致。看到后面大段的0000沒(méi)，我們只需要修改文件頭就可以了。00000c0偏移量以后的不用修改。

[root@localhost test]# vim -b tmp.ibd  
:%!xxd -r #一定要先執(zhí)行這一步  
:wq  

5、把待恢復(fù)的ibd文件覆蓋剛剛創(chuàng)建的新表的ibd文件。修改文件權(quán)限為MySQL用戶(hù)。

6、重啟MySQL，重啟時(shí)加上參數(shù)innodb_force_recovery。

7、將數(shù)據(jù)dump出來(lái)，找回?cái)?shù)據(jù)成功。

工具七：extundelete

這個(gè)工具是基于Linux的文件恢復(fù)工具，可以用來(lái)恢復(fù)誤刪除的表，對(duì)于DML和truncate操作無(wú)能為力。其主要原理是在Linux文件系統(tǒng)中，刪除文件只是刪除了文件系統(tǒng)的inode信息，物理文件仍然在磁盤(pán)上，通過(guò)此工具即可將誤刪除的文件恢復(fù)正常。當(dāng)然前提是物理文件沒(méi)有被覆蓋。類(lèi)似的工具還有ext3grep、debugfs等，不再贅述。

工具八：Percona Data Recovery Tool for InnoDB

這個(gè)工具是Percona公司開(kāi)發(fā)的一款I(lǐng)nnoDB數(shù)據(jù)恢復(fù)工具，目前已經(jīng)停止開(kāi)發(fā)，但是仍然可用。它通過(guò)在原始數(shù)據(jù)文件(ibd) 中直接提取表的行記錄，實(shí)現(xiàn)我們從損壞的表恢復(fù)數(shù)據(jù)的目的。要完成這類(lèi)恢復(fù)，前提是要知道待恢復(fù)的表結(jié)構(gòu)。Percona Data Recovery Tool for InnoDB直接讀取InnoDB的物理頁(yè)，按照我們給出的表定義，把數(shù)據(jù)恢復(fù)成類(lèi)csv文件?；謴?fù)后的數(shù)據(jù)可能包含正確的行記錄，也可能包含不正確的行記錄，并且拿到的數(shù)據(jù)比較亂，需要做進(jìn)一步的處理才能導(dǎo)入到數(shù)據(jù)庫(kù)中。這個(gè)辦法是沒(méi)有辦法中的辦法了，不得已而為之，希望大家都不會(huì)用到這個(gè)工具。

以上為本人在運(yùn)維MySQL過(guò)程中總結(jié)的數(shù)據(jù)恢復(fù)經(jīng)驗(yàn)，希望能給大家?guī)?lái)幫助，謝謝!