MySQL表被drop如何恢复?（mysql drop 恢复）

一、使用限制和说明：

1、MySQL5.6.6及以上版本。

1、主从innodb_file_per_table必须设置为on。

2、必须是innodb表，非分区表。

4、表结构必须一致。

5、不支持外键约束。

二、原理

利用MySQL表空间传输特性，即transportable tablespace特性，允许表空间从一个实例移动到另一个实例上，将.cfg 和 .ibd

文件复制过去，用于再导入时更新表元数据。

三、拿备份恢复后的ibd文件恢复：

恢复单表备份

shell> xtrabackup --prepare --export --target-dir=/backup/base_20220304

需恢复的表名为aaa，在test库下，从备份文件中物理拷贝过来aaa.ibd文件

mysql> create table aaa;

mysql> alter table aaa discard tablespace;

shell> cp /backup/base_20220304/aaa.cfg /opt/mysql3306/data/test/aaa.cfg

shell> cp /backup/base_20220304/aaa.ibd /opt/mysql3306/data/test/aaa.ibd

shell> chown mysql.mysql aaa.* && chmod 644 aaa.*

mysql> alter table aaa import tablespace; ----时间长短受ibd文件大小的限制

四、拿实例运行中的ibd文件恢复，例如延迟从库

实例a：待恢复

实例b：正常状态

1、实例a：

mysql> alter table aaa discard tablespace;

2、实例b：执行flush tables ... for export语句静默表并生成.cfg元数据文件。flush tables ... for export 这个执行之后，会话不能退出，否则cfg自动消失。

mysql> flush tables aaa for export;

shell> cp /opt/mysql3306/data/test/aaa.cfg /tmp ----备份

shell> cp /opt/mysql3306/data/test/aaa.ibd /tmp

mysql> unlock tables;

3、scp复制实例b上tmp目录的.ibd和.cfg文件到实例a上。

4、实例a：

mysql> alter table aaa import tablespace;

五、注意事项

1、上述操作务必不能删除.frm文件，如需删除，请提前cp一份。

2、必须确保双方ibd文件对应的表结构是一致，否则后面恢复就很麻烦，甚至需要重启实例恢复。

六、复杂问题

1、例如前一天全备后，第二天做过加字段，改换过字段顺序；或者拿测试环境表结构恢复，之后才发现实际表结构与生产不一致；

回答：如果拿来恢复的ibd文件对应的表结构与实际要恢复的表结构不一致时，因为没有提前发现，这时执行alter table aaa import tablespace会报错某些字段不一致，不要慌，drop table aaa，然后创建与拿来恢复的ibd文件一样的表结构，重新恢复。如果这时新建的表还是与拿来恢复的ibd文件表结构不一致，后续恢复如遇到报错就需要重启实例恢复。

2、表被drop后，没有保留表结构，或者不记得表结构了。

回答：MySQL官方下载并安装mysqlfrm，可以恢复ibd文件对应的表结构。

[root@test mysql-utilities-1.6.5]#tar -zxvf mysql-utilities-1.6.5.tar.gz

[root@test mysql-utilities-1.6.5]#cd mysql-utilities-1.6.5/

[root@test mysql-utilities-1.6.5]# ll

total 140

-rw-r--r-- 1 mysql mysql 37814 Mar 3 09:48 CHANGES.txt

-rw-r--r-- 1 mysql mysql 17987 Mar 3 09:48 LICENSE.txt

-rw-r--r-- 1 mysql mysql 928 Mar 3 09:48 PKG-INFO

-rw-r--r-- 1 mysql mysql 34819 Mar 3 09:48 README.txt

drwxr-xr-x 4 root root 36 Mar 3 09:48 build

drwxr-xr-x 3 mysql mysql 17 Mar 3 09:48 docs

-rw-r--r-- 1 mysql mysql 6680 Mar 3 09:48 info.py

-rw-r--r-- 1 root root 5597 Mar 3 09:48 info.pyc

drwxr-xr-x 4 mysql mysql 79 Mar 3 09:48 mysql

drwxr-xr-x 2 mysql mysql 4096 Mar 3 09:48 scripts

-rw-r--r-- 1 mysql mysql 14232 Mar 3 09:48 setup.py

drwxr-xr-x 2 mysql mysql 4096 Mar 3 09:48 unit_tests

[root@test mysql]# python setup.py build

[root@test mysql]# python setup.py install

mysqlfrm --diagnostic /opt/mysql3306/data/test.frm

注意：这样恢复的表结构可能字段类型和长度与原表不一样，但字段名和顺序肯定一致，可以参考之前的表结构字段类型和长度，人工组织下表结构。

码字不易，如果对你有帮助，欢迎点赞支持；如果对文章内容有任何的疑问或者是我有写的不对的地方，欢迎评论区与我探讨。