在mysql中由于某種原因保存有ibd文件，但是表已經(jīng)被刪除或者frm文件損壞亦或者ibdata文件損壞/丟失等。本文模擬在這種情況下，通過(guò)mysql自身技術(shù)即可完成ibd文件恢復(fù).

測(cè)試環(huán)境mysql版本

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.6.25 |

+-----------+

1 row in set (0.00 sec)

mysql主要參數(shù)

mysql> show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.00 sec)

mysql> show variables like 'innodb_force_recovery';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_force_recovery | 0 |

+-----------------------+-------+

1 row in set (0.00 sec)

innodb_file_per_table這個(gè)參數(shù)為on才能夠?qū)崿F(xiàn)每個(gè)表存儲(chǔ)單獨(dú)的ibd文件.innodb_force_recovery參數(shù)默認(rèn)范圍0

測(cè)試表情況

mysql> use xifenfei;

Database changed

mysql> show tables;

+-----------------------------+

| Tables_in_xifenfei |

+-----------------------------+

| user_login |

+-----------------------------+

1 rows in set (0.00 sec)

mysql> select count(*) from user_login;

+----------+

| count(*) |

+----------+

| 48 |

+----------+

1 row in set (0.02 sec)

mysql> desc user_login;

+------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+--------------+------+-----+---------+-------+

| ID | varchar(255) | NO | PRI | NULL | |

| ACCOUNT | varchar(255) | YES | | NULL | |

| LifeCycle | int(11) | YES | | NULL | |

| Name | varchar(255) | YES | | NULL | |

| Password | varchar(255) | YES | | NULL | |

| Role | varchar(255) | YES | | NULL | |

| UTime | varchar(255) | YES | | NULL | |

| UserID | varchar(255) | YES | | NULL | |

| UserName | varchar(255) | YES | | NULL | |

| UserStatus | int(11) | YES | | NULL | |

+------------+--------------+------+-----+---------+-------+

10 rows in set (0.05 sec)

mysql> select * from user_login limit 1;

+----------------------------------+---------+-----------+-----------+----------

------------------------+------+---------------------+--------------------------

--------+----------+------------+

| ID | ACCOUNT | LifeCycle | Name | Password

| Role | UTime | UserID

| UserName | UserStatus |

+----------------------------------+---------+-----------+-----------+----------

------------------------+------+---------------------+--------------------------

--------+----------+------------+

| 010d6c85a76c44cba80d07cbd8590bb2 | hyh | 0 | 胡元會(huì) | 698d51a19

d8a121ce581499d7b701668 | |6| | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e

d5cfee8 | NULL | 1 |

+----------------------------------+---------+-----------+-----------+----------

------------------------+------+---------------------+--------------------------

--------+----------+------------+

1 row in set (0.00 sec)

mysql> show create table user_login \G;

*************************** 1. row *************

Table: user_login

Create Table: CREATE TABLE `user_login` (

`ID` varchar(255) NOT NULL,

`ACCOUNT` varchar(255) DEFAULT NULL,

`LifeCycle` int(11) DEFAULT NULL,

`Name` varchar(255) DEFAULT NULL,

`Password` varchar(255) DEFAULT NULL,

`Role` varchar(255) DEFAULT NULL,

`UTime` varchar(255) DEFAULT NULL,

`UserID` varchar(255) DEFAULT NULL,

`UserName` varchar(255) DEFAULT NULL,

`UserStatus` int(11) DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> show variables like 'datadir';

+---------------+-----------------------------------------------+

| Variable_name | Value |

+---------------+-----------------------------------------------+

| datadir | D:\xifenfei\mysql-5.6.25-winx64\data\ |

+---------------+-----------------------------------------------+

1 row in set (0.00 sec)

備份ibd文件

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd

驅(qū)動(dòng)器 D 中的卷沒(méi)有標(biāo)簽。

卷的序列號(hào)是 4215-1F18

D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目錄

2016-12-02 20:07 98,304 user_login.ibd

1 個(gè)文件 98,304 字節(jié)

0 個(gè)目錄 78,789,591,040 可用字節(jié)

C:\Users\XIFENFEI>cp D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd d:/

C:\Users\XIFENFEI>dir d:\user_login.ibd

驅(qū)動(dòng)器 D 中的卷沒(méi)有標(biāo)簽。

卷的序列號(hào)是 4215-1F18

d:\ 的目錄

2016-12-25 23:15 98,304 user_login.ibd

1 個(gè)文件 98,304 字節(jié)

0 個(gè)目錄 78,789,591,040 可用字節(jié)

模擬刪除表(ibd文件也被刪除)

mysql> drop table xifenfei.user_login;

Query OK, 0 rows affected (0.03 sec)

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd

驅(qū)動(dòng)器 D 中的卷沒(méi)有標(biāo)簽。

卷的序列號(hào)是 4215-1F18

D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目錄

找不到文件

創(chuàng)建新表

mysql> CREATE TABLE `user_login` (

-> `ID` varchar(255) NOT NULL,

-> `ACCOUNT` varchar(255) DEFAULT NULL,

-> `LifeCycle` int(11) DEFAULT NULL,

-> `Name` varchar(255) DEFAULT NULL,

-> `Password` varchar(255) DEFAULT NULL,

-> `Role` varchar(255) DEFAULT NULL,

-> `UTime` varchar(255) DEFAULT NULL,

-> `UserID` varchar(255) DEFAULT NULL,

-> `UserName` varchar(255) DEFAULT NULL,

-> `UserStatus` int(11) DEFAULT NULL,

-> PRIMARY KEY (`ID`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.03 sec)

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd

驅(qū)動(dòng)器 D 中的卷沒(méi)有標(biāo)簽。

卷的序列號(hào)是 4215-1F18

D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目錄

2016-12-25 23:19 98,304 user_login.ibd

1 個(gè)文件 98,304 字節(jié)

0 個(gè)目錄 78,789,591,040 可用字節(jié)

mysql> select count(*) from xifenfei.user_login;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

停掉mysql,替換user_login.ibd

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd

驅(qū)動(dòng)器 D 中的卷沒(méi)有標(biāo)簽。

卷的序列號(hào)是 4215-1F18

D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目錄

2016-12-25 23:22 98,304 user_login.ibd

1 個(gè)文件 98,304 字節(jié)

0 個(gè)目錄 78,787,141,632 可用字節(jié)

C:\Users\XIFENFEI>cp d:\user_login.ibd D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd

驅(qū)動(dòng)器 D 中的卷沒(méi)有標(biāo)簽。

卷的序列號(hào)是 4215-1F18

D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目錄

2016-12-02 20:07 98,304 user_login.ibd

1 個(gè)文件 98,304 字節(jié)

0 個(gè)目錄 78,787,141,632 可用字節(jié)

啟動(dòng)mysql 服務(wù),查詢數(shù)據(jù)庫(kù)

mysql> select count(*) from xifenfei.user_login;

ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> exit

Bye

C:\Users\XIFENFEI>mysql -uroot

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

mysql 日志報(bào)錯(cuò)

2016-12-25 23:31:07 11632 [Note] MySQL: ready for connections.

Version: '5.6.25' socket: '' port: 3306 MySQL Community Server (GPL)

InnoDB: Error: tablespace id is 56 in the data dictionary

InnoDB: but in file .\xifenfei\user_login.ibd it is 47!

2016-12-25 23:31:31 2eb8 InnoDB: Assertion failure in thread 11960 in file fil0fil.cc line 796

InnoDB: We intentionally generate a memory trap.

InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

InnoDB: If you get repeated assertion failures or crashes, even

InnoDB: immediately after the mysqld startup, there may be

很明顯由于替換的ibd文件和現(xiàn)在數(shù)據(jù)庫(kù)記錄的ibd文件的page的字典信息不匹配，因?yàn)閿?shù)據(jù)庫(kù)無(wú)法正常查詢?cè)摂?shù)據(jù)，而且mysql為了安全直接把實(shí)例給crash了.

恢復(fù)操作

mysql> show variables like 'innodb_force_recovery';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_force_recovery | 1 |

+-----------------------+-------+

1 row in set (0.00 sec)

mysql> alter table xifenfei.user_login discard tablespace;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> alter table xifenfei.user_login import tablespace;

Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> select count(*) from xifenfei.user_login;

+----------+

| count(*) |

+----------+

| 48 |

+----------+

1 row in set (0.00 sec)

mysql> select * from xifenfei.user_login limit 1;

+----------------------------------+---------+-----------+-----------+----------

------------------------+------+---------------------+--------------------------

--------+----------+------------+

| ID | ACCOUNT | LifeCycle | Name | Password

| Role | UTime | UserID

| UserName | UserStatus |

+----------------------------------+---------+-----------+-----------+----------

------------------------+------+---------------------+--------------------------

--------+----------+------------+

| 010d6c85a76c44cba80d07cbd8590bb2 | hyh | 0 | 胡元會(huì) | 698d51a19

d8a121ce581499d7b701668 | |6| | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e

d5cfee8 | NULL | 1 |

+----------------------------------+---------+-----------+-----------+----------

------------------------+------+---------------------+--------------------------

--------+----------+------------+

1 row in set (0.00 sec)

通過(guò)mysql自帶的discard tablespace和import tablespace操作后,表數(shù)據(jù)已經(jīng)可以完成查詢了.

mysql日志

2016-12-25 23:34:08 10464 [ERROR] InnoDB: Failed to find tablespace for table '"xifenfei"."user_login"' in the cache. Attempting to load the tablespace with space id 56.

2016-12-25 23:34:08 10464 [ERROR] InnoDB: In file '.\xifenfei\user_login.ibd', tablespace id and flags are 47 and 0, but in the InnoDB data dictionary they are 56 and 0. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-12-25 23:34:08 10464 [ERROR] InnoDB: Could not find a valid tablespace file for 'xifenfei/user_login'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-12-25 23:34:08 30e8 InnoDB: cannot calculate statistics for table "xifenfei"."user_login" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

2016-12-25 23:34:08 10464 [ERROR] InnoDB: Cannot delete tablespace 56 because it is not found in the tablespace memory cache.

2016-12-25 23:34:08 10464 [Warning] InnoDB: Cannot delete tablespace 56 in DISCARD TABLESPACE. Tablespace not found

2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk

2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk - done!

2016-12-25 23:34:41 10464 [Note] InnoDB: Phase I - Update all pages

2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk

2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk - done!

2016-12-25 23:34:41 10464 [Warning] InnoDB: Tablespace 'xifenfei/user_login' exists in the cache with id 47 != 56

2016-12-25 23:34:41 10464 [Warning] InnoDB: Freeing existing tablespace 'xifenfei/user_login' entry from the cache with id 56

2016-12-25 23:34:41 10464 [Note] InnoDB: Phase III - Flush changes to disk

2016-12-25 23:34:41 10464 [Note] InnoDB: Phase IV - Flush complete

mysql日志依舊報(bào)了page字典信息不匹配.但是數(shù)據(jù)已經(jīng)可以訪問(wèn),通過(guò)mysqldump導(dǎo)出重新創(chuàng)建表即可.如果由于ibd損壞使用該方法無(wú)法恢復(fù),請(qǐng)參考:MySQL drop database恢復(fù)(恢復(fù)方法同樣適用MySQL drop table,delete,truncate table)

總結(jié)

以上是生活随笔為你收集整理的mysql异常恢复工具_[MySQL异常恢复]mysql ibd文件恢复的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。