MySQL中怎么备份恢复单个innodb表

共计 2659 个字符，预计需要花费 7 分钟才能阅读完成。

MySQL 中怎么备份恢复单个 innodb 表，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

使用限制和说明

innodb_file_per_table 必须设置为 on，在 MySQL5.6.6 版本默认是开启的。居留在共享系统表空间的表不能静默。

当表静默时，只有只读事务被允许。

当导入表空间时，页面大小必须与导入实例的页面大小相符合。

DISCARD TABLESPACE 不支持分区表，也就意味着 transportable tablespaces 也不支持分区表。如果在分区表上执行 ALTER TABLE … DISCARD TABLESPACE 将会返回下面的错误信息：ERROR 1031 (HY000): Table storage engine for part doesn t have this option.

当 foreign_key_checks= 1 时，DISCARD TABLESPACE 不支持主键外键约束关系。操作这些表时需要设置为 foreign_key_checks。

ALTER TABLE … IMPORT TABLESPACE 不强制外键约束。如果表之间有外键约束，所有的表应该在同一个时间点被导出。

ALTER TABLE … IMPORT TABLESPACE 导入表空间不要求.cfg 元数据文件。然而在导入时缺少了.cfg 文件元数据检查就无法完成，或返回下面的信息：InnoDB: IO Read error: (2, No such file or directory) Error opening .\test\t.cfg , will attempt to import without schema verification 1 row in set (0.00 sec)。

当没有不匹配的表结构时，导入没有.cfg 文件可能会更方便。此外，在元数据不能从.ibd 文件中收集的故障恢复时，导入没有.cfg 可能更有用的。

导出导入的 MySQL 版本需要相同。否则，文件必须要在导入的服务器上创建。

在复制架构中，主和从必须设置 innodb_file_per_table=1。

在 windows 中，文件是不区分大小写的，而 Linux 和 unix 是区分大小写的，在跨平台导入导出时，需要设置 lower_case_table_names=1。

将表空间复制到另一台上

此过程将演示如何从一个运行的 MySQL 服务器实例上将表空间复制到另一台上。假设源实例为 server_A，目的实例为 server_B。

在 server_A 上

mysql use test;

mysql CREATE TABLE ttlsa(id INT) engine=InnoDB;

在 server_B 上

mysql use test;

mysql CREATE TABLE ttlsa(id INT) engine=InnoDB;

在 server_B 上

放弃现有的表空间。在表空间导入前，InnoDB 必须丢弃已连接到接受表的表空间。

1mysql ALTER TABLE ttlsa DISCARD TABLESPACE;

在 server_A 上

执行 FLUSH TABLES … FOR EXPORT 语句静默表并生成.cfg 元数据文件。FLUSH TABLES … FOR EXPORT 这个执行之后，会话不能退出，否则 cfg 自动消失。

mysql use test;

mysql FLUSH TABLES ttlsa FOR EXPORT;

文件.cfg 创建在 InnoDB 数据目录。

在 server_A 上

复制.ibd 和.cfg 文件到 server_B 上;

shell scp /path/to/datadir/test/ttlsa.{ibd,cfg} destination-server:/path/to/datadir/test

文件.ibd 和.cfg 必须在释放共享锁之前复制。

在 server_A 上

释放 FLUSH TABLES … FOR EXPORT 语句锁

mysql use test;

mysql UNLOCK TABLES;

在 server_B 上

导入表空间

mysql use test;

mysql ALTER TABLE ttlsa IMPORT TABLESPACE;

Transportable Tablespace 内幕

以下说明在表空间传输过程中的内部和错误日志信息。

当在 server_B 上执行 ALTER TABLE … DISCARD TABLESPACE

该表锁定在 X 模式下;

表空间从该表分离;

当在 server_A 上执行 FLUSH TABLES … FOR EXPORT

表锁定在共享模式下;

purge coordinator 线程停止;

脏页被同步到磁盘上;

表元数据写入到二进制.cfg 文件中;

日志信息如下：

[Note] InnoDB: Sync to disk of test . ttlsa started.

[Note] InnoDB: Stopping purge

[Note] InnoDB: Writing table metadata to ./test/ttlsa.cfg

[Note] InnoDB: Table test . ttlsa flushed to disk

当在 server_A 上执行 UNLOCK TABLES

二进制.cfg 文件将删除;

共享锁将释放，purge coordinator 线程将重启;

日志信息如下：

[Note] InnoDB: Deleting the meta-data file ./test/ttlsa.cfg

[Note] InnoDB: Resuming purge

当在 server_B 上执行 ALTER TABLE … IMPORT TABLESPACE

每个表空间页面将检查损坏;

每个空间 ID 和日志序号 (LSN) 将更新;

标志有效的和 LSN 更新头页;

Btree 页将更新;

页面状态被设置为脏将被写入到磁盘;

日志信息如下：

[Note] InnoDB: Importing tablespace for table test/ttlsa that was exported from host ubuntu

[Note] InnoDB: Phase I – Update all pages

[Note] InnoDB: Sync to disk

[Note] InnoDB: Sync to disk – done!

[Note] InnoDB: Phase III – Flush changes to disk

[Note] InnoDB: Phase IV – Flush complete

看完上述内容，你们掌握 MySQL 中怎么备份恢复单个 innodb 表的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！