使用pt-online-schema-change对大表进行在线ddl

一. 问题背景

当表数据量足够大时,对表进行ddl是一件很头痛的事情,因为大多ddl会涉及锁表,锁表时间足够久可能会影响线上业务
mysql支online ddl提供了一些支持,对部分ddl,我们可以直接在线上执行而不影响线上用户使用,具体哪些ddl可在线上使用可参见Mysql Online DDL 支持情况

但仍存在一些些ddl会涉及锁表,同时因为数据量过大出现各种异常情况导致失败.

二. pt-online-schema-change

1. 为了上述问题,pt-online-schema-change应运而生,那么pt-online-schema-change是怎样解决这一问题的呢? pt-online-schema-change解决这一问题并没有用到什么黑科技,只是对一些简单的操作进行了组合,大致过程如下:

   a. 创建一张新表
   b. 对新表执行ddl语句
   c. 小批量的将旧表的数据迁移到新表,重复多次,直到同步完成.

   d. 创建新表的同步,创建了增删改的触发器,将对旧表的数据变更同步到新表.
   e. 完成同步后,将旧表删除,新表rename成旧表,删除旧表.
   f. 完成升级

2. 升级准备

   a. 提前作好数据库备份.

   b. 准备一个具有 增删改查+触发器操作权限的用户.

   c. 保证数据库有足够的空间,需要的空间可按下面sql估算,另外还要考虑到升级过程中数据的增长.

   select 1.5*(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 from information_schema.TABLES where TABLE_NAME = 't1';

   d. 升级过程中,会产生大量的binlog, 可视情况临时关闭binlog,或将binlog单独挂在其他磁盘上.

3. 安装pt-online-schema-change

   a. 依赖项

   yum -y install perl perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5 perl-TermReadKey

   b. 下载pt-online-schema-change

   wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/redhat/7/x86_64/percona-toolkit-3.3.1-1.el7.x86_64.rpm

   c.安装percona-toolkit

   rpm -ivh percona-toolkit-3.3.1-1.el7.x86_64.rpm

4. 执行升级语句

   1. 试运行(–dry-run),执行除迁移数据/表重命名外的所有工作

      注意:
      a. ddl语句中alter table需要省略
      b. 自行修改相应的alter/D/t/user/password/host/port
      参数说明
      alter 要修改的操作
      D 数据库名
      t 表名
      user 用户名
      passowrd 密码
      host 地址
      port 端口

      pt-online-schema-change --alter "modify finally_score decimal(10,2) not null" D=d1,t=t1 --user=admin --password=123456 --host=127.0.0.1 -port=3306 --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --dry-run

   2. 正式执行(先在测试环境执行,确认无误后再在生产执行)

      注意:

      1. ddl语句中alter table需要省略
      2. 自行修改相应的alter/D/t/user/password/host/port

      nohup pt-online-schema-change --alter "modify finally_score decimal(10,2) not null" D=d1,t=t1 --user=admin --password=123456 --host=127.0.0.1 -port=3306 --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute > pt.log &

   3. 查看进度:

      通过日志查看
      tail -f pt.log

      通过新表的数据查看(新表的名称格式为(_旧表名称_new),可通过show tables like '%旧表%'查看)
      select * from information_schema.tables where table_name = '_tableName_new'