Mysql Innodb表空间传输

一、迁移步骤

• 创建与源表相同结构的目标表空表
• 将目标表discard tablespace(即删除目标表ibd文件)
• 将源表ibd文件copy至当前目标表ibd所在目录
• 重命名源表ibd文件名(与目标表ibd文件表相同)
• 将源表ibd导入(import tablespace)

二、操作过程

将mydb数据库下的源表t，迁移至test库目标表t1

1.获取目标表t的创建表的语句

mysql> use mydb
Database changed

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.00 sec)


mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

2.在test库创建t1表

mysql> use test;
Database changed

mysql> CREATE TABLE `t1` (
    ->   `id` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

3.删除t1.ibd文件(discard tablespace)

mysql> select @@datadir;
+-------------------+
| @@datadir         |
+-------------------+
| /data/mysql/data/ |
+-------------------+
1 row in set (0.00 sec)

[root@db01 ~]# ll /data/mysql/data/test/
total 112
-rw-r-----. 1 mysql mysql    65 Jul  4 21:46 db.opt
-rw-r-----. 1 mysql mysql  8556 Jul  4 21:53 t1.frm
-rw-r-----. 1 mysql mysql 98304 Jul  4 21:53 t1.ibd

mysql> alter table t1 discard tablespace;
Query OK, 0 rows affected (0.00 sec)

[root@db01 ~]# ll /data/mysql/data/test/
total 16
-rw-r-----. 1 mysql mysql   65 Jul  4 21:46 db.opt
-rw-r-----. 1 mysql mysql 8556 Jul  4 21:53 t1.frm

4.copy t.ibd至test库

mysql> flush table t for export;
Query OK, 0 rows affected (0.00 sec)
在 FLUSH TABLE 之后，这个表就被锁定了，DML 操作是阻塞的，也就意味着迁移的过程中，是无法直接写入数据的。
另外FLUSH TABLES ... FOR EXPORT语句确保对命名表的更改已刷新到磁盘，以便在实例运行时可以进行二进制表副本。当FLUSH TABLES ... FOR EXPORT运行时， InnoDB 会产生一个.cfg在同一个数据库的目录表文件。该.cfg文件包含导入表空间文件时用于模式验证的元数据。
这个命令值得一提的是，保持当前的窗口，不要关闭，如果关闭，.cfg文件就会自动删除，可以看到命令运行后生成了.cfg文件。

[root@db01 ~]# ll /data/mysql/data/mydb
total 116
-rw-r-----. 1 mysql mysql    67 Jun 27 16:40 db.opt
-rw-r-----. 1 mysql mysql   354 Jul  4 22:27 t.cfg
-rw-r-----. 1 mysql mysql  8556 Jul  4 21:43 t.frm
-rw-r-----. 1 mysql mysql 98304 Jul  4 21:44 t.ibd

[root@db01 ~]# cp -a /data/mysql/data/mydb/t.ibd /data/mysql/data/test/
[root@db01 ~]# cp -a /data/mysql/data/mydb/t.cfg /data/mysql/data/test/
[root@db01 ~]# ll /data/mysql/data/test/
total 116
-rw-r-----. 1 mysql mysql    65 Jul  4 21:46 db.opt
-rw-r-----. 1 mysql mysql  8556 Jul  4 21:53 t1.frm
-rw-r-----. 1 mysql mysql   354 Jul  4 22:27 t.cfg
-rw-r-----. 1 mysql mysql 98304 Jul  4 21:44 t.ibd
这个时候尤其需要注意 ibd 文件的权限（应该为 600），属主属组都是 mysql 用户。


mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

5.重命名t.ibd为t1.ibd

[root@db01 ~]# cd /data/mysql/data/test/
[root@db01 test]# ll
total 116
-rw-r-----. 1 mysql mysql    65 Jul  4 21:46 db.opt
-rw-r-----. 1 mysql mysql  8556 Jul  4 21:53 t1.frm
-rw-r-----. 1 mysql mysql   354 Jul  4 22:27 t.cfg
-rw-r-----. 1 mysql mysql 98304 Jul  4 21:44 t.ibd

[root@db01 test]# mv t.ibd t1.ibd
[root@db01 test]# mv t.cfg t1.cfg
[root@db01 test]# ll
total 116
-rw-r-----. 1 mysql mysql    65 Jul  4 21:46 db.opt
-rw-r-----. 1 mysql mysql   354 Jul  4 22:27 t1.cfg
-rw-r-----. 1 mysql mysql  8556 Jul  4 21:53 t1.frm
-rw-r-----. 1 mysql mysql 98304 Jul  4 21:44 t1.ibd

6.导入

[root@db01 test]# mysql -uroot -p test
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

mysql> alter table t1 import tablespace;
Query OK, 0 rows affected (0.03 sec)

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



注意:如果没有4步中flush table t for export生成的cfg文件copy至test库，在import tablespace会有以下warning.
mysql> alter table t1 import tablespace;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t1.cfg', will attempt to import without schema verification |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)