导入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
子句的信息,请参见“在外部创建表”。 - 如果
ROW_FORMAT
未在表定义中明确定义或ROW_FORMAT=DEFAULT
使用选项,innodb_default_row_format
则源实例和目标实例上的设置必须相同。否则,当您尝试导入操作时,将报告架构不匹配错误。使用SHOW CREATE TABLE
检查表定义。使用SHOW VARIABLES
检查innodb_default_row_format
设置。有关相关信息,请参见定义表的行格式。
导入表格
此示例演示如何导入驻留在每个表文件表空间中的常规非分区表。
在目标实例上,创建一个与您要导入的表具有相同定义的表。(您可以使用
SHOW CREATE TABLE
语法获取表定义。)如果表定义不匹配,则在尝试导入操作时将报告架构不匹配错误。mysql>
USE test; mysql>CREATE TABLE t1 (c1 INT)ENGINE =INNODB;在目标实例上,丢弃刚创建的表的表空间。(在导入之前,必须丢弃接收表的表空间。)
mysql>
ALTER TABLE t1DISCARD TABLESPACE ;在源实例上,运行
FLUSH TABLES ... FOR EXPORT
以静默要导入的表。停顿表时,表上仅允许只读事务。mysql>
USE test; mysql>FLUSH TABLES t1FOR EXPORT ;FLUSH TABLES ... FOR EXPORT
确保对命名表的更改已刷新到磁盘,以便可以在服务器运行时进行二进制表复制。当FLUSH TABLES ... FOR EXPORT
运行时,InnoDB
生成.cfg
的表的架构目录元数据文件。该.cfg
文件包含用于在导入操作期间进行模式验证的元数据。将
.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
在源实例上,用于
UNLOCK TABLES
释放FLUSH TABLES ... FOR EXPORT
语句获取的锁:mysql>
USE test; mysql>UNLOCK TABLES ;在目标实例上,导入表空间:
mysql>
USE test; mysql>ALTER TABLE t1IMPORT TABLESPACE ;
导入分区表
本示例演示了如何导入分区表,其中每个表分区都位于每个表文件表空间中。
在目标实例上,创建具有与要导入的分区表相同定义的分区表。(您可以使用
SHOW CREATE TABLE
语法获取表定义。)如果表定义不匹配,则在尝试导入操作时将报告架构不匹配错误。mysql>
USE test; mysql>CREATE TABLE t1 (i int)ENGINE = InnoDBPARTITION 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在目标实例上,丢弃分区表的表空间。(在导入操作之前,必须丢弃接收表的表空间。)
mysql>
ALTER TABLE t1DISCARD TABLESPACE ;.ibd
分区表的三个表空间文件从目录中丢弃,剩下以下文件:/datadir/test
mysql>
\ ! ls /path/to/datadir/test/ db.opt t1.frm在源实例上,运行
FLUSH TABLES ... FOR EXPORT
以静默要导入的分区表。停顿表时,表上仅允许只读事务。mysql>
USE test; mysql>FLUSH TABLES t1FOR 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
只能在表上运行,不能在单个表分区上运行。将
.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
在源实例上,用于
UNLOCK TABLES
释放由FLUSH TABLES ... FOR EXPORT
以下对象获得的锁:mysql>
USE test; mysql>UNLOCK TABLES ;在目标实例上,导入分区表的表空间:
mysql>
USE test; mysql>ALTER TABLE t1IMPORT TABLESPACE ;
导入表分区
本示例演示了如何导入单个表分区,其中每个分区都位于每个表文件表空间文件中。
在以下示例中,导入了四分区表的两个分区(p2
和p3
)。
在目标实例上,创建与您要从中导入分区的分区表定义相同的分区表。(您可以使用
SHOW CREATE TABLE
语法获取表定义。)如果表定义不匹配,则在尝试导入操作时将报告架构不匹配错误。mysql>
USE test; mysql>CREATE TABLE t1 (i int)ENGINE = InnoDBPARTITION 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在目标实例上,丢弃要从源实例导入的分区。(在导入分区之前,必须从接收到的分区表中丢弃相应的分区。)
mysql>
ALTER TABLE t1DISCARD PARTITION p2, p3TABLESPACE ;.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表上运行,无论分区和子分区表名是允许的。指定分区名称后,该分区的子分区将包括在操作中。在源实例上,运行
FLUSH TABLES ... FOR EXPORT
以静默分区表。停顿表时,表上仅允许只读事务。mysql>
USE test; mysql>FLUSH TABLES t1FOR 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
只能在表上运行,不能在单个表分区上运行。将用于分区和分区的
.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
在源实例上,用于
UNLOCK TABLES
释放由FLUSH TABLES ... FOR EXPORT
以下对象获得的锁:mysql>
USE test; mysql>UNLOCK TABLES ;在目标实例上,导入表分区
p2
和p3
:mysql>
USE test; mysql>ALTER TABLE t1IMPORT PARTITION p2, p3TABLESPACE ;注意
当
ALTER TABLE ... IMPORT PARTITION ... TABLESPACE
上subpartitioned表上运行,无论分区和子分区表名是允许的。指定分区名称后,该分区的子分区将包括在操作中。
局限性
- 在传输表空间功能仅支持驻留在文件的每个表的表空间的表。驻留在系统表空间或常规表空间中的表不支持此功能。共享表空间中的表不能被静默。
FLUSH TABLES ... FOR EXPORT
带有FULLTEXT
索引的表不支持,因为无法刷新全文搜索辅助表。导入带有FULLTEXT
索引的表后,运行OPTIMIZE TABLE
以重建FULLTEXT
索引。或者,FULLTEXT
在导出操作之前删除索引,并在目标实例上导入表后重新创建索引。- 由于
.cfg
元数据文件的限制,在导入分区表时,不会针对分区类型或分区定义差异报告架构不匹配。报告列差异。 在MySQL 8.0.19之前,索引键部分的排序顺序信息未存储到
.cfg
表空间导入操作期间使用的元数据文件中。因此,假定索引关键字部分的排序顺序是升序的,这是默认设置。结果,如果使用DESC索引键部分排序顺序定义了导入操作中涉及的一个表,而没有使用另一表,则记录可以以意外的顺序排序。解决方法是删除并重新创建受影响的索引。有关索引键部分排序顺序的信息,请参见“ CREATE INDEX语句”。该
.cfg
文件格式是在MySQL 8.0.19更新,包括索引键部分排序顺序信息。上述问题不会影响MySQL 8.0.19或更高版本服务器实例之间的导入操作。
使用说明
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。(禁止lower_case_table_names
使用与初始化服务器时使用的设置不同的设置来启动服务器。)[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/8.0/en/innodb-troubleshooting.html