首页 > Linux入门大全, Linux配置管理工具, SQL数据库管理 > pt-online-schema-change使用说明

pt-online-schema-change使用说明

2014年10月21日

pt-online-schema-change使用说明

http://blog.chinaunix.net/uid-16844903-id-4010770.html

  • 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
本文的评论功能被关闭了.