加入收藏 | 设为首页 | 会员中心 | 我要投稿 衡阳站长网 （https://www.0734zz.cn/）- 数据集成、设备管理、备份、数据加密、智能搜索!
当前位置： 首页 > 站长学院 > MySql教程 > 正文

MySQL分区怎样迁移

发布时间：2022-06-26 20:02:43 所属栏目：MySql教程 来源：互联网
导读：需求来源 MySQL越来越流行，而且存储在MySQL的数据量也越来越大，单表数据达亿行已经是非常常见的现象，而这些表里面保存了大量的历史记录，严重影响SQL执行的效率。本文是针对客户需求，迁移MySQL Innodb大表分区中部分历史归档分区到其他实例或者其他库表
副标题[/!--empirenews.page--] 　　需求来源
　　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年数据的分区（也就是分区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 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 ...方式速度更快，数据立即可用，而且对业务的影响更小。
 
　　| 作者简介
 
　　岳雷·沃趣科技数据库工程师
 
　　熟悉MySQL体系结构和innodb存储引擎工作原理；以及MySQL备份恢复、复制、数据迁移等技术；专注于MySQL、MariaDB开源数据库，喜好开源技术。

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
      站长推荐
      热点阅读

        【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

        建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的优质浏览效果

        Copygight © 2017-2023 https://www.0734zz.cn/ All Rights Reserved. 衡阳站长网