pt-online-schema-change[实战系列]

官方文档

pt-online-schema-change是个可以不锁表的情况下更改表结构的工具,也可以添加索引,建议在表数据量极大的时候使用

安装

apt install percona-toolkit

准备一个千万行数据的大表

CREATE TABLE person(
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键',
    person_id tinyint not null comment '用户id',
    person_name VARCHAR(200) comment '用户名称',
    gmt_create datetime comment '创建时间',
    gmt_modified datetime comment '修改时间'
) comment '人员信息表';

添加一条数据

insert into person values(1, 1,'user_1', NOW(), now());

以下 sql 执行 20 次约等于 100 万条数据执行 24 次 1600 万条数据

insert into person(id, person_id, person_name, gmt_create, gmt_modified)
select @i:=@i+1,
left(rand()*10,10) as person_id,
concat('user_',@i%2048),
date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND),
date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)
from person;

pt-online-schema-change[快问快答]

pt-online-schema-change 添加一列c1

pt-online-schema-change --host=192.168.6.14 --user=root --password=123456 --alter "ADD COLUMN c1 INT" D=t1,t=person --print --execute

--host 主机
--user 账号
--password 密码
--alter 修改表结构语句
D 数据库
t 表
--print 打印工具执行过程
--execute 确认开始

因为表数据量过大,一时半会执行不完,这个时候可以去添加几条新数据,删除几条数据,执行成功,然后等待pt-online-schema-change执行完毕,刷新表发现成功添加字段,刚刚添加的数据也在,删除的数据也不见了

pt-online-schema-change[快问快答]

流程

检测有没从库,如果有从库,从库也会执行添加字段操作
创建一个新表 _person_new

执行

ALTER TABLE `t1`.`_person_new` ADD COLUMN c1 INT

创建三个触发器

#删除操作触发器
Event : DELETE 
Name  : pt_osc_t1_person_del 
SQL   : CREATE TRIGGER `pt_osc_t1_person_del` AFTER DELETE ON `t1`.`person` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `t1`.`_person_new` WHERE `t1`.`_person_new`.`id` <=> OLD.`id`; END  
Suffix: del 
Time  : AFTER

#更新操作触发器
Event : UPDATE 
Name  : pt_osc_t1_person_upd 
SQL   : CREATE TRIGGER `pt_osc_t1_person_upd` AFTER UPDATE ON `t1`.`person` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `t1`.`_person_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `t1`.`_person_new`.`id` <=> OLD.`id`; REPLACE INTO `t1`.`_person_new` (`id`, `person_id`, `person_name`, `gmt_create`, `gmt_modified`) VALUES (NEW.`id`, NEW.`person_id`, NEW.`person_name`, NEW.`gmt_create`, NEW.`gmt_modified`); END  
Suffix: upd 
Time  : AFTER

#添加操作触发器
Event : INSERT 
Name  : pt_osc_t1_person_ins 
SQL   : CREATE TRIGGER `pt_osc_t1_person_ins` AFTER INSERT ON `t1`.`person` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `t1`.`_person_new` (`id`, `person_id`, `person_name`, `gmt_create`, `gmt_modified`) VALUES (NEW.`id`, NEW.`person_id`, NEW.`person_name`, NEW.`gmt_create`, NEW.`gmt_modified`);END  
Suffix: ins 
Time  : AFTER

从person表拷贝数据到_person_new表

INSERT LOW_PRIORITY IGNORE INTO `t1`.`_person_new` (`id`, `person_id`, `person_name`, `gmt_create`, `gmt_modified`) SELECT `id`, `person_id`, `person_name`, `gmt_create`, `gmt_modified` FROM `t1`.`person` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE

改名,删除旧表

#原子操作 person改名 person_old,person_new改名person
RENAME TABLE `t1`.`person` TO `t1`.`_person_old`, `t1`.`_person_new` TO `t1`.`person`
#删除表person_old
DROP TABLE IF EXISTS `t1`.`_person_old`
#删除触发器
DROP TRIGGER IF EXISTS `t1`.`pt_osc_t1_person_del`
DROP TRIGGER IF EXISTS `t1`.`pt_osc_t1_person_upd`
DROP TRIGGER IF EXISTS `t1`.`pt_osc_t1_person_ins`