【MySQL运维】大表ONLINE DDL之PTOSC

TangLu 538 2021-06-02

一、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的大表创建出一个新表和对应的触发器,然后复制原表数据到新表,复制期间如果有新数据产生则通过触发器复制到新表里。数据复制完成后会删除原表,再将新表改名。整个执行进度是可以看见的。

# 安装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进行变更操作的表必须具有主键或者唯一索引,否则无法执行

版权声明
本站所有文章均为原创,转载请注明出处!小站维护不易,如果对您有所帮助,希望能点击一下站内广告,谢谢!
上一篇:【MySQL运维】复制表到当前数据库方法
下一篇:使用fio工具进行磁盘性能测试教程
相关文章

 发表评论

暂时没有评论,来抢沙发吧~

微信二维码