[[194731]]

前言

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

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

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

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

正文

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

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

不….!

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

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

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

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

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

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

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

恢復(fù)思路：

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

舉個(gè)例子：

庫(kù)名：test_restore

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

表文件：G_RESTORE.ibd、G_RESTORE.frm

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

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

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

2. 查看并修改test_restore庫(kù)中表在新實(shí)例中的id

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

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

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

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

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

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

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

保存結(jié)果如下:

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

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

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

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

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

innodb_force_recovery=6  
innodb_purge_threads=0 

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

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

導(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 

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

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

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

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文件，格式如下：(庫(kù)名–表名–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)建所有庫(kù)，準(zhǔn)備所有表結(jié)構(gòu)，寫腳本，依據(jù)space id號(hào)自動(dòng)創(chuàng)建新表

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

參考備份語(yǔ)句：

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

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

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

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

#!/bin/bash 
#因?yàn)榍懊婕僭O(shè)為10，所以從11開(kāi)始創(chuàng)建 
oid=11 
 
#打開(kāi)前面生成的ibd.txt文件，按行讀取”庫(kù)名–表名–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個(gè)表后，才真正導(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，與原來(lái)備份的.ibd文件編號(hào)一致，導(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ù)庫(kù)實(shí)例目錄下，修改my.cnf

innodb_force_recovery=6 
innodb_purge_threads=0 

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

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

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

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

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

但是可以通過(guò)修改MySQL配置文件[mysqld]部分中innodb_file_per_table的參數(shù)來(lái)開(kāi)啟獨(dú)立表空間模式，每個(gè)數(shù)據(jù)庫(kù)的每個(gè)表都會(huì)生成一個(gè)數(shù)據(jù)空間。

優(yōu)點(diǎn)：

1.每個(gè)表都有自已獨(dú)立的表空間。

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

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

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

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

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

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

缺點(diǎn)：

單表增加過(guò)大，如超過(guò)100個(gè)G。

結(jié)論：

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

配置方式：

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

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

innodb_file_per_table=1 

2.查看是否開(kāi)啟：

mysql> show variables like ‘%per_table%’; 

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

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

mysql> show variables like ‘%per_table%’;