大表修改利器：pt-online-schame-change
纪先生笔记｜爱运动的码农
pt-online-schame-change
使用场景： 在线修改大数据量表结构(ALTER tables without locking them)
文档参考：https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html
好处:
降低主从延时的风险
可以限速、限资源，避免操作时MySQL负载过高建议:
在业务低峰期做，将影响降到最低直接原表修改缺点：
当表的数据量很大的时候，如果直接在线修改表结构，严重影响线上环境，而且耗时不可预估注意：
需要确认表必须包含主键或者唯一索引
工具会创建触发器，所以原表上不能有触发器
有外键的表需要注意使用参数--alter-foreign-keys-method(现在业务上不建议表中外键关联，建议在业务中控制)
原理:
- 首先它会新建一张一模一样的表，表名一般是_new后缀
- 然后在这个新表执行更改字段操作
- 然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行
- 最后将原表的数据拷贝到新表中，然后替换掉原表
0. 备份数据（先做）
1. 安装
下载安装包：
wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/source/tarball/percona-toolkit-3.3.1.tar.gz
解压
tar -xvf percona-toolkit-3.3.1.tar.gz
安装一些依赖包
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
yum -y install perl-Digest-MD52. 测试可用
在解压包的bin目录下执行,看是否正常，查看命令
./pt-online-schema-change --help3. 参数字段及含义
| 参数 | 含义 |
|---|---|
| --user= | 连接用户名 |
| --password= | 连接密码 |
| --host= | 连接IP |
| P= | 端口 |
| --alter= | 执行表变更的语句 |
| D= | database 库名 |
| t= | table 表名 |
| --charset=utf8 | 使用utf8编码，避免中文乱码 |
| --no-check-alter | 不检查alter语句 |
| 打印操作日志 | |
| --execute | 执行修改表结构，真正的执行alter，–dry-run与–execute必须指定一个，二者相互排斥 |
| –dry-run | 创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，与--print配合查看执行细节 |
4. 具体操作
- 添加一个字段
如果执行失败，检查alter语句，如果确认无误 可以避免检查--no-check-alter
./pt-online-schema-change --user=xxxx --password=xxxx --host=xxx.xxx.xxx.xxxx --alter "add column group_id bigint(20) not NULL default '0' comment 'test' " P=30306,D=h_pushcenter,t=h_message --charset=utf8 --no-version-check --print --execute- 修改字段
sql语句
ALTER TABLE `h_message` MODIFY COLUMN `group_id` int(20) NOT NULL DEFAULT '1';pt工具
--alter "MODIFY COLUMN group_id int(20) NOT NULL DEFAULT '1'"- 修改字段名
sql语句
ALTER TABLE `h_message` CHANGE column group_id group_id_0 bigint(20);pt工具
--alter "CHANGE group_id group_id_0 bigint(20)"- 添加索引
sql语句
ALTER TABLE `h_message` ADD INDEX h_message_n1(group_id);pt工具
--alter "ADD INDEX h_message_n1(group_id)"5.操作日志
- 创建new结尾的新表
Creating new table...
CREATE TABLE `h_pushcenter`.`_h_message_new` .....
Created new table h_pushcenter._h_message_new OK.- 新表执行alter操作
Altering new table...
ALTER TABLE `h_pushcenter`.`_h_message_new` add column group_id bigint(20) not NULL default '0' comment 'test'
Altered `h_pushcenter`.`_h_message_new` OK.- 原表上创建3个触发器
Creating triggers...
Event : DELETE
Event : UPDATE
Event : INSERT
Created triggers OK.- 复制数据到新表
Copying approximately 8187 rows...
Copied rows OK.- 重命名新旧两个表，然后替换，删除旧表
2021-05-19T10:33:08 Swapping tables...
RENAME TABLE `h_pushcenter`.`h_message` TO `h_pushcenter`.`_h_message_old`, `h_pushcenter`.`_h_message_new` TO `h_pushcenter`.`h_message`
2021-05-19T10:33:09 Swapped original and new tables OK.
2021-05-19T10:33:09 Dropping old table...
DROP TABLE IF EXISTS `h_pushcenter`.`_h_message_old`
2021-05-19T10:33:09 Dropped old table `h_pushcenter`.`_h_message_old` OK.- 删除触发器
2021-05-19T10:33:09 Dropping triggers...
DROP TRIGGER IF EXISTS `h_pushcenter`.`pt_osc_h_pushcenter_h_message_del`
DROP TRIGGER IF EXISTS `h_pushcenter`.`pt_osc_h_pushcenter_h_message_upd`
DROP TRIGGER IF EXISTS `h_pushcenter`.`pt_osc_h_pushcenter_h_message_ins`
2021-05-19T10:33:09 Dropped triggers OK.- 完成
“Java质变之路“ 学习技能知识（有书籍有视频， 看书太枯燥，可以先看视频，之后在看书会容易很多，吸收的更多）
编辑于 2021-07-06 19:43
