MySql 传输表空间 Transportable Tablespaces

官方文档：http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html

MySql 传输表空间的限制:
1.MySQL 5.6及以上版本
2.打开innodb_file_per_table
3.Linux中开启lower_case_table_names
4.MySQL 5.6中有些版本不支持分区表的传输表空间
5.源库和目标库的page size必须一致
6.设置foreign_key_checks=0.
7. ALTER TABLE ... IMPORT TABLESPACE 不强制检查主外键关系。如果表中有外键，应该同时导出主表和子表。
8.源库和目标库的版本最好一致

mysql> SHOW VARIABLES WHERE variable_name IN
('version','innodb_file_per_table','lower_case_table_names','innodb_page_size');
+------------------------+------------+
| Variable_name          | Value      |
+------------------------+------------+
| innodb_file_per_table  | ON         |
| innodb_page_size       | 16384      |
| lower_case_table_names | 1          |
| version                | 5.6.12-log |
+------------------------+------------+
4 rows in set

模拟在同一个mysql的不同schema下进行表空间传输;

1.切换到wordpress库
mysql> use wordpress;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

2.创建表t1
mysql> CREATE TABLE `t1` (
    ->   `a` int(11) NOT NULL,
    ->   `b` int(11) DEFAULT NULL,
    ->   `c` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`a`),
    ->   UNIQUE KEY `b` (`b`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

3.插入t1两条数据
mysql> insert into t1 values(1,3,5);
Query OK, 1 row affected (0.11 sec)

mysql> insert into t1 values(2,4,6);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    3 |    5 |
| 2 |    4 |    6 |
+---+------+------+
2 rows in set (0.00 sec)

4.切换到sakila库
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

5.在sakila新建t1表
mysql> CREATE TABLE `t1` (
    ->   `a` int(11) NOT NULL,
    ->   `b` int(11) DEFAULT NULL,
    ->   `c` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`a`),
    ->   UNIQUE KEY `b` (`b`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

6.对sakila.t1 执行卸载表空间操作
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)

7. 切换回wordpress库
mysql> use wordpress;

mysql>system  ls -l /mysql5.6/data/wordpress/t1.*
-rw-rw---- 1 mysql mysql   8602 Oct 21 11:57 /mysql5.6/data/wordpress/t1.frm
-rw-rw---- 1 mysql mysql 114688 Oct 21 14:16 /mysql5.6/data/wordpress/t1.ibd

8.执行表空间导出
mysql> FLUSH TABLE t1 FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)

执行导出表空间后，在mysql的datadir/wordpress的目录下多了t1.cfg文件。
mysql>system  ls -l /mysql5.6/data/wordpress/t1.*
-rw-rw---- 1 mysql mysql    513 Oct 21 14:39 /mysql5.6/data/wordpress/t1.cfg
-rw-rw---- 1 mysql mysql   8602 Oct 21 11:57 /mysql5.6/data/wordpress/t1.frm
-rw-rw---- 1 mysql mysql 114688 Oct 21 14:16 /mysql5.6/data/wordpress/t1.ibd

9.将t1.ibd 和t1.cfg 拷贝到sakila目录下
mysql> system cp /mysql5.6/data/wordpress/t1.{ibd,cfg} /mysql5.6/data/sakila/

mysql> system ls /mysql5.6/data/sakila/t1.*
/mysql5.6/data/sakila/t1.cfg  /mysql5.6/data/sakila/t1.frm  /mysql5.6/data/sakila/t1.ibd

10. 解锁
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

11.切换到sakila库
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

12. 导入t1
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
Query OK, 0 rows affected (0.01 sec)

13. 检查数据是否恢复成功
mysql> select * from t1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    3 |    5 |
| 2 |    4 |    6 |
+---+------+------+
2 rows in set (0.00 sec)