mysql 可传输表空间_MySql 传输表空间 Transportable Tablespaces

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.创建表t1mysql> 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. 导入t1mysql> 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)