pt-online-schema-change 测试使用-包含生成测试数据
# 参考网址： https://www.2cto.com/database/201703/618280.html
一、简要描述
1、mysql创建测试表
drop table IF EXISTS t_user;
CREATE TABLE `t_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2、生成测试数据
delimiter //
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE pre_name BIGINT;
DECLARE ageVal INT;
DECLARE i INT;
SET pre_name=187635267;
SET ageVal=100;
SET i=1;
WHILE i <= 10000000 DO
INSERT INTO t_user(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+1)%30,NOW(),NOW());
SET pre_name=pre_name+100;
SET i=i+1;
END WHILE;
END // delimiter ;
call proc_batch_insert();
1、打印-增加列
pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="add info varchar(500) comment '个人简历'" --print
1、执行-增加列
pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="add info varchar(500) comment '个人简历'" --execute
# 2、打印-删除列
pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="drop info" --print
2、执行-删除列
pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="drop info" --execute
3、打印-增加索引
pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="drop index idx_name" --print
3、执行-增加索引
pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="add index idx_name(name(12))" --execute
4、打印-删除索引
pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="drop index idx_name" --print
4、执行-删除索引
pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="drop index idx_name" --execute
5、打印-修改列
pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="modify name varchar(20) not null default '' comment '用户名'" --print
5、执行-修改列
pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="modify name varchar(20) not null default '' comment '用户名'" --execute
二、详细操作记录
1、打印-增加列
[root@my3-224 ~]# pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="add info varchar(500) comment '个人简历'" --print
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
Exiting without altering `db222`.`t_user` because neither --dry-run nor --execute was specified. Please read the tool's documentation carefully before using this tool.
[root@my3-224 ~]#
1、执行-增加列
[root@my3-224 opt]# pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="add info varchar(500) comment '个人简历'" --execute
No slaves found. See --recursion-method if host my1-222 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 `db222`.`t_user`...
Creating new table...
Created new table db222._t_user_new OK.
Altering new table...
Altered `db222`.`_t_user_new` OK.
2018-05-18T19:32:53 Creating triggers...
2018-05-18T19:32:54 Created triggers OK.
2018-05-18T19:32:54 Copying approximately 898443 rows...
2018-05-18T19:33:13 Copied rows OK.
2018-05-18T19:33:13 Analyzing new table...
2018-05-18T19:33:13 Swapping tables...
2018-05-18T19:33:14 Swapped original and new tables OK.
2018-05-18T19:33:14 Dropping old table...
2018-05-18T19:33:14 Dropped old table `db222`.`_t_user_old` OK.
2018-05-18T19:33:14 Dropping triggers...
2018-05-18T19:33:14 Dropped triggers OK.
Successfully altered `db222`.`t_user`.
[root@my3-224 opt]#
2、打印-删除列
[root@my3-224 ~]# pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="drop info" --print
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
Exiting without altering `db222`.`t_user` because neither --dry-run nor --execute was specified. Please read the tool's documentation carefully before using this tool.
[root@my3-224 ~]#
2、执行-删除列
[root@my3-224 ~]# pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="drop info" --execute
No slaves found. See --recursion-method if host my1-222 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 `db222`.`t_user`...
Creating new table...
Created new table db222._t_user_new OK.
Altering new table...
Altered `db222`.`_t_user_new` OK.
2018-05-18T19:44:19 Creating triggers...
2018-05-18T19:44:20 Created triggers OK.
2018-05-18T19:44:20 Copying approximately 996723 rows...
2018-05-18T19:44:38 Copied rows OK.
2018-05-18T19:44:38 Analyzing new table...
2018-05-18T19:44:39 Swapping tables...
2018-05-18T19:44:39 Swapped original and new tables OK.
2018-05-18T19:44:39 Dropping old table...
2018-05-18T19:44:39 Dropped old table `db222`.`_t_user_old` OK.
2018-05-18T19:44:39 Dropping triggers...
2018-05-18T19:44:39 Dropped triggers OK.
Successfully altered `db222`.`t_user`.
[root@my3-224 ~]#
3、打印-增加索引
[root@my3-224 ~]# pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="add index idx_name(name(12))" --print
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
Exiting without altering `db222`.`t_user` because neither --dry-run nor --execute was specified. Please read the tool's documentation carefully before using this tool.
[root@my3-224 ~]#
3、执行-增加索引
[root@my3-224 ~]# pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="add index idx_name(name(12))" --execute
No slaves found. See --recursion-method if host my1-222 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 `db222`.`t_user`...
Creating new table...
Created new table db222._t_user_new OK.
Altering new table...
Altered `db222`.`_t_user_new` OK.
2018-05-18T19:49:24 Creating triggers...
2018-05-18T19:49:24 Created triggers OK.
2018-05-18T19:49:24 Copying approximately 996723 rows...
Copying `db222`.`t_user`: 52% 00:27 remain
2018-05-18T19:50:20 Copied rows OK.
2018-05-18T19:50:20 Analyzing new table...
2018-05-18T19:50:20 Swapping tables...
2018-05-18T19:50:20 Swapped original and new tables OK.
2018-05-18T19:50:20 Dropping old table...
2018-05-18T19:50:20 Dropped old table `db222`.`_t_user_old` OK.
2018-05-18T19:50:20 Dropping triggers...
2018-05-18T19:50:20 Dropped triggers OK.
Successfully altered `db222`.`t_user`.
[root@my3-224 ~]#
4、打印-删除索引
[root@my3-224 ~]# pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="drop index idx_name" --print
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
Exiting without altering `db222`.`t_user` because neither --dry-run nor --execute was specified. Please read the tool's documentation carefully before using this tool.
[root@my3-224 ~]#
4、执行-删除索引
[root@my3-224 ~]# pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="drop index idx_name" --execute
No slaves found. See --recursion-method if host my1-222 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 `db222`.`t_user`...
Creating new table...
Created new table db222._t_user_new OK.
Altering new table...
Altered `db222`.`_t_user_new` OK.
2018-05-18T21:42:45 Creating triggers...
2018-05-18T21:42:45 Created triggers OK.
2018-05-18T21:42:45 Copying approximately 996723 rows...
Copying `db222`.`t_user`: 61% 00:19 remain
2018-05-18T21:43:35 Copied rows OK.
2018-05-18T21:43:35 Analyzing new table...
2018-05-18T21:43:35 Swapping tables...
2018-05-18T21:43:35 Swapped original and new tables OK.
2018-05-18T21:43:35 Dropping old table...
2018-05-18T21:43:35 Dropped old table `db222`.`_t_user_old` OK.
2018-05-18T21:43:35 Dropping triggers...
2018-05-18T21:43:35 Dropped triggers OK.
Successfully altered `db222`.`t_user`.
[root@my3-224 ~]#
5、打印-修改列
[root@my3-224 ~]# pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="modify name varchar(20) not null default '' comment '用户名'" --print
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
Exiting without altering `db222`.`t_user` because neither --dry-run nor --execute was specified. Please read the tool's documentation carefully before using this tool.
[root@my3-224 ~]#
5、执行-修改列
[root@my3-224 ~]# pt-online-schema-change --host=192.168.65.128 --user=davie --password=davie123 --port=3306 D=db222,t=t_user --charset=utf8mb4 --nocheck-replication-filters --chunk-size=10000 --sleep=0 --alter="modify name varchar(20) not null default '' comment '用户名'" --execute
No slaves found. See --recursion-method if host my1-222 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 `db222`.`t_user`...
Creating new table...
Created new table db222._t_user_new OK.
Altering new table...
Altered `db222`.`_t_user_new` OK.
2018-05-18T20:07:14 Creating triggers...
2018-05-18T20:07:14 Created triggers OK.
2018-05-18T20:07:14 Copying approximately 996723 rows...
2018-05-18T20:07:38 Copied rows OK.
2018-05-18T20:07:38 Analyzing new table...
2018-05-18T20:07:38 Swapping tables...
2018-05-18T20:07:38 Swapped original and new tables OK.
2018-05-18T20:07:38 Dropping old table...
2018-05-18T20:07:39 Dropped old table `db222`.`_t_user_old` OK.
2018-05-18T20:07:39 Dropping triggers...
2018-05-18T20:07:39 Dropped triggers OK.
Successfully altered `db222`.`t_user`.
[root@my3-224 ~]#