1.大致流程
将a实例的表的数据迁移到b实例上. 1.在目标实例b上创建一个相同的表 2.在目标库b上执行ALTER TABLE t DISCARD TABLESPACE; 3.在源库a上执行FLUSH TABLES t FOR EXPORT;生成.cfg文件 4.将.ibd文件和.cfg文件拷贝到目标实例b 5.在源库a执行unlock tables; 6.在目标库b执行ALTER TABLE t IMPORT TABLESPACE; |
2.详细步骤
b实例上: create database yy;
在a实例上: show create table y;将建表语句复制到b实例use yy后并执行. 相应表文件是y.frm和y.ibd. a实例上:
mysql> create database yy;
mysql> use yy;
mysql> create table y (name varchar(10),age int);
mysql> insert into y values ('yy.y',25);
mysql> insert into y values ('yy.y',26);
mysql> insert into y values ('yy.y',27);
b实例上:
mysql> create database yy;
mysql> use yy;
mysql> create table y (name varchar(10),age int);
|
2.在b实例上: ALTER TABLE y DISCARD TABLESPACE;
discard的意思就是从数据库detached,会删除ibd文件,保留frm文件。也就意味着,你可以对frm文件操作,比如:rename table,drop table ,但是不能对ibd文件操作,比如:dml.
b实例上删除y表的数据文件:
[root@lbg2 ~]# cd /home/mysql3306/mysql3306/yy
[root@lbg2 yy]# ls
db.opt y.frm y.ibd
mysql> ALTER TABLE y DISCARD TABLESPACE;
[root@lbg2 yy]# ls
db.opt y.frm
|
表y这时候处于quiesce状态,只读,且创建.cfg metadata文件. a实例上: [root@master yy]# ls
db.opt y.frm y.ibd mysql> FLUSH TABLES y FOR EXPORT; [root@master yy]# ls db.opt y.cfg y.frm y.ibd flush tables .. for export 会加锁,这时候,千万不能退出终端或session,否则加锁无效且.cfg文件自动删除。 |
4.将a实例中y.cfg和y.ibd拷贝到b实例对应位置.并在b上修改对应权限.
在a实例: [root@master yy]# scp y.cfg root@192.168.88.9:/home/mysql3306/mysql3306/yy [root@master yy]# scp y.ibd root@192.168.88.9:/home/mysql3306/mysql3306/yy 在b实例: [root@lbg2 yy]# ls
db.opt y.cfg y.frm y.ibd [root@lbg2 yy]# chown -R mysql.mysql /home/mysql3306/mysql3306/yy |
5.在实例a执行unlock tables解锁;
a实例: mysql> unlock tables;
执行unlock tables 来释放FLUSH TABLES ... FOR EXPORT 加的locks。 |
6.在实例b上导入tablespace: ALTER TABLE y IMPORT TABLESPACE;
b实例: mysql> ALTER TABLE y IMPORT TABLESPACE; mysql> select * from y; +------+------+ | name | age | +------+------+ | yy.y | 25 | | yy.y | 26 | | yy.y | 27 | +------+------+ 完成表空间迁移。 |
说明:迁移数据会对表加锁.完成迁移后b实例下,依旧保留y.cfg,但解锁后,a实例下y.cfg自动删除.
3.innodb可传输表空间注意事项
必须开启 innodb_file_per_table 当这个表处于quiesced状态,甚至不能被select 两边实例的page size 一致 5.7 版本之前,不支持分区表transport外键相关的表,必须设置 foreign_key_checks=0 才能成功 ALTER TABLE ... IMPORT TABLESPACE 不需要.cfg metadata file . 但是,这样的话,MySQL就不会对schema进行verificate 5.6以及更高版本,import&export 版本必须在同一个series 在replication环境中,master & slave 都必须开启 innodb_file_per_table 对于InnoDB general tablespace,不支持discard & import tablespace 如果两边服务器的table row_format设置的不一样,会导致schema mismatch error 加密过的InnoDB tablespace 必须要拷贝.cfp 文件 |
4.MyISAM表空间迁移
1. flush table with read lock
2. 直接复制数据文件和表结构文件 |