MySQL大表快速迁移复制（表空间迁移）

MySQL(5.6.6及以上)支持表空间传输：表空间传输可以很方便的将一个表或者多个表,从一个数据库实例导入到另一个数据库实例.
在数据恢复和数据迁移的时候，非常有用，尤其是数据量很大的表。相对于mysqldump,表空间传输的方式更快，更灵活。

• 确保innodb_file_per_table开启

MariaDB [db_pub]> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

使用表空间传输有两种方式

1、MySQL原生命令

• 首先,在源实例执行

show create table image_info_del \G;

• 得到创建表的SQL语句后在目标实例执行上面返回的建表SQL创建一个同名的表.

CREATE TABLE `image_info_del` ( ……) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 清空刚创建的表空间 (用于删除ibd文件)

alter table test.image_info_del discard tablespace;

• 然后在源实例执行

flush table db_pub.image_info_del for export;

可以看到,执行命令之后,innodb_buffer_pool中的脏页刷新到了磁盘。并产生了一个 cfg文件.在命令执行之后,该表只能只读访问.

• 复制ibd文件,cfg文件,trg文件(记录触发器信息，可能没有)拷贝到目标实例的数据库中
  然后源实例执行

cp image_info_del.{cfg,ibd} test/

• 解锁源数据库表锁

unlock tables;

• 最后在目标实例执行表空间导入

alter table test.image_info_del import tablespace;

2、使用innobackupex,协助表空间传输

原生的方式适合小范围导出,比如一两个表.
如果导出的表很多,可以用innobackupex进行辅助,并且innobackupex没有锁,不影响线上的业务.

• 首先,批量导出表结构(mysqldump -d)

• 在目标实例批量创建表并批量清空表空间
  alter table 表名 discard tablespace;(用于删除ibd文件)

• 然后在源实例执行导出

innobackupex -u root -p xxx --defaults-file=/home/mysql/mysql-5.6.14/my.cnf  --include='test.stat*' /backup_data

或者使用 tables-file的方式

innobackupex --user=root --password=xxx --defaults-file=/home/mysql/mysql-5.6.14/my.cnf --tables-file=/tmp/table_name_list.txt /backup_data

再或者(--databases=参数也可以直接命令行指定库或表名，例如:--databases="test.order_log")

innobackupex --user=root --password=xxx --defaults-file=/home/mysql/mysql-5.6.14/my.cnf --databases=/tmp/table_name_list.txt /backup_data

• 导出完成之后,执行恢复
  innobackupex --apply-log --export /backup_data/对应备份日期目录

• 然后将ibd文件,cfg文件和exp文件拷贝到目标实例数据库

• 最后在目标实例执行表空间导入

alter table 表名 import tablespace;