pt-osc（pt-online-schema-change的缩写）用于对mysql不锁表的ALTER TABLE操作

官方解释简洁易懂

1
pt-online-schema-change - ALTER tables without locking them.

由于最近踩了坑，我们需要精细的看下osc提供的参数

规范

参数

选项 描述 说明
操作必选
--host -h
--password -p 登录mysql的密码
--port -P mysql端口
--charset 连接后的字符集
--database 连接的数据库
--alter 多语句使用逗号分隔
--check-alter 检测alter语句语法
--print 标准输出
--dry-run 创建new表
不创建触发器
不拷贝数据
不替换新老表		 试运行
--execute 必须指定下面的选项，否则只做检查，然后退出
[官方友情提示：
在没有认真阅读文档的情况下，不要指定这些选项]		 实际执行alter操作
安全配置
--lock-wait-timeout
--chunk-time
–check-interval 修改间隔
--max-lag 每次拷贝数据后观察主从同步延迟是否大于给定值 会暂停
--max-load 会暂停
--progress 按指定间隔标准错误输出
[默认time:30 -- 30秒打印一次]		 支持3种方式
percentage:percentage
time:seconds
iterations:number
–recurse
--critical-load 每次chunk后检查mysql当前状态
Threads_connected=x,Threads_running=x		 会终止
--chunk-size 控制每次数据拷贝块所查询的行数大小
可以使用K,M,G单位
[默认1000]
受--chunk-time和--chunk-size-limit影响		 会终止
--chunk-size-limit --chunk-size的倍数
--chunk-time 以这个时间为单位获取本次数据拷贝的量，
动态调整下一次数据拷贝的量
[默认0.5秒]		 0的情况下，需要通过
--chunk-size
--chunk-size-limit
控制数据拷贝量
3个chunk相关的参数
--[no]drop-new-table [默认自动删除]
--[no]drop-old-table [默认自动删除]
运行中有报错，需要手动删除		 操作前评估表大小
大表需要手动删除
日志相关
--progress 打印标准错误输出
--statistics 打印各事件统计信息
额外配置
--alter-foreign-keys-method 有外键（开发规范尽量禁止使用外键） 该选项下--[no]drop-new-table不工作，因此不清除新表
如果同时使用了--force，在alter-foreign-keys-method = none
--config 以逗号分隔存储于配置文件，使用时第一选项必须是此选项
自动化的同学可以考虑这个
–defaults-file mysql的相关选项，需要指定配置文件的绝对路径
注意事项
--[no]check-replication-filters 确保从库没有配置
binlog_ignore_db
replicate_do_db
否则运行终止
[默认检查]
--default-engine 创建新表时采用mysql的默认引擎
[默认创建新表时同老表引擎保持一致]		 危险，不建议使用
会引起主从表引擎不一致的情况
从库创建新表时会采用mysql的默认引擎
--null-to-not-null [表结构优化类选项]
会将NULL的列更改为NOT NULL，对于没有明确指定默认值的，MySQL会根据字段类型提供默认值
比如数值是0，字符串是’’		 字符串的默认值还好，对于数值的页面可能会显示不想看到的0

操作顺序

试运行 --dry-run 

1
2
3
4
5
6
7
8
9
10
11
12
13
# pt-online-schema-change -h127.0.0.1 -u{username} -p{paswword} -P{port} \
--charset utf8 --chunk-time 0.03 --check-interval 5 --max-lag 3 \
--critical-load Threads_connected:1000,Threads_running:80 \
--max-load Threads_connected:1000,Threads_running:80 \
--set-vars innodb_lock_wait_timeout=1,lock_wait_timeout=5 \
--tries create_triggers:20:1,drop_triggers:20:1,copy_rows:20:1,swap_tables:20:1 \
--progress percentage,5 \
--check-replication-filters --check-alter --recurse=1 \
--alter-foreign-keys-method=auto --statistics \
--no-version-check --recursion-method=processlist \
D={database},t={table} \
--alter "{ADD column;MODIFY column;}" \
--print --dry-run

实际执行 --execute

1
2
3
4
5
6
7
8
9
10
11
12
13
# pt-online-schema-change -h127.0.0.1 -u{username} -p{paswword} -P{port} \
--charset utf8 --chunk-time 0.03 --check-interval 5 --max-lag 3 \
--critical-load Threads_connected:1000,Threads_running:80 \
--max-load Threads_connected:1000,Threads_running:80 \
--set-vars innodb_lock_wait_timeout=1,lock_wait_timeout=5 \
--tries create_triggers:20:1,drop_triggers:20:1,copy_rows:20:1,swap_tables:20:1 \
--progress percentage,5 \
--check-replication-filters --check-alter --recurse=1 \
--alter-foreign-keys-method=auto --statistics \
--no-version-check --recursion-method=processlist \
D={database},t={table} \
--alter "{ddl}" \
--print --execute
参数 说明
{username} 用户名
{paswword} 密码
{port} 端口
{database} 数据库名称
{table} 表名称
{ddl} ALTER TABLE后面的DDL语句
例如：ADD column; MODIFY column;

以上每个步骤分别独立运行，不可放到一句中运行

小技巧

工具依赖主键和唯一索引，如果要删除主键／重建主键

1
2
3
1.使用pt-osc建立唯一索引
2.使用pt-osc删除主键
3.使用pt-osc建立新主键

DBA的福音

1
--null-to-not-null

DBA可以通过某一次ALTER TABLE需要，借机对表结构进行优化

这里有个前提：
首先，公司的DDL上线规范，是否允许DBA发起这样的优化修改
其次，DBA需要提前向开发同学说明，字段DEFAULT VALUE是由MySQL根据字段类型自己提供的，那么对业务程序的处理和页面显示的影响，需要开发同学进行评估。所以，如果默认值不能满足业务，该选项不适用
总结，一切以业务为导向
原则：字段约束的规范化应该尽可能的在字段创建阶段完成

to be continued…