參考：mysql Innodb表空間卸載、遷移、裝載的使用方法

注意！此方法只適用于innodb_file_per_table獨(dú)立表空間的InnoDB實(shí)例。

此種方法可以恢復(fù)ibdata文件被誤刪、被惡意修改，沒有從庫和備份數(shù)據(jù)的情況下的數(shù)據(jù)恢復(fù)，不能保證數(shù)據(jù)庫所有表數(shù)據(jù)的100%恢復(fù)，目的是盡可能多的恢復(fù)。

恢復(fù)數(shù)據(jù)前提是表結(jié)構(gòu)定義文件frm可以使用，如果有下面報(bào)錯(cuò)就比較麻煩，需要手動(dòng)恢復(fù)frm文件。

我的鏈接：https://www.cnblogs.com/jiangxu67/p/4755097.html

150821 16:31:27 [ERROR] /usr/local/mysql51/libexec/mysqld: Incorrect information in file: './t/test1.frm'

InnoDB引擎ibdata和ibd文件結(jié)構(gòu)

結(jié)構(gòu)圖：https://github.com/jeremycole/innodb_diagrams

恢復(fù)原理：

因?yàn)榕渲胕nnodb_file_per_table的獨(dú)立表空間后，InnoDB引擎表數(shù)據(jù)、索引保存在ibd文件，ibdata文件只負(fù)責(zé)undo、double write、insert buffer...一些環(huán)節(jié)。

當(dāng)然如果MySQL是異常關(guān)閉同時(shí)ibdata損壞的情況下會(huì)丟失一部分?jǐn)?shù)據(jù)，但如果數(shù)據(jù)庫是單點(diǎn)，那盡量能恢復(fù)多少是多少，至少比數(shù)據(jù)全部丟失好很多。

ibdata文件中有一個(gè)數(shù)據(jù)字典data dictionary，記錄的是實(shí)例中每個(gè)表在ibdata中的一個(gè)邏輯位置，而在ibd文件中也存儲(chǔ)著同樣的一個(gè)tablespace id，兩者必須一致InnoDB引擎才能正常加載到數(shù)據(jù)，否則會(huì)報(bào)錯(cuò)：

2015-08-18 10:30:30 12571 [ERROR] InnoDB: Error: tablespace id in file ‘.\test\test1.ibd’ is 112, but in the InnoDB InnoDB: data dictionary it is 1

實(shí)際上我們對(duì)于ibdata文件中的 undo、double write、insert buffer數(shù)據(jù)可以并不擔(dān)心，我們只需要利用一個(gè)空的實(shí)例，一個(gè)干凈的ibdata文件，通過卸載和加載表空間把ibd文件與ibdata文件關(guān)聯(lián)。

恢復(fù)步驟：

準(zhǔn)備一臺(tái)新實(shí)例

1、建表，在新實(shí)例中建需要恢復(fù)表的表名臨時(shí)表。

這塊建議一次性將表都建好，可以統(tǒng)一檢查frm文件是否有損壞，注意字符集。

#循環(huán)建表[root@test1 db1]$ for i in `ls | grep ".ibd" | awk -F '.' '{print $1}'`;do mysql -uroot -p -S /tmp/mysql.sock -e "use test;create table ${i} (a int)engine=innodb default charset=utf8"; done

2、停止實(shí)例，添加配置innodb_force_recovery = 6

3、替換frm文件

#備份新表frm[root@test1 test]$ cp ./*.frm ./bak[root@test1 test]$ ls ./bak#刪除新表frm，將需要恢復(fù)表的frm復(fù)制到test目錄[root@test1 test]$ rm -rf ./*.frm[root@test1 db1]$ for i in `ls | grep ".frm" | awk -F '.' '{print $1}'`;do cp $i.frm ../db1/;done

4、啟動(dòng)實(shí)例，檢查表

#循環(huán)檢查表是否能夠打開[root@test1 db1]$ for i in `ls | grep ".ibd" | awk -F '.' '{print $1}'`;do mysql -uroot -p -S /tmp/mysql.sock -e "use test;show create table $i \G" --default-character-set=utf8 >> ./build1.txt 2>&1 ;done

如果在輸出文件中出現(xiàn)以下錯(cuò)誤則需要修復(fù)frm文件，沒有錯(cuò)誤可以繼續(xù)。修復(fù)frm見帖子開始的鏈接。

150821 16:31:27 [ERROR] /usr/local/mysql51/libexec/mysqld: Incorrect information in file: './t/test1.frm'

5、獲取ibd文件中的tablespace id

ibd文件需要hexdump打開，ibd文件的0x24,0x25位置記錄的是該表的tablespace id，我們可以通過腳本一次性獲取所有表的id。

[root@test1 db1]$ for i in `ls | grep ".ibd" | awk -F '.' '{print $1}'`;do a=`hexdump -C $i.ibd |head -n 3 |tail -n 1|awk '{print $6$7}'`;mysql -uroot -p -S /tmp/mysql.sock -e "select conv('$a',16,10)" | grep -v conv >> ./id.txt 2>&1;done

然后按照id從小到大排序，因?yàn)楹竺嫘枰凑読d從小到大恢復(fù)，不用反復(fù)重做新實(shí)例。

6、去掉innodb_force_recovery = 6配置，重啟生效

7、建表生成tablespace id

這里注意，如果ibd文件中的tablespace id是5001，那么就需要建5000個(gè)臨時(shí)表。

另外注意建表后系統(tǒng)的openfile可能會(huì)很大，需要先修改系統(tǒng)的參數(shù)，或者建和刪表可以一起做。

[root@test1 db1]$ for i in {1..5000};do mysql -uroot -p -S /tmp/mysql.sock -e "use tmp_table;create table table_${i} (a int)engine=innodb default charset=utf8"; done

8、建需要恢復(fù)的表結(jié)構(gòu)

9、卸載表空間

alter table table_name discard tablespace;

10、替換ibd文件

11、加載表空間

alter table table_name import tablespace;

官方對(duì)于卸載表和加載表的說明：

ALTER TABLE tbl_name DISCARD TABLESPACE;This deletes the current .ibd file, so be sure that you have a backup first. Attempting to modify the table contents while the tablespace file is discarded results in an error. You can perform the DDL operations listed in Section 14.10, “InnoDB and Online DDL” while the tablespace file is discarded.To import the backup .ibd file back into the table, copy it into the database directory, and then issue this statement:ALTER TABLE tbl_name IMPORT TABLESPACE;The tablespace file need not necessarily have been created on the server into which it is imported later. In MySQL 5.6, importing a tablespace file from another server works if the both servers have GA (General Availablility) status and their versions are within the same series. Otherwise, the file must have been created on the server into which it is imported.

按照以上步驟就可以把數(shù)據(jù)讀取出來，然后使用mysqldump導(dǎo)出。

如果字符集不一致或者字段類型不一致可能讀取出來的數(shù)會(huì)出現(xiàn)數(shù)據(jù)錯(cuò)誤、亂碼或者串列。

MySQL 5.6對(duì)于表結(jié)構(gòu)要求很嚴(yán)格，如果字段類型與原表不一致會(huì)報(bào)錯(cuò)。

最新評(píng)論