InnoDB磁盘结构之导入InnoDB表

InnoDB磁盘结构之导入InnoDB表

本节介绍如何使用可传输表空间功能导入表，该功能允许导入表，分区表或每个表文件表空间中的单个表分区。您可能要导入表的原因有很多：

• 在非生产MySQL服务器实例上运行报告，以避免在生产服务器上增加额外的负载。
• 将数据复制到新的副本服务器。
• 从备份的表空间文件中还原表。
• 与导入转储文件相比，移动数据是一种更快的方法，这需要重新插入数据和重建索引。
• 使用适合您的存储要求的存储介质将数据移动到服务器。例如，您可以将忙碌的表移至SSD设备，或将大表移至高容量HDD设备。

在传输表空间功能在本节中的下列主题所述：

• 先决条件
• 导入表格
• 导入分区表
• 导入表分区
• 局限性
• 使用说明
• 内部构造

先决条件

• 该innodb_file_per_table 变量必须启用，默认情况下为启用。
• 表空间的页面大小必须与目标MySQL服务器实例的页面大小匹配。 InnoDB页面大小由innodb_page_size变量定义，该 变量在初始化MySQL服务器实例时配置。
• 如果表具有外键关系，则 foreign_key_checks必须在执行之前将其禁用DISCARD TABLESPACE。另外，您应该在相同的逻辑时间点导出所有与外键相关的表，因为 ALTER TABLE ... IMPORT TABLESPACE这不会对导入的数据施加外键约束。为此，请停止更新相关表，提交所有事务，获取表上的共享锁，然后执行导出操作。
• 从另一个MySQL服务器实例导入表时，两个MySQL服务器实例都必须具有通用状态（GA），并且必须具有相同的版本。否则，必须在将表导入到的同一MySQL服务器实例上创建表。
• 如果表是通过在DATA DIRECTORY语句中指定子句在外部目录中创建的CREATE TABLE，则在目标实例上替换的表必须使用相同的DATA DIRECTORY 子句定义。如果子句不匹配，则报告架构不匹配错误。要确定源表是否用DATA DIRECTORY子句定义，请使用 SHOW CREATE TABLE查看表定义。有关使用该 DATA DIRECTORY子句的信息，请参见 第14.6.1.2节“在外部创建表”。
• 如果ROW_FORMAT未在表定义中明确定义或 ROW_FORMAT=DEFAULT使用选项， innodb_default_row_format 则源实例和目标实例上的设置必须相同。否则，当您尝试导入操作时，将报告架构不匹配错误。使用 SHOW CREATE TABLE检查表定义。使用SHOW VARIABLES检查 innodb_default_row_format 设置。有关相关信息，请参见 定义表的行格式。

导入表格

本示例演示如何导入驻留在每个表文件表空间中的常规非分区表。

1. 在目标实例上，创建一个与您要导入的表具有相同定义的表。（您可以使用SHOW CREATE TABLE语法获取表定义。）如果表定义不匹配，则在尝试导入操作时将报告架构不匹配错误。

   mysql> USE test;
   mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;
   

2. 在目标实例上，丢弃刚创建的表的表空间。（在导入之前，必须丢弃接收表的表空间。）

   mysql> ALTER TABLE t1 DISCARD TABLESPACE;
   

3. 在源实例上，运行 FLUSH TABLES ... FOR EXPORT以静默要导入的表。停顿表时，表上仅允许只读事务。

   mysql> USE test;
   mysql> FLUSH TABLES t1 FOR EXPORT;
   

   FLUSH TABLES ... FOR EXPORT确保对命名表的更改已刷新到磁盘，以便可以在服务器运行时进行二进制表副本的复制。当 FLUSH TABLES ... FOR EXPORT运行时， InnoDB生成 .cfg的表的架构目录元数据文件。该.cfg文件包含在导入操作期间用于架构验证的元数据。

4. 将.ibd文件和 .cfg元数据文件从源实例复制到目标实例。例如：

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

   的.ibd文件和 .cfg文件必须释放共享锁之前如在下一步中所述被复制。

   注意

   如果要从加密的表空间导入表，则 除了元数据文件外，还会 InnoDB生成一个 文件。该 文件必须与文件一起复制到目标实例 。该 文件包含一个传输密钥和一个加密的表空间密钥。导入时， 使用传输密钥解密表空间密钥。有关相关信息，请参见 “ InnoDB静态数据加密”。 .cfp``.cfg``.cfp``.cfg``.cfp``InnoDB

5. 在源实例上，用于 UNLOCK TABLES释放该FLUSH TABLES ... FOR EXPORT语句获取的锁 ：

   mysql> USE test;
   mysql> UNLOCK TABLES;
   

6. 在目标实例上，导入表空间：

   mysql> USE test;
   mysql> ALTER TABLE t1 IMPORT TABLESPACE;
   

导入分区表

本示例演示了如何导入分区表，其中每个表分区都位于每个表文件表空间中。

1. 在目标实例上，创建具有与要导入的分区表相同定义的分区表。（您可以使用SHOW CREATE TABLE语法获取表定义 。）如果表定义不匹配，则在尝试导入操作时将报告架构不匹配错误。

   mysql> USE test;
   mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 3;
   

   在 目录中， 三个分区中的每个分区都有一个表空间文件。 /*datadir*/test``.ibd

   mysql> \! ls /path/to/datadir/test/
   db.opt  t1.frm  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd
   

2. 在目标实例上，丢弃分区表的表空间。（在导入操作之前，必须丢弃接收表的表空间。）

   mysql> ALTER TABLE t1 DISCARD TABLESPACE;
   

   .ibd分区表 的三个表空间文件从 目录中丢弃 ，剩下以下文件： /*datadir*/test

   mysql> \! ls /path/to/datadir/test/
   db.opt  t1.frm
   

3. 在源实例上，运行 FLUSH TABLES ... FOR EXPORT以静默要导入的分区表。停顿表时，表上仅允许只读事务。

   mysql> USE test;
   mysql> FLUSH TABLES t1 FOR EXPORT;
   

   FLUSH TABLES ... FOR EXPORT确保将对命名表的更改刷新到磁盘上，以便可以在服务器运行时进行二进制表复制。当 FLUSH TABLES ... FOR EXPORT运行时， InnoDB产生 .cfg在表的每个表的表空间文件的架构目录元数据文件。

   mysql> \! ls /path/to/datadir/test/
   db.opt t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd
   t1.frm  t1#P#p0.cfg  t1#P#p1.cfg  t1#P#p2.cfg
   

   这些.cfg文件包含在导入表空间时用于架构验证的元数据。 FLUSH TABLES ... FOR EXPORT只能在表上运行，不能在单个表分区上运行。

4. 将.ibd和 .cfg文件从源实例架构目录复制到目标实例架构目录。例如：

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

   的.ibd和.cfg 文件必须释放共享锁之前如在下一步中所述被复制。

   注意

   如果要从加密的表空间导入表，则 除了元数据文件外，还会 InnoDB生成 文件。这些 文件必须与文件一起复制到目标实例 。这些 文件包含一个传输密钥和一个加密的表空间密钥。导入时， 使用传输密钥解密表空间密钥。有关相关信息，请参见 “ InnoDB静态数据加密”。 .cfp``.cfg``.cfp``.cfg``.cfp``InnoDB

5. 在源实例上，用于 UNLOCK TABLES释放由FLUSH TABLES ... FOR EXPORT以下对象获得的锁 ：

   mysql> USE test;
   mysql> UNLOCK TABLES;
   

6. 在目标实例上，导入分区表的表空间：

   mysql> USE test;
   mysql> ALTER TABLE t1 IMPORT TABLESPACE;
   

导入表分区

本示例演示了如何导入单个表分区，其中每个分区都位于每个表文件表空间文件中。

在以下示例中，导入了四分区表的两个分区（p2 和p3）。

1. 在目标实例上，创建一个定义与您要从中导入分区的分区表相同的分区表。（您可以使用SHOW CREATE TABLE语法获取表定义。）如果表定义不匹配，则在尝试导入操作时将报告架构不匹配错误。

   mysql> USE test;
   mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;
   

   在 目录中， 四个分区中的每个分区都有一个表空间文件。 /*datadir*/test``.ibd

   mysql> \! ls /path/to/datadir/test/
   db.opt  t1.frm  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd t1#P#p3.ibd
   

2. 在目标实例上，丢弃要从源实例导入的分区。（在导入分区之前，必须从接收到的分区表中丢弃相应的分区。）

   mysql> ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;
   

   .ibd从 目标实例上的目录中删除了两个废弃分区 的表空间文件 ，并保留了以下文件： /*datadir*/test

   mysql> \! ls /path/to/datadir/test/
   db.opt  t1.frm  t1#P#p0.ibd  t1#P#p1.ibd
   

   注意

   当ALTER TABLE ... DISCARD PARTITION ... TABLESPACE上subpartitioned表上运行，无论分区和子分区表名是允许的。指定分区名称后，该分区的子分区将包含在操作中。

3. 在源实例上，运行 FLUSH TABLES ... FOR EXPORT以静默分区表。停顿表时，表上仅允许只读事务。

   mysql> USE test;
   mysql> FLUSH TABLES t1 FOR EXPORT;
   

   FLUSH TABLES ... FOR EXPORT确保将对命名表的更改刷新到磁盘上，以便可以在实例运行时进行二进制表复制。当 FLUSH TABLES ... FOR EXPORT运行时， InnoDB生成 .cfg的每个表中的架构目录表的表空间文件的元数据文件。

   mysql> \! ls /path/to/datadir/test/
   db.opt  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd t1#P#p3.ibd
   t1.frm  t1#P#p0.cfg  t1#P#p1.cfg  t1#P#p2.cfg t1#P#p3.cfg
   

   这些.cfg文件包含在导入操作期间用于架构验证的元数据。 FLUSH TABLES ... FOR EXPORT只能在表上运行，不能在单个表分区上运行。

4. 将用于分区和分区的.ibd和 .cfg文件 从源实例架构目录复制到目标实例架构目录。 p2``p3

   shell> scp t1#P#p2.ibd t1#P#p2.cfg t1#P#p3.ibd t1#P#p3.cfg destination-server:/path/to/datadir/test
   

   的.ibd和.cfg 文件必须释放共享锁之前如在下一步中所述被复制。

   注意

   如果要从加密的表空间导入分区，则除了元数据文件外，还会 InnoDB生成一个 文件。这些 文件必须与文件一起复制到目标实例 。这些 文件包含一个传输密钥和一个加密的表空间密钥。导入时， 使用传输密钥解密表空间密钥。有关相关信息，请参见 “ InnoDB静态数据加密”。 .cfp``.cfg``.cfp``.cfg``.cfp``InnoDB

5. 在源实例上，用于 UNLOCK TABLES释放由FLUSH TABLES ... FOR EXPORT以下对象获得的锁 ：

   mysql> USE test;
   mysql> UNLOCK TABLES;
   

6. 在目标实例上，导入表分区 p2和p3：

   mysql> USE test;
   mysql> ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;
   

   注意

   当ALTER TABLE ... IMPORT PARTITION ... TABLESPACE上subpartitioned表上运行，无论分区和子分区表名是允许的。指定分区名称后，该分区的子分区将包含在操作中。

局限性

• 在传输表空间功能仅支持驻留在文件的每个表的表空间的表。驻留在系统表空间或常规表空间中的表不支持此功能。共享表空间中的表不能被静默。
• FLUSH TABLES ... FOR EXPORT带有FULLTEXT索引的表不支持此操作，因为无法刷新全文搜索辅助表。导入带有FULLTEXT索引的表后，运行 OPTIMIZE TABLE以重建 FULLTEXT索引。或者，FULLTEXT在导出操作之前删除 索引，并在目标实例上导入表后重新创建索引。
• 由于.cfg元数据文件的限制，在导入分区表时，不会针对分区类型或分区定义差异报告架构不匹配。报告列差异。

使用说明

• ALTER TABLE ... IMPORT TABLESPACE不需要 .cfg元数据文件来导入表。但是，不带.cfg文件导入时不执行元数据检查，并且会发出类似于以下内容的警告：

  Message: 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 仅当预期不会发生架构不匹配时，才应考虑 导入不包含元数据文件的表。在没有.cfg元数据的崩溃恢复方案中，不带文件的导入功能 可能会很有用。

• 在Windows上，InnoDB内部以小写形式存储数据库，表空间和表名。为了避免在区分大小写的操作系统（例如Linux和Unix）上出现导入问题，请使用小写名称创建所有数据库，表空间和表。一种简便的方法是 在创建数据库，表空间或表之前将其添加 lower_case_table_names=1到[mysqld]您的 my.cnf或my.ini文件中：

  [mysqld]
  lower_case_table_names=1
  

• 在分区表上 运行时 ALTER TABLE ... DISCARD PARTITION ... TABLESPACE， ALTER TABLE ... IMPORT PARTITION ... TABLESPACE分区表和子分区表名称均被允许。指定分区名称后，该分区的子分区将包含在操作中。

内部构造

以下信息描述了在表导入过程中写入错误日志的内部信息和消息。

在ALTER TABLE ... DISCARD TABLESPACE目标实例上运行时间：

• 该表被锁定为X模式。
• 表空间与表分离。

在 FLUSH TABLES ... FOR EXPORT源实例上运行时：

• 刷新要导出的表已锁定为共享模式。
• 清除协调器线程已停止。
• 脏页已同步到磁盘。
• 表元数据将写入二进制 .cfg文件。

此操作的预期错误日志消息：

[Note] InnoDB: Sync to disk of '"test"."t1"' started.
[Note] InnoDB: Stopping purge
[Note] InnoDB: Writing table metadata to './test/t1.cfg'
[Note] InnoDB: Table '"test"."t1"' flushed to disk

在UNLOCK TABLES源实例上运行时：

• 二进制.cfg文件被删除。
• 释放要导入的一个或多个表上的共享锁，并重新启动清除协调器线程。

此操作的预期错误日志消息：

[Note] InnoDB: Deleting the meta-data file './test/t1.cfg'
[Note] InnoDB: Resuming purge

在ALTER TABLE ... IMPORT TABLESPACE目标实例上运行时，导入算法将对要导入的每个表空间执行以下操作：

• 检查每个表空间页是否损坏。
• 每页上的空间ID和日志序列号（LSN）都会更新。
• 验证标志，并更新标题页的LSN。
• Btree页面已更新。
• 页面状态设置为脏，以便将其写入磁盘。

此操作的预期错误日志消息：

[Note] InnoDB: Importing tablespace for table 'test/t1' that was exported
from host 'host_name'
[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

注意

您还可能会收到一条警告，告知您表空间已被丢弃（如果您丢弃了目标表的表空间），并且一条消息指出由于缺少.ibd文件而无法计算统计信息：

[Warning] InnoDB: Table "test"."t1" tablespace is set as discarded.
7f34d9a37700 InnoDB: cannot calculate statistics for table
"test"."t1" because the .ibd file is missing. For help, please refer to
http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html

更多内容欢迎关注我的个人公众号“韩哥有话说”，100G人工智能学习资料，大量后端学习资料等你来拿。