MySQL分区如何迁移，就是这么简单

最新推荐文章于 2025-07-13 17:39:26 发布
转载 最新推荐文章于 2025-07-13 17:39:26 发布 · 388 阅读 · 0
文章标签：

#MySQL #数据库

本文详细介绍了一种高效迁移MySQL Innodb大表分区至其他实例或库的方法，通过传输表空间，实现历史数据快速归档，减少业务影响，提高迁移速度。

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

  • 迁移过程中存在短暂时间内业务不可写，建议提前做好准备

操作步骤

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

root@localhost : testdba 02:03:18> use test

Database changed

root@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）。   

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

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 changed

root@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)

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

1

2

3

4

root@localhost : testdba 02:00:05> use testdba;

Database changed

root@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和确保该表的脏页刷到磁盘，并加共享表锁 

1

2

3

4

5

6

7

8

9

10

11

root@localhost : testdba 02:00:24> USE test;

Database changed

root@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.ibd

sbtest2#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.frm

sbtest2#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.cfg

sbtest2#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.ibd

sbtest2#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传输到新表所在的位置，并赋予权限 

1

2

3

4

5

6

[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.ibd

sbtest2#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.frm

sbtest2#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语句窗口，释放共享表锁 

1

2

3

4

root@localhost : test 02:00:29> USE test;

Database changed

root@localhost : test 02:01:07> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

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

1

2

3

4

root@localhost : test 02:01:07> USE testdba;

Database changed

root@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)

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

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)

| 总结

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

 

| 作者简介

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

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

转载来源：http://blog.itpub.net/28218939/viewspace-2638041

MySQL迁移表分区【图文教程】
甘蓝的专栏
08-23 759
MySQL迁移表分区【图文教程】，手把手操作。
移动MySQL数据文件到新分区的步骤分析
MwgmPhilosophy的博客
09-27 278
在移动数据文件之前，需要确定新分区的路径。可以使用MySQL提供的备份工具或者使用适当的命令行工具来执行备份操作。以上就是将MySQL数据文件移动到新分区的详细步骤。在移动数据文件之前，应该停止MySQL服务以确保没有任何正在访问数据库的活动。复制完成后，需要确保新分区上的数据文件具有正确的权限。在MySQL数据库中，如果需要将数据文件移动到新的分区，可以按照以下步骤进行操作。在移动数据文件后，需要更新MySQL的配置文件以指示数据库使用新的数据文件路径。首先，需要复制MySQL数据文件到新分区。
mysql数据迁移
qq_41081716的博客
06-08 665
会禁用MySQL的权限系统，使得任何用户都能够无需密码即可连接数据库并拥有全部权限，【这会带来严重的安全风险。综上所述，该命令的作用是从IP地址为xxx.xxx.x.xxx、端口为3307的MySQL服务器上，使用root用户和密码xxxxxx登录，并备份数据库。2、若原库和目标库的数据结构不一样，可以创建一个新库，将原库数据清洗至新库，然后将新库数据导入目标库。: MySQL数据库备份工具，用于导出数据库的结构和数据为SQL格式的文件。: 导出的SQL文件名，备份数据将被保存到这个文件中，文件扩展名。
MySQL分区如何迁移
03-11 381
| 背景 需求来源 MySQL越来越流行，而且存储在MySQL的数据量也越来越大，单表数据达亿行已经是非常常见的现象，而这些表里面保存了大量的历史记录，严重影响...
mysql表分区迁移_MySQL5.7传输表空间——迁移分区表
weixin_42415681的博客
01-21 490
Mysql 传输表空间--将InnoDB分区表复制到另一个实例(二)实验环境：(都是mysql5.7)源库：192.168.2.200 mysql5.7.16 zhangdb下的emp_2分区表的目标库：192.168.2.100 mysql5.7.18 test下 (将zhangdb的emp表，导入到目标库的test schema下)--：在源数据库中创建测试分区表...
Mysql迁移到Oracle就是这么简单
01-03
MySQL和Oracle是两种广泛应用的DBMS，它们各有特点，但有时需要在两者之间进行数据迁移。本篇文章将深入探讨如何从MySQL迁移到Oracle，以及在过程中可能遇到的问题和解决方案。 首先，MySQL是一款开源、免费的DBMS...
MySQL 分区功能应用专门实现全方位详解与示例
最新发布
行思理的博客
07-13 513
MySQL分区功能详解：将大表数据分散存储到不同物理分区，提升查询性能和管理效率。文章介绍了6种分区类型（RANGE、LIST、HASH、KEY、COLUMNS和子分区）及具体实现方法，包含创建语法、数据插入和分区维护操作。重点分析了RANGE分区的实战应用，包括按时间分区、分区剪枝和动态管理（添加/删除/合并分区）。同时提供了分区最佳实践、性能监控方法以及使用限制说明，帮助开发者合理应用分区技术优化大数据表处理。建议生产环境使用前充分测试验证分区策略效果。
MySQL交换分区的实例详解
09-09
MySQL交换分区是数据库管理系统中的一种高级操作，它允许用户在不使用复杂的SQL语句或备份恢复的情况下，将分区表中的一个分区与一个普通表的数据进行互换。这在处理大量数据时非常有用，例如在数据迁移、数据清理或...
MySQL下海量数据的迁移步骤分享
09-10
MySQL海量数据迁移是一项复杂且耗时的任务，尤其是当数据量达到6亿条记录时，传统的备份与恢复方法已经不再适用。因此，了解MySQL海量数据迁移的正确步骤和注意事项是至关重要的。 首先，我们来探讨一下MySQL的分区...
MySQL分区表迁移术：数据迁移的高效策略
MySQL分区表基础与迁移需求 在数据库系统中，表的分区技术能够帮助我们改善大型数据库表的性能和可管理性。特别是在需要维护大量数据时，分区表可以作为一个有力的工具来优化查询、减少锁争用以及实现表的高效备份...
mysql分区转移_迁移mysql分区_MySQL
weixin_42493639的博客
01-20 299
bitsCN.com今天发现主从mysql的slave时忘记把数据目录安装最大的分区上，直接装到根分区了，df以下已经使用根分区95%了，只要迁移了。说明：a. 是ec2的主机，合作方提供的时候都是把最大分区挂到/mnt下，好由我们自己定义df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 9.9G 8.9G 529M 95% ...
mysql 分区_MySQL分区如何迁移
weixin_39738416的博客
12-05 219
| 背景需求来源MySQL越来越流行，而且存储在MySQL的数据量也越来越大，单表数据达亿行已经是非常常见的现象，而这些表里面保存了大量的历史记录，严重影响SQL执行的效率。本文是针对客户需求，迁移MySQL Innodb大表分区中部分历史归档分区到其他实例或者其他库表，而且迁移过程尽量减少对业务环境的影响。环境介绍MySQL 5.7.21Centos 7.4innodb_file_per_tab...
mysql 表空间传输_MySQL5.7传输表空间——迁移分区表
weixin_30709179的博客
01-18 219
Mysql 传输表空间--将InnoDB分区表复制到另一个实例(二)实验环境：(都是mysql5.7)源库：192.168.2.200 mysql5.7.16 zhangdb下的emp_2分区表的目标库：192.168.2.100 mysql5.7.18 test下 (将zhangdb的emp表，导入到目标库的test schema下)--：在源数据库中创建测试分区表...
mysql表分区迁移_Oracle分区表迁移
weixin_28686771的博客
01-28 155
有时，我们需要在另一个Oracle服务器上重建一个表，而这个表包含非常多的分区/子分区时，DB Link + CTAS不是个好选择，因为我们需有时，我们需要在另一个Oracle服务器上重建一个表，而这个表包含非常多的分区/子分区时，DB Link + CTAS不是个好选择，因为我们需要列出所有分区，得到所有分区信息也需要一些时间。这时imp/exp或者Data Pump会是个好的选择。我们分两种情...
Mysql数据库迁移到新的分区
wuqiayan的专栏
12-15 207
Zabbix数据库迁移 service mysql stop mv /usr/local/mysql/var/ /zabbixdb ln -s /zabbixdb/var /usr/local/mysql/var/ chown -R mysql:mysql /zabbixdb/var sed -i 's/SELINUX=enforcing/SE...
MySQL数据库--几种数据迁移的方法详解都在这了！看完必懂
热门推荐
m0_58269070的博客
03-23 2万+
大型分布式系统犹如一个生命，系统中各个服务犹如骨骼，其中的数据犹如血液，而Kafka犹如经络，串联整个系统。这份Kafka源码笔记通过大量的设计图展示、代码分析、示例分享，把Kafka的实现脉络展示在读者面前，帮助读者更好地研读Kafka代码。麻烦帮忙转发一下这篇文章+关注我如经络，串联整个系统。这份Kafka源码笔记通过大量的设计图展示、代码分析、示例分享，把Kafka的实现脉络展示在读者面前，帮助读者更好地研读Kafka代码。麻烦帮忙转发一下这篇文章+关注我。
mysql5.7分区表迁移备份
渡目成书
04-18 817
注意，mysql版本为5.7，5.6部分版本可能支持 整个分区表备份 1.源实例创建分区表 mysql> create table t1 (i int) Engine=InnoDB partition by key(i) partitions 3; mysql> insert into t1 select 1; mysql> insert into t1 select ...
数据迁移 mysql ——> Hive（按天分区）
qq125293177的博客
12-05 1276
注意：Hive建表时，要加上Hive分区和字段分割符。create table *** （**** int ，**** varchar（255））  PARTITIONED BY (   `store_day` int COMMENT '存储日期')    ROW FORMAT SERDE   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'...
mysql数据文件迁移到新硬盘分区的方法
qq591840685的专栏
12-07 719
service mysql stop mv /usr/local/mysql/var/yaoyao/ /mnt/mysql ln -s /mnt/mysql/yaoyao/ /usr/local/mysql/var/ chown -R mysql:mysql /mnt/mysql/ http://www.2cto.com/database/201506/406245.html
oracle分区表数据迁移到mysql
01-16
下面是一个简单的例子展示怎样借助于Sqoop把来自HDFS上的CSV格式文件迁移到MySQL里去[^4]: ```bash sqoop import \ --connect jdbc:mysql://192.168.0.74:3306/test?characterEncoding=UTF-8 \ --username root \...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值