《MySQL如何利用ibd文件恢復(fù)數(shù)據(jù)？》要點(diǎn)：
本文介紹了MySQL如何利用ibd文件恢復(fù)數(shù)據(jù)？，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

前言

數(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ù)技能!

正文

還記得我們之前寫過的《只需一招,讓失控的研發(fā)愛上你》嗎?前文提到過我們?nèi)粘Ｊ褂玫谋容^多的兩種數(shù)據(jù)庫(kù)恢復(fù)方法是：

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

不….!

在線上這種錯(cuò)綜復(fù)雜的架構(gòu)中,其實(shí)還有很多未知的原因,我們是沒法預(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)境,是否開啟了獨(dú)立表空間,如果已經(jīng)開啟的話,那恭喜你,有很大的機(jī)會(huì)可以恢復(fù)全部數(shù)據(jù).我們可以依賴每個(gè)數(shù)據(jù)庫(kù)目錄下的frm和ibd文件來(lái)實(shí)現(xiàn)數(shù)據(jù)恢復(fù),一般來(lái)說(shuō)如果使用了InnoDB但沒開啟獨(dú)立表空間的話,所有的數(shù)據(jù)庫(kù)表信息和元數(shù)據(jù)都會(huì)寫入ibdata文件里,這樣長(zhǎng)久運(yùn)行的話,ibdata文件會(huì)變得越來(lái)越大,數(shù)據(jù)庫(kù)性能下降.InnoDB提供了開啟獨(dú)立表空間參數(shù),可以讓數(shù)據(jù)獨(dú)立存放起來(lái),這樣子ibdata文件只用于存放一些引擎相關(guān)的索引信息,實(shí)際的數(shù)據(jù)寫入到獨(dú)立的frm和ibd文件里.

好,有了frm和ibd文件,我們可以開始嘗試數(shù)據(jù)恢復(fù)了,他的過程比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引擎開啟了獨(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

直接打開為亂碼,轉(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ù)過程,看到這里大家一定會(huì)產(chǎn)生另一個(gè)疑問吧?線上的場(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ù)分開備份的),或者從其他有相同表結(jié)構(gòu)的服務(wù)器上備份再拷貝過來(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開始創(chuàng)建
oid=11

#打開前面生成的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ō)明：

使用過MySQL的同學(xué),剛開始接觸最多的莫過于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,這樣就感覺不爽,增刪數(shù)據(jù)庫(kù)的時(shí)候,ibdata1文件不會(huì)自動(dòng)收縮,單個(gè)數(shù)據(jù)庫(kù)的備份也將成為問題.通常只能將數(shù)據(jù)使用mysqldump導(dǎo)出,然后再導(dǎo)入解決這個(gè)問題.

但是可以通過修改MySQL配置文件[mysqld]部分中innodb_file_per_table的參數(shù)來(lá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ù)后可以通過:alter table TableName engine=innodb;回縮不用的空間.

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

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

缺點(diǎn)：

單表增加過大,如超過100個(gè)G.

結(jié)論：

共享表空間在Insert操作上少有優(yōu)勢(shì).其它都沒獨(dú)立表空間表現(xiàn)好.當(dāng)啟用獨(dú)立表空間時(shí),請(qǐ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)閉獨(dú)享表空間

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

mysql> show variables like ‘%per_table%’;

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/2731.html