AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL分区如何迁移

2019-02-28 1996
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
云数据库 RDS PostgreSQL，高可用系列 2核4GB
简介： 背景需求来源MySQL越来越流行，而且存储在MySQL的数据量也越来越大，单表数据达亿行已经是非常常见的现象，而这些表里面保存了大量的历史记录，严重影响SQL执行的效率。本文是针对客户需求，迁移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
  • 迁移过程中存在短暂时间内业务不可写，建议提前做好准备
操作步骤
查看需要迁移表（原表）结构
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年数据的分区。
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)
清除新表所有的分区独立表空间，为导入原表的分区独立表空间做准备
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和确保该表的脏页刷到磁盘，并加共享表锁
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传输到新表所在的位置，并赋予权限
[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语句窗口，释放共享表锁
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)
进入新表所在的实例或新表所在的库，手动导入分区表空间，进行数据恢复（应用传输到新表的分区表空间）
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)
表空间迁移完成，数据恢复完成，最后校验数据准确性
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 ...方式速度更快，数据立即可用，而且对业务的影响更小。
文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
SQL
Linux
存储
关键词：
云数据库 RDS MySQL 版迁移
云数据库 RDS MySQL 版分区
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
powdba
目录
相关文章
游客7wkr3y7oxyt7a
|
26天前
|
存储 运维 关系型数据库
从MySQL到云数据库，数据库迁移真的有必要吗？
本文探讨了企业在业务增长背景下，是否应从 MySQL 迁移至云数据库的决策问题。分析了 MySQL 的优势与瓶颈，对比了云数据库在存储计算分离、自动化运维、多负载支持等方面的优势，并提出判断迁移必要性的五个关键问题及实施路径，帮助企业理性决策并落地迁移方案。
游客7wkr3y7oxyt7a
27 0
大数据文摘
|
3月前
|
人工智能 运维 关系型数据库
数据库运维：mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧，重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库，推荐使用mysqldump进行逻辑导出和source导入；10GB以上可考虑mydumper与myloader工具；100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句，并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法，可实现高效的数据迁移。
大数据文摘
583 1
互联网课堂
|
3月前
|
关系型数据库 MySQL 数据库
MySQL数据库上云迁移
本文介绍了将数据库迁移到RDS for Mysql的两种主要方法：停服迁移和不停服迁移。停服迁移适合可短暂中断服务的场景，通过mysqldump或DTS完成；不停服迁移适用于需保持业务连续性的场景，推荐使用DTS实现结构、全量及增量数据迁移。文中详细列出了每种方法的具体操作步骤，帮助企业根据需求选择合适的迁移方案。
互联网课堂
79 1
MySQL数据库上云迁移
游客kufrkwrbkmpsa
|
6月前
|
存储 SQL 关系型数据库
【YashanDB知识库】MySQL迁移至崖山char类型数据自动补空格问题
**简介**：在MySQL迁移到崖山环境时，若字段类型为char(2)，而应用存储的数据仅为'0'或'1'，查询时崖山会自动补空格。原因是mysql的sql_mode可能启用了PAD_CHAR_TO_FULL_LENGTH模式，导致保留CHAR类型尾随空格。解决方法是与应用确认数据需求，可将崖山环境中的char类型改为varchar类型以规避补空格问题，适用于所有版本。
游客kufrkwrbkmpsa
125 17
游客pb5c4iyea3ani
|
6月前
|
安全 关系型数据库 MySQL
【YashanDB知识库】YMP从mysql迁移到崖山，报错：服务器错误
【YashanDB知识库】YMP从mysql迁移到崖山，报错：服务器错误
游客pb5c4iyea3ani
74 6
游客kufrkwrbkmpsa
|
6月前
|
关系型数据库 MySQL Java
【YashanDB知识库】Kettle迁移MySQL到YashanDB
本文介绍了使用Kettle将MySQL数据库中的中文数据迁移到YashanDB的方法，解决因YMP不支持Latin1字符集导致的乱码问题。提供了Windows和Linux两种环境下的操作步骤，包括配置JAVA环境、解压作业包、设置数据库连接（MySQLInput与YashanOutput）、修改表列表配置文件及运行迁移任务。Windows环境支持图形界面便于调试，Linux环境网络性能更优。通过详细的操作指南，确保数据迁移成功并可重试无冲突。
游客kufrkwrbkmpsa
120 3
游客kufrkwrbkmpsa
|
6月前
|
SQL 关系型数据库 MySQL
【YashanDB知识库】字符集latin1的MySQL中文数据如何迁移到YashanDB
本文探讨了在使用YMP 23.2.1.3迁移MySQL Server字符集为latin1的中文数据至YashanDB时出现乱码的问题。问题根源在于MySQL latin1字符集存放的是实际utf8编码的数据，而YMP尚未支持此类场景。文章提供了两种解决方法：一是通过DBeaver直接迁移表数据；二是将MySQL表数据转换为Insert语句后手动插入YashanDB。同时指出，这两种方法适合单张表迁移，多表迁移可能存在兼容性问题，建议对问题表单独处理。
游客kufrkwrbkmpsa
109 0
【YashanDB知识库】字符集latin1的MySQL中文数据如何迁移到YashanDB
游客kufrkwrbkmpsa
|
6月前
|
安全 关系型数据库 MySQL
【YashanDB知识库】YMP从mysql迁移到崖山，报错：服务器错误
【YashanDB知识库】YMP从mysql迁移到崖山，报错：服务器错误
游客kufrkwrbkmpsa
66 0
【YashanDB知识库】YMP从mysql迁移到崖山，报错：服务器错误
阿里云开发者
|
弹性计算 关系型数据库 数据库
手把手带你从自建 MySQL 迁移到云数据库，一步就能脱胎换骨
阿里云瑶池数据库来开课啦！自建数据库迁移至云数据库 RDS原来只要一步操作就能搞定！点击阅读原文完成实验就可获得一本日历哦～
阿里云开发者
291 7
游客kufrkwrbkmpsa
|
6月前
|
Oracle 关系型数据库 MySQL
使用崖山YMP 迁移 Oracle/MySQL 至YashanDB 23.2 验证测试
这篇文章是作者尚雷关于使用崖山YMP迁移Oracle/MySQL至YashanDB 23.2的验证测试分享。介绍了YMP的产品信息，包括架构、版本支持等，还详细阐述了外置库部署、YMP部署、访问YMP、数据源管理、任务管理（创建任务、迁移配置、离线迁移、校验初始化、一致性校验）及MySQL迁移的全过程。
游客kufrkwrbkmpsa
99 2

热门文章

最新文章

  • 1
    Ubuntu 22.04.1上安装MySQL 8.0及设置root密码的注意事项
  • 2
    使用命令行cmd查询MySQL表结构信息技巧分享。
  • 3
    MySQL数据库中进行日期比较的多种方法介绍。
  • 4
    MySQL中binlog、redolog与undolog的不同之处解析
  • 5
    MySQL数据库性能调优：实用技术与策略
  • 6
    在CentOS 8.x上安装Percona Xtrabackup工具备份MySQL数据步骤。
  • 7
    MySQL数据库约束条件概述及其重要性讨论。
  • 8
    降低MySQL高CPU使用率的优化策略。
  • 9
    MySQL数据库命令行操作_mysql命令行
  • 10
    从MySQL到云数据库，数据库迁移真的有必要吗？
  • 1
    Go语言中如何连接 MySQL，基础必备！
    121
  • 2
    在 MySQL 中使用 `REPLACE` 函数
    1453
  • 3
    在 MySQL 中使用 `RIGHT` 函数
    864
  • 4
    在 MySQL 中使用 `RTRIM` 函数
    531
  • 5
    面试官：说说MySQL调优？
    186
  • 6
    揭秘：如何在无影的Serverless世界中，悄无声息地操控MySQL数据库！
    186
  • 7
    问题1：Navicat连接不上mysql8的简单解决办法
    2235
  • 8
    MySQL 常见日志清理策略
    568
  • 9
    【优秀python web设计】基于Python flask的猫眼电影可视化系统，可视化用echart，前端Layui，数据库用MySQL，包括爬虫
    377
  • 10
    MySQL 给数据表增加一列，一定会锁表吗？
    613

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • MySQL 技术大全：开发、优化与运维实战
  • 阿里云数据库运维实战手册
  • 搭建电商项目架构连接MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    OSS存储类型概述