Innodb表空间迁移过程

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.详细步骤

 

     1.在b实例上，创建a实例相同database和与表结构.(假设目标表是yy.y)

 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

 

    3.在a实例上,FLUSH  TABLES  y  FOR EXPORT;

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