pt-online-schema-change 测试使用-包含生成测试数据

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 ~]#