ALTER TABLE DISCARD/IMPORT TABLESPACE

zhgc

ALTER TABLE tbl_name DISCARD TABLESPACE;

For an InnoDB table that is created with its own tablespace in an .ibd file, that file can be discarded and imported. To discard the .ibd file, use this statement:

ALTER TABLE tbl_name DISCARD TABLESPACE;
This deletes the current .ibd file, so be sure that you have a backup first. Attempting to access the table while the tablespace file is discarded results in an error.

To import the backup .ibd file back into the table, copy it into the database directory, and then issue this statement:

ALTER TABLE tbl_name IMPORT TABLESPACE;
The tablespace file must have been created on the server into which it is imported later.

Note
The ALTER TABLE ... IMPORT TABLESPACE feature does not enforce foreign key constraints on imported data.

zhgc

我们在恢复表的时候，要保证row_format和ibd文件的一致，如果ibd文件是compact的话，需要建表的时候，设置成row_format=compact,

我们使用：show table status like 't_dict'\G，查看表的属性

zhgc

如果你有.ibd文件的一个干净的备份，你可以按如下操作从被起源的地方恢复它到MySQL安装中：相当快速

1. 发出这个ALTER TABLE语句：

2. ALTER TABLE tbl_name DISCARD TABLESPACE;
警告：这个语句删除当前.ibd文件。

3. 把备份的.ibd文件放回到恰当的数据库目录。

4. 发出这个ALTER TABLE语句:

5. ALTER TABLE tbl_name IMPORT TABLESPACE;