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

最新推荐文章于 2024-08-16 07:58:08 发布
最新推荐文章于 2024-08-16 07:58:08 发布
阅读量316 收藏
点赞数 1
分类专栏： 杂谈 文章标签： 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

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

操作步骤

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

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

veesing
关注
专栏目录
MySQL迁移表分区【图文教程】
甘蓝的专栏
08-23 557
MySQL迁移表分区【图文教程】，手把手操作。
MySQL 分区表介绍与测试
For-的博客
05-26 675
一、基本介绍： 分区表是一个独立的逻辑表，底层由多个物理的文件组成，分区对于 SQL 层来说是透明的； 下图可以看出，表 em_elec_meter_data 的多个分区是使用了 # 进行标识。 分区表的使用场景： 表非常大以至于无法全部都放在内存中，或者热点数据只有在表的最后部分，其他都是历史数据。 分区表的数据作用在不同的设备上，高效地利用多个硬件设备；例如有 1TB 的数据，服务器的硬盘容量只有 500 GB，那么可以使用其他设备平摊未能放下的数据。 需要独立备份和恢复分区，这在非常大数据集的场景
MySQL分区如何迁移
03-11 297
| 背景 需求来源 MySQL越来越流行，而且存储在MySQL的数据量也越来越大，单表数据达亿行已经是非常常见的现象，而这些表里面保存了大量的历史记录，严重影响...
MySQL大表如何在不停机的情况下进行分表操作
最新发布
weixin_37057698的博客
08-16 54
在现代应用中，随着数据量的不断增长，数据库中的某些表会变得非常庞大，从而导致查询性能下降，维护困难。在不停止服务的情况下对大表进行分表操作，既能提高数据库性能，又不会影响业务的正常运行。本文将详细探讨如何在MySQL数据库中，采用合理的方法在不停机的情况下实现大表的分表操作。概述MySQL数据库在处理大数据量时，单表的体积...
mysql表分区迁移_MySQL5.7传输表空间——迁移分区表
weixin_42415681的博客
01-21 403
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分区转移_迁移mysql分区_MySQL
weixin_42493639的博客
01-20 248
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 157
| 背景需求来源MySQL越来越流行，而且存储在MySQL的数据量也越来越大，单表数据达亿行已经是非常常见的现象，而这些表里面保存了大量的历史记录，严重影响SQL执行的效率。本文是针对客户需求，迁移MySQL Innodb大表分区中部分历史归档分区到其他实例或者其他库表，而且迁移过程尽量减少对业务环境的影响。环境介绍MySQL 5.7.21Centos 7.4innodb_file_per_tab...
Mysql迁移到Oracle就是这么简单
01-03
MySQL和Oracle是两种广泛应用的DBMS，它们各有特点，但有时需要在两者之间进行数据迁移。本篇文章将深入探讨如何从MySQL迁移到Oracle，以及在过程中可能遇到的问题和解决方案。 首先，MySQL是一款开源、免费的DBMS...
MySQL交换分区的实例详解
09-09
MySQL交换分区是数据库管理系统中的一种高级操作，它允许用户在不使用复杂的SQL语句或备份恢复的情况下，将分区表中的一个分区与一个普通表的数据进行互换。这在处理大量数据时非常有用，例如在数据迁移、数据清理或...
MySQL下海量数据的迁移步骤分享
09-10
MySQL海量数据迁移是一项复杂且耗时的任务，尤其是当数据量达到6亿条记录时，传统的备份与恢复方法已经不再适用。因此，了解MySQL海量数据迁移的正确步骤和注意事项是至关重要的。 首先，我们来探讨一下MySQL的分区...
mysql 分区表重命名_全面学习分区表及分区索引(14)--截断、移动和重命名
weixin_33187554的博客
01-20 1206
2、移动表分区(MovePartition)Movepartition与modifypartition的功能相似，但又比之更加强劲，比如可以修改分区所在表空间等等，与movetable的操作很类似，某些时间也非常有用，比如降低行迁移。语法很简单：Altertabletbnamemovepartition/subpartitionptname.....;例如：JSSWEB>...
mysql 表空间传输_MySQL5.7传输表空间——迁移分区表
weixin_30709179的博客
01-18 154
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 109
有时，我们需要在另一个Oracle服务器上重建一个表，而这个表包含非常多的分区/子分区时，DB Link + CTAS不是个好选择，因为我们需有时，我们需要在另一个Oracle服务器上重建一个表，而这个表包含非常多的分区/子分区时，DB Link + CTAS不是个好选择，因为我们需要列出所有分区，得到所有分区信息也需要一些时间。这时imp/exp或者Data Pump会是个好的选择。我们分两种情...
Mysql数据库迁移到新的分区
wuqiayan的专栏
12-15 154
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...
mysql5.7分区表迁移备份
渡目成书
04-18 626
注意，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数据迁移
qq_41081716的博客
06-08 390
会禁用MySQL的权限系统，使得任何用户都能够无需密码即可连接数据库并拥有全部权限，【这会带来严重的安全风险。综上所述，该命令的作用是从IP地址为xxx.xxx.x.xxx、端口为3307的MySQL服务器上，使用root用户和密码xxxxxx登录，并备份数据库。2、若原库和目标库的数据结构不一样，可以创建一个新库，将原库数据清洗至新库，然后将新库数据导入目标库。: MySQL数据库备份工具，用于导出数据库的结构和数据为SQL格式的文件。: 导出的SQL文件名，备份数据将被保存到这个文件中，文件扩展名。
数据迁移 mysql ——> Hive（按天分区）
qq125293177的博客
12-05 1202
注意：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 665
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
mysql 分区 导出_mysql普通表变成分区表导入导出
weixin_39807954的博客
01-19 370
环境：tidb、linux1、先确认主键、创建分区表(用于代替原表)SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name=‘wpt_dzxx_chdzb_20200813‘ AND constraint_name=‘PRIMARY‘;2、去除原表重复主键的行3、统计原表总数select coun...
从SQL Server到MySQL数据库迁移指南
"这篇文档详细介绍了从SQL Server到MySQL的迁移过程，涵盖了迁移的业务优势、技术优势，以及具体的迁移概述。文档包含了多个附录，提供了数据类型、语法、运算符和日期函数的对比，以及T-SQL转换的建议，旨在帮助...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值