[[194731]]

前言

數(shù)據(jù)庫丟失之痛

磁盤壞道、斷電等意外不是常態(tài)，但遇上了就足夠你“驚心動魄”!

如果是數(shù)據(jù)庫損壞造成的數(shù)據(jù)丟失，Binlog也不可用了，怎么辦?~~

為了在短時間內(nèi)無損恢復(fù)數(shù)據(jù)以保證業(yè)務(wù)穩(wěn)定性，除了利用binlog，我們還修煉了一招新的恢復(fù)技能!

正文

我們?nèi)粘Ｊ褂玫谋容^多的兩種數(shù)據(jù)庫恢復(fù)方法是：

以上兩種方法都可以實現(xiàn)實時性的回檔，但是你會認(rèn)為有了這兩種技能就夠了嗎?

不….!

在線上這種錯綜復(fù)雜的架構(gòu)中，其實還有很多未知的原因，我們是沒法預(yù)知的。例如以下這種情況：

因辛勤勞動而折壽的磁盤產(chǎn)生成長壞道，導(dǎo)致數(shù)據(jù)庫損壞。而又剛好損壞了ibdata文件和binlog文件。那么如果還想著以定時備份+binlog恢復(fù)的方案就不可能了，難道只能用定點備份回檔嗎?深思熟慮后，作為一名運維人員，我們是絕對不會在萬不得已的情況下實行有損回檔，因為這對業(yè)務(wù)產(chǎn)生太大的影響了，但是除此之外又能怎么辦呢?下面我們將要放一門大招!!!

首先檢查數(shù)據(jù)庫環(huán)境，是否開啟了獨立表空間，如果已經(jīng)開啟的話，那恭喜你，有很大的機會可以恢復(fù)全部數(shù)據(jù)。我們可以依賴每個數(shù)據(jù)庫目錄下的frm和ibd文件來實現(xiàn)數(shù)據(jù)恢復(fù)，一般來說如果使用了InnoDB但沒開啟獨立表空間的話，所有的數(shù)據(jù)庫表信息和元數(shù)據(jù)都會寫入ibdata文件里，這樣長久運行的話，ibdata文件會變得越來越大，數(shù)據(jù)庫性能下降。InnoDB提供了開啟獨立表空間參數(shù)，可以讓數(shù)據(jù)獨立存放起來，這樣子ibdata文件只用于存放一些引擎相關(guān)的索引信息，實際的數(shù)據(jù)寫入到獨立的frm和ibd文件里。

好，有了frm和ibd文件，我們可以開始嘗試數(shù)據(jù)恢復(fù)了，他的過程比binlog還原既驚險又有趣!首先我們來看一下關(guān)于ibd和frm的說明：

.frm文件：保存了每個表的元數(shù)據(jù)，包括表結(jié)構(gòu)的定義等，該文件與數(shù)據(jù)庫引擎無關(guān)。

.ibd文件：InnoDB引擎開啟了獨立表空間(my.ini中配置innodb_file_per_table = 1)產(chǎn)生的存放該表的數(shù)據(jù)和索引的文件。

我們都知道，對于InnoDB的數(shù)據(jù)庫，如果不把整個數(shù)據(jù)目錄拷貝，只拷貝指定數(shù)據(jù)庫目錄到新的實例下，數(shù)據(jù)庫是認(rèn)不出來的。那么如何根據(jù)這兩個文件還恢復(fù)數(shù)據(jù)庫呢?

恢復(fù)思路：

由于ibdata文件上存放了一些關(guān)于引擎的索引信息，ibdata文件損壞導(dǎo)致表名索引丟失而無法啟動。那么我們可以先把原來舊的整個數(shù)據(jù)目錄改名備份，然后重新初始化數(shù)據(jù)庫生成新的ibdata文件，然后重新創(chuàng)建原有的數(shù)據(jù)庫以及對應(yīng)的表，***把備份的表空間id號改為新建的表空間id號(ibdata文件里有每個表唯一的表空間索引id，該id由創(chuàng)建新表的數(shù)量依次遞增)，這樣就可以恢復(fù)原來的數(shù)據(jù)庫了。

舉個例子：

庫名：test_restore

表結(jié)構(gòu)：db_struc.sql

表文件：G_RESTORE.ibd、G_RESTORE.frm

1. 創(chuàng)建新庫，導(dǎo)入表結(jié)構(gòu)

#mysql -uroot –p**** -e “create database test_restore”

#mysql -uroot –p**** test_restore < db_struc.sql

2. 查看并修改test_restore庫中表在新實例中的id

#vim -b /data/database/mysql/test_restore/G_RESTORE.ibd

直接打開為亂碼，轉(zhuǎn)成16進制查看。Vi中執(zhí)行 :%!xxd 轉(zhuǎn)化為16進制。結(jié)果為 :

如圖所示。G_RESTORE表在mysql數(shù)據(jù)庫中的id為00fe。

修改備份的G_RESTORE.ibd文件。操作同上，注意需先備份。

#cp G_RESTORE.ibd{,_back}  
#vim -v G_RESTORE.ibd 

將011b修改為00fe 。注意。修改完成后需要在vim中先執(zhí)行 :%!xxd -r

再wq 保存退出文件。不然保存到的是16進制查看的結(jié)果。

保存結(jié)果如下:

將修改好的G_RESTORE.ibd 替換掉新數(shù)據(jù)庫中的G_RESTORE.ibd文件。

關(guān)于ibdata表id的解釋：

參考官方文檔解釋，每個表空間分配了4個字節(jié)存儲了表空間id信息,***偏移量地址為38。還有一組預(yù)留的表空間id，同樣是4個字節(jié)，***偏移量地址為42。

3. 驗證并還原mysql數(shù)據(jù)

關(guān)閉mysql。修改my.conf。

innodb_force_recovery=6  
innodb_purge_threads=0 

啟動數(shù)據(jù)庫。如果不修改。數(shù)據(jù)庫會認(rèn)為G_RESTORE已被損壞。

Select 一下，即可查看到還原結(jié)果，但此時插入數(shù)據(jù)會報錯，應(yīng)盡快將數(shù)據(jù)dump出來 ,導(dǎo)回原來的實例中。

導(dǎo)出數(shù)據(jù)，再導(dǎo)入數(shù)據(jù)，恢復(fù)完畢!

#mysqldump -uroot –p****** test_restore > test_restore.sql  
#mysql -uroot –p****** test_restore < test_restore.sql 

說明：變更了新的space id后的.ibd表文件，啟動數(shù)據(jù)庫后只能認(rèn)出數(shù)據(jù)，但不能寫入，這是因為原ibdata文件不僅保存了space id索引，還同時保存了一些其它的元數(shù)據(jù)。為了使元數(shù)據(jù)補全，所以采取導(dǎo)出、再導(dǎo)入的操作。

以上舉例為單個庫表的恢復(fù)過程，看到這里大家一定會產(chǎn)生另一個疑問吧?線上的場景不可能是只有一個表的，數(shù)據(jù)庫表很多的情況下，這樣一個個表的修改，速度無疑是太慢了。那么存在大量表的情況下如何恢復(fù)呢?思路是，取得備份的ibd文件的id值，按id值順序來建表，中間跨度隨便建表語句來湊夠數(shù)(每個表空間索引id由創(chuàng)建新表的數(shù)量依次遞增)。實現(xiàn)方式如下：

1. 獲取備份數(shù)據(jù)庫ibd文件的space id號，并排序。

for ibd in `find test_restore/ -name “*.ibd”` ; do echo -e “${ibd//\// } \c” ;hexdump -C ${ibd} |head -n 3 |tail -n 1|awk ‘{print strtonum(“0x”$6$7)}’ ;done | sort -n -k 3 | column -t > /tmp/ 

生成的ibd.txt文件，格式如下：(庫名–表名–SpaceId)

2. 新建表，查看當(dāng)前表空間id(假設(shè)space id為10)

#mysql -uroot –p****** -e”create table test.tt(a bool)”  
#hexdump -C mysql/test/tt.ibd |head -n 3 |tail -n 1|awk ‘{print strtonum(“0x”$6$7)}’ 

3. 先創(chuàng)建所有庫，準(zhǔn)備所有表結(jié)構(gòu)，寫腳本，依據(jù)space id號自動創(chuàng)建新表

準(zhǔn)備好數(shù)據(jù)庫表結(jié)構(gòu)，可以從備份文件里取出來(我們備份方式是把結(jié)構(gòu)和數(shù)據(jù)分開備份的)，或者從其他有相同表結(jié)構(gòu)的服務(wù)器上備份再拷貝過來。

參考備份語句：

mysqldump -uroot –p****** -d ${db} –T /data/backup/${db}/ 

創(chuàng)建原有的數(shù)據(jù)庫：

mysql -uroot –p****** -e “create database ${db}” 

恢復(fù)表id創(chuàng)建表腳本：

#!/bin/bash 
#因為前面假設(shè)為10，所以從11開始創(chuàng)建 
oid=11 
 
#打開前面生成的ibd.txt文件，按行讀取”庫名–表名–SpaceId” 
cat /tmp/ibd.txt | while read db tb id ;do 
 
#假如我們需要恢復(fù)catetory表，他的id為415，基于id是創(chuàng)表自增的原則，即415-11=404， 
#我們還需要循環(huán)創(chuàng)建404個表后，才真正導(dǎo)入catetory表結(jié)構(gòu)。 
for ((oid;oid<id;oid++)); do 
mysql -uroot –p****** -e “create table test.t(a bool);drop table test.t;” && echo “${oid} ok” 
done 
 
#循環(huán)創(chuàng)建404次表后，id為415，與原來備份的.ibd文件編號一致，導(dǎo)入表結(jié)構(gòu) 
mysql -uroot –p****** ${db} < /data/backup/${db}/${tb%%.ibd}.sql && echo “${oid} ${db}/${tb%%.ibd}.sql ok” 
let oid=oid+1 
done 

4. 檢查表空間id 和備份的是否一致

for ibd in `find test_restore/ -name “*.ibd”` ; do echo -e “${ibd//\// } \c” ;hexdump -C ${ibd} |head -n 3 |tail -n 1|awk ‘{print strtonum(“0x”$6$7)}’ ;done | sort -n -k 3 | column -t > /tmp/ibd2.txt 

確認(rèn)一致后，拷貝備份的.ibd文件到新數(shù)據(jù)庫實例目錄下，修改my.cnf

innodb_force_recovery=6 
innodb_purge_threads=0 

啟動數(shù)據(jù)庫。后續(xù)步驟如同單表恢復(fù)，直接導(dǎo)出恢復(fù)到原來實例中即可。

當(dāng)然，這種方式是在數(shù)據(jù)庫出現(xiàn)極端情況下，不得不采取的一種方式，線上最重要的還是做好主從同步和定時備份，從而規(guī)避此類風(fēng)險。

關(guān)于InnoDB引擎獨立表空間說明：

使用過MySQL的同學(xué)，剛開始接觸最多的莫過于MyISAM表引擎了，這種引擎的數(shù)據(jù)庫會分別創(chuàng)建三個文件：表結(jié)構(gòu)、表索引、表數(shù)據(jù)空間。我們可以將某個數(shù)據(jù)庫目錄直接遷移到其他數(shù)據(jù)庫也可以正常工作。然而當(dāng)你使用InnoDB的時候，一切都變了。

InnoDB默認(rèn)會將所有的數(shù)據(jù)庫InnoDB引擎的表數(shù)據(jù)存儲在一個共享空間中：ibdata1，這樣就感覺不爽，增刪數(shù)據(jù)庫的時候，ibdata1文件不會自動收縮，單個數(shù)據(jù)庫的備份也將成為問題。通常只能將數(shù)據(jù)使用mysqldump導(dǎo)出，然后再導(dǎo)入解決這個問題。

但是可以通過修改MySQL配置文件[mysqld]部分中innodb_file_per_table的參數(shù)來開啟獨立表空間模式，每個數(shù)據(jù)庫的每個表都會生成一個數(shù)據(jù)空間。

優(yōu)點：

1.每個表都有自已獨立的表空間。

2.每個表的數(shù)據(jù)和索引都會存在自已的表空間中。

3.可以實現(xiàn)單表在不同的數(shù)據(jù)庫中移動。

4.空間可以回收(除drop table操作處，表空不能自已回收)

a) Drop table操作自動回收表空間，如果對于統(tǒng)計分析或是日值表，刪除大量數(shù)據(jù)后可以通過:alter table TableName engine=innodb;回縮不用的空間。

b) 對于使innodb-plugin的Innodb使用turncate table也會使空間收縮。

c) 對于使用獨立表空間的表，不管怎么刪除，表空間的碎片不會太嚴(yán)重的影響性能，而且還有機會處理。

缺點：

單表增加過大，如超過100個G。

結(jié)論：

共享表空間在Insert操作上少有優(yōu)勢。其它都沒獨立表空間表現(xiàn)好。當(dāng)啟用獨立表空間時，請合理調(diào)整一下：innodb_open_files。

配置方式：

1.innodb_file_per_table設(shè)置.開啟方法：

在my.cnf中[mysqld]下設(shè)置

innodb_file_per_table=1 

2.查看是否開啟：

mysql> show variables like ‘%per_table%’; 

3.關(guān)閉獨享表空間

innodb_file_per_table=0關(guān)閉獨立的表空間

mysql> show variables like ‘%per_table%’;