场景

在维护数据库中，总会遇到被要求在线修改表结构的情况，修改一些业务小表时对影响较小，但修改大表，对业务的影响是巨大的。常见的问题就为线上的业务阻塞。

在线修改大表可能的影响

• 在线修改大表的表结构执行时间往往不可预估，一般时间较长
• 由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作
• 如果长时间的修改表结构，中途修改失败，由于修改表结构是一个事务，因此失败后会还原表结构，在这个过程中表都是锁着不可写入
• 修改大表结构容易导致数据库CPU、IO等性能消耗，使MySQL服务器性能降低
• 在线修改大表结构容易导致主从延时，从而影响业务读取

pt-online-schema-change介绍

pt-online-schema-change是percona公司开发的一个工具，在percona-toolkit包里面可以找到这个功能，它可以在线修改表结构

工作原理

• 如果存在外键，根据 alter-foreign-keys-method 参数的值，检测外键相关的表，做相应设置的处理。没有使用 alter-foreign-keys-method 指定特定的值，该工具不予执行
• 创建一个新的空表，其命名规则是：下划线 + 原表名 +_new—-_原表名_new
• 根据 alter 语句，更新新表的表结构；
• 创建触发器，用于记录从拷贝数据开始之后，对源数据表继续进行数据修改的操作记录下来，用于数据拷贝结束后，执行这些操作，保证数据不会丢失。如果表中已经定义了触发器这个工具就不能工作了。
• 拷贝数据，从源数据表中拷贝数据到新表中。
• 修改外键相关的子表，根据修改后的数据，修改外键关联的子表。
• rename 源数据表为 old 表，把新表 rename 为源表名，其通过一个 RENAME TABLE 同时处理两个表，实现原子操作。（RENAME TABLE dbteamdb.user TO dbteamdb._user_old, dbteamdb._user_new TO dbteamdb.user）
• 将 old 表删除、删除触发器。

优势

• 降低主节点和备份节点、只读节点间的延时风险
• 可以限速、限资源，避免操作时MySQL负载过高

下载安装

以源码包安装为例系统为Centos，官网下载地址： https://www.percona.com/downloads/percona-toolkit/LATEST/

1. 下载和安装

$wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/tarball/percona-toolkit-3.3.1_x86_64.tar.gz
$tar zxvf percona-toolkit-3.3.1_x86_64.tar.gz
$yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL -y
$perl Makefile.PL
$make -j 4
$make install

2. pt-online-schema-change命令参数，

   注：

   pt-online-schema-change --help 可以查看参数的使用，我们只是要修改个表结构，只需要知道几个简单的参数就可以了

   --user:
       -u，连接的用户名
   --password：
       -p，连接的密码
   --database：
       -D，连接的数据库
   --port
       -P，连接数据库的端口
   --host:
       -h，连接的主机地址
   --socket:
       -S，连接的套接字文件
   --ask-pass 隐式输入连接 MySQL 的密码
   --defaults-file
       -F，读取配置文件
   --alter：
       结构变更语句，不需要 alter table 关键字。可以指定多个更改，用逗号分隔。如下场景，需要注意：
           不能用 RENAME 来重命名表。
           列不能通过先删除，再添加的方式进行重命名，不会将数据拷贝到新列。
           如果加入的列非空而且没有默认值，则工具会失败。即其不会为你设置一个默认值，必须显示指定。
           删除外键 (drop foreign key constrain_name) 时，需要指定名称_constraint_name，而不是原始的 constraint_name。
           如：CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)，需要指定：--alter "DROP FOREIGN KEY _fk_foo"
   --execute      执行修改表结构
   --charset=utf8 使用utf8编码，避免中文乱码
   --no-version-check  不检查版本
   

3. 简易脚本 online-change.sh

   注： pt-online-schema-change的参数太多，为了简化使用，提供了一个脚本 online-change.sh，在使用前，根据实际情况修改脚本的参数，比如：host、user参数

   #!/bin/bash
   set -e 
   _table=$1	# 修改的表名
   _alter_sql=$2	# 修改的SQL语句
   
   # 被修改数据库实例的地址
   host='host'		
   # 被修改数据库实例登录的用户名
   user='user'
   # 被修改数据库实例登录的密码
   passwd='password'
   # 被修改数据库实例的库
   db='database name'
   
   /usr/bin/pt-online-schema-change --charset=utf8 --no-version-check --user=${user} --password=${passwd} --host=${host}  P=3306,D=${db},t=$_table --alter 
   "${_alter_sql}" --execute
   

   实践

   以测试表为例：

   CREATE TABLE `online_table` (
     `id` int(11) primary key,
     `name` varchar(10) DEFAULT NULL,
     `age` int(11) DEFAULT NULL
   ) engine = innodb default charset utf8;
   

   1. 添加表字段 添加表字段SQL语句为：

      ALTER TABLE online_table ADD COLUMN role_no tinyint(4) DEFAULT NULL;
      

      使用pt-online-schema-change则可以这样写

      sh online-change.sh online_table "ADD COLUMN role_no tinyint(4) DEFAULT NULL"
      

   2. 修改表字段

      修改表字段SQL语句为：

      ALTER TABLE online_table MODIFY COLUMN role_no int(11) unsigned NOT NULL DEFAULT '0';
      

      使用pt-online-schema-change则可以这样写

      sh online-change.sh online_table "MODIFY COLUMN role_no int(11) unsigned NOT NULL DEFAULT '0'"
      

   3. 修改表字段名

      修改表字段名SQL语句为

      ALTER TABLE online_table CHANGE COLUMN role_no role_seq varchar(30);
      

      使用pt-online-schema-change则可以这样写

      sh online-change.sh online_table "CHANGE COLUMN role_no role_seq varchar(30)"
      

   4. 添加索引

      添加索引SQL语句为：

      ALTER TABLE online_table ADD INDEX idx_age(age);
      

      使用pt-online-schema-change则可以这样写

      sh online-change.sh online_table "ADD INDEX idx_age(age)"
      

建议

• pt-online-schema-change工具还有很多其他的参数，可以有很多限制，比如限制CPU、线程数量、从库状态等等，不过我做过一个超过6000W表的结构修改，发现几乎不影响性能，很稳定很流畅的就修改了表结构，所以，对以上常规参数的使用基本能满足业务
• 一定要在业务低峰期做，这样才能确保万无一失