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; |
| 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. 直接复制数据文件和表结构文件 |