mysql 001%3e002_2016 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788 - 惜分飞...

在mysql中由于某种原因保存有ibd文件，但是表已经被删除或者frm文件损坏亦或者ibdata文件损坏/丢失等。本文模拟在这种情况下，通过mysql自身技术即可完成ibd文件恢复.

测试环境mysql版本

mysql> select version();

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

| version() |

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

| 5.6.25 |

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

1 row in set (0.00 sec)

mysql主要参数

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这个参数为on才能够实现每个表存储单独的ibd文件.innodb_force_recovery参数默认范围0

测试表情况

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 | 胡元会 | 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

驱动器 D 中的卷没有标签。

卷的序列号是 4215-1F18

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

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

1 个文件 98,304 字节

0 个目录 78,789,591,040 可用字节

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

驱动器 D 中的卷没有标签。

卷的序列号是 4215-1F18

d:\ 的目录

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

1 个文件 98,304 字节

0 个目录 78,789,591,040 可用字节

模拟删除表(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

驱动器 D 中的卷没有标签。

卷的序列号是 4215-1F18

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

找不到文件

创建新表

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

驱动器 D 中的卷没有标签。

卷的序列号是 4215-1F18

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

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

1 个文件 98,304 字节

0 个目录 78,789,591,040 可用字节

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

驱动器 D 中的卷没有标签。

卷的序列号是 4215-1F18

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

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

1 个文件 98,304 字节

0 个目录 78,787,141,632 可用字节

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

驱动器 D 中的卷没有标签。

卷的序列号是 4215-1F18

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

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

1 个文件 98,304 字节

0 个目录 78,787,141,632 可用字节

启动mysql 服务,查询数据库

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 日志报错

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文件和现在数据库记录的ibd文件的page的字典信息不匹配，因为数据库无法正常查询该数据，而且mysql为了安全直接把实例给crash了.

恢复操作

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 | 胡元会 | 698d51a19

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

d5cfee8 | NULL | 1 |

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

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

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

1 row in set (0.00 sec)

通过mysql自带的discard tablespace和import tablespace操作后,表数据已经可以完成查询了.

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日志依旧报了page字典信息不匹配.但是数据已经可以访问,通过mysqldump导出重新创建表即可.如果由于ibd损坏使用该方法无法恢复,请参考:MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)