如何从ibd文件中恢复数据

发布网友

我来回答

共3个回答

热心网友

在使用表空间的情况下,如果不慎使得innodb存储引擎的元数据文件ibdata损坏,我们还可以挽救宝贵的数据.因为在innodb使用表空间的情况下,ibdata文件会记录每个innodb表的id,只要使得ibd中的表id和ibdata文件中记录的表id相同,就能够打开表,读取到数据.

#创建表

CREATE TABLE `ibdtest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) NOT NULL COMMENT '表b中的id',
  `content` char(255) NOT NULL COMMENT '操作内容,系统生成',
  `mark` char(255) NOT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#添加数据
INSERT ibdtest (fid,content,mark) VALUES (1,'1','1'),(2,'2','2');

SELECT * FROM ibdtest;

关闭MySQL将ibdtest.ibd copy出来,放到其他数据库中来模拟灾难.

[root@localhost ~]#/opt/soft/mysql/bin/mysqladmin -p123456 shutdown

120130 18:31:50 mysqld_safe mysqld from pidfile /opt/soft/mysql/60137.localdomain.pid ended

[1]+ Done                    /opt/soft/mysql/bin/mysqld_safe--defaults-file=/opt/soft/mysql/config/my.cnf --user=mysql

 [root@localhost ~]# cd /home/soft/mysql/data/test/
[root@localhost test]# ll
total 1296
-rw-rw----. 1 mysql mysql  8612 Jan 18 00:06 a.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 00:24 a.ibd
-rw-rw----. 1 mysql mysql  8624 Jan 30 08:34 area.frm
-rw-rw----. 1 mysql mysql 98304 Jan 30 08:36 area.ibd
-rw-rw----. 1 mysql mysql  82 Jan 18 00:05 b.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 00:08 b.ibd
-rw-rw----. 1 mysql mysql  8693 Jan 30 18:27 ibdtest.frm
-rw-rw----. 1 mysql mysql 98304 Jan 30 18:28 ibdtest.ibd
-rw-rw----. 1 mysql mysql  8728 Jan  6 16:23 testa.frm
-rw-rw----. 1 mysql mysql 98304 Jan 10 04:10 testa.ibd
-rw-rw----. 1 mysql mysql  8693 Jan 30 14:30 testmc.frm
-rw-rw----. 1 mysql mysql 98304 Jan 30 14:30 testmc.ibd
-rw-rw----. 1 mysql mysql  8693 Jan 30 13:54 testme.frm
-rw-rw----. 1 mysql mysql 98304 Jan 30 13:55 testme.ibd
-rw-rw----. 1 mysql mysql  8693 Jan 30 14:40 testmm.frm
-rw-rw----. 1 mysql mysql 98304 Jan 30 14:45 testmm.ibd
-rw-rw----. 1 mysql mysql  8693 Jan 30 13:40 testmu.frm
-rw-rw----. 1 mysql mysql 98304 Jan 30 13:40 testmu.ibd
-rw-rw----. 1 mysql mysql  8693 Jan 30 11:08 testmv.frm
-rw-rw----. 1 mysql mysql 98304 Jan 30 11:10 testmv.ibd
-rw-rw----. 1 mysql mysql  8694 Jan  4 21:55 testuser.frm
-rw-rw----. 1 mysql mysql 98304 Jan  4 22:04 testuser.ibd
-rw-rw----. 1 mysql mysql  84 Jan 14 21:55 user.frm
-rw-rw----. 1 mysql mysql 98304 Jan 14 21:55 user.ibd
[root@localhost test]# cp ibdtest.ibd /home/download/
[root@localhost test]# cd /home/download/

#vim打开ibd,使用16进制查看
[root@localhost download]# vim -b ibdtest.ibd 
:%!xxd  

从下图中能看到 此表在 当前mysql数据库中的id为0x10,即16.

此时,我们假设灾难发生,ibdata损坏…

只剩下了ibdtest.ibd文,我们跳转到另一个mysql服务器上,用同样的建表语句创建ibdtest表.

这时我们打开这个mysql服务器下的ibdtest.ibd看看:

这个表的id为0x16,即为22,那么,我们只需将原有的ibdtest.ibd表id修改为0x16即可.

出保存的时候一定要记得使用:%!xxd  -r

退出保存.

并将修改好的文件覆盖掉新的ibdtest.ibd即可,

此mysql服务器会认为该表损毁,无法打开,没关系,修改innodb_force_recovery = 6,

重启mysql服务:

Select下,就知道数据是否恢复了没有:

此时,无法执行写操作,应尽快将数据mp出来,修改innodb_force_recovery = 0,重启服务,创建新表后,把数据倒回去就ok了.恢复数据就不演示了.

热心网友

创建已经丢失的表结构

先要安装 mysql-utilities。

// RedHatyum -y install mysql-server mysql-utilities// Debianapt install mysql-utilities


使用 mysqlfrm 从 .frm 文件里面找回建表语句。

// 分析一个 .frm 文件生成建表的语句mysqlfrm --diagnostic /var/lib/mysql/test/t1.frm// 分析一个目录下的全部.frm文件生成建表语句root@username:~# mysqlfrm --diagnostic /var/lib/mysql/my_db/bk/ >createtb.sqlroot@username:~# grep "^CREATE TABLE" createtb.sql |wc -l124


可以看到一共生成了 124 个建表语句。

有很多时候也可以从其它库里面生成建表语句，如同一个应用的其它数据库或不同的测试环境，采用下面的 mysqlmp 生成建表语句：

mysqlmp --no-data --compact my_db>createtb.sql


登录 MySQL 生成表。

mysql> create database my_db;mysql> use my_dbDatabase changedmysql> source createtb.sqlQuery OK, 0 rows affected (0.07 sec)......


导入旧的数据文件

将新建的没有包括数据的 .ibd 文件抛弃

root@username:/var/lib/mysql/my_db# ll *.ibd|wc12411167941root@username:/var/lib/mysql/my_db# mysql -e "show tables from my_db" \| grep -v  Tables_in_my_db  \| while read a; do mysql -e "ALTER TABLE my_db.$a DISCARD TABLESPACE"; doneroot@username:/var/lib/mysql/my_db# ll *.ibd|wcls: cannot access '*.ibd': No such file or directory000


可以看到所有的 .idb 文件都已经被抛弃了。然后把旧的有数据的 .ibd 文件拷贝到这个 my_db 目录下面，别忘了把属主改过来：chown mysql. *，再把这些数据文件 import 到数据库中。

root@username:/var/lib/mysql/my_db# mysql -e "show tables from my_db" \| grep -v  Tables_in_my_db  \| while read a; \do mysql -e "ALTER TABLE my_db.$a import TABLESPACE"; done


导入完成后检查表

使用 mysqlcheck 对数据库 my_db 下的所有表进行检查：

root@username:/var/lib/mysql/my_db# mysqlcheck -c my_dbmy_db.cdp_backup_point                             OK......


所有的表都导入成功。

热心网友

参考：网页链接

在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|  | 2016-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-winx\data\ |+---------------+-----------------------------------------------+1 row in set (0.00 sec)

   

备份ibd文件

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx\data\xifenfei\user_login.ibd 驱动器 D 中的卷没有标签。 卷的序列号是 4215-1F18  D:\xifenfei\mysql-5.6.25-winx\data\xifenfei 的目录 2016-12-02  20:07            98,304 user_login.ibd               1 个文件         98,304 字节               0 个目录 78,7,591,040 可用字节C:\Users\XIFENFEI>cp D:\xifenfei\mysql-5.6.25-winx\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,7,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-winx\data\xifenfei\user_login.ibd 驱动器 D 中的卷没有标签。 卷的序列号是 4215-1F18  D:\xifenfei\mysql-5.6.25-winx\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-winx\data\xifenfei\user_login.ibd 驱动器 D 中的卷没有标签。 卷的序列号是 4215-1F18  D:\xifenfei\mysql-5.6.25-winx\data\xifenfei 的目录 2016-12-25  23:19            98,304 user_login.ibd               1 个文件         98,304 字节               0 个目录 78,7,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-winx\data\xifenfei\user_login.ibd 驱动器 D 中的卷没有标签。 卷的序列号是 4215-1F18  D:\xifenfei\mysql-5.6.25-winx\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-winx\data\xifenfei\user_login.ibdC:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx\data\xifenfei\user_login.ibd 驱动器 D 中的卷没有标签。 卷的序列号是 4215-1F18  D:\xifenfei\mysql-5.6.25-winx\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 ring querymysql> exitBye C:\Users\XIFENFEI>mysql -urootERROR 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 dictionaryInnoDB: 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 796InnoDB: 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, 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|  | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065ed5cfee8 | NULL     |          1 |+----------------------------------+---------+-----------+-----------+----------------------------------+------+---------------------+----------------------------------+----------+------------+1 row in set (0.00 sec)

   

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

2016-12-25 23:34:08 104 [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 104 [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 104 [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.html2016-12-25 23:34:08 104 [ERROR] InnoDB: Cannot delete tablespace 56 because it is not found in the tablespace memory cache.2016-12-25 23:34:08 104 [Warning] InnoDB: Cannot delete tablespace 56 in DISCARD TABLESPACE. Tablespace not found2016-12-25 23:34:41 104 [Note] InnoDB: Sync to disk2016-12-25 23:34:41 104 [Note] InnoDB: Sync to disk - done!2016-12-25 23:34:41 104 [Note] InnoDB: Phase I - Update all pages2016-12-25 23:34:41 104 [Note] InnoDB: Sync to disk2016-12-25 23:34:41 104 [Note] InnoDB: Sync to disk - done!2016-12-25 23:34:41 104 [Warning] InnoDB: Tablespace 'xifenfei/user_login' exists in the cache with id 47 != 562016-12-25 23:34:41 104 [Warning] InnoDB: Freeing existing tablespace 'xifenfei/user_login' entry from the cache with id 562016-12-25 23:34:41 104 [Note] InnoDB: Phase III - Flush changes to disk2016-12-25 23:34:41 104 [Note] InnoDB: Phase IV - Flush complete

   

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