导航：首页 > 数据库 >

MySQL分区如何迁移

发表于：2024-11-30 作者：千家信息网编辑

千家信息网最后更新 2024年11月30日，| 背景需求来源MySQL越来越流行，而且存储在MySQL的数据量也越来越大，单表数据达亿行已经是非常常见的现象，而这些表里面保存了大量的历史记录，严重影响SQL执行的效率。本文是针对客户需求，迁移M

千家信息网最后更新 2024年11月30日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@localhost : testdba 02:03:18> use testDatabase changedroot@localhost : test 08:37:50> show create table sbtest2;+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| sbtest2 | CREATE TABLE `sbtest2` (`id` int(10) DEFAULT NULL,`name` varchar(20) COLLATE utf8_bin DEFAULT NULL,`date` int(20) DEFAULT NULL,KEY `idx_fenqu` (`date`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin/*!50100 PARTITION BY RANGE (date)(PARTITION p0 VALUES LESS THAN (20161201) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (20170101) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (20170201) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (20170301) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (20170401) ENGINE = InnoDB, PARTITION p5 VALUES LESS THAN (20170501) ENGINE = InnoDB, PARTITION p6 VALUES LESS THAN (20170601) ENGINE = InnoDB, PARTITION p7 VALUES LESS THAN (20170701) ENGINE = InnoDB, PARTITION p8 VALUES LESS THAN (20170801) ENGINE = InnoDB, PARTITION p9 VALUES LESS THAN (20170901) ENGINE = InnoDB, PARTITION p10 VALUES LESS THAN (20171001) ENGINE = InnoDB, PARTITION p11 VALUES LESS THAN (20171101) ENGINE = InnoDB, PARTITION p12 VALUES LESS THAN (20171201) ENGINE = InnoDB, PARTITION p13 VALUES LESS THAN (20180101) ENGINE = InnoDB, PARTITION p14 VALUES LESS THAN (20180201) ENGINE = InnoDB, PARTITION p15 VALUES LESS THAN (20180301) ENGINE = InnoDB, PARTITION p16 VALUES LESS THAN (20180401) ENGINE = InnoDB, PARTITION p17 VALUES LESS THAN (20180501) ENGINE = InnoDB, PARTITION p18 VALUES LESS THAN (20180601) ENGINE = InnoDB, PARTITION p19 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)root@localhost : test 12:04:03> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_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 |+----------------+------------+20 rows in set (0.00 sec)

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

root@localhost : test 01:59:36> create database testdba;Query OK, 1 row affected (0.12 sec)root@localhost : test 01:59:44> use testdba;Database changedroot@localhost : testdba 06:04:26> CREATE TABLE `sbtest2` (-> id int(10),-> name varchar(20),-> date int(20),-> key idx_fenqu(date)-> )-> PARTITION BY RANGE (date) (-> PARTITION p2 VALUES LESS THAN (20170201),-> PARTITION p3 VALUES LESS THAN (20170301),-> PARTITION p4 VALUES LESS THAN (20170401),-> PARTITION p5 VALUES LESS THAN (20170501),-> PARTITION p6 VALUES LESS THAN (20170601),-> PARTITION p7 VALUES LESS THAN (20170701),-> PARTITION p8 VALUES LESS THAN (20170801),-> PARTITION p9 VALUES LESS THAN (20170901),-> PARTITION p10 VALUES LESS THAN (20171001),-> PARTITION p11 VALUES LESS THAN (20171101),-> PARTITION p12 VALUES LESS THAN (20171201),-> PARTITION p13 VALUES LESS THAN (20180101)-> );Query OK, 0 rows affected (0.22 sec)

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

root@localhost : testdba 02:00:05> use testdba;Database changedroot@localhost : testdba 02:00:23> ALTER TABLE sbtest2 DISCARD PARTITION p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13 TABLESPACE;Query OK, 0 rows affected (0.27 sec)

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

root@localhost : testdba 02:00:24> USE test;Database changedroot@localhost : test 02:00:29> FLUSH TABLES test.sbtest2 FOR EXPORT;Query OK, 0 rows affected (0.00 sec)[root@slave test]# cd /var/lib/mysql/data/mydata/test[root@slave test]# ls db.opt sbtest2#P#p10.cfg sbtest2#P#p12.ibd sbtest2#P#p15.cfg sbtest2#P#p17.ibd sbtest2#P#p2.cfg sbtest2#P#p4.ibd sbtest2#P#p7.cfg sbtest2#P#p9.ibdsbtest2#P#p0.cfg sbtest2#P#p10.ibd sbtest2#P#p13.cfg sbtest2#P#p15.ibd sbtest2#P#p18.cfg sbtest2#P#p2.ibd sbtest2#P#p5.cfg sbtest2#P#p7.ibd sbtest2.frmsbtest2#P#p0.ibd sbtest2#P#p11.cfg sbtest2#P#p13.ibd sbtest2#P#p16.cfg sbtest2#P#p18.ibd sbtest2#P#p3.cfg sbtest2#P#p5.ibd sbtest2#P#p8.cfgsbtest2#P#p1.cfg sbtest2#P#p11.ibd sbtest2#P#p14.cfg sbtest2#P#p16.ibd sbtest2#P#p19.cfg sbtest2#P#p3.ibd sbtest2#P#p6.cfg sbtest2#P#p8.ibdsbtest2#P#p1.ibd sbtest2#P#p12.cfg sbtest2#P#p14.ibd sbtest2#P#p17.cfg sbtest2#P#p19.ibd sbtest2#P#p4.cfg sbtest2#P#p6.ibd sbtest2#P#p9.cfg

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

[root@slave test]# cp sbtest2#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@slave test]# ls ../testdba/db.opt sbtest2#P#p11.cfg sbtest2#P#p12.ibd sbtest2#P#p2.cfg sbtest2#P#p3.ibd sbtest2#P#p5.cfg sbtest2#P#p6.ibd sbtest2#P#p8.cfg sbtest2#P#p9.ibdsbtest2#P#p10.cfg sbtest2#P#p11.ibd sbtest2#P#p13.cfg sbtest2#P#p2.ibd sbtest2#P#p4.cfg sbtest2#P#p5.ibd sbtest2#P#p7.cfg sbtest2#P#p8.ibd sbtest2.frmsbtest2#P#p10.ibd sbtest2#P#p12.cfg sbtest2#P#p13.ibd sbtest2#P#p3.cfg sbtest2#P#p4.ibd sbtest2#P#p6.cfg sbtest2#P#p7.ibd sbtest2#P#p9.cfg[root@slave test]# chown -R mysql:mysql /var/lib/mysql

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

root@localhost : test 02:00:29> USE test;Database changedroot@localhost : test 02:01:07> UNLOCK TABLES;Query OK, 0 rows affected (0.00 sec)

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

root@localhost : test 02:01:07> USE testdba;Database changedroot@localhost : testdba 02:01:14> ALTER TABLE sbtest2 IMPORT PARTITION p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13 TABLESPACE;Query OK, 0 rows affected (0.62 sec)

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

root@localhost : testdba 02:03:16> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sbtest2' and TABLE_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 |+----------------+------------+12 rows in set (0.00 sec)