pt-online 修改表的结构-优快云博客

本文链接：https://blog.youkuaiyun.com/syousetu/article/details/109198254

pt-online-schema-change是一个在线修改表结构的工具，可以避免ALTER TABLE语句导致的服务阻塞。它通过创建新表、复制数据、添加触发器来实现平滑迁移。在业务低峰期使用，可以限制速度和资源，减少主从延迟风险。本文介绍了pt-online-schema-change的安装、使用方法及参数设置，包括添加字段、修改字段、修改字段名和添加索引等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景介绍

使用alter语句修改表的结构的时候，有的时候会卡住，可以使用pt-online ，这个是在线修改表结构的一个工具

原理介绍

首先它会新建一张一模一样的表，表明一般是_new后缀
然后在这个新表执行更改字段操作
然后再原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的sql语句也在新表中执行
最后将原表的数据拷贝到新表中，然后替换掉原表

使用

使用pi-online-schema-change执行sql的日志 SQL语句：

alter table `tmp_task_user` ADD support tinyint(1) unsigned NOT NULL DEFAULT '1';
sh pt.sh tmp_task_user "ADD COLUMN support tinyint(1) unsigned NOT NULL DEFAULT '1'"

日志输出：

tmp_task_user
ADD COLUMN support tinyint(1) unsigned NOT NULL DEFAULT '1'
No slaves found.  See --recursion-method if host h=127.0.0.1,P=3306 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test_db`.`tmp_task_user`...
Creating new table...
Created new table test_db._tmp_task_user_new OK.
Altering new table...
Altered `test_db`.`_tmp_task_user_new` OK.
2018-05-14T18:14:21 Creating triggers...
2018-05-14T18:14:21 Created triggers OK.
2018-05-14T18:14:21 Copying approximately 6 rows...
2018-05-14T18:14:21 Copied rows OK.
2018-05-14T18:14:21 Analyzing new table...
2018-05-14T18:14:21 Swapping tables...
2018-05-14T18:14:21 Swapped original and new tables OK.
2018-05-14T18:14:21 Dropping old table...
2018-05-14T18:14:21 Dropped old table `test_db`.`_tmp_task_user_old` OK.
2018-05-14T18:14:21 Dropping triggers...
2018-05-14T18:14:21 Dropped triggers OK.
Successfully altered `test_db`.`tmp_task_user`.

好处：

降低主从延时的风险
可以限速、限资源，避免操作时mysql负载过高
建议：
在业务低峰期做，将影响降到最低

pt-onlien安装

在官网下载对应的版本，官网下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST/
下载解压之后就可以看到pt-online-schema-change
该工具需要一些依赖包，直接执行不成功时一般会有提示，这里可以提前yum安装

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

pt-online-schema-change使用

1、参数

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

2、为避免每次都要输入一堆参数，可以写一个脚本复用一下，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

3、添加表字段

alert 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"

4、修改表字段

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'"

5、修改表字段名

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)"

6、添加索引 SQL语句

sql语句

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

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

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

参考链接：https://www.cnblogs.com/lkj371/p/11430265.html