表空间迁移

表空间迁移。

有如下原因你可能需要将InnoDB表复制到不同的数据库服务器上。

• 不增加生产负载的情况下生成 一个报表
• 在一个新的服务器上建立一个和生产上数据相同的表
• 做一个备份在发生问题或错误操作时用于恢复
• 快速将数据从一个服务器迁移到另一个服务器

命令FLUSH TABLES ... FOREXPORT 使.ibd文件保持一致的状态。只有文件处于一致的状态我们才可以复制它。这个文件也会同时创建一个扩展名.cfg的二进制的文件。命令ALTER TABLE ...IMPORT TABLESPACE 会使用这个二进制文件对导入过程进行校验。

对于 MySQL 5.6.8版本, ALTER TABLE ...IMPORT TABLESPACE 命令不再一定需要一个扩展名为.cfg二进制文件了。但如果真的没有这个文件我们会收到下面这样一个警告。

Message:InnoDB: IO Read error: (2, No such file or directory) Error opening '.\

test\t.cfg',will attempt to import without schema verification

1row in set (0.00 sec)

     

这个特性有时候还是很有用的。比如，在模式不匹配的导入过程中，或者在一些需要恢复的情景下，元数据又不能从.ibd文件获得，则这个命令不需要一个扩展名为.cfg的二进制文件就可以导入的特性就很有用。

可迁移表空间的限制：

• innodb_file_per_table 一定要打开成 ON. 在共享表空间上的表不能使用这个特性。
• 当表处理静默状态时,只有只读语句可以使用这张表。
• 当导入表空间时，目的库的页尺寸要和源库的页尺寸相匹配。
• DISCARD TABLESPACE 不支持分区表。如果你在分区表上使用命令 ALTER     TABLE ... DISCARD TABLESPACE 你会看到如下错误: ERROR 1031 (HY000): 表引擎没有这个选项。
• DISCARD TABLESPACE 命令不支持有父子关系的表。如果 foreign_key_checks 被设置成1. 在使用命令之前我们可以将这一参数设置为0. foreign_key_checks=0.
• ALTER     TABLE ... IMPORT TABLESPACE 命令在导入表时不会检查主外键关系。
• 如果是实时复制的时候, innodb_file_per_table 必需在主服务和从服务上设置为ON。

 MySQL5.6支持transportable tablespaces（可传输表空间）、允许单表空间(每个表的.ibd文件)动态导入导出

innodb导出物理导入导出备份

实施的基本步骤：

1、新建一个一样的表结构（数据库名称可以不一样）示例如下：
※约束条件、字符集等等也必须一致，建议使用show create table t1; 来获取创建表的SQL，否则在新服务器上导入表空间的时候会提示1808错误（如图）。

root@localhost [ww]>CREATE TABLE `t1` (

  `id` int(11) DEFAULT NULL,  `username` varchar(10) CHARACTER SET utf8 DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

2、旧表上刷新表数据

root@localhost [ww]> flush table 表名 for export   #会生成一个表名.cfg的文件（为共享的数据字典文件），并随之锁定表及有外键相关联的表

3、在新的表空间下卸载表空间

root@localhost [ww]> alter table 表名 discard tablespace;   #表名.ibd文件消失

4、将表名.cfg和表名.ibd文件拷贝到新库下

cp 表名.cfg 表名.ibd /XX/new database (两台机器通过SCP命令进行传输)

5、修改表的属主属组权限使用mysql能访问数据

chown -R mysql:mysql /XX/new database/表名.cfg 表名.ibd 

6、进入新库，导入表空间

root@localhost [ww]> alter table 表名 import tablespace;   (表名.cfg的文件依然存在)

7、查看数据文件即可

root@localhost [ww]> select * from 表名;

8、执行检查一下表的状态（可以不执行）

root@localhost [ww]> check table 表名;

※但是将MySQL5.6版本的表文件传输至MySQL5.7版本（确切的是MySQL5.7.7以后的版本）在执行完

alter table 表名 import tablespace;后会报1808号错误，类似错误内容格式如下：

ERROR 1808 (HY000):Schema mismatch (Table flags don't match, server table has 0x8 and the meta-data file has 0x1)

或者：

ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)

    第二个提示比较明显，究其原因是从MySQL5.7.7版本中innodb_file_format参数（该参数表示innodb文件格式）的默认值发生了变化，在MySQL5.7.7以前innodb_file_format参数默认是Antelope，而默认的行格式是（ROW_FORMAT）是COMPACT，从MySQL5.7.7以后版本innodb_file_format默认值为Barracuda，默认的行格式是（ROW_FORMAT）是DYNAMIC，所以需要在创建表结构时指定row_format=compact

1、在新库中删除对应的表

root@localhost [ww]> drop table 表名;

2、查看新库中数据文件是否删除完毕，如没有通过命令删除

rm -rf 表名.*

3、创建新的表结构并指定行格式为compact

CREATE TABLE `表名` (
  `ID` INT(11),
  `Name` CHAR(35)
) ROW_FORMAT=COMPACT

4、在新的表空间下卸载表空间

root@localhost [ww]> alter table 表名 discard tablespace;   #表名.ibd文件消失

5、将表名.cfg和表名.ibd文件拷贝到新库下

cp 表名.cfg 表名.ibd /XX/new database (两天机器通过SCP命令进行传输)

6、修改表的属主属组权限使用mysql能访问数据

chown -R mysql:mysql /XX/new database/表名.cfg 表名.ibd 

7、进入新库，导入表空间

root@localhost [ww]> alter table 表名 import tablespace;   (表名.cfg的文件依然存在)

8、执行检查一下表的状态（可以不执行）

root@localhost [ww]> check table 表名;

9、查看数据文件即可

root@localhost [ww]> select * from 表名;

显示如下：数据表正常！

参考：https://www.percona.com/blog/2015/12/01/how-to-transport-tablespace-from-mysql-5-6-to-mysql-5-7/