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

2023-11-15 09:18:25 浏览数 (2)

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 t1G
*************************** 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)

注意：

  1.  该方法有一定风险，执行前务必做好备份。
  2.  只能用在 MySQL 8.0.13 及以上版本，因为之前版本的 default 中只能是常量，不支持表达式。
  3. 手工修改 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
mysql schema 备份 测试 数据

0 人点赞