使用 pt-online-schema-change 将 MySQL 表改为分区表

MySQL 版本： 8.0.16 percona-toolkit 版本：3.0.6

1. 安装 percona-toolkit

代码语言：javascript

复制

yum install perl-TermReadKey.x86_64 yum install perl-DBI yum install perl-DBD-MySQL yum install perl-Time-HiRes yum install perl-IO-Socket-SSL

rpm -ivh percona-toolkit-3.0.6-1.el6.x86_64.rpm

2. 创建测试表并插入数据

代码语言：javascript

复制

use test;
create table t1 (

id bigint not null auto_increment primary key,

remark varchar(32) default null comment '备注',

createtime timestamp not null default current_timestamp comment '创建时间');
insert into t1 (remark,createtime) values('a','2023-05-05 05:05:05');

insert into t1 (remark,createtime) values('b','2023-06-06 06:06:06');

insert into t1 (remark,createtime) values('c','2023-07-07 07:07:07');

insert into t1 (remark,createtime) values('d','2023-08-08 08:08:08');

insert into t1 (remark,createtime) values('e','2023-09-09 09:09:09');

insert into t1 (remark,createtime) values('f','2023-10-10 10:10:10');

3. 执行 pt-online-schema-change

代码语言：javascript

复制

pt-online-schema-change 

--socket="/data/18253/mysqldata/mysql.sock" 

--user="wxy" 

--password="123456" 

--charset="utf8mb4" 

--chunk-size=10000 

--recursion-method="processlist" 

--check-interval=10s 

--max-lag=60s 

--nocheck-replication-filters 

--nocheck-unique-key-change 

--no-check-alter 

--critical-load="Threads_running=512" 

--max-load="Threads_running=256" 

D="test",t="t1" 

--alter="

add column yearmonth int not null default (year(createtime)*100+month(createtime)),

drop primary key,

add primary key(id,yearmonth)

partition by range(yearmonth)

(partition p202305 values less than(202306),

partition p202306 values less than(202307),

partition p202307 values less than(202308),

partition p202308 values less than(202309),

partition p202309 values less than(202310),

partition p202310 values less than(202311),

partition p202311 values less than(202312)

);

" 

--progress=time,30 

--execute

4. 验证

代码语言：javascript

复制

mysql> use test;

Database changed

mysql> insert into t1 (remark,createtime) values('g','2023-11-11 11:11:11');

Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;

+----+--------+---------------------+-----------+

| id | remark | createtime          | yearmonth |

+----+--------+---------------------+-----------+

|  1 | a      | 2023-05-05 05:05:05 |    202305 |

|  2 | b      | 2023-06-06 06:06:06 |    202306 |

|  3 | c      | 2023-07-07 07:07:07 |    202307 |

|  4 | d      | 2023-08-08 08:08:08 |    202308 |

|  5 | e      | 2023-09-09 09:09:09 |    202309 |

|  6 | f      | 2023-10-10 10:10:10 |    202310 |

|  7 | g      | 2023-11-11 11:11:11 |    202311 |

+----+--------+---------------------+-----------+

7 rows in set (0.00 sec)
mysql> show create table t1\G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE t1 (

id bigint(20) NOT NULL AUTO_INCREMENT,

remark varchar(32) DEFAULT NULL COMMENT '备注',

createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

yearmonth int(11) NOT NULL,

PRIMARY KEY (id,yearmonth)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

/*!50100 PARTITION BY RANGE (yearmonth)

(PARTITION p202305 VALUES LESS THAN (202306) ENGINE = InnoDB,

PARTITION p202306 VALUES LESS THAN (202307) ENGINE = InnoDB,

PARTITION p202307 VALUES LESS THAN (202308) ENGINE = InnoDB,

PARTITION p202308 VALUES LESS THAN (202309) ENGINE = InnoDB,

PARTITION p202309 VALUES LESS THAN (202310) ENGINE = InnoDB,

PARTITION p202310 VALUES LESS THAN (202311) ENGINE = InnoDB,

PARTITION p202311 VALUES LESS THAN (202312) ENGINE = InnoDB) */

1 row in set (0.00 sec)

注意：

该方法有一定风险，执行前务必做好备份。
只能用在 MySQL 8.0.13 及以上版本，因为之前版本的 default 中只能是常量，不支持表达式。
手工修改 alter 不行，因为不能加 not null，否则报错：

代码语言：javascript

复制

mysql> alter table t1 

    -> add column yearmonth int not null default (year(b)*100+month(b));

ERROR 1048 (23000): Column 'yearmonth' cannot be null