innodb表空间传输,是MySQL5.6开始加入的新特性,支持普通表空间拷贝到其他实例下,MySQL5.7支持分区表的表空间传输,使innodb表的拷贝变得更加简单容易。
方便是方便了,但也要需要注意:
(1) innodb表空间传输不要用来做主从复制,否则会出现数据不一致的问题;
(2) 使用之前,要确认使用了innodb_file_per_table即独立表空间;
(3) 在表空间导出的过程中,事务不能进行写操作, 应该注意选择操作时间, 应该选择业务低峰期操作;
(4) 默认不支持导出有外键的表, 可以通过set foreign_key_checks=0强制忽略, 但仅限于普通表, 而分区表暂时不支持这样操作。
我们知道了innodb表空间传输的特点和使用注意事项, 现在考虑一下应用场景。因为不做主从复制,就只能做一些离线方面的使用,比如把线上某个生产表拿到离线环境做统计分析等等。
下面介绍innodb普通表空间传输和分区表空间传输的操作过程
- innodb普通表空间传输
1.1 目标库
mysql> create table t2(id int auto_increment, name varchar(20), primary key(id));
mysql> insert into t2(name) values('aa'),('bb'),('cc');
mysql> alter table t2 discard tablespace;
1.2 源库
mysql> flush tables t2 for export;
shell> scp -P2222 /data/mysql/mysql_3306/data/db1/t2.{cfg,ibd} 172.16.123.103:/data/mysql/mysql_3306/data/test/
mysql> unlock tables;
1.3 目标库
shell> chown mysql.mysql /data/mysql/mysql_3306/data/test/t2.*
mysql> alter table t2 import tablespace; -
innodb分区表空间传输
测试分区表结构create table t3(id int auto_increment, name varchar(20), primary key(id)) partition by key(id) partitions 4;
2.1 目标库
mysql> create table t3(id int auto_increment, name varchar(20), primary key(id)) partition by key(id) partitions 4;
插入测试数据(省略)
2.2 源库
mysql> flush tables t3 for export; #导出整个分区表
shell> cp -a /data/mysql/mysql_3306/data/db1/t3* /var/tmp/
mysql> unlock tables;
2.3 目标库
2.3.1 导入全数分区
目标库
mysql> alter table t3 discard tablespace;
或
mysql> alter table t3 discard partition all tablespace;
源库
shell> scp -P2222 -r t3* 172.16.123.103:/data/mysql/mysql_3306/data/test/
shell> chown mysql.mysql /data/mysql/mysql_3306/data/test/ -R
目标库
mysql> alter table t3 import tablespace;
2.3.2 导入指定分区
只导p1分区
目标库
mysql> alter table t3 discard partition p1 tablespace;
源库
shell> scp -P2222 -r t3p1 172.16.123.103:/data/mysql/mysql_3306/data/test/
目标库
mysql> alter table t3 import partition p1 tablespace;
总结一步操作的步骤:
源库上flush tables tbname for export;--->拷贝文件-->unlock tables;
目标库上alter table tbname discard [partition partition_names | ALL] tablespace;-->拷贝文件过来,改权限-->alter table tbname IMPORT [PARTITION partition_names | ALL] TABLESPACE;
再次提醒, 做flush tables xx discard tablespace之前,务必三思, 一定要搞清楚在哪个库操作, 万一在生产库上操作,这就悲剧了!