MySQL分区如何迁移
短信预约 -IT技能 免费直播动态提醒
| 背景
需求来源
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
|
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
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)
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)
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
[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
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开源数据库，喜好开源技术。
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/5.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
