小谈 MySQL 第十话·InnoDB 直接拷贝文件实现表的复制

写在最前：

在 MySQL 中如果要迁移一张表导入另一个环境中，常规的做法就是使用备份工具备份，比如 mysqldump，然后拷贝备份到目标环境导入。如果表数据量很大，导出 dump 文件很大的情况下，使用导出导入工具其实要花费不少的时间。

怎么样提高效率呢❓

有一种方案就想冷备份一样，直接拷贝表数据文件到目标环境，当然 MySQL 早期版本这么做是不支持的，因为会有很多关联数据在 ibdata 中，InnoDB 的数据存储对应的数据字典信息，是存放在共享表空间中，无法直接剥离出来，而在 5.6/5.7 中，推出了一个很不错的特性，就是迁移表空间，可以把这个配置信息剥离出来，简单来说就是把数据文件直接拷贝到目标环境，在目标环境挂载即可。

对于 InnoDB，可以使每个表使用单独的表空间，也就是每个表都有自己的文件，称为 File-Per-Table。可以利用这个特点，直接拷贝对应文件，把表复制到另一个 MySQL 实例下，又被称为 Transportable Tablespace。

1、前提条件

这样操作的一个基本前提条件是使用独立表空间，开启 innodb_file_per_table：

mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

2、操作步骤

	操作步骤	MySQL instance 00	MySQL instance 01	information
1	MySQL instance 00 创建一张测试表并插入数据	mysql> CREATE DATABASE test_meta; mysql> use test_meta; mysql> CREATE TABLE `wf_test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID', `test_name` varchar(128) NOT NULL DEFAULT '' COMMENT '名称', `create_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表'; mysql> insert into wf_test values("showufei");
2	MySQL instance 01 创建一张相同的测试表		mysql> CREATE DATABASE test_meta; mysql> use test_meta; mysql> CREATE TABLE `wf_test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID', `test_name` varchar(128) NOT NULL DEFAULT '' COMMENT '名称', `create_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表';	如果第 7 步通过 import tablespace 来进行数据文件挂载失败报错时，需要重写建表添加属性 row_format： mysql> CREATE TABLE `wf_test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID', `test_name` varchar(128) NOT NULL DEFAULT '' COMMENT '名称', `create_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 row_format=compact COMMENT='测试表'; 为什么在5.6迁移至5.7会有报错❓ 原因就是 Innodb_file_format 在 5.6 中是 Antelope，在 MySQL 5.7中是Barracuda，主要是在表压缩和行的动态格式上有所改变。（更多详细说明可参考官方文档：https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-7.html）
3	MySQL instance 01 discard 掉现有表空间		mysql> ALTER TABLE wf_test DISCARD TABLESPACE;
4	MySQL instance 00 运行 FLUSH TABLES ... FOR EXPORT 命令	mysql> FLUSH TABLES wf_test FOR EXPORT;		在数据目录下可以看到多出一个 wf_test.cfg文件
5	从 MySQL instance 00 拷贝 .ibd 和 .cfg 文件 到 MySQL instance 01	$ scp /home/mysql/data/test_meta/wf_test.{cfg,ibd} instance_01-IP:/home/mysql/data/test_meta/		直接覆盖就好，但是注意文件的 owner 和 group 权限
6	MySQL instance 00 释放  FLUSH TABLES 命令获取的锁	mysql> UNLOCK TABLES;
7	MySQL instance 01 挂载拷贝过来的表空间		mysql> ALTER TABLE wf_test IMPORT TABLESPACE;	如果两实例版本跨度比较大，如 5.6 到 5.7 通过 import tablespace 来进行数据文件挂载会报如下错误： ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.) 或 ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x5 and the meta-data file has 0x1) 通过错误信息可以发现和表的一个属性有关解决方案：添加属性 row_format（如上建表语句）
8	MySQL instance 01 校验数据		mysql> select count(*) from wf_test; mysql> select * from wf_test;	数据量可以接受的前提下直接 select * 校验