概述

作为DBA，我们也常常会碰到这样的需求：需要在不影响线上业务的情况下给表添加一个字段或索引。如果是一张只有几百或几千条记录的小表，这样的需求是非常容易解决的。但如果所管理的表数据量已经上亿、而且应用与数据库交互非常频繁，不允许停机窗口的出现，这样的需求又该如何满足？

大多数的alter table操作都会涉及lock-->copy to new table-->rename-->unlock的过程，锁表时间会很长,而且alter table 的process不可被kill,一旦执行就不可回退。

在MySQL5.5和之前版本，在运行的生产环境对大表（超过数百万纪录）执行Alter操作是一件很困难的事情。因为将重建表和锁表，影响用户者的使用。

从MySQL5.6开始，Online DDL特性被引进。他增强了很多种类的Alter Table操作避免拷贝表和锁表，在运行Alter操作的同时允许运行select,insert,update,delete语句。因此在最新版本，我们可以通过使用ALGORITHM和LOCK选项抑制文件拷贝和加锁。

但是即使在MySQL5.6，仍然有一些Alter操作（增加/删除列，增加/删除主键,改变数据类型等）需要重建表。并不建议直接在线上使用alter table。如果线上有DDL的需求我们建议使用以下两种方式：

1、主从架构轮询修改

2、使用在线修改工具online-schema-change

对于第一种方式，使用的前提是，你的数据库架构是一个集群，如果不是，也就无所谓的轮询修改。修改的原理就是利用主从服务，在应用无感知的情况下，得到停机窗口，进行修改。今天我们着重讲第二种方式，利用第三方工具实现在线大表的DDL操作。这种方式在alter操作更改表结构的时候不用锁定表，也就是说执行alter的时候不会阻塞写和读取操作。

参考官网：https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

1、用法

pt-online-schema-change [OPTIONS] DSN

pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.

2、参数

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 不检查版本，在阿里云服务器中一般加入此参数，否则会报错

截图如下：

3、脚本复用

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

#!/bin/bash
#copyright by hwb 
table=$1
alter_conment=$2
cnn_host='172.16.xx'
cnn_user='root'
cnn_pwd='xxxx'
cnn_db='lcpdb'
echo "$table"
echo "$alter_conment"
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 --no-check-alter

下面用例子来说明下。

4、添加表字段

如添加表字段SQL语句为:

ALTER TABLE tb_test ADD COLUMN column1 tinyint(4) DEFAULT NULL;

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

sh pt_epms.sh tb_test "ADD COLUMN column1 tinyint(4) DEFAULT NULL"

执行如下：

查看表结构已经修改完成：

5、修改表字段

SQL语句：

ALTER TABLE tb_test MODIFY COLUMN id int(11) unsigned NOT NULL DEFAULT '0';

pt-online-schema-change工具:

sh pt_epms.sh tb_test "MODIFY COLUMN id int(11) unsigned NOT NULL DEFAULT '0'"

执行如下：

查看表结构：

6、修改表字段名

SQL语句:

ALTER TABLE tb_test CHANGE COLUMN name adress varchar(30);

pt-online-schema-change工具:

sh pt_epms.sh tb_test "CHANGE COLUMN name address varchar(30)"

7、添加索引

SQL语句:

ALTER TABLE tb_test ADD INDEX idx_address(address);

pt-online-schema-change工具:

sh pt_epms.sh tb_test "ADD INDEX idx_address(address)"

总结

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

注意一定要在业务低峰期做，这样才能确保万无一失。

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~