[[194731]]

前言

數據庫丟失之痛

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

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

為了在短時間內無損恢復數據以保證業務穩定性，除了利用binlog，我們還修煉了一招新的恢復技能!

正文

我們日常使用的比較多的兩種數據庫恢復方法是：

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

不….!

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

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

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

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

.frm文件：保存了每個表的元數據，包括表結構的定義等，該文件與數據庫引擎無關。

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

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

恢復思路：

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

舉個例子：

庫名：test_restore

表結構：db_struc.sql

表文件：G_RESTORE.ibd、G_RESTORE.frm

1. 創建新庫，導入表結構

#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

直接打開為亂碼，轉成16進制查看。Vi中執行 :%!xxd 轉化為16進制。結果為 :

如圖所示。G_RESTORE表在mysql數據庫中的id為00fe。

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

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

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

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

保存結果如下:

將修改好的G_RESTORE.ibd 替換掉新數據庫中的G_RESTORE.ibd文件。

關于ibdata表id的解釋：

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

3. 驗證并還原mysql數據

關閉mysql。修改my.conf。

innodb_force_recovery=6  
innodb_purge_threads=0 

啟動數據庫。如果不修改。數據庫會認為G_RESTORE已被損壞。

Select 一下，即可查看到還原結果，但此時插入數據會報錯，應盡快將數據dump出來 ,導回原來的實例中。

導出數據，再導入數據，恢復完畢!

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

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

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

1. 獲取備份數據庫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. 新建表，查看當前表空間id(假設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. 先創建所有庫，準備所有表結構，寫腳本，依據space id號自動創建新表

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

參考備份語句：

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

創建原有的數據庫：

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

恢復表id創建表腳本：

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

確認一致后，拷貝備份的.ibd文件到新數據庫實例目錄下，修改my.cnf

innodb_force_recovery=6 
innodb_purge_threads=0 

啟動數據庫。后續步驟如同單表恢復，直接導出恢復到原來實例中即可。

當然，這種方式是在數據庫出現極端情況下，不得不采取的一種方式，線上最重要的還是做好主從同步和定時備份，從而規避此類風險。

關于InnoDB引擎獨立表空間說明：

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

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

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

優點：

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

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

3.可以實現單表在不同的數據庫中移動。

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

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

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

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

缺點：

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

結論：

共享表空間在Insert操作上少有優勢。其它都沒獨立表空間表現好。當啟用獨立表空間時，請合理調整一下：innodb_open_files。

配置方式：

1.innodb_file_per_table設置.開啟方法：

在my.cnf中[mysqld]下設置

innodb_file_per_table=1 

2.查看是否開啟：

mysql> show variables like ‘%per_table%’; 

3.關閉獨享表空間

innodb_file_per_table=0關閉獨立的表空間

mysql> show variables like ‘%per_table%’;