mysql 分区_MySQL分区如何迁移

| 背景

需求来源

MySQL越来越流行，而且存储在MySQL的数据量也越来越大，单表数据达亿行已经是非常常见的现象，而这些表里面保存了大量的历史记录，严重影响SQL执行的效率。本文是针对客户需求，迁移MySQL Innodb大表分区中部分历史归档分区到其他实例或者其他库表，而且迁移过程尽量减少对业务环境的影响。

环境介绍

• MySQL 5.7.21
• Centos 7.4
• innodb_file_per_table=1

| MySQL常用的Innodb迁移方法

• MySQL Enterprise Backup（物理备份，类似于xtrabackup）
• Copying Data Files (冷备份)
• 逻辑导出和导入(mysqldump,mydumper,mysqlpump)
• 可传输的表空间

| 迁移方案（可传输的表空间）

准备工作

• MySQL版本必须是5.7
• 迁移过程中存在短暂时间内业务不可写，建议提前做好准备

操作步骤

查看需要迁移表（原表）结构

root

按照个人迁移分区表需求，可以把历史分区迁移到其他MySQL实例，也可以迁移到同一MySQL实例的其他库中。首先创建与原表相同表结构的分区表，在创建分区表时，我们只需要创建我们需要迁移的表分区结构。例：下面是迁移案例，由于只迁移2017年数据，所以表结构只创建了存储2017年数据的分区（也就是分区p2-p13）。

root

清除新表所有的分区独立表空间，为导入原表的分区独立表空间做准备

root

在原表中执行FLUSH TABLES ... FOR EXPORT（在分区表空间传输没有完成之前，不要退出该会话或者执行unlock tables;操作），用来获取元数据校验文件.cfg和确保该表的脏页刷到磁盘，并加共享表锁

root

进入到原表ibd所在的目录下，把原表需要迁移的分区表空间和元数据校验文件.cfg传输到新表所在的位置，并赋予权限

[

切回到执行FLUSH TABLES ... FOR EXPORT语句窗口，释放共享表锁

root

进入新表所在的实例或新表所在的库，手动导入分区表空间，进行数据恢复（应用传输到新表的分区表空间）

root

表空间迁移完成，数据恢复完成，最后校验数据准确性

root

| 总结

以上是我们使用MySQL的分区表空间传输方法，解决了分区表历史数据归档到其他实例或者同一实例其他库的问题。对比逻辑迁移方式mysqldump或者insert .. select ...方式速度更快，数据立即可用，而且对业务的影响更小。

| 作者简介

岳雷·沃趣科技数据库工程师

熟悉MySQL体系结构和innodb存储引擎工作原理；以及MySQL备份恢复、复制、数据迁移等技术；专注于MySQL、MariaDB开源数据库，喜好开源技术。