6种innodb数据字典恢复方法

6种innodb数据字典恢复方法

https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html

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文件

http://www.cnblogs.com/gomysql/p/3702216.html

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 |
+----------+