MySQL物理拷贝一张Innodb表的方法

在 MySQL 中，物理拷贝表是一个相对复杂的过程，尤其是与逻辑导出（如使用 mysqldump 或导出 CSV 文件）相比。这个过程涉及直接操作数据库文件，因此需要格外小心。

物理拷贝表的具体步骤：

1、创建相同结构的空表：

首先，需要在目标数据库（dbtest）下执行 CREATE TABLE cptest LIKE test;，以创建一个与源表结构相同的空表 cptest。

2、丢弃旧表空间：

执行 ALTER TABLE cptest DISCARD TABLESPACE;。这一步会删除 cptest.ibd 文件，为导入新的表空间做准备。

3、准备导出源表空间：

执行 FLUSH TABLE test FOR EXPORT;。这一步会将表 test 锁定为只读模式，并在 dbtest 目录下生成一个 test.cfg 配置文件，为表空间的导出做准备。

4、拷贝文件：

在文件系统层面上执行拷贝命令，将 test.cfg 和 test.ibd 文件从源表的目录复制到目标表的目录。需要确保拷贝后的文件（cptest.cfg 和 cptest.ibd）能被 MySQL 进程正确读写。

5、解锁表：

执行 UNLOCK TABLES; 以释放之前设置的表锁定。

6、导入表空间：

执行 ALTER TABLE cptest IMPORT TABLESPACE;。这一步会将新的cptest.ibd 文件与表 cptest 结合，完成数据的物理拷贝过程。

注意事项：

• 在执行 FLUSH TABLES test FOR EXPORT; 后，表 test 会处于只读状态，直到执行 UNLOCK TABLES; 之后才会释放锁。
• 在导入表空间时（ALTER TABLE cptest IMPORT TABLESPACE），MySQL 会尝试匹配文件中的表空间 ID 与数据字典中的相应记录。因此，每个数据页的表空间 ID 都将被检查并修改以确保一致性。这一点对于大型文件来说尤其重要，因为每个数据页都需要更新，这可能是一个时间消耗较大的过程。

优点与局限性：

物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：

• 必须是全表拷贝，不能只拷贝部分数据；
• 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；
• 由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。