InnoDB 表手动删除后的恢复

MySQL · liushuiwuqing · 于 2年前发布 · 2353 次阅读

模拟过程

1.新建表

create table innodbt (id int auto_increment primary key ,name varchar(20));

2.进入datadir 目录,找到对应的文件,手动删除数据

   rm innodbt.frm innodbt.ibd

InnoDB 存储引擎不同于 MyISAM ,MyISAM 可以手动的在操作系统层面删除,InnoDB 会记录 tablespace 的 id。手动删除会造成系统表空间不完整。如果现在重启这个 MySQL 实例,然后查看错误日志就可以找到类似下面的这段话

150525 16:02:12  InnoDB: Error: table 'test/innodbt'
InnoDB: in InnoDB data dictionary has tablespace id 1000,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
**InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html**
InnoDB: for how to resolve the issue.

下面的措施其实就是根据错误日志中给出的 innodb-troubleshooting 来解决这个问题的。

恢复步骤

1.如果前面模拟的时候你 没有 rm innodbt.ibd,那么 innodbt 表的数据可以看得到,赶紧dump出来保存下来。方法如下:

1.1 相同的库上新建一个表结构一致的、表名不同的table

mysql>create table tmpt (id int auto_increment primary key ,name varchar(20));

1.2进入到操作系统对应的数据目录复制 tmpt 表结构到 innodbt 原来的目录下:

cp tmpt.frm innodbt.frm

1.3 查看数据

mysql> show tables;     #可以发现 innodbt 这个表
mysql> flush tables;     #避免表定义缓存在内存中,刷新一下。

mysql>select from innodbt limit n; #如果没有显示 innodbt 不存在,表示数据可以被识别。 验证通过以后 mysql>select from innodbt into outfile 'filepath'; 保存下来。

3.3 这个时候你会发现 innodbt 表可以像原来那样使用了。

搞定了。

2.如果前面都删除了,那就简单了,只需要执行下面的操作,让 InnoDB 系统表文件(ibdata*)记录的表空间是干净的就可以了。

2.1 相同的库上新建一个表结构一致的、表名不同的table

mysql> create table tmpt (id int auto_increment primary key ,name varchar(20));

2.2进入到操作系统对应的数据目录复制 tmpt 表结构到 innodbt 原来的目录下:

$ cp tmpt.frm innodbt.frm
$ cp temp.ibd innodbt.ibd

2.3 进入 MySQL ,show tables 可以发现 innodbt 这个表

root@localhost [intm]>show tables;
root@localhost [intm]>show create table innodbt;

就这样就算是搞定了,只是没有数据了!可以通过原来的逻辑备份导入数据把。

自己研究的原因

一. InnoDB 存储引擎虽然在 系统表空间(ibdata file) 中虽然存储了 table 的 tablespace id。当我们手动 rm t.frm t.ibd 的时候,系统表空间仍然还存在 t 的定义信息(不同于MyISAM,它的 internal metadata 也存在表的信息。)。所以当我重建一个表结构一样的表(包括 storage engine 也要一样),只要把 frm ibd 文件复制过去,其实在 InnoDB 那边看来,t 的信息并没有变,表定义结构+表数据文件 都可以对的上,也就可以识别了。

二. drop 原理。

根据 MySQL 官网上的说法,drop table t的时候,是'''先删除 t.frm''' +相对应的InnoDB 数据目录下的数据文件 t.ibd 。 如果t.ibd 和 t.frm 跟 InnoDB internal metadata 不一致的话,那么 drop table t 的时候,你会发现 t.idb 是没法删除的,t.frm 会删除掉。

操作过程

1.建个新表
CREATE TABLE innodbt1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.进入底层的InnoDB 数据目录下
$ cp innodbt1.frm innodbt.frm
$ cp innodbt1.ibd innodbt.ibd

3.mysql> show tables;     #innodbt 会显示出来,我理解是因为 innodbt.frm 缘故。

4.mysql> drop table innodbt;
ERROR 1051 (42S02): Unknown table 'innodbt'

5.$ ls
进入InnoDB 数据目录下 ,执行 ls,你会发现 innodbt.ibd 仍然存在。

官方解释:

14.3.5.13 InnoDB Table and Index Structures
Role of the .frm File for InnoDB Tables

MySQL stores its data dictionary information for tables in .frm files in database directories. Unlike other
MySQL storage engines, InnoDB also encodes information about the table in its own internal data
dictionary inside the tablespace. When MySQL drops a table or a database, it deletes one or more
.frm files as well as the corresponding entries inside the InnoDB data dictionary. You cannot move
InnoDB tables between databases simply by moving the .frm files.

Definition of ib-file set
** The .frm files are created and managed by MySQL, and can sometimes get out of sync with  the internal metadata in InnoDB.**

最后小结

其实我刚开始学习 MySQL 的时候,经常会手动的把表 rm 掉,后面发现 InnoDB 不能这么干的时候就长了个心眼,进入 MySQL drop 命令操作了。

goolge 上有说 mysql_upgrade 或者 drop database 可以。drop database 可以(相当于把父目录给干掉了,但是代价有点大!), mysql_upgrade 不靠谱,测试了下也不行。


本帖已经被管理员设置为: 精华帖 !
共收到 2 条回复 MySQL

@ruyi 第一次来写这样正经的内容,格式或者内容哪里不好的,麻烦指出来下,下次我把不懂的也拿出来大家共同学习把。我现在主要学习的还是 MySQL 为主的基础知识。

ruyi#22年前 0 个赞

@liushuiwuqing 非常感谢。写的很不错。

代码可以在前面加4个空格就自动变成代码高亮了。

多行代码请在代码区域的前一行和后一行各添加三个Esc键下面的点号(`)就可以了 你可以试试看

3楼 已删除.
4楼 已删除.
5楼 已删除.
6楼 已删除.
7楼 已删除.
回复本帖 (需要登录)