【MySQL运维】大表ONLINE DDL之PTOSC
一、ONLINE DDL介绍
从MySQL5.6开始支持ONLINE DDL，5.7、8.0版本也在针对ONLINE DDL特性进行升级。关于不同的DDL操作对DML带来的影响，可以参考MySQL官网的online ddl说明（https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html#online-ddl-column-operations）。
#ONLINE DDL语法,由于lock字句和algorithm字句一般采用默认值，所以平时没写，所以下面DDL命令是等价的 alter table t add name varchar(10) not null; alter table t add name varchar(10) not null,lock=default,algorithm=default;
二、PTOSC工具介绍
ptosc是pt-online-schema-change的简称，由Percona-toolkit工具包所提供，是一个在线修改表结构工具。使用ptosc对大表进行DDL可以最大程度的减少锁表阻塞，并且主从延迟可控。其原理就是为需要进行DDL的大表创建出一个新表和对应的触发器，然后复制原表数据到新表，复制期间如果有新数据产生则通过触发器复制到新表里。数据复制完成后会删除原表，再将新表改名。整个执行进度是可以看见的。在使用pt-osc添加唯一索引时，一定要确保加唯一索引的字段没有重复值，否则这些重复数据会被删除掉，造成数据丢失（由于INSERT和UPDATE触发器使用REPLCAE INTO的方式对新表进行更新，该语句和INSERT不同的是REPLACE INTO在插⼊数据到表中时如果发现表中已有此⾏数据（根据主键或者唯⼀索引判断）则先删除此⾏数据，然后再进行插⼊新的数据。所以触发器所建立出来的新表此时包含唯一索引，导致REPLACE INTO操作将新表中的重复记录删除再插入当前记录，最终导致数据丢失。）
# 安装percona-toolkit
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-toolkit
· 数据库相关选项
u=：指定数据库用户信息
p=：指定数据库用户密码
h=：指定数据库地址
D=：指定需要操作的数据库
t=：指定需要操作的表
· PTOSC相关选型
--alter：指定DDL语句，不过需要去掉ALTER TABLE TABLE_NAME，直接指定后半部分
--execute：指定该选项后才会真正的执行DDL操作
--dry-run：干跑模式，会创建新表但不会创建触发器以及复制数据，主要用于了解执行步骤与细节。--dry-run与--execute必须指定一个，二者互斥。常和--print配合
--print：打印SQL语句到标准输出。可以看到工具所执行的语句，常和--dry-run配合
--chunk-size：每次复制数据的数量，默认1000，可以稍微改大点
--check-alter：是否检查语法，默认yes
--recursion-method：检查从库的方法，默认为show processlist，none表示不检查从库状态
--max-lag：检查从库延迟时间，默认1秒，如果超过阈值则暂停复制，等待下一次检查。如果没有延迟则继续复制
--check-interval：检查从库延迟时间的频率，默认1秒
--[no]check-replication-filters：检测主从复制是否存在过滤配置，如存在binlog_ignore_db和replicate_do_db选项会禁止操作。因为如果Master更新的表不存在于Slave，主从会出错
--[no]swap-tables：复制完成后交换原表和新表，除非配置了--no-drop-old-table，默认为yes
--[no]drop-old-table：复制完成后删除原表，如果出错则会保留原表，默认为yes
--[no]drop-new-table：复制完成后删除新表，默认yes。
--[no]drop-triggers：复制完成后删除原表上的触发器。如果配置--no-drop-triggers则会强制开启--no-drop-old-table，代表如果不删除旧表上的触发器就强制不能删除旧表。
--new-table-name：配置新表的名称，默认格式是tablename_new
--progress：输出进度报告
--quiet：不输出信息
--set-vars：设置MySQL变量，多个变量用逗号分割。默认会进行wait_timeout=10000,innodb_lock_wait_timeout=1,lock_wait_timeout=60的设置
--max-load：检查复制过程中数据库负载，如果超过阈值则暂停复制。可以指定多个指标，逗号分割，默认为Threads_running=25
--critical-load：与--max-load作用相同，只不过超过阈值时是直接退出而不是暂停。可以指定多个指标，逗号分割，默认为Threads_running=50
三、PTOSC使用示例
#语法示例
pt-online-schema-change
--alter "这里写alter table之后的那部分SQL语句"
--alter-foreign-keys-method=rebuild_constraints
--check-interval=5
--check-replication-filters=no
--chunk-size=10000
--critical-load=thread_connected:4000,thread_running:300
--max-load=thread_connected:2500,thread_running:200
--recursion-method=none
#DDL语句
pt-online-schema-change h=10.3.0.224,P=3311,u=dba,p=123456,D=$database_name,t=$table_name --alter "MODIFY COLUMN insure_json longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '投保JSON';" --execute
需要注意的是使用PTOSC进行变更操作的表必须具有主键或者唯一索引，否则无法执行
评论