《MySQL數(shù)據(jù)恢復的九把瑞士軍刀》要點：
本文介紹了MySQL數(shù)據(jù)恢復的九把瑞士軍刀，希望對您有用。如果有疑問，可以聯(lián)系我們。

作者介紹

李輝,新浪愛彩票運維負責人,常用網(wǎng)名：門牙沒了.主導新浪愛彩票的MySQL運維工作.培訓合伙人、資深講師,中國科學院大學在讀研究生(大數(shù)據(jù)方向),擅長大型項目的關(guān)系型數(shù)據(jù)庫運維和管理,現(xiàn)在在數(shù)據(jù)庫運維自動化方向研究.

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

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

工具一：完全備份+binlog

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

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

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

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

示例1：

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

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

示例2：

某開發(fā)在處理用戶充值狀態(tài)時,漏掉了where條件,導致全表被更新.

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

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

從這里我們也可以看出此方法并不是很嚴謹,比較適合小規(guī)模的恢復.

工具三：MySQL flashback

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

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

工具四：innodb_force_recovery

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

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

1. SRV_FORCE_IGNORE_CORRUPT:忽略檢查到的corrupt頁
2. SRV_FORCE_NO_BACKGROUND:阻止主線程的運行,如主線程需要執(zhí)行full purge操作,會導致crash
3. SRV_FORCE_NO_TRX_UNDO:不執(zhí)行事務回滾操作
4. SRV_FORCE_NO_IBUF_MERGE:不執(zhí)行插入緩沖的合并操作
5. SRV_FORCE_NO_UNDO_LOG_SCAN:不查看重做日志,InnoDB存儲引擎會將未提交的事務視為已提交
6. SRV_FORCE_NO_LOG_REDO:不執(zhí)行前滾的操作.

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

工具五：DISCARD、IMPORT TABLESPACE

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

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

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

操作過程：

1. 應用物理備份的log：innobackupex –apply-log
2. 備份數(shù)據(jù)庫中的ibd：cp test.ibd test.bak
3. 丟棄數(shù)據(jù)庫中的ibd：alter table test discard tablespace;
4. 復制物理備份中的ibd到數(shù)據(jù)庫目錄：cp /bak/test.ibd /data/test/; chown mysql:mysql /data/test/test.ibd
5. 導入ibd：alter table test import tablespace;

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

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

操作過程：

1. 應用物理備份的log：innobackupex –apply-log
2. 數(shù)據(jù)庫中重建表：create table test(id int);
3. 關(guān)閉數(shù)據(jù)庫
4. 用物理備份中的ibd覆蓋數(shù)據(jù)庫中的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. 重復執(zhí)行執(zhí)行步驟6,直到?jīng)]有任何輸出為止
8. 啟動MySQL

工具六：手工修改ibd

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

操作過程：

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

2、使用vim打開此表的ibd文件,16進制查看.

3、使用vim打開要恢復的ibd文件,16進制查看

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

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

6、重啟MySQL,重啟時加上參數(shù)innodb_force_recovery.

7、將數(shù)據(jù)dump出來,找回數(shù)據(jù)成功.

工具七：extundelete

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

工具八：Percona Data Recovery Tool for InnoDB

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

以上為本人在運維MySQL過程中總結(jié)的數(shù)據(jù)恢復經(jīng)驗,希望能給大家?guī)韼椭?謝謝!

文章來自微信公眾號：DBAplus社群

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/4214.html