pt-online-schema-change使用

下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST/

下载对应系统的rpm包

安装

  1. # 安装依赖
  2. yum -y install perl-DBI perl-DBD-MySQL perl-Digest-MD5 perl-IO-Socket-SSL perl-TermReadKey
  3. # 安装percona-toolkit
  4. rpm -ivh percona-toolkit-3.3.1-1.el7.x86_64.rpm

使用案例

  1. pt-online-schema-change --user=root --password=root123 --host=127.0.0.1 P=3306,D=traffic_uat,t=users --no-version-check --execute --alter "ADD COLUMN  pid int(10) DEFAULT 0"

常用选项说明

  • --host=xxx --user=xxx --password=xxx
    连接实例信息，缩写-h xxx -u xxx -p xxx，密码可以使用参数--ask-pass 手动输入。

  • --alter


结构变更语句，不需要

ALTER TABLE


关键字。与原始ddl一样可以指定多个更改，用逗号分隔。

  • 绝大部分情况下表上需要有主键或唯一索引，因为工具在运行当中为了保证新表也是最新的，需要旧表上创建 DELETE和UPDATE 触发器，同步到新表的时候有主键会更快。个别情况是，当alter操作就是在c1列上建立主键时，DELETE触发器将基于c1列。
  • 子句不支持 rename 去给表重命名。
  • alter命令原表就不支持给索引重命名，需要先drop再add，在pt-osc也一样。(mysql 5.7 支持 RENAME INDEX old_index_name TO new_index_name)
    但给字段重命名，千万不要drop-add，整列数据会丢失，使用change col1 col1_new type constraint（保持类型和约束一致，否则相当于修改 column type，不能online）
  • 子句如果是add column并且定义了not null，那么必须指定default值，否则会失败。
  • 如果要删除外键（名 fk_foo），使用工具的时候外键名要加下划线，比如--alter "DROP FOREIGN KEY _fk_foo"
    • D=db_name,t=table_name
      指定要ddl的数据库名和表名

  • --max-load
    默认为Threads_running=25。每个chunk拷贝完后，会检查 SHOW GLOBAL STATUS 的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式： status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会这只其为当前值的120%。
    因为拷贝行有可能会给部分行上锁，Threads_running 是判断当前数据库负载的绝佳指标。

  • --max-lag
    默认1s。每个chunk拷贝完成后，会查看所有复制Slave的延迟情况（Seconds_Behind_Master）。要是延迟大于该值，则暂停复制数据，直到所有从的滞后小于这个值。--check-interval配合使用，指定出现从库滞后超过 max-lag，则该工具将睡眠多长时间，默认1s，再检查。如--max-lag=5 --check-interval=2。
    熟悉percona-toolkit的人都知道--recursion-method可以用来指定从库dsn记录。另外，如果从库被停止，将会永远等待，直到从开始同步，并且延迟小于该值。

  • --chunk-time
    默认0.5s，即拷贝数据行的时候，为了尽量保证0.5s内拷完一个chunk，动态调整chunk-size的大小，以适应服务器性能的变化。
    也可以通过另外一个选项--chunk-size禁止动态调整，即每次固定拷贝 1k 行，如果指定则默认1000行，且比 chunk-time 优先生效

  • --set-vars
    使用pt-osc进行ddl要开一个session去操作，set-vars可以在执行alter之前设定这些变量，比如默认会设置--set-vars "wait_timeout=10000,innodb_lock_wait_timeout=1,lock_wait_timeout=60"。
    因为使用pt-osc之后ddl的速度会变慢，所以预计2.5h只能还不能改完，记得加大wait_timeout。

  • -–charset
    --charset=utf8 使用utf8编码，避免中文乱码

  • --dry-run
    创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节，和--print配合最佳。。

  • --execute
    确定修改表，则指定该参数。真正执行alter。–dry-run与–execute必须指定一个，二者相互排斥

  • --no-version-check

    不检查版本，在阿里云服务器中一般加入此参数，否则会报错

工作工程

  1. 创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)
  2. 在新表执行alter table 语句（速度应该很快）
  3. 在原表中创建触发器3个触发器分别对应insert,update,delete操作
  4. 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表
  5. Rename 原表到old表中，在把临时表Rename为原表
  6. 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理
  7. 默认最后将旧原表删除