MySQL数据迁移之表空间传输

IT那活儿发布于2023-01-11 13:20 / 3838人阅读

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_commentG
*************************** 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_testG 
*************************** 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/mysql
mysql@192.168.0.22s 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]$ 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进行导出，然后重新导入到其它环境，效率缓慢；则可以采用传输表空间方法，提高效率缩短时间，满足业务需求。

END

更多精彩干货分享

点击下方名片关注

IT那活儿

GPU云服务器云服务器 mysql数据传输 mysql数据迁移 mysql 数据文件迁移 mysql 大数据量数据迁移

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129873.html

Activiti6之表结构分析-引擎配置及流程部署

摘要：如图流程引擎创建完成后，只会对进行操作，属性数据表存储整个流程引擎级别的数据初始化表结构时，会默认插入四条记录，流程部署测试流程部署，先把上面的流程引擎配置的注解改为。如图资源流程定义数据表这里面存放的就是我们部署的资源元数据信息。关于activiti是什么，我这里就不多说了，我们直接上路，O(∩_∩)O哈哈~ 引擎配置配置方式有好几种：1）： /** * 获取默认的流程引擎实例...

xorpay 2019-08-16 17:02 评论0 收藏0
阿里云如何打破Oracle迁移上云的壁垒

摘要：摘要第九届中国数据库技术大会，阿里云数据库产品专家萧少聪带来以阿里云如何打破迁移上云的壁垒为题的演讲。于是，阿里云给出了上面的解决方案。摘要： 2018第九届中国数据库技术大会，阿里云数据库产品专家萧少聪带来以阿里云如何打破Oracle迁移上云的壁垒为题的演讲。Oracle是指数据库管理系统，面对Oracle迁移上云的壁垒，阿里云如何能够打破它呢？本文提出了Oracle 到云数据库P...

chavesgu 2019-06-25 18:39 评论0 收藏0
跨云迁移过程中的数据同步及一致性校验实践（一）

摘要：通过对一些客户的跨云迁移过程进行总结，发现普遍存在的挑战有三点数据完整性和一致性挑战。简而言之，跨云迁移过程中的数据一致性主要就集中在存量数据的迁移如何保证一致。前言随着互联网业务发展对容灾以及对访问加速、多供应商成本控制等需求的产生，互联网公司的多云部署和跨云迁移逐渐成为刚需，而在此过程中，最困扰运维和研发人员的就是数据的迁移和同步。俗语说上屋搬下屋，搬洒一箩谷，在业务的迁移过程中一旦...

Tecode 2022-06-28 18:59 评论0 收藏0