将单张大表迁移到其他实例方法

将单张大表迁移到其他实例方法

前提 innodb_file_per_table 为on

环境说明：
   将testdb库底下t1表迁移到testdb2库下
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| testdb2            |
+--------------------+
8 rows in set (0.00 sec)


mysql> use testdb;
Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)


mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|    32768 |
+----------+
1 row in set (0.02 sec)


mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


步骤：
1、在testdb2上创建一张相同的表；
mysql> use testdb2;
Database changed
mysql> CREATE TABLE `t1` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `name` varchar(200) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)


2、在testdb2上执行：ALTER TABLE t1 DISCARD TABLESPACE；
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.03 sec)


3、在testdb上执行：FLUSH TABLES t1 FOR EXPORT;生成.cfg文件；
mysql> FLUSH TABLES t1 FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)


4、将.ibd文件和.cfg文件拷贝到testdb2；
[root@mytest02 testdb]# cp t1.cfg t1.ibd /var/lib/mysql/testdb2/


5、在testdb执行UNLOCK TABLES;解锁
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


6、在testdb2执行ALTER TABLE t1 IMPORT TABLESPACE;
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
Query OK, 0 rows affected (0.06 sec)


7、测试
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|    32768 |
+----------+
1 row in set (0.02 sec)


迁移成功。