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/[email protected]'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 表空间导出/导入过程非常相似。但是，在这里需要注意以下两点：
- 必须将 .cfp 文件复制到目标服务器。
- 迁移表需要加密（ENCRYPTION = Y）
总 结
在实际环境中，对部分大表进行迁移，业务中断时间短，使用mysqldump进行导出，然后重新导入到其它环境，效率缓慢；则可以采用传输表空间方法，提高效率缩短时间，满足业务需求。
标签：enc,之表,传输,rw,-----,mysql,test,data,MySQL From： https://www.cnblogs.com/xulinforDB/p/17496196.html