官方文档: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)