mysql在线修改大表结构之pt-online-schema-change

前言

MySQL大字段的DDL操作：加减字段、索引、修改字段属性等，在5.1之前都是非常耗时耗力的，特别是会对MySQL服务产生影响。在5.1之后随着Plugin Innodb的出现在线加索引的提高了很多，但是还会影响（时间缩短了），主要是在更改期间，会生成一个互斥锁，阻塞对整个表的所有操作。不过MySQL 5.6可以避免上面的情况，支持在线DDL操作了。但目前大部分在用的版本都是5.6之前的，所以DDL操作一直是运维人员头疼的事。那如何在不锁表的情况下安全快速地更新表结构？现在来说明下percona-toolkit的pt-online-schema-change（简称:OSC）的使用说明，可以很好的解决上述的问题。

在我们的以前做法中，为了不影响线上业务，我们一般采用：先在线下从库更改表结构，然后替换线上从库，这样一台台的修改，最后做一下主库切换，这个过程会耗费很长时间，并且在做主库切换时，风险也非常的大，我们怎样才能让时间更短，且能不阻塞读写情况下在线修改呢？

在线修改大表的可能影响：

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

目前InnoDB引擎是通过以下步骤来进行DDL的：

1. 按照原始表（original_table）的表结构和DDL语句，新建一个不可见的临时表（tmp_table）
2. 在原表上加write lock，阻塞所有更新操作（insert、delete、update等）
3. 执行insert into tmp_table select * from original_table
4. rename original_table和tmp_table，最后drop original_table
5. 释放 write lock。

我们可以看见在InnoDB执行DDL的时候，原表是只能读不能写的。

为此 perconal 推出一个工具 pt-online-schema-change ，其特点是修改过程中不会造成读写阻塞。

工作原理：

如果表有外键，除非使用 –alter-foreign-keys-method 指定特定的值，否则工具不予执行。

1. 创建一个和你要执行 alter 操作的表一样的空表结构。
2. 执行表结构修改，然后从原表中的数据到copy到 表结构修改后的表，
3. 在原表上创建触发器将 copy 数据的过程中，在原表的更新操作 更新到新表.注意:如果表中已经定义了触发器这个工具就不能工作了。
4. copy 完成以后，用rename table 新表代替原表，默认删除原表。

安装pt-online-schema-change：

#安装依赖
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum -y install perl-Digest-MD5
yum install perl-IO-Socket-SSL
# 切换目录
cd /usr/local/src
# 下载
wget percona.com/get/percona-toolkit.tar.gz
# 解压
tar -zvxf percona-toolkit.tar.gz
cd percona-toolkit-3.0.13/
# 安装perl依赖
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
perl Makefile.PL
# 编译安装
make
make install
# 验证
pt-online-schema-change

使用pt-online-schema-change：

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

--user=        连接mysql的用户名
--password=    连接mysql的密码
--host=        连接mysql的地址
P=3306         连接mysql的端口号
D=             连接mysql的库名
t=             连接mysql的表名
--alter        修改表结构的语句
--execute      执行修改表结构
--charset=utf8 使用utf8编码，避免中文乱码
--no-version-check  不检查版本，在阿里云服务器中一般加入此参数，否则会报错

为避免每次都要输入一堆参数，写个脚本复用一下，pt.sh:

#!/bin/bash
table=$1
alter_conment=$2

cnn_host='127.0.0.1'
cnn_user='user'
cnn_pwd='password'
cnn_db='database_name'

echo "$table"
echo "$alter_conment"
/root/percona-toolkit-2.2.19/bin/pt-online-schema-change --charset=utf8 --no-version-check --user=${cnn_user} --password=${cnn_pwd} --host=${cnn_host}  P=3306,D=${cnn_db},t=$table --alter="${alter_conment}" --execute

如添加表字段SQL语句为:
ALTER TABLE test ADD COLUMN status tinyint(4) NOT NULL DEFAULT 0;
那么使用pt-online-schema-change则可以这样写
sh pt.sh test "ALTER TABLE test ADD COLUMN status tinyint(4) NOT NULL DEFAULT 0"

运行结果如下：

其他：

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

文档

1.官方参考:https://www.percona.com/doc/p...
2.官方下载:https://www.percona.com/downl...

参考：https://segmentfault.com/a/1190000014924677