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;
