MySQL分區怎樣遷移

2024-07-24 12:34:44

字體：大中小

來源：轉載

供稿：網友

　　需求來源
　　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開源數據庫，喜好開源技術。

（編輯：武林網）

上一篇：重新學習Mysql數據庫5 依據MySQL索引原理進行分析與優化

下一篇：MYSQL 查找數據排序數據和分組數據