MySQL數(shù)據(jù)恢復(fù)的九把瑞士軍刀

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

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

工具一：完全備份+binlog

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

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

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

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

示例1：

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

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

誤操作語句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：

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

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

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

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

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

工具三：MySQL flashback

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

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

工具四：innodb_force_recovery

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

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

SRV_FORCE_IGNORE_CORRUPT:忽略檢查到的corrupt頁

SRV_FORCE_NO_BACKGROUND:阻止主線程的運(yùn)行，如主線程需要執(zhí)行full purge操作，會導(dǎo)致crash

SRV_FORCE_NO_TRX_UNDO:不執(zhí)行事務(wù)回滾操作

SRV_FORCE_NO_IBUF_MERGE:不執(zhí)行插入緩沖的合并操作

SRV_FORCE_NO_UNDO_LOG_SCAN:不查看重做日志，InnoDB存儲引擎會將未提交的事務(wù)視為已提交

SRV_FORCE_NO_LOG_REDO:不執(zhí)行前滾的操作。

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

工具五：DISCARD、IMPORT TABLESPACE

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

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

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

操作過程：

應(yīng)用物理備份的log：innobackupex --apply-log

備份數(shù)據(jù)庫中的ibd：cp test.ibd test.bak

丟棄數(shù)據(jù)庫中的ibd：alter table test discard tablespace;

復(fù)制物理備份中的ibd到數(shù)據(jù)庫目錄：cp /bak/test.ibd /data/test/; chown mysql:mysql /data/test/test.ibd

導(dǎo)入ibd：alter table test import tablespace;

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

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

操作過程：

應(yīng)用物理備份的log：innobackupex --apply-log

數(shù)據(jù)庫中重建表：create table test(id int);

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

用物理備份中的ibd覆蓋數(shù)據(jù)庫中的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對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ù)庫上創(chuàng)建表，然后修改待恢復(fù)的ibd的文件頭，使之適應(yīng)新表的space id和index id，從而讀取出ibd中的數(shù)據(jù)。

操作過程：

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

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

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

:%!xxd

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

4、修改要恢復(fù)的ibd文件，將紅方框中的值修改的和剛剛創(chuàng)建的新表的ibd文件一致?？吹胶竺娲蠖蔚?000沒，我們只需要修改文件頭就可以了。00000c0偏移量以后的不用修改。

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

:%!xxd -r #一定要先執(zhí)行這一步

:wq

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

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

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

工具七：extundelete

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

工具八：Percona Data Recovery Tool for InnoDB

下一篇：mysql誤刪數(shù)據(jù)庫表的frm文件和ibd文件？試試這樣恢復(fù)