mysql 表空间传输,「MySQL」- 可传输表空间（大表迁移）

「MySQL」- 可传输表空间(大表迁移)

更新日期：2020年01月02日

@IGNORECHANGE

什么是 可传输表空间？如何进行 InnoDB 大表迁移？

需要具备的知识

什么是「可传输表空间」(Transportable Tablespace)？

允许将「表空间」从一个实例移动到另一个实例的功能。可以简单理解为：把表直接复制到另外一个实例上。没错，这里的“直接复制”指的就是物理复制(执行 cp 命令)。

在很早之前，对「InnoDB表空间」来说，这是不可能的，因为所有的表数据都是「系统表空间」的一部分。「系统表空间」就是这个样子的，很多表都在一个文件里，没有办法单独对某个表进行物理复制。

在 MySQL 5.6 及更高版本中，(1)语法FLUSH TABLES ... FOR EXPORT将使InnoDB表进入准备状态，以便复制到另一台服务器上；(3)在另外一台服务器上运行ALTER TABLE ... DISCARD TABLESPACE与ALTER TABLE ... IMPORT TABLESPACE将会将复制的数据文件引入另一个实例中。(2)单独的.cfg文件(需要与.ibd文件一同复制)用于在导入表空间时更新表的元数据(例如，space ID)。

要使用此功能，那InnoDB表必须是在innodb_file_per_table=ON的情况下创建的，因为这样每个InnoDB表才会有自己的表空间。其实就是说：当前表属于「表文件表空间」，就是每张表的数据、索引都是分离的，两张表的数据、索引不会在同一个文件中。

使用场景

1)在不对生产服务器产生额外负载的情况下，运行某些报告、汇总、统计。

2)在新的从服务器上，为表设置相同的数据。

3)在出现问题或错误后，还原表的备份版本。

4)作为一种更快速的数据移动方式，比逻辑备份还原更快。数据立即可用，而不必重新插入并重建索引。

5)将「表文件表空间」移动到具有更适合系统要求的存储介质的服务器。例如，将读写频繁的表放在SSD设备上，或在高容量HDD设备上使用大表。

操作示例

系统环境：CentOS Linux release 7.4.1708 (Core)

软件版本：mysql Ver 14.14 Distrib 5.6.45, for Linux (x86_64) using EditLine wrapper

################################################################################

# ON SOURCE

################################################################################

# 在源实例上，运行FLUSH TABLES ... FOR EXPORT以停顿表并创建.cfg元数据文件：

# 语句FLUSH TABLES ... FOR EXPORT从MySQL 5.6.6开始提供。

# 该语句确保已将对指定表的更改刷新到磁盘，以便在服务器运行时可以创建二进制表副本。

# 当运行FLUSH TABLES ... FOR EXPORT时，InnoDB在与表相同的数据库目录中生成.cfg文件。

# 而.cfg文件包含导入表空间文件时用于模式验证的元数据。

# ！！！此时：表被刷新，以导出，在共享模式下被锁定；「清除协调器线程」已停止；脏页面与磁盘同步；表

# ！！！元数据将写入二进制.cfg文件。此时会看到相应的Note日志。

FLUSH TABLES core_company_resume_history0 FOR EXPORT;

################################################################################

# ONSOURCE, TO DESTINATION

################################################################################

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

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

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

################################################################################

# ON SOURCE

################################################################################

# 在源实例上，使用UNLOCK TABLES释放FLUSH TABLES... FOR EXPORT获取的锁。

# ！！！此时：二进制.cfg文件已删除；将导入要导入的表上的共享锁，并重新启动清除协调程序线程。会看到

# ！！！相应的Note日志。

UNLOCK TABLES;

################################################################################

# ON DESTINATION

################################################################################

chown -R mysql: .

################################################################################

# ON DESTINATION

################################################################################

# 在目标实例上，创建一个表(如果不存在)

CREATE TABLE ...

################################################################################

# ON DESTINATION

################################################################################

# 在目标实例上，放弃现有表空间。 (在导入表空间之前，InnoDB必须丢弃附加到接收表的表空间。)

# ！！！此时：该表被锁定在X模式下；表空间与表分离。可能会有表空间被丢弃的日志，这是正常的。

ALTER TABLE core_company_resume_history0 DISCARD TABLESPACE;

################################################################################

# ON DESTINATION

################################################################################

# 在目标实例上，导入表空间

# 语句ALTER TABLE ... IMPORT TABLESPACE不会对导入的数据强制执行外键约束。

# 如果表之间存在外键约束，则应在相同(逻辑)时间点导出所有表。

# 在这种情况下，您将停止更新表，提交所有事务，获取表上的共享锁，然后执行导出操作。

# ！！！此时：检查每个表空间页面是否损坏；每页上的space ID和日志序列号(LSN)都会更新；验证标志

# ！！！并更新标题页的LSN；Btree页面已更新。页面状态设置为脏，以便将其写入磁盘。会看到对应的Note

# ！！！日志

ALTER TABLE core_company_resume_history0 IMPORT TABLESPACE;

限制和使用说明

#1 只有当innodb_file_per_table设置为ON(默认设置)时，表空间复制过程才可用。驻留在共享「系统表空间」中的表无法停顿。

#2 当表静默时，受影响的表上只允许只读事务。

#3 导入表空间时，「页大小」必须与导入实例的「页大小」匹配。

#4「分区表」不支持DISCARD TABLESPACE，这意味着也不支持可传输表空间。如果在分区表上运行ALTER TABLE ... DISCARD TABLESPACE，则会返回以下错误：ERROR 1031 (HY000): Table storage engine for 'part' doesn't have this option.

#5 当foreign_key_checks设置为1时，具有父子(主键 - 外键)关系的表空间不支持DISCARD TABLESPACE。在丢弃父子表的表空间之前，先进行foreign_key_checks=0设置。

#6 ALTER TABLE ... IMPORT TABLESPACE不对导入的数据强制执行外键约束。如果表之间存在外键约束，则应在相同(逻辑)时间点导出所有表。

#7 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文件的情况下导入的功能在崩溃恢复方案中非常有用，在这种情况下无法从.ibd文件中收集元数据。

#8 在MySQL 5.6或更高版本中，如果两个实例都具有GA(一般可用性)状态，且其版本在同一系列中，则从另一个MySQL服务器实例导入表空间文件是可以的。否则，必须在导入该文件的同一服务器实例上创建该文件。

#9 在复制方案中，必须在主服务器和从服务器上将innodb_file_per_table设置为ON。

#10 在Windows上，InnoDB在内部以小写形式存储数据库，表空间和表名。为避免在区分大小写的操作系统(如Linux和UNIX)上导入问题，请使用小写名称创建所有数据库、表空间、表。一种方便的方法是在创建数据库，表空间或表之前，将lower_case_table_names=1添加到my.cnf或my.ini文件的[mysqld]部分。

#11 具有FULLTEXT索引的表不支持FLUSH TABLES ... FOR EXPORT。不刷新「全文搜索辅助表」。导入具有FULLTEXT索引的表后，运行OPTIMIZE TABLE以重建FULLTEXT索引。或者，在导出操作之前删除FULLTEXT索引，并在导入目标实例上的表后重新创建它们。

#12 IMPORT TABLESPACE不是主从复制友好。可能会导致从库卡住。

See the corresponding bug on MariaDB: https://jira.mariadb.org/browse/MDEV-12437

参考文献