internal难点化解办法_Mysql_脚本之家,6种innodb数据字典复苏措施

网站事件日志出现了这个错误:

6种innodb数据字典恢复方法

事件类型: 错误事件来源: MySQL事件种类: 无事件 ID: 100日期:
2012-8-14事件: 8:07:59用户: N/A计算机: abcserver描述:复制代码 代码如下:Cannot find or open table
phpwind/pw_zhuanti from the internal data dictionary of InnoDB though
the .frm file for the table exists. Maybe you have deleted and recreated
InnoDB data files but have forgotten to delete the corresponding .frm
files of InnoDB tables, or you have moved .frm files to another
database? or, the table contains indexes that this version of the
enginedoesn’t support.See

you can resolve the problem.For more information, see Help and Support
Center at .

但是查了一下,并没有这个表啊。这个的错误的意思大概是某表已经删除了,但是.frm文件还存在,所以会造成读取错误等等。

 

于是服务器上找了一下,在mysql数据存储文件夹,找到了pw_zhuanti.frm文件,删除,之后解决了,再也没有出现过这个问题了。

frm文件重于一切,无论是改表结构还是删表

 

问题一
误删frm文件或者把frm文件改名为其他名字,比如123.frm改为123.txt的解决办法
或者把frm文件删除了

CREATE TABLE Failure Due to Orphan Table

A symptom of an out-of-sync data dictionary is that a CREATE TABLE
statement fails. If this occurs, look in the server’s error log. If the
log says that the table already exists inside the InnoDB internal data
dictionary, you have an orphan table inside the InnoDB tablespace files
that has no corresponding .frm file. The error message looks like
this:

InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

解决方法
如果只是改名把frm文件重新改回123.frm即可
如果是误删frm文件,那么在另一个库新建一个表结构和表名一样的表,然后把frm文件拷贝到当前数据库下

 CREATE TABLE `tt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

删除frm文件之后,不停止mysql,执行show create table依然能显示tt表的表结构
根据表结构在school2库创建tt表,拷贝frm文件到school库,改文件权限,重启mysql,tt表正常



rm -f ./tt.frm 
ll

cd school2
ll
cp tt.frm  ../school/
cd ../school/
ll
chown mysql:mysql  ./tt.frm
/etc/init.d/mysql stop
/etc/init.d/mysql start
/usr/local/mysql/bin/mysql -uroot -p -S  /data/mysql/mysql3306/tmp/mysql.sock

 


问题二
误删了ibd文件
ERROR 1016: Can’t open file: ‘child2.ibd’. (errno: 1)

解决方法
直接把frm文件删除即可,整个表就会删除

我的测试结果是 ,直接drop table就可以了,连frm文件也会删除


问题三
Orphan Intermediate Tables
删除孤儿中间表
ALTER TABLE 操作 (ALGORITHM=INPLACE),一般会产生孤儿中间表


问题四
Orphan Temporary Tables
删除孤儿临时表
在做table-copying表数据拷贝的时候 ALTER TABLE 操作
(ALGORITHM=COPY),mysql突然挂了
会产生孤儿临时表


问题五
表空间不存在
Tablespace Does Not Exist
ibd文件和frm文件被误删
但是ibdata1里面的InnoDB data dictionary 数据字典依然保留着表空间ID
tablespace id N

解决方法
在其他库里创建一个相同表结构的表,然后把frm文件拷贝到当前库,然后drop
table把表删除
innodb会把ibd文件丢失的信息打印到errorlog里面

CREATE TABLE `t6` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

删除frm和ibd文件之后,不停止mysql,执行show create table依然能显示t6表的表结构
根据表结构在school2库创建t6表,拷贝frm文件到school库,改文件权限,然后删除t6表,drop table t6,删除成功

2017-05-20 12:17:17 7fe40808b700  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
2017-05-20 12:17:17 32064 [ERROR] InnoDB: File ./school/tt.ibd: 'delete' returned OS error 71.
2017-05-20 12:33:14 7fe40808b700  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
2017-05-20 12:33:14 32064 [ERROR] InnoDB: File ./school/t6.ibd: 'delete' returned OS error 71.

 

 


 

问题六
还原孤儿 File-Per-Table ibd文件
Restoring Orphan File-Per-Table ibd Files
没有frm文件只有ibd文件

解决方法
利用表空间传输,根据表结构新建同样的表,然后导入ibd文件

mysql> CREATE DATABASE sakila;

mysql> USE sakila;

mysql> CREATE TABLE actor (
    ->    actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->    first_name VARCHAR(45) NOT NULL,
    ->    last_name VARCHAR(45) NOT NULL,
    ->    last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->    PRIMARY KEY  (actor_id),
    ->    KEY idx_actor_last_name (last_name)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;


mysql> ALTER TABLE sakila.actor DISCARD TABLESPACE;

shell> cp /backup_directory/actor.ibd path/to/mysql-5.7/data/sakila/


mysql> ALTER TABLE sakila.actor IMPORT TABLESPACE; SHOW WARNINGS;    
Query OK, 0 rows affected, 1 warning (0.15 sec)

Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory)
Error opening './sakila/actor.cfg', will attempt to import
without schema verification


mysql> SELECT COUNT(*) FROM sakila.actor;
+----------+
| count(*) |
+----------+
|      200 |
+----------+

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注