背景
现网中业务表的数据量很大，执行sql查询很耗资源且速度很慢，现决定把业务大表修改为分区表。但是大家都知道，在MySQL中对大表直接执行DDL是高风险操作，可能会导致长时间的锁表和业务性能下降。为了减少直接操作对业务的影响，尽量避免主从延迟以及不影响在线dml操作，我们可以使用Percona公司的pt-online-schema-change工具来完成本次修改分区表的操作。
在现网中操作务必要先做好测试，做好备份，在业务低峰期操作！切记！
本文主要在虚拟机中模拟大表修改为分区表的过程，供需要的朋友参考！
pt-online-schema-change介绍及工作原理
pt-online-schema-change，简称pt-osc，用于对大数据量的表做DDL操作，并且在修改过程中不会造成读写阻塞。
工作原理：
- 首先新建一张和原表结构一模一样的新表，表名一般是_new后缀
- 然后在这个新表上更改表结构
- 接着在原表上增加delete/update/insert三个after触发器，在原表中要执行的语句也在新表中执行
- copy原表中的数据到新表
- 将原表改名，并将新表改成原表名
- 删除原表、触发器
使用须知：
- 表要有主键或者唯一索引
- 表不能有trigger，因为pt-osc工具会创建触发器。
- 有外键的表需要注意使用参数–alter-foreign-keys-method
pt-online-schema-change下载
下载地址：
https://www.percona.com/downloads
pt-online-schema-change安装
tar -zxvf percona-toolkit-3.6.0_x86_64.tar.gz
使用pt-online-schema-change工具把普通表修改为分区表
创建普通表
use testdb
CREATE TABLE test (
id int(11) NOT NULL AUTO_INCREMENT ,
dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间',
name varchar(30) COMMENT '名字',
PRIMARY KEY p_id (id) ,
KEY ind_dt (dt) USING BTREE
);
在表中插入600万行数据
DELIMITER $$
CREATE PROCEDURE InsertData()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE max INT DEFAULT 6000000;
WHILE i <= max DO
INSERT INTO test (dt, name) VALUES (NOW(), i);
set i = i + 1;
END WHILE;
END$$
DELIMITER ;
call InsertData();
构造几条其它分区的数据
INSERT INTO test (dt, name) VALUES(STR_TO_DATE('2023-08-23 10:15:03', '%Y-%m-%d %T'),'a');
INSERT INTO test (dt, name) VALUES(STR_TO_DATE('2023-09-23 09:15:03', '%Y-%m-%d %T'),'b');
INSERT INTO test (dt, name) VALUES(STR_TO_DATE('2024-03-24 10:15:03', '%Y-%m-%d %T'),'c');
INSERT INTO test (dt, name) VALUES(STR_TO_DATE('2024-04-24 10:15:03', '%Y-%m-%d %T'),'d');
INSERT INTO test (dt, name) VALUES(STR_TO_DATE('2024-05-24 10:15:03', '%Y-%m-%d %T'),'e');
#检查记录数
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 6000005 |
+----------+
1 row in set (1.17 sec)
#检查最小日期和最大日期，为后续做按月分区做准备，确保分区可以包含所有的月份：
mysql> select min(dt),max(dt) from test;
+---------------------+---------------------+
| min(dt) | max(dt) |
+---------------------+---------------------+
| 2023-08-23 10:15:03 | 2024-09-25 14:11:21 |
+---------------------+---------------------+
1 row in set (0.01 sec)
#检查表上是否有外键
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'database_name' -- 替换数据库名
AND TABLE_NAME = 'table_name' -- 替换表名
AND REFERENCED_TABLE_NAME IS NOT NULL;\G;
本例中表上没有创建外键，跳过此步骤。
pt_osc把test表修改为分区表过程
使用--dry-run参数了解执行步骤与细节
dry-run并不真正执行，可以看到语句的执行步骤与细节。
[root@node2 bin]# ./pt-online-schema-change --user=root --password='***' --charset=utf8 D=testdb,t=test --alter "PARTITION BY RANGE COLUMNS(dt)
> (PARTITION p202307 VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202308 VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202309 VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202310 VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202311 VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202312 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202401 VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202402 VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202403 VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202404 VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202405 VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202406 VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202407 VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202408 VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202409 VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202410 VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202411 VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202412 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202501 VALUES LESS THAN ('2025-02-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202502 VALUES LESS THAN ('2025-03-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202503 VALUES LESS THAN ('2025-04-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202504 VALUES LESS THAN ('2025-05-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202505 VALUES LESS THAN ('2025-06-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202506 VALUES LESS THAN ('2025-07-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202507 VALUES LESS THAN ('2025-08-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202508 VALUES LESS THAN ('2025-09-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202509 VALUES LESS THAN ('2025-10-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202510 VALUES LESS THAN ('2025-11-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202511 VALUES LESS THAN ('2025-12-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202512 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
> PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)" --recursion-method=none --drop-old-table --print --statistics --dry-run
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Starting a dry run. `testdb`.`test` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
CREATE TABLE `testdb`.`_test_new` (
`id` int NOT NULL AUTO_INCREMENT,
`dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '鏃堕棿',
`name` varchar(30) DEFAULT NULL COMMENT '鍚嶅瓧',
PRIMARY KEY (`id`),
KEY `ind_dt` (`dt`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6368624 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Created new table testdb._test_new OK.
Altering new table...
ALTER TABLE `testdb`.`_test_new` PARTITION BY RANGE COLUMNS(dt)
(PARTITION p202307 VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202308 VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202309 VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202310 VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202311 VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202312 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202401 VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202402 VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202403 VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202404 VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202405 VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202406 VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202407 VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202408 VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202409 VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202410 VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202411 VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202412 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202501 VALUES LESS THAN ('2025-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202502 VALUES LESS THAN ('2025-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202503 VALUES LESS THAN ('2025-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202504 VALUES LESS THAN ('2025-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202505 VALUES LESS THAN ('2025-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202506 VALUES LESS THAN ('2025-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202507 VALUES LESS THAN ('2025-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202508 VALUES LESS THAN ('2025-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202509 VALUES LESS THAN ('2025-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202510 VALUES LESS THAN ('2025-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202511 VALUES LESS THAN ('2025-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202512 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)
2024-09-25T17:50:30 Dropping new table...
DROP TABLE IF EXISTS `testdb`.`_test_new`;
2024-09-25T17:50:30 Dropped new table OK.
# Event Count
# ====== =====
# INSERT 0
Dry run complete. `testdb`.`test` was not altered.
Error altering new table `testdb`.`_test_new`: DBD::mysql::db do failed: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered). [for Statement "ALTER TABLE `testdb`.`_test_new` PARTITION BY RANGE COLUMNS(dt)
(PARTITION p202307 VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202308 VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202309 VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202310 VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202311 VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202312 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202401 VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202402 VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202403 VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202404 VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202405 VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202406 VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202407 VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202408 VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202409 VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202410 VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202411 VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202412 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202501 VALUES LESS THAN ('2025-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202502 VALUES LESS THAN ('2025-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202503 VALUES LESS THAN ('2025-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202504 VALUES LESS THAN ('2025-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202505 VALUES LESS THAN ('2025-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202506 VALUES LESS THAN ('2025-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202507 VALUES LESS THAN ('2025-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202508 VALUES LESS THAN ('2025-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202509 VALUES LESS THAN ('2025-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202510 VALUES LESS THAN ('2025-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202511 VALUES LESS THAN ('2025-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202512 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)"] at ./pt-online-schema-change line 9812.
# Event Count
# ====== =====
# INSERT 0
Dry run complete. `testdb`.`test` was not altered.
可以看到执行异常了：A PRIMARY KEY must include all columns in the table’s partitioning function (prefixed columns are not considered)，需要在test表的主键列里面加上分区键dt。
修改test表的主键列
[root@node2 bin]# ./pt-online-schema-change -uroot -p'***' --alter='DROP PRIMARY KEY, ADD PRIMARY KEY (id,dt)' --charset=utf8 D=testdb,t=test --execute --print --statistics
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
No slaves found. See --recursion-method if host node2 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `testdb`.`test`...
You are trying to add an unique key. This can result in data loss if the data is not unique.
Please read the documentation for the --check-unique-key-change parameter.
You can check if the column(s) contain duplicate content by running this/these query/queries:
SELECT IF(COUNT(DISTINCT id, dt) = COUNT(*),
'Yes, the desired unique index currently contains only unique values',
'No, the desired unique index contains duplicated values. There will be data loss'
) AS IsThereUniqueness FROM `testdb`.`test`;
Keep in mind that these queries could take a long time and consume a lot of resources
# Event Count
# ====== =====
# INSERT 0
`testdb`.`test` was not altered.
修改主键或者唯一键的时候，会要求我们自行做数据重复性校验，用上面的sql检查后确认表中无重复数据，加--nocheck-unique-key-change参数继续执行。执行过程如下：
[root@node2 bin]# ./pt-online-schema-change -uroot -p'***' --alter='DROP PRIMARY KEY, ADD PRIMARY KEY (id,dt)' --charset=utf8 D=testdb,t=test --nocheck-unique-key-change --execute --print --statistics
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
No slaves found. See --recursion-method if host node2 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `testdb`.`test`...
--alter contains 'DROP PRIMARY KEY'. Dropping and altering the primary key can be dangerous, especially if the original table does not have other unique indexes.
The tool should handle this correctly, but you should test it first and carefully examine the triggers which rely on the PRIMARY KEY or a unique index. Specify --no-check-alter to disable this check and perform the --alter.
--check-alter failed.
# Event Count
# ====== =====
# INSERT 0
`testdb`.`test` was not altered.
[root@node2 bin]#
可以看到，命令仍然不能正常执行，按照提示加入--no-check-alter参数执行，主键重新创建成功。执行过程如下：
[root@node2 bin]# ./pt-online-schema-change -uroot -p'***' --alter='DROP PRIMARY KEY, ADD PRIMARY KEY (id,dt)' --charset=utf8 --no-check-alter D=testdb,t=test --execute --print --statistics
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
No slaves found. See --recursion-method if host node2 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `testdb`.`test`...
Creating new table...
CREATE TABLE `testdb`.`_test_new` (
`id` int NOT NULL AUTO_INCREMENT,
`dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间',
`name` varchar(30) DEFAULT NULL COMMENT '名字',
PRIMARY KEY (`id`),
KEY `ind_dt` (`dt`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6368624 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Created new table testdb._test_new OK.
Altering new table...
ALTER TABLE `testdb`.`_test_new` DROP PRIMARY KEY, ADD PRIMARY KEY (id,dt)
Altered `testdb`.`_test_new` OK.
2024-09-25T18:31:01 Creating triggers...
-----------------------------------------------------------
Event : DELETE
Name : pt_osc_testdb_test_del
SQL : CREATE TRIGGER `pt_osc_testdb_test_del` AFTER DELETE ON `testdb`.`test` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `testdb`.`_test_new` WHERE `testdb`.`_test_new`.`id` <=> OLD.`id` AND `testdb`.`_test_new`.`dt` <=> OLD.`dt`; END
Suffix: del
Time : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Event : UPDATE
Name : pt_osc_testdb_test_upd
SQL : CREATE TRIGGER `pt_osc_testdb_test_upd` AFTER UPDATE ON `testdb`.`test` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `testdb`.`_test_new` WHERE !(OLD.`id` <=> NEW.`id` AND OLD.`dt` <=> NEW.`dt`) AND `testdb`.`_test_new`.`id` <=> OLD.`id` AND `testdb`.`_test_new`.`dt` <=> OLD.`dt`; REPLACE INTO `testdb`.`_test_new` (`id`, `dt`, `name`) VALUES (NEW.`id`, NEW.`dt`, NEW.`name`); END
Suffix: upd
Time : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Event : INSERT
Name : pt_osc_testdb_test_ins
SQL : CREATE TRIGGER `pt_osc_testdb_test_ins` AFTER INSERT ON `testdb`.`test` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `testdb`.`_test_new` (`id`, `dt`, `name`) VALUES (NEW.`id`, NEW.`dt`, NEW.`name`);END
Suffix: ins
Time : AFTER
-----------------------------------------------------------
2024-09-25T18:31:01 Created triggers OK.
2024-09-25T18:31:01 Copying approximately 6260873 rows...
INSERT LOW_PRIORITY IGNORE INTO `testdb`.`_test_new` (`id`, `dt`, `name`) SELECT `id`, `dt`, `name` FROM `testdb`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 72892 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `testdb`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Copying `testdb`.`test`: 32% 01:03 remain
Copying `testdb`.`test`: 65% 00:31 remain
Copying `testdb`.`test`: 96% 00:02 remain
2024-09-25T18:32:35 Copied rows OK.
2024-09-25T18:32:35 Analyzing new table...
2024-09-25T18:32:35 Swapping tables...
RENAME TABLE `testdb`.`test` TO `testdb`.`_test_old`, `testdb`.`_test_new` TO `testdb`.`test`
2024-09-25T18:32:35 Swapped original and new tables OK.
2024-09-25T18:32:35 Dropping old table...
DROP TABLE IF EXISTS `testdb`.`_test_old`
2024-09-25T18:32:35 Dropped old table `testdb`.`_test_old` OK.
2024-09-25T18:32:35 Dropping triggers...
DROP TRIGGER IF EXISTS `testdb`.`pt_osc_testdb_test_del`
DROP TRIGGER IF EXISTS `testdb`.`pt_osc_testdb_test_upd`
DROP TRIGGER IF EXISTS `testdb`.`pt_osc_testdb_test_ins`
2024-09-25T18:32:35 Dropped triggers OK.
# Event Count
# ====== =====
# INSERT 180
Successfully altered `testdb`.`test`.
查看现在的test表结构：
mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT,
`dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间',
`name` varchar(30) DEFAULT NULL COMMENT '名字',
PRIMARY KEY (`id`,`dt`),
KEY `ind_dt` (`dt`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6368624 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
可以看到，主键列已经包含了分区键dt。
再次使用--dry-run
[root@node2 bin]# ./pt-online-schema-change --user=root --password='***' --charset=utf8 D=testdb,t=test --alter "PARTITION BY RANGE COLUMNS(dt)
> (PARTITION p202307 VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202308 VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202309 VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202310 VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202311 VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202312 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202401 VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202402 VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202403 VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202404 VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202405 VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202406 VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202407 VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202408 VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202409 VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202410 VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202411 VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202412 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202501 VALUES LESS THAN ('2025-02-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202502 VALUES LESS THAN ('2025-03-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202503 VALUES LESS THAN ('2025-04-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202504 VALUES LESS THAN ('2025-05-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202505 VALUES LESS THAN ('2025-06-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202506 VALUES LESS THAN ('2025-07-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202507 VALUES LESS THAN ('2025-08-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202508 VALUES LESS THAN ('2025-09-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202509 VALUES LESS THAN ('2025-10-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202510 VALUES LESS THAN ('2025-11-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202511 VALUES LESS THAN ('2025-12-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202512 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
> PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)" --recursion-method=none --drop-old-table --print --statistics --dry-run
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Starting a dry run. `testdb`.`test` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
CREATE TABLE `testdb`.`_test_new` (
`id` int NOT NULL AUTO_INCREMENT,
`dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间',
`name` varchar(30) DEFAULT NULL COMMENT '名字',
PRIMARY KEY (`id`,`dt`),
KEY `ind_dt` (`dt`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6368624 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Created new table testdb._test_new OK.
Altering new table...
ALTER TABLE `testdb`.`_test_new` PARTITION BY RANGE COLUMNS(dt)
(PARTITION p202307 VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202308 VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202309 VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202310 VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202311 VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202312 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202401 VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202402 VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202403 VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202404 VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202405 VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202406 VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202407 VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202408 VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202409 VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202410 VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202411 VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202412 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202501 VALUES LESS THAN ('2025-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202502 VALUES LESS THAN ('2025-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202503 VALUES LESS THAN ('2025-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202504 VALUES LESS THAN ('2025-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202505 VALUES LESS THAN ('2025-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202506 VALUES LESS THAN ('2025-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202507 VALUES LESS THAN ('2025-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202508 VALUES LESS THAN ('2025-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202509 VALUES LESS THAN ('2025-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202510 VALUES LESS THAN ('2025-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202511 VALUES LESS THAN ('2025-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202512 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)
Altered `testdb`.`_test_new` OK.
Not creating triggers because this is a dry run.
-----------------------------------------------------------
Skipped trigger creation:
Event : DELETE
Name : pt_osc_testdb_test_del
SQL : CREATE TRIGGER `pt_osc_testdb_test_del` AFTER DELETE ON `testdb`.`test` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `testdb`.`_test_new` WHERE `testdb`.`_test_new`.`id` <=> OLD.`id` AND `testdb`.`_test_new`.`dt` <=> OLD.`dt`; END
Suffix: del
Time : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Skipped trigger creation:
Event : UPDATE
Name : pt_osc_testdb_test_upd
SQL : CREATE TRIGGER `pt_osc_testdb_test_upd` AFTER UPDATE ON `testdb`.`test` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `testdb`.`_test_new` WHERE !(OLD.`id` <=> NEW.`id` AND OLD.`dt` <=> NEW.`dt`) AND `testdb`.`_test_new`.`id` <=> OLD.`id` AND `testdb`.`_test_new`.`dt` <=> OLD.`dt`; REPLACE INTO `testdb`.`_test_new` (`id`, `dt`, `name`) VALUES (NEW.`id`, NEW.`dt`, NEW.`name`); END
Suffix: upd
Time : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Skipped trigger creation:
Event : INSERT
Name : pt_osc_testdb_test_ins
SQL : CREATE TRIGGER `pt_osc_testdb_test_ins` AFTER INSERT ON `testdb`.`test` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `testdb`.`_test_new` (`id`, `dt`, `name`) VALUES (NEW.`id`, NEW.`dt`, NEW.`name`);END
Suffix: ins
Time : AFTER
-----------------------------------------------------------
Not copying rows because this is a dry run.
INSERT LOW_PRIORITY IGNORE INTO `testdb`.`_test_new` (`id`, `dt`, `name`) SELECT `id`, `dt`, `name` FROM `testdb`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` > ?) OR (`id` = ? AND `dt` >= ?)) AND ((`id` < ?) OR (`id` = ? AND `dt` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 74148 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id`, `id`, `dt` FROM `testdb`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` > ?) OR (`id` = ? AND `dt` >= ?)) ORDER BY `id`, `dt` LIMIT ?, 2 /*next chunk boundary*/
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
DROP TRIGGER IF EXISTS `testdb`.`pt_osc_testdb_test_del`
DROP TRIGGER IF EXISTS `testdb`.`pt_osc_testdb_test_upd`
DROP TRIGGER IF EXISTS `testdb`.`pt_osc_testdb_test_ins`
2024-09-25T18:50:33 Dropping new table...
DROP TABLE IF EXISTS `testdb`.`_test_new`;
2024-09-25T18:50:33 Dropped new table OK.
# Event Count
# ====== =====
# INSERT 0
Dry run complete. `testdb`.`test` was not altered.
[root@node2 bin]#
可以看到，dry run修改原表为分区表的执行过程如下：
- 创建_new的新表
- 把_new的新表修改为分区表
- 不会在原表上创建delete，update，insert的after触发器
- 不会拷贝数据到_new的新表中
- 不swap table
- 不drop 原表
- drop _new的新表
修改test表为分区表
./pt-online-schema-change --user=root --password='***' --charset=utf8 D=testdb,t=test --alter "PARTITION BY RANGE COLUMNS(dt)
(PARTITION p202307 VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202308 VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202309 VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202310 VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202311 VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202312 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202401 VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202402 VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202403 VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202404 VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202405 VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202406 VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202407 VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202408 VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202409 VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202410 VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202411 VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202412 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202501 VALUES LESS THAN ('2025-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202502 VALUES LESS THAN ('2025-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202503 VALUES LESS THAN ('2025-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202504 VALUES LESS THAN ('2025-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202505 VALUES LESS THAN ('2025-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202506 VALUES LESS THAN ('2025-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202507 VALUES LESS THAN ('2025-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202508 VALUES LESS THAN ('2025-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202509 VALUES LESS THAN ('2025-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202510 VALUES LESS THAN ('2025-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202511 VALUES LESS THAN ('2025-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202512 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)" --recursion-method=none --drop-old-table --print --statistics --execute
执行过程如下：
[root@node2 bin]# ./pt-online-schema-change --user=root --password='***' --charset=utf8 D=testdb,t=test --alter "PARTITION BY RANGE COLUMNS(dt)
> (PARTITION p202307 VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202308 VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202309 VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202310 VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202311 VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202312 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202401 VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202402 VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202403 VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202404 VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202405 VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202406 VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202407 VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202408 VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202409 VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202410 VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202411 VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202412 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202501 VALUES LESS THAN ('2025-02-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202502 VALUES LESS THAN ('2025-03-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202503 VALUES LESS THAN ('2025-04-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202504 VALUES LESS THAN ('2025-05-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202505 VALUES LESS THAN ('2025-06-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202506 VALUES LESS THAN ('2025-07-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202507 VALUES LESS THAN ('2025-08-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202508 VALUES LESS THAN ('2025-09-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202509 VALUES LESS THAN ('2025-10-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202510 VALUES LESS THAN ('2025-11-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202511 VALUES LESS THAN ('2025-12-01 00:00:00') ENGINE = InnoDB,
> PARTITION p202512 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
> PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)" --recursion-method=none --drop-old-table --print --statistics --execute
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
No slaves found. See --recursion-method if host node2 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `testdb`.`test`...
Creating new table...
CREATE TABLE `testdb`.`_test_new` (
`id` int NOT NULL AUTO_INCREMENT,
`dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间',
`name` varchar(30) DEFAULT NULL COMMENT '名字',
PRIMARY KEY (`id`,`dt`),
KEY `ind_dt` (`dt`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6368624 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Created new table testdb._test_new OK.
Altering new table...
ALTER TABLE `testdb`.`_test_new` PARTITION BY RANGE COLUMNS(dt)
(PARTITION p202307 VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202308 VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202309 VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202310 VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202311 VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202312 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202401 VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202402 VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202403 VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202404 VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202405 VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202406 VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202407 VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202408 VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202409 VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202410 VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202411 VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202412 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202501 VALUES LESS THAN ('2025-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202502 VALUES LESS THAN ('2025-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202503 VALUES LESS THAN ('2025-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202504 VALUES LESS THAN ('2025-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202505 VALUES LESS THAN ('2025-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202506 VALUES LESS THAN ('2025-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202507 VALUES LESS THAN ('2025-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202508 VALUES LESS THAN ('2025-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202509 VALUES LESS THAN ('2025-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202510 VALUES LESS THAN ('2025-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202511 VALUES LESS THAN ('2025-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202512 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)
Altered `testdb`.`_test_new` OK.
2024-09-25T18:52:46 Creating triggers...
-----------------------------------------------------------
Event : DELETE
Name : pt_osc_testdb_test_del
SQL : CREATE TRIGGER `pt_osc_testdb_test_del` AFTER DELETE ON `testdb`.`test` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `testdb`.`_test_new` WHERE `testdb`.`_test_new`.`id` <=> OLD.`id` AND `testdb`.`_test_new`.`dt` <=> OLD.`dt`; END
Suffix: del
Time : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Event : UPDATE
Name : pt_osc_testdb_test_upd
SQL : CREATE TRIGGER `pt_osc_testdb_test_upd` AFTER UPDATE ON `testdb`.`test` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `testdb`.`_test_new` WHERE !(OLD.`id` <=> NEW.`id` AND OLD.`dt` <=> NEW.`dt`) AND `testdb`.`_test_new`.`id` <=> OLD.`id` AND `testdb`.`_test_new`.`dt` <=> OLD.`dt`; REPLACE INTO `testdb`.`_test_new` (`id`, `dt`, `name`) VALUES (NEW.`id`, NEW.`dt`, NEW.`name`); END
Suffix: upd
Time : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Event : INSERT
Name : pt_osc_testdb_test_ins
SQL : CREATE TRIGGER `pt_osc_testdb_test_ins` AFTER INSERT ON `testdb`.`test` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `testdb`.`_test_new` (`id`, `dt`, `name`) VALUES (NEW.`id`, NEW.`dt`, NEW.`name`);END
Suffix: ins
Time : AFTER
-----------------------------------------------------------
2024-09-25T18:52:46 Created triggers OK.
2024-09-25T18:52:46 Copying approximately 6349950 rows...
INSERT LOW_PRIORITY IGNORE INTO `testdb`.`_test_new` (`id`, `dt`, `name`) SELECT `id`, `dt`, `name` FROM `testdb`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` > ?) OR (`id` = ? AND `dt` >= ?)) AND ((`id` < ?) OR (`id` = ? AND `dt` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 74273 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id`, `id`, `dt` FROM `testdb`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` > ?) OR (`id` = ? AND `dt` >= ?)) ORDER BY `id`, `dt` LIMIT ?, 2 /*next chunk boundary*/
Copying `testdb`.`test`: 32% 01:02 remain
Copying `testdb`.`test`: 65% 00:32 remain
Copying `testdb`.`test`: 99% 00:00 remain
2024-09-25T18:54:17 Copied rows OK.
2024-09-25T18:54:17 Analyzing new table...
2024-09-25T18:54:17 Swapping tables...
RENAME TABLE `testdb`.`test` TO `testdb`.`_test_old`, `testdb`.`_test_new` TO `testdb`.`test`
2024-09-25T18:54:17 Swapped original and new tables OK.
2024-09-25T18:54:17 Dropping old table...
DROP TABLE IF EXISTS `testdb`.`_test_old`
2024-09-25T18:54:17 Dropped old table `testdb`.`_test_old` OK.
2024-09-25T18:54:17 Dropping triggers...
DROP TRIGGER IF EXISTS `testdb`.`pt_osc_testdb_test_del`
DROP TRIGGER IF EXISTS `testdb`.`pt_osc_testdb_test_upd`
DROP TRIGGER IF EXISTS `testdb`.`pt_osc_testdb_test_ins`
2024-09-25T18:54:17 Dropped triggers OK.
# Event Count
# ====== =====
# INSERT 175
Successfully altered `testdb`.`test`.
[root@node2 bin]#
可以看到，execute修改原表为分区表的真实执行过程如下：
- 创建_new的新表
- 把_new的新表修改为分区表
- 在原表上创建delete，update，insert的after触发器
- 拷贝原表的数据到_new的新表中
- analyze _new的新表
- swap table，把原表的表名rename为 _old`, 新表的表名rename为原表
- drop _old的表
- drop 原表上的触发器
- 完成
如果生产环境中有从库的话，为了降低对从库的影响，也可以考虑加参数–recursion-method，–max-lag等
如果需要优化pt-osc工具执行时的性能，请参考附录中pt-online-schema-change用法调整相关参数。
碰到的问题
./pt-online-schema-change --user=root --password=’***’ --charset=utf8 D=testdb,t=test --alter "PARTITION BY RANGE COLUMNS(dt)报错如下：
Cannot connect to MySQL: install_driver(mysql) failed: Attempt to reload DBD/mysql.pm aborted.
Compilation failed in require at (eval 30) line 3.
at ./pt-online-schema-change line 2345.
18 at ./pt-online-schema-change line 8815.
排查过程：
解决办法：
下载：perl-DBD-MySQL-4.050-1.el7.x86_64.rpm
然后：rpm -ivh perl-DBD-MySQL-4.050-1.el7.x86_64.rpm # 如果碰到包冲突，把操作系统上旧包卸载掉即可。
安装好perl-DBD-MySQL-4.050-1.el7.x86_64.rpm后，pt-online-schema-change工具就可以正常连接mysql8数据库了。
参考链接：https://www.cnblogs.com/kerrycode/p/13888984.html
附录：pt-online-schema-change用法
[root@node2 ~]# /tools/percona-toolkit-3.6.0/bin
[root@node2 bin]# ./pt-online-schema-change --help
pt-online-schema-change alters a table's structure without blocking reads or
writes. Specify the database and table in the DSN. Do not use this tool before
reading its documentation and checking your backups carefully. For more
details, please use the --help option, or try 'perldoc
/tools/percona-toolkit-3.6.0/bin/pt-online-schema-change' for complete
documentation.
Usage: pt-online-schema-change [OPTIONS] DSN
Options:
--alter=s The schema modification, without the ALTER TABLE keywords
--alter-foreign-keys-method=s How to modify foreign keys so they
reference the new table
--[no]analyze-before-swap Execute ANALYZE TABLE on the new table
before swapping with the old one (default
yes)
--ask-pass Prompt for a password when connecting to
MySQL
--binary-index This option modifies the behavior of --
history such that the history table's
upper and lower boundary columns are
created with the BLOB data type
--channel=s Channel name used when connected to a
server using replication channels
--charset=s -A Default character set
--[no]check-alter Parses the --alter specified and tries to
warn of possible unintended behavior (
default yes)
--[no]check-foreign-keys Check for self-referencing foreign keys (
default yes)
--check-interval=m Sleep time between checks for --max-lag (
default 1). Optional suffix s=seconds, m=
minutes, h=hours, d=days; if no suffix, s
is used.
--[no]check-plan Check query execution plans for safety (
default yes)
--[no]check-replication-filters Abort if any replication filter is set on
any server (default yes)
--check-slave-lag=s Pause the data copy until this replica's
lag is less than --max-lag
--[no]check-unique-key-change Avoid pt-online-schema-change to run if
the specified statement for --alter is
trying to add an unique index (default yes)
--chunk-index=s Prefer this index for chunking tables
--chunk-index-columns=i Use only this many left-most columns of
a --chunk-index
--chunk-size=z Number of rows to select for each chunk
copied (default 1000)
--chunk-size-limit=f Do not copy chunks this much larger than
the desired chunk size (default 4.0)
--chunk-time=f Adjust the chunk size dynamically so each
data-copy query takes this long to
execute (default 0.5)
--config=A Read this comma-separated list of config
files; if specified, this must be the
first option on the command line
--critical-load=A Examine SHOW GLOBAL STATUS after every
chunk, and abort if the load is too high (
default Threads_running=50)
--data-dir=s Create the new table on a different
partition using the DATA DIRECTORY feature
--database=s -D Connect to this database
--default-engine Remove ENGINE from the new table
--defaults-file=s -F Only read mysql options from the given file
--[no]drop-new-table Drop the new table if copying the original
table fails (default yes)
--[no]drop-old-table Drop the original table after renaming it (
default yes)
--[no]drop-triggers Drop triggers on the old table. --no-drop-
triggers forces --no-drop-old-table (
default yes)
--dry-run Create and alter the new table, but do not
create triggers, copy data, or replace the
original table
--execute Indicate that you have read the
documentation and want to alter the table
--[no]fail-on-stopped-replication If replication is stopped, fail with an
error (exit status 128) instead of waiting
until replication is restarted (default
yes)
--force This option bypasses confirmation in case
of using alter-foreign-keys-method = none,
which might break foreign key constraints
--help Show help and exit
--history Write job progress to a table
--history-table=s Create the --history database and table if
they do not exist (default percona.
pt_osc_history)
--host=s -h Connect to host
--max-flow-ctl=f Somewhat similar to --max-lag but for PXC
clusters
--max-lag=m Pause the data copy until all replicas'
lag is less than this value (default 1s).
Optional suffix s=seconds, m=minutes, h=
hours, d=days; if no suffix, s is used.
--max-load=A Examine SHOW GLOBAL STATUS after every
chunk, and pause if any status variables
are higher than their thresholds (default
Threads_running=25)
--new-table-name=s New table name before it is swapped. %T is
replaced with the original table name (
default %T_new)
--null-to-not-null Allows MODIFYing a column that allows NULL
values to one that doesn't allow them
--only-same-schema-fks Check foreigns keys only on tables on the
same schema than the original table
--password=s -p Password to use when connecting
--pause-file=s Execution will be paused while the file
specified by this param exists
--pid=s Create the given PID file
--plugin=s Perl module file that defines a
pt_online_schema_change_plugin class
--port=i -P Port number to use for connection
--preserve-triggers Preserves old triggers when specified
--print Print SQL statements to STDOUT
--progress=a Print progress reports to STDERR while
copying rows (default time,30)
--quiet -q Do not print messages to STDOUT (
disables --progress)
--recurse=i Number of levels to recurse in the
hierarchy when discovering replicas
--recursion-method=a Preferred recursion method for discovering
replicas (default processlist,hosts)
--remove-data-dir If the original table was created using
the DATA DIRECTORY feature, remove it and
create the new table in MySQL default
directory without creating a new isl file (
default no)
--resume=i Resume altering table from the last
completed chunk
--reverse-triggers Copy the triggers added during the copy in
reverse order
--set-vars=A Set the MySQL variables in this comma-
separated list of variable=value pairs
--skip-check-slave-lag=d DSN to skip when checking slave lag
--slave-password=s Sets the password to be used to connect to
the slaves
--slave-user=s Sets the user to be used to connect to the
slaves
--sleep=f How long to sleep (in seconds) after
copying each chunk (default 0)
--socket=s -S Socket file to use for connection
--statistics Print statistics about internal counters
--[no]swap-tables Swap the original table and the new,
altered table (default yes)
--tries=a How many times to try critical operations
--user=s -u User for login if not current user
--version Show version and exit
--[no]version-check Check for the latest version of Percona
Toolkit, MySQL, and other programs (
default yes)
--where=s Copy only rows matching this WHERE clause
Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time
Rules:
--dry-run and --execute are mutually exclusive.
This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.
DSN syntax is key=value[,key=value...] Allowable DSN keys:
KEY COPY MEANING
=== ==== =============================================
A yes Default character set
D no Database for the old and new table
F yes Only read default options from the given file
P yes Port number to use for connection
S yes Socket file to use for connection
h yes Connect to host
p yes Password to use when connecting
t no Table to alter
u yes User for login if not current user
If the DSN is a bareword, the word is treated as the 'h' key.
Options and values after processing arguments:
--alter (No value)
--alter-foreign-keys-method (No value)
--analyze-before-swap TRUE
--ask-pass FALSE
--binary-index FALSE
--channel (No value)
--charset (No value)
--check-alter TRUE
--check-foreign-keys TRUE
--check-interval 1
--check-plan TRUE
--check-replication-filters TRUE
--check-slave-lag (No value)
--check-unique-key-change TRUE
--chunk-index (No value)
--chunk-index-columns (No value)
--chunk-size 1000
--chunk-size-limit 4.0
--chunk-time 0.5
--config /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-online-schema-change.conf,/root/.percona-toolkit.conf,/root/.pt-online-schema-change.conf
--critical-load Threads_running=50
--data-dir (No value)
--database (No value)
--default-engine FALSE
--defaults-file (No value)
--drop-new-table TRUE
--drop-old-table TRUE
--drop-triggers TRUE
--dry-run FALSE
--execute FALSE
--fail-on-stopped-replication TRUE
--force FALSE
--help TRUE
--history FALSE
--history-table percona.pt_osc_history
--host (No value)
--max-flow-ctl (No value)
--max-lag 1
--max-load Threads_running=25
--new-table-name %T_new
--null-to-not-null FALSE
--only-same-schema-fks FALSE
--password (No value)
--pause-file (No value)
--pid (No value)
--plugin (No value)
--port (No value)
--preserve-triggers FALSE
--print FALSE
--progress time,30
--quiet FALSE
--recurse (No value)
--recursion-method processlist,hosts
--remove-data-dir TRUE
--resume (No value)
--reverse-triggers FALSE
--set-vars
--skip-check-slave-lag (No value)
--slave-password (No value)
--slave-user (No value)
--sleep 0
--socket (No value)
--statistics FALSE
--swap-tables TRUE
--tries (No value)
--user (No value)
--version FALSE
--version-check TRUE
--where (No value)
[root@node2 bin]#
官方文档
https://docs.percona.com/percona-toolkit/pt-online-schema-change.html
总结
如果生产环境中有从库的话，为了降低对从库的影响，也可以考虑加参数–recursion-method，–max-lag等
如果需要优化pt-osc工具执行时的性能，请参考pt-online-schema-change --help用法以及官方文档调整相关参数。
把学到的东西分享出来是一种快乐，祝大家都能学有所获！
如果您觉得文章对您有帮助，请点赞支持，谢谢！
最后修改时间：2024-09-26 10:07:29
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
