Shulou(Shulou.com)06/01 Report--

| | background demand source |

MySQL is becoming more and more popular, and the amount of data stored in MySQL is becoming larger and larger. It is a very common phenomenon that hundreds of millions of rows of data are stored in a single table, and a large number of historical records are kept in these tables, which seriously affect the efficiency of SQL execution. According to the needs of customers, this paper migrates part of the historical archive partition of MySQL Innodb large table partition to other instances or other database tables, and the migration process minimizes the impact on the business environment.

Environment introduction

MySQL 5.7.21

Centos 7.4

Innodb_file_per_table=1

| | Innodb migration method commonly used in MySQL |

MySQL Enterprise Backup (physical backup, similar to xtrabackup)

Copying Data Files (cold backup)

Logical Export and Import (mysqldump,mydumper,mysqlpump)

Transferable tablespace

| | preparation of migration solution (table space that can be transferred) |

MySQL version must be 5.7

The business is unwritable for a short period of time in the process of migration. It is recommended to prepare in advance.

Operation steps to view the structure of the table to be migrated (original table) root@localhost: testdba 02:03:18 > use testDatabase changedroot@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 Colette VALUES LESS THAN (20161201) 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 PARTITION (20180401) PARTITION = VALUES LESS THAN, VALUES LESS THAN p17 (20180501) VALUES LESS THAN = VALUES LESS THAN 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 | | 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) according to personal migration partition table requirements | You can migrate historical partitions to other MySQL instances or to other libraries of the same MySQL instance. First, create a partitioned table with the same table structure as the original table. When creating a partitioned table, we only need to create the table partitioning structure that we need to migrate. Example: the following is a migration case. Since only 2017 data is migrated, the table structure only creates partitions to store 2017 data (i.e. partition 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 changedroot@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) clears all partitioned independent tablespaces of the new table in preparation for importing the partitioned independent tablespaces of the original table

Root@localhost: testdba 02:00:05 > use testdba;Database changedroot@localhost: testdba 02:00:23 > ALTER TABLE sbtest2 DISCARD PARTITION p2, p3, p4, p5, direction, p7, p8, p9, p10, p11, p12, p13 TABLESPACE;Query OK, 0 rows affected (0.27 sec), execute FLUSH TABLES in the original table. FOR EXPORT (do not exit the session or perform a unlock tables; operation until the partition tablespace transfer is complete) to obtain the metadata check file .cfg and to ensure that the dirty pages of the table are brushed to disk and shared table locks are added

Root@localhost: testdba 02:00:24 > USE test;Database changedroot@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.ibdsbtest2#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.frmsbtest2#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.cfgsbtest2#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.ibdsbtest2#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 goes to the directory where the original table ibd is located. Transfer the partitioned tablespace and metadata check file .cfg that the original table needs to migrate to the location of the new table and grant permissions

[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.ibdsbtest2#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.frmsbtest2#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 switch back to executing FLUSH TABLES. FOR EXPORT statement window, releasing shared table locks

Root@localhost: test 02:00:29 > USE test;Database changedroot@localhost: test 02:01:07 > UNLOCK TABLES;Query OK, 0 rows affected (0.00 sec) enter the instance or library where the new table is located, import the partitioned tablespace manually, and perform data recovery (apply the partitioned tablespace transferred to the new table)

Root@localhost: test 02:01:07 > USE testdba;Database changedroot@localhost: testdba 02:01:14 > ALTER TABLE sbtest2 IMPORT PARTITION p2Tablespace migration is complete, data recovery is complete, and data recovery is complete. Finally, check the accuracy of the data.

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) | Summary

The above is that we use MySQL's partition table space transfer method to solve the problem of archiving the historical data of partition tables to other instances or other libraries of the same instance. Compare the logical migration methods of mysqldump or insert.. Select... The method is faster, the data is immediately available, and has less impact on the business.

| | author profile |

Yue Lei Walk Science and Technology Database engineer

Familiar with MySQL architecture and the working principle of innodb storage engine; and MySQL backup recovery, replication, data migration and other technologies; focus on MySQL, MariaDB open source database, like open source technology.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.