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 添加一列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执行完毕,刷新表发现成功添加字段,刚刚添加的数据也在,删除的数据也不见了

流程

• 检测有没从库,如果有从库,从库也会执行添加字段操作

• 创建一个新表 _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`

• 结束

注意事项

• 如果表里面已经添加了触发器,该工具将不会启动,可以尝试用gh-ost工具,一款基于binlog的不锁表修改结构工具
• 如果表里面有外键,除非你指定–alter-foreign-keys-method参数,否则工具将不会启动
• 如果表里没有主键或者唯一键,该工具将不会启动
• 如果表里检测到复制过滤器,该工具将不会启动
• 如果该工具观察到任何复制延迟的副本，它会暂停数据复制操作
• 如果检测到服务器负载过大，该工具会暂停或中止其操作。

本作品采用《CC 协议》，转载必须注明作者和本文链接