展开全部
这个可以哦，只有62616964757a686964616fe59b9ee7ad9431333433623138ibd，只要你能够提供表的ddl语句，或者提供对应的ibdata，可以对其进行恢复，参考：网页链接
在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 changedmysql> 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 | 胡元会 | 698d51a19d8a121ce581499d7b701668 | |6| | -08-30 06:04:32 | 0fe3bc4dd9654687a4b85065ed5cfee8 | NULL | 1 |+----------------------------------+---------+-----------+-----------+----------------------------------+------+---------------------+----------------------------------+----------+------------+1 row in set (0.00 sec)mysql> show create table user_login \G;*************************** 1. row *************Table: user_loginCreate 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=utf81 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-1F18D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录-12-02 20:07 98,304 user_login.ibd1 个文件 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-1F18d:\ 的目录-12-25 23:15 98,304 user_login.ibd1 个文件 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-1F18D:\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-1F18D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录-12-25 23:19 98,304 user_login.ibd1 个文件 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-1F18D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录-12-25 23:22 98,304 user_login.ibd1 个文件 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.ibdC:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd驱动器 D 中的卷没有标签。卷的序列号是 4215-1F18D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录-12-02 20:07 98,304 user_login.ibd1 个文件 98,304 字节0 个目录 78,787,141,632 可用字节
启动mysql 服务,查询数据库
mysql> select count(*) from xifenfei.user_login;ERROR (HY000): Lost connection to MySQL server during querymysql> exitByeC:\Users\XIFENFEI>mysql -urootERROR (HY000): Can't connect to MySQL server on 'localhost' (10061)
mysql 日志报错
-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 dictionaryInnoDB: but in file .\xifenfei\user_login.ibd it is 47!-12-25 23:31:31 2eb8 InnoDB: Assertion failure in thread 11960 in file line 796InnoDB: We intentionally generate a memory trap.InnoDB: Submit a detailed bug report to .InnoDB: If you get repeated assertion failures or crashes, evenInnoDB: 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 | 胡元会 | 698d51a19d8a121ce581499d7b701668 | |6| | -08-30 06:04:32 | 0fe3bc4dd9654687a4b85065ed5cfee8 | NULL | 1 |+----------------------------------+---------+-----------+-----------+----------------------------------+------+---------------------+----------------------------------+----------+------------+1 row in set (0.00 sec)
通过mysql自带的discard tablespace和import tablespace操作后,表数据已经可以完成查询了.
mysql日志
-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.-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 /doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.-12-25 23:34:08 10464 [ERROR] InnoDB: Could not find a valid tablespace file for 'xifenfei/user_login'. See /doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.-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 /doc/refman/5.6/en/innodb-troubleshooting.html-12-25 23:34:08 10464 [ERROR] InnoDB: Cannot delete tablespace 56 because it is not found in the tablespace memory cache.-12-25 23:34:08 10464 [Warning] InnoDB: Cannot delete tablespace 56 in DISCARD TABLESPACE. Tablespace not found-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk - done!-12-25 23:34:41 10464 [Note] InnoDB: Phase I - Update all pages-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk - done!-12-25 23:34:41 10464 [Warning] InnoDB: Tablespace 'xifenfei/user_login' exists in the cache with id 47 != 56-12-25 23:34:41 10464 [Warning] InnoDB: Freeing existing tablespace 'xifenfei/user_login' entry from the cache with id 56-12-25 23:34:41 10464 [Note] InnoDB: Phase III - Flush changes to disk-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)
如果觉得《mysql8只有ibd文件_只有ibd文件还能恢复数据吗》对你有帮助，请点赞、收藏，并留下你的观点哦！
