MySQL Innodb表空间传输

不吃饱哪有力气减肥啊。

innodb表空间传输，是MySQL5.6开始加入的新特性，支持普通表空间拷贝到其他实例下，MySQL5.7支持分区表的表空间传输，使innodb表的拷贝变得更加简单容易。

方便是方便了，但也要需要注意:

• innodb表空间传输不要用来做主从复制，否则会出现数据不一致的问题;
• 使用之前，要确认使用了innodb_file_per_table即独立表空间;
• 在表空间导出的过程中，事务不能进行写操作, 应该注意选择操作时间, 应该选择业务低峰期操作;
• 默认不支持导出有外键的表, 可以通过set foreign_key_checks=0强制忽略, 但仅限于普通表, 而分区表暂时不支持这样操作。

我们知道了innodb表空间传输的特点和使用注意事项, 现在考虑一下应用场景。因为不做主从复制，就只能做一些离线方面的使用，比如把线上某个生产表拿到离线环境做统计分析等等。

下面介绍innodb普通表空间传输和分区表空间传输的操作过程
1.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;

2.innodb分区表空间传输
测试分区表结构

mysql> 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之前，务必三思, 一定要搞清楚在哪个库操作, 万一在生产库上操作，这就悲剧了!

转载

Huang Jinqiang