pt-online-schema-change使用说明(未完待续)

官方文档:

• NAME
• SYNOPSIS
• Usage
• Example
• RISKS
• DESCRIPTION
• Percona XtraDB Cluster
• OUTPUT
• OPTIONS

NAME

pt-online-schema-change
不锁表的情况下,修改表结构.该工具执行的基本流程如下:

1. 判断各种参数
2. 根据原表"t",创建一个名称为"_t_new"的新表
3. 执行ALTER TABLE语句修改新表"_t_new"
4. 创建3个触发器,名称格式为pt_osc_库名_表名_操作类型,比如

   CREATE TRIGGER `pt_osc_dba_t_del` AFTER DELETE ON `dba`.`t` FOR EACH ROW DELETE IGNORE FROM `dba`.`_t_new` WHERE `dba`.`_t_new`.`id` <=> OLD.`id`
   CREATE TRIGGER `pt_osc_dba_t_upd` AFTER UPDATE ON `dba`.`t` FOR EACH ROW REPLACE INTO `dba`.`_t_new` (`id`, `a`, `b`, `c1`) VALUES (NEW.`id`, NEW.`a`, NEW.`b`, NEW.`c1`)
   CREATE TRIGGER `pt_osc_dba_t_ins` AFTER INSERT ON `dba`.`t` FOR EACH ROW REPLACE INTO `dba`.`_t_new` (`id`, `a`, `b`, `c1`) VALUES (NEW.`id`, NEW.`a`, NEW.`b`, NEW.`c1`)
   

5. 开始复制数据,比如

   INSERT LOW_PRIORITY IGNORE INTO `dba`.`_t_new` (`id`, `a`, `b`, `c1`) SELECT `id`, `a`, `b`, `c1` FROM `dba`.`t` LOCK IN SHARE MODE /*pt-online-schema-change 28014 copy table*/
   

6. 复制完成后,交互原表和新表,执行RENAME命令,如 RENAME TABLE t to _t_old, _t_new to t;
7. 删除老表,_t_old
8. 删除触发器
9. 修改完成

SYNOPSIS

Usage

pt-online-schema-change [OPTIONS] DSN

1. 在不阻塞读写的情况下,修改表结构.在DSN中指定库和名.
2. 在认真读文档之前,不要使用这个工具,并且操作前要做好备份.

Example

1. 给sakila.actor添加一列:

   pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor
   
   相当于执行如下SQL:
   use sakila;
   alter table actor add column c1 INT;
   

2. 把sakila.actor改为InnoDB引擎.如果它已经是一个InnoDB引擎的表,相当于在不阻塞读写的情况下,执行了OPTIMIZE TABLE操作.

   pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor
   
   相当于执行如下SQL:
   use sakila;
   alter table actor engine=innodb;

RISKS

Percona工具集在测试和实际使用中,都证明它是成熟的,但是,所有的操作数据的工具对可能对系统或者数据库造成风险.在使用这个工具之前,请:

• 详细阅读工具的文档说明
• 查看bugs列表
• 在非线上环境测试这个工具
• 备份线上数据,并且要验证这个备份

DESCRIPTION

pt-online-schema-change 模仿MySQL修改内部表的方法,不过它会复制你想改变的表.也就是说,原始的表不会被锁,客户端可以持续的读写数据.

pt-online-schema-change 根据原表创建一个新的空表,并按需求修改.然后从原表向新表复制数据.当复制完成时,挪走原始表,并用新表代替.默认情况下,会把原表删除.

数据复制过程是一小块一小块的复制,也可以通过--chunk-time参数调整块的大小.这个参数的工作原理与pt-table-check中的类似.

在复制数据过程中,任何数据的改动都会在新表中体现出来,因为这个工具会在原表中创建触发器,在原表更新的数据,在新表也进行了更新.如果在原表已经定义过触发器,那么这个工具就不能工作了.

当工具把数据全部复制到新表后,它会自动执行RENAME TABLE操作,同时修改原表和新表.这个操作完成后,会自动删除原表.

外键会让工具操作起来更负载并且会增加额外的风险.有外键约束的情况下,自动重命名原表会让外键失效.所以,在表结构修改完成后,该工具必须重新更新外键.这个工具有两种方法可以完成这个操作.你可以看文档的--alter-foreign-keys-method参数.
外键也会导致一些副作用,修改完成的表会出现相同的外键索引(除非在ALTER语句中指定了不同的外键),但是这些外键的名字不会一样,避免冲突.

为了安全,这个工具只有在指定--execute参数时才会修改表.默认情况下,是不加--execute参数的.该工具支持多种措施,防止产生高负载或其他问题,比如,自动检查从库,连接从库,可能会使用下列方法进行安全检查:

• 如果检测到启用了”复制过滤器”,该工具不会执行操作.见--[no]check-replication-filters参数
• 如果检测到复制出现延迟,该工具会暂停复制数据.见--max-lag 参数
• 如果检测到服务器负载过高,该工具会暂停或者停止.见--max-load和--critical-load 参数
• 该工具设置 innodb_lock_wait_timeout=1 and (for MySQL 5.5 and newer) lock_wait_timeout=60,因此它尽可能的减小锁争用,避免影响其他事务.这两个MySQL变量值可以通过 --set-ars参数设置.(innodb_lock_wait_timeout,在准备执行一次事务时,如果innodb等待了50秒(默认值)后还没有获得所申请的数据锁,innodb就将回滚这次事务. lock_wait_timeout尝试获取元数据锁的等待时间(默认值是一年),原数据锁是指访问表,触发器等对象而产生的锁.)
• 如果修改的表存在外键约束,并且没有指定--alter-foreign-keys-method参数,那么该工具不会执行操作.
• 该工具不能修改 Percona XtraDB Cluster中各节点的MyISAM表

Percona XtraDB Cluster

pt-online-schema-change 可以在Percona XtraDB Cluster (PXC) 5.5.28-23.7 或者更高的版本使用.但是它有两个限制:必须是InnoDB引擎的表,并且wsrep_OSU_method 要设置为TOI,否则会报错退出.

OUTPUT

该工具是动态打印信息到STDOUT(标准输出)的,因此可以看到它正在做的事情.在数据复制阶段,它把进度信息打印到STDERR(标准错误输出)中.可以指定--print参数,得到更多的信息.
如果--statistics被指定,在最后完成时,会生成一个如下的报告:

# Event  Count
# ====== =====
# INSERT     1

OPTIONS

参数 --dry-run 和 --execute 是互斥的.
该工具接收额外的命名行参数.更多信息请参考”SYNOPSIS"和usage部分.

• --alter
  type: string
  结构修改,不带ALTER TABLE关键字.你可以执行多个表的修改操作,在它们之间用逗号分割.关于ALTER TABLE语法,请参考MySQL手册.
  该参数有以下局限性,如果被触发,会导致这个工具执行失败:
  • 不能指定rename语句.
  • 不能用删除列添加新列的方法重命名.该工具将不能复制原表中此列的数据到新列.
  • 如果你添加一个没有默认值新列并且属性设置为NOT NULL,该工具将不能执行,它不会给你指定一个默认值.
  • 删除外键约束,需要指定一个外键的名字,但是,这个名字并不是外键的名字,而是一个区别于外键的名字.这是MySQL的限制.在创建新表的时候, pt-online-schema-change会创建一个以下划线开头的外键.比如

    CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)
    

    然后,必须这样指定

    --alter "DROP FOREIGN KEY _fk_foo" 

  • 在MySQL 5.0版本操作可能会有问题.

• --alter-foreign-keys-method
  如何把外键引用到新表?需要特殊处理带有外键约束的表,以保证它们可以应用到新表.当重命名表的时候,外键关系会带到重命名后的表上.
  该工具有两种方法,可以自动找到子表,并修改约束关系.
  • auto, 在rebuild_constraints和drop_swap两种处理方式中选择一个.
  • rebuild_constraints, 使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞.
  • drop_swap, 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表.
    这种方式很快,也不会产生阻塞,但是有风险:
    1, 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误.
    2, 如果重命名表出现错误,也不能回滚了.因为原表已经被删除.
  • none, 类似"drop_swap"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面.使用SHOW ENGINE INNODB STATUS;命令会发现如下错误信息

    Trying to add to index `idx_fk_staff_id` tuple:
    DATA TUPLE: 2 fields;
    0: len 1; hex 05; asc  ;;
    1: len 4; hex 80000001; asc     ;;
    But the parent table `sakila`.`staff_old`
    or its .ibd file does not currently exist!
    

• --ask-pass
  连接MySQL时,提示输入密码

• --charset
  设置字符集,相当于用客户端执行"SET NAMES UTF8"命令

• --[no]check-alter
  解析并检查alter指定的命令:
  • 在以前的版本,使用CHANGE COLUMN命令会导致数据丢失,现在的版本虽然改进,但是在执行前,还应该使用 --dry-run 和 --print 查看一下详细的操作情况.
  • DROP PRIMARY KEY, 执行该命令的话,会发出警告.

• --check-interval
  检查间隔,默认是1秒.请看--max-lag参数.

• --[no]check-plan
  为了安全,检查查询的执行计划.默认情况下,这个工具在执行查询之前会先EXPLAIN,以获取一次少量的数据,如果是不好的EXPLAIN,那么会获取一次大量的数据.
  这个工具会多次执行EXPALIN,如果EXPLAIN不同的结果,那么就会认为这个查询是不安全的.

• --[no]check-replication-filters
  检查MySQL的复制过滤器,如果存在就报错退出.
  如:binlog_ignore_db 和 replicate_do_db

• --check-slave-lag
  指定一个从库的DSN连接地址,如果从库超过--max-lag参数设置的值,就会暂停操作.

• --chunk-index
  为chunk指定一个索引(使用FORCE INDEX语法).
  默认情况下,工具会自动选择一个合适的索引.如果指定的索引不存在,该工具会自动选择一个合适的.

• --chunk-index-columns
  选择使用具有n列的索引,多用于复合索引.

• --chunk-size
  指定块的大小,默认是1000行,可以添加k,M,G后缀.这个块的大小要尽量与--chunk-time匹配.
  如果明确指定这个选项,那么每个块就会指定行数的大小.

• --chunk-size-limit
  当需要复制的块远大于设置的chunk-size大小,就不复制.默认值是4.0
  一个没有主键或唯一索引的表,块大小就是不确定的.

• --chunk-time
  在chunk-time执行的时间内,动态调整chunk-size的大小,以适应服务器性能的变化.
  该参数设置为0,或者指定chunk-size,都可以禁止动态调整.

• --config
  执行配置文件,必须在命令行的第一个参数位置.

• --critical-load