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

在线修改大表的可能影响

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

pt-online-schema-change介绍

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

原理：

• 首先它会新建一张一模一样的表，表名一般是_new后缀

• 然后在这个新表执行更改字段操作

• 然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行

• 最后将原表的数据拷贝到新表中，然后替换掉原表

 

好处:

• 降低主从延时的风险
• 可以限速、限资源，避免操作时MySQL负载过高

建议:

• 在业务低峰期做，将影响降到最低

pt-online安装

1.去官网下载对应的版本，官网下载地址:https://www.percona.com/downl...

2.下载解压之后就可以看到pt-online-schema-change

wget https://www.percona.com/downloads/percona-toolkit/3.1.0/binary/redhat/7/x86_64/percona-toolkit-3.1.0-2.el7.x86_64.rpm

安装

yum install |grep percona-toolkit

yum install -y percona-toolkit-3.1.0-2.el7.x86_64.rpm

3.该工具需要一些依赖包，直接执行不成功时一般会有提示，这里可以提前yum安装

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

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=uft8 使用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 `tb_test` ADD COLUMN `column1` tinyint(4) DEFAULT NULL;

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

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

 

修改表字段 SQL语句：

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

pt-online-schema-change工具:

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

修改表字段名 SQL语句:

ALTER TABLE `tb_test` CHANGE COLUMN age adress varchar(30);

pt-online-schema-change工具:

sh pt.sh tb_test "CHANGE COLUMN age address varchar(30)"

添加索引 SQL语句:

ALTER TABLE `tb_test` ADD INDEX idx_address(address);

pt-online-schema-change工具:

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