• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 导入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设置。有关相关信息,请参见定义表的行格式。

    导入表格

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

    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.cfpInnoDB

    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.cfpInnoDB

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

      mysql> USE test;
      mysql> UNLOCK TABLES;
      
    6. 在目标实例上,导入分区表的表空间:

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

    导入表分区

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

    在以下示例中,导入了四分区表的两个分区(p2p3)。

    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文件从源实例架构目录复制到目标实例架构目录。p2p3

      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.cfpInnoDB

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

      mysql> USE test;
      mysql> UNLOCK TABLES;
      
    6. 在目标实例上,导入表分区p2p3

      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元数据文件的限制,在导入分区表时,不会针对分区类型或分区定义差异报告架构不匹配。报告列差异。
    • 在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 ... TABLESPACEALTER 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