mysql5.7表空间迁移_MySQL5.7迁移表空间——普通表

---在工作中经常遇到将一个InnoDB表从一个实例，移动或者复制到另一个实例，其实有很多的方法，在5.6之前常用的是通过物理或者逻辑备份来实现。

在5.6.6+的版本中，用到了一种基于表空间迁移的快速方法，即类似Oracle TTS。下面本次测试在MySQL5.7环境中迁移普通表。

实验环境：(都是mysql5.7)

源库：192.168.2.200      mysql5.7.16  zhangdb下的emp表

目标库：192.168.2.100    mysql5.7.18  test下  (将zhangdb的emp表，导入到目标库的test schema下)

--：在源库(192.168.2.200)创建一个测试表mysql> create database zhangdb;

mysql> use zhangdb

mysql> create table emp (id int,name varchar(9));

mysql> insert into emp values (1,'zhang');

mysql> insert into emp values (2,'zhang');

mysql> insert into emp values (3,'zhang');

mysql> insert into emp values (4,'zhang');

mysql> insert into emp values (5,'zhang');

mysql> select * from emp;

+------+-------+

| id   | name  |

+------+-------+

|    1 | zhang |

|    2 | zhang |

|    3 | zhang |

|    4 | zhang |

|    5 | zhang |

+------+-------+

5 rows in set (0.00 sec)

--：在源库(192.168.2.200)上运行FLUSH TABLES … FOR EXPORT 锁定表并生成.cfg元数据文件

mysql> flush tables emp for export;   --(注意：这一步是把表锁住了，不能insert update)

Query OK, 0 rows affected (0.00 sec)

[[email protected] data]# cd zhangdb

db.opt  emp.cfg  emp.frm  emp.ibd

total 116

-rw-r----- 1 mysql mysql    67 May 25 15:36 db.opt

-rw-r----- 1 mysql mysql   430 May 25 15:40 emp.cfg   ---生成的cfg文件

-rw-r----- 1 mysql mysql  8586 May 25 15:37 emp.frm

-rw-r----- 1 mysql mysql 98304 May 25 15:37 emp.ibd

--：在目标数据库(192.168.2.100)创建源表结构，并丢弃现有的表空间，如下

MySQL [(none)]> use test

Database changed

MySQL [test]> CREATE TABLE `emp` (

->   `id` int(11) DEFAULT NULL,

->   `name` varchar(9) DEFAULT NULL

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Query OK, 0 rows affected (0.41 sec)

MySQL [test]>alter table  emp DISCARD TABLESPACE; ---丢弃现有表空间

注意：

※约束条件、字符集等等也必须一致，建议使用show create table t1; 来获取创建表的SQL，否则在新服务器上导入表空间的时候会提示1808错误。

--：将.ibd文件和.cfg元数据文件从源实例复制到目标实例

[[email protected] zhangdb]# scp emp.ibd emp.cfg [email protected]:/mysql/data/test/ ---传输到目标数据库的test schema目录

[[email protected] test]# chown mysql.mysql emp.cfg emp.ibd  --在目标数据库中授权

--在源数据库(192.168.2.200)解锁emp表

mysql> unlock table;

Query OK, 0 rows affected (0.01 sec)

--在目标数据库中(192.168.2.100)导入emp表空间，然后查看表是否正确MySQL [test]> alter table emp import tablespace;

Query OK, 0 rows affected (0.23 sec)

MySQL [test]> show tables;

+----------------+

| Tables_in_test |

+----------------+

| emp            |

+----------------+

1 row in set (0.06 sec)

MySQL [test]> select * from emp;

+------+-------+

| id   | name  |

+------+-------+

|    1 | zhang |

|    2 | zhang |

|    3 | zhang |

|    4 | zhang |

|    5 | zhang |

+------+-------+

5 rows in set (0.00 sec)

注意：

ALTER TABLE … IMPORT TABLESPACE特性并不强制对导入的数据施加外键约束。如果表之间有外键约束，那么所有表都应该在相同的(逻辑的)点上导出。在这种情况下，您将停止更新表，提交所有事务，在表上获得共享锁，然后执行导出操作。

原文：http://blog.51cto.com/fengfeng688/2149038