MySQL数据迁移之表空间传输-526互联

MySQL数据迁移之表空间传输

MySQL数据迁移之表空间传输
背景
环境
迁移步骤
总结

背景

日常工作中经常遇到将一个InnoDB表从一个实例，移动或者复制到另一个实例，其实有很多的方法，在5.6之前常用的是通过物理或者逻辑备份来实现。在MySQL 5.6.6版本中引入了一种基于表空间快速迁移的功能（类似Oracle TTS），我们可以直接将表空间复制到另一台服务器数据库中。这对于大表来说是一个非常有用的方法。可传输表空间机制比任何其他导出和导入表的方法都快，因为只需要使用传统的 Linux 命令（cp、scp、rsync）将数据文件复制到目标位置即可。

传输表空间的方式可以跨大版本迁移数据

环境

准备两台数据库服务器——database1 和database2
在两台服务器上都运行MySQL 5.7.32版本的数据库
两个数据库的数据都采用静态加密
使用传输表空间功能将database1数据库中的加密表“test.enc_data_test”复制到database2

▼▼▼
mysql> select @@version, @@version_comment\G
*************************** 1. row ***************************
@@version: 5.7.32-log
@@version_comment: MySQL Community Server (GPL)
1 row in set (0.00 sec)

mysql> show create table test.enc_data_test\G
*************************** 1. row ***************************    
Table: enc_data_test
Create Table: CREATE TABLE `enc_data_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(16) DEFAULT NULL, `exec_time` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=165520 DEFAULT CHARSET=utf8 ENCRYPTION='Y'
1 row in set (0.00 sec)

迁移步骤

1. 准备源表

确保enc_data_test表的更新已经刷新到磁盘，以便在服务器运行时制作表副本，在database1中：

▼▼▼
mysql> flush table enc_data_test for export;Query OK, 0 rows affected (0.00 sec)

执行该命令后，将在 MySQL 数据目录中创建两个附加文件（.cfg 和 .cfp）

▼▼▼
-rw-r----- 1 mysql mysql 61 Jun 17 09:24 db.opt
-rw-r----- 1 mysql mysql 454 Jun 17 09:43 enc_data_test.cfg
-rw-r----- 1 mysql mysql 100 Jun 17 09:43 enc_data_test.cfp
-rw-r----- 1 mysql mysql 8626 Jun 17 09:37 enc_data_test.frm
-rw-r----- 1 mysql mysql 98304 Jun 17 09:43 enc_data_test.ibd

2. 将 .ibd、.cfg 和 .cfp 文件从 database1 复制到 database2

执行完第一步后，需要将表文件（.ib、.cfg、.cfp）复制到目标服务器database2

▼▼▼
[mysql@db01 test]$ scp -r enc_data_test.ibd enc_data_test.cfp enc_data_test.cfg 192.168.0.22:/home/mysqlmysql@192.168.0.22's password:enc_data_test.ibd 100% 96KB 6.9MB/s 00:00enc_data_test.cfp 100% 100 37.5KB/s 00:00enc_data_test.cfg 100% 454 148.5KB/s 00:00

3. 在 database1 上解锁表

将表文件复制到目标服务器 (database2) 后，需要在 database1 上解锁表以允许对其操作

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

4. 在database2上创建表结构

在目标数据库database2上创建空表

▼▼▼
mysql> CREATE TABLE `enc_data_test` (  -> `id` int(11) NOT NULL AUTO_INCREMENT,  -> `name` varchar(16) DEFAULT NULL,  -> `exec_time` datetime DEFAULT CURRENT_TIMESTAMP,  -> PRIMARY KEY (`id`)  -> ) ENGINE=InnoDB AUTO_INCREMENT=165520 DEFAULT CHARSET=utf8 ENCRYPTION='Y';
Query OK, 0 rows affected (0.02 sec)

确保表结构“ENCRYPTION=Y”上添加了加密。否则，在导入过程中会出现以下错误信息

▼▼▼
mysql> alter table enc_data_test import tablespace;
ERROR 1808 (HY000): Schema mismatch (Encryption attribute in the file does not match the dictionary.)

5. 删除.idb文件

在 database2 上创建空表后，建产生两个文件（.frm 和 .ibd）。需要删除 .ibd 文件，以便从 database1复制表空间过来

▼▼▼
[mysql@db02 test]$ ll
total 112
-rw-r----- 1 mysql mysql 61 Jun 17 09:24 db.opt
-rw-r----- 1 mysql mysql 8626 Jun 17 09:53 enc_data_test.frm
-rw-r----- 1 mysql mysql 98304 Jun 17 09:53 enc_data_test.ibd
mysql> alter table enc_data_test discard tablespace;
Query OK, 0 rows affected (0.13 sec)
[mysql@db02 test]$ ls -lrth
total 16K
-rw-r----- 1 mysql mysql 61 Jun 17 09:24 db.opt
-rw-r----- 1 mysql mysql 8.5K Jun 17 09:53 enc_data_test.frm

6. 复制表空间到数据文件目录

将表空间文件（从database1）拷贝到数据库文件夹下的data目录下

▼▼▼
[mysql@db02 test]$ cp -r /home/mysql/enc_data_test.ibd ./
[mysql@db02 test]$ cp -r /home/mysql/enc_data_test.cf* ./
[mysql@db02 test]$ ls -lrth
total 120K
-rw-r----- 1 mysql mysql 61 Jun 17 09:24 db.opt
-rw-r----- 1 mysql mysql 8.5K Jun 17 09:53 enc_data_test.frm
-rw-r----- 1 mysql mysql 96K Jun 17 10:01 enc_data_test.ibd
-rw-r----- 1 mysql mysql 100 Jun 17 10:01 enc_data_test.cfp
-rw-r----- 1 mysql mysql 454 Jun 17 10:01 enc_data_test.cfg

确保在.cfp 文件存在，如果没有，导入将无法进行，并且会出现以下错误

▼▼▼
mysql> alter table enc_data_test import tablespace;ERROR 1808 (HY000): Schema mismatch (Table is in an encrypted tablespace, but the encryption meta-data file cannot be found while importing.)

7. 导入表空间

在目标数据库运行一下命令进行导入

▼▼▼
mysql> alter table enc_data_test import tablespace;
Query OK, 0 rows affected (0.02 sec)
mysql> select count(*)from enc_data_test;
+----------+| count(*) |+----
------+| 1000 |+----------+
1 row in set (0.00 sec)

迁移过程与正常的 InnoDB 表空间导出/导入过程非常相似。但是，在这里需要注意以下两点：