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

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

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');

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

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> alter table t1 
    -> add column yearmonth int not null default (year(b)*100+month(b));
ERROR 1048 (23000): Column 'yearmonth' cannot be null