MySQL数据迁移之表空间传输

IT那活儿 2021-06-30

1327

一. 简介

日常工作中经常遇到将一个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_testCreate 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:00 enc_data_test.cfp 100% 100 37.5KB/s 00:00 enc_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]$ lltotal 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.ibdmysql> alter table enc_data_test discard tablespace;Query OK, 0 rows affected (0.13 sec)[mysql@db02 test]$ ls -lrthtotal 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 -lrthtotal 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 表空间导出/导入过程非常相似。但是，在这里需要注意以下两点：

必须将 .cfp 文件复制到目标服务器。
迁移表需要加密（ENCRYPTION = Y）

四. 总结

在实际环境中，对部分大表进行迁移，业务中断时间短，使用mysqldump进行导出，然后重新导入到其它环境，效率缓慢；则可以采用传输表空间方法，提高效率缩短时间，满足业务需求。

END

更多精彩干货分享

点击下方名片关注

IT那活儿

mysql

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL数据迁移之表空间传输

评论