| 背景

需求来源

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@localhost:testdba02:03:18>usetestDatabasechangedroot@localhost:test08:37:50>showcreatetablesbtest2;+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|sbtest2|CREATETABLE`sbtest2`(`id`int(10)DEFAULTNULL,`name`varchar(20)COLLATEutf8_binDEFAULTNULL,`date`int(20)DEFAULTNULL,KEY`idx_fenqu`(`date`))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin/*!50100PARTITIONBYRANGE(date)(PARTITIONp0VALUESLESSTHAN(20161201)ENGINE=InnoDB,PARTITIONp1VALUESLESSTHAN(20170101)ENGINE=InnoDB,PARTITIONp2VALUESLESSTHAN(20170201)ENGINE=InnoDB,PARTITIONp3VALUESLESSTHAN(20170301)ENGINE=InnoDB,PARTITIONp4VALUESLESSTHAN(20170401)ENGINE=InnoDB,PARTITIONp5VALUESLESSTHAN(20170501)ENGINE=InnoDB,PARTITIONp6VALUESLESSTHAN(20170601)ENGINE=InnoDB,PARTITIONp7VALUESLESSTHAN(20170701)ENGINE=InnoDB,PARTITIONp8VALUESLESSTHAN(20170801)ENGINE=InnoDB,PARTITIONp9VALUESLESSTHAN(20170901)ENGINE=InnoDB,PARTITIONp10VALUESLESSTHAN(20171001)ENGINE=InnoDB,PARTITIONp11VALUESLESSTHAN(20171101)ENGINE=InnoDB,PARTITIONp12VALUESLESSTHAN(20171201)ENGINE=InnoDB,PARTITIONp13VALUESLESSTHAN(20180101)ENGINE=InnoDB,PARTITIONp14VALUESLESSTHAN(20180201)ENGINE=InnoDB,PARTITIONp15VALUESLESSTHAN(20180301)ENGINE=InnoDB,PARTITIONp16VALUESLESSTHAN(20180401)ENGINE=InnoDB,PARTITIONp17VALUESLESSTHAN(20180501)ENGINE=InnoDB,PARTITIONp18VALUESLESSTHAN(20180601)ENGINE=InnoDB,PARTITIONp19VALUESLESSTHANMAXVALUEENGINE=InnoDB)*/|+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)root@localhost:test12:04:03>SELECTPARTITION_NAME,TABLE_ROWSFROMINFORMATION_SCHEMA.PARTITIONSWHERETABLE_NAME='sbtest2';+----------------+------------+|PARTITION_NAME|TABLE_ROWS|+----------------+------------+|p0|22||p1|2||p2|2||p3|2||p4|2||p5|2||p6|2||p7|2||p8|2||p9|2||p10|2||p11|2||p12|2||p13|2||p14|2||p15|2||p16|2||p17|2||p18|2||p19|14|+----------------+------------+20rowsinset(0.00sec)

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


root@localhost:test01:59:36>createdatabasetestdba;QueryOK,1rowaffected(0.12sec)root@localhost:test01:59:44>usetestdba;Databasechangedroot@localhost:testdba06:04:26>CREATETABLE`sbtest2`(->idint(10),->namevarchar(20),->dateint(20),->keyidx_fenqu(date)->)->PARTITIONBYRANGE(date)(->PARTITIONp2VALUESLESSTHAN(20170201),->PARTITIONp3VALUESLESSTHAN(20170301),->PARTITIONp4VALUESLESSTHAN(20170401),->PARTITIONp5VALUESLESSTHAN(20170501),->PARTITIONp6VALUESLESSTHAN(20170601),->PARTITIONp7VALUESLESSTHAN(20170701),->PARTITIONp8VALUESLESSTHAN(20170801),->PARTITIONp9VALUESLESSTHAN(20170901),->PARTITIONp10VALUESLESSTHAN(20171001),->PARTITIONp11VALUESLESSTHAN(20171101),->PARTITIONp12VALUESLESSTHAN(20171201),->PARTITIONp13VALUESLESSTHAN(20180101)->);QueryOK,0rowsaffected(0.22sec)

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


root@localhost:testdba02:00:05>usetestdba;Databasechangedroot@localhost:testdba02:00:23>ALTERTABLEsbtest2DISCARDPARTITIONp2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13TABLESPACE;QueryOK,0rowsaffected(0.27sec)

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


root@localhost:testdba02:00:24>USEtest;Databasechangedroot@localhost:test02:00:29>FLUSHTABLEStest.sbtest2FOREXPORT;QueryOK,0rowsaffected(0.00sec)[root@slavetest]#cd/var/lib/mysql/data/mydata/test[root@slavetest]#lsdb.optsbtest2#P#p10.cfgsbtest2#P#p12.ibdsbtest2#P#p15.cfgsbtest2#P#p17.ibdsbtest2#P#p2.cfgsbtest2#P#p4.ibdsbtest2#P#p7.cfgsbtest2#P#p9.ibdsbtest2#P#p0.cfgsbtest2#P#p10.ibdsbtest2#P#p13.cfgsbtest2#P#p15.ibdsbtest2#P#p18.cfgsbtest2#P#p2.ibdsbtest2#P#p5.cfgsbtest2#P#p7.ibdsbtest2.frmsbtest2#P#p0.ibdsbtest2#P#p11.cfgsbtest2#P#p13.ibdsbtest2#P#p16.cfgsbtest2#P#p18.ibdsbtest2#P#p3.cfgsbtest2#P#p5.ibdsbtest2#P#p8.cfgsbtest2#P#p1.cfgsbtest2#P#p11.ibdsbtest2#P#p14.cfgsbtest2#P#p16.ibdsbtest2#P#p19.cfgsbtest2#P#p3.ibdsbtest2#P#p6.cfgsbtest2#P#p8.ibdsbtest2#P#p1.ibdsbtest2#P#p12.cfgsbtest2#P#p14.ibdsbtest2#P#p17.cfgsbtest2#P#p19.ibdsbtest2#P#p4.cfgsbtest2#P#p6.ibdsbtest2#P#p9.cfg

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


[root@slavetest]#cpsbtest2#P#p2.*sbtest2#P#p3.*sbtest2#P#p4.*sbtest2#P#p5.*sbtest2#P#p6.*sbtest2#P#p7.*sbtest2#P#p8.*sbtest2#P#p9.*sbtest2#P#p10.*sbtest2#P#p11.*sbtest2#P#p12.*sbtest2#P#p13.*/var/lib/mysql/data/mydata/testdba/[root@slavetest]#ls../testdba/db.optsbtest2#P#p11.cfgsbtest2#P#p12.ibdsbtest2#P#p2.cfgsbtest2#P#p3.ibdsbtest2#P#p5.cfgsbtest2#P#p6.ibdsbtest2#P#p8.cfgsbtest2#P#p9.ibdsbtest2#P#p10.cfgsbtest2#P#p11.ibdsbtest2#P#p13.cfgsbtest2#P#p2.ibdsbtest2#P#p4.cfgsbtest2#P#p5.ibdsbtest2#P#p7.cfgsbtest2#P#p8.ibdsbtest2.frmsbtest2#P#p10.ibdsbtest2#P#p12.cfgsbtest2#P#p13.ibdsbtest2#P#p3.cfgsbtest2#P#p4.ibdsbtest2#P#p6.cfgsbtest2#P#p7.ibdsbtest2#P#p9.cfg[root@slavetest]#chown-Rmysql:mysql/var/lib/mysql

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


root@localhost:test02:00:29>USEtest;Databasechangedroot@localhost:test02:01:07>UNLOCKTABLES;QueryOK,0rowsaffected(0.00sec)

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


root@localhost:test02:01:07>USEtestdba;Databasechangedroot@localhost:testdba02:01:14>ALTERTABLEsbtest2IMPORTPARTITIONp2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13TABLESPACE;QueryOK,0rowsaffected(0.62sec)

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


root@localhost:testdba02:03:16>SELECTPARTITION_NAME,TABLE_ROWSFROMINFORMATION_SCHEMA.PARTITIONSWHERETABLE_NAME='sbtest2'andTABLE_SCHEMA='testdba';+----------------+------------+|PARTITION_NAME|TABLE_ROWS|+----------------+------------+|p2|2||p3|2||p4|2||p5|2||p6|2||p7|2||p8|2||p9|2||p10|2||p11|2||p12|2||p13|2|+----------------+------------+12rowsinset(0.00sec)

| 总结

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

| 作者简介

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

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