Mysql表空间迁移
一、迁移步骤
- 创建与源表相同结构的目标表空表
- 将目标表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)