在 mysql 5.5 版本以前,修改表结构如添加索引、修改列,需要锁表,期间不能写入,对于大表这简直是灾难。从5.5特别是5.6里,情况有了好转,支持Online DDL,pt-online-schema-change是Percona-toolkit一员,通过改进原生ddl的方式,达到不锁表在线修改表结构。 1)创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构) 2)在新表执行alter table 语句(速度应该很快) 3)在原表中创建触发器3个触发器分别对应insert,update,delete操作 4)以一定块大小从原表拷贝数据到临时表,拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表 5)Rename 原表到old表中,在把临时表Rename为原表 6)如果有参考该表的外键,根据alter-foreign-keys-method参数的值,检测外键相关的表,做相应设置的处理 7)默认最后将旧原表删除 这里只介绍部分常用的选项 因为拷贝行有可能会给部分行上锁,Threads_running 是判断当前数据库负载的绝佳指标。 熟悉percona-toolkit的人都知道--recursion-method 可以用来指定从库dsn记录。另外,如果从库被停止,将会永远等待,直到从开始同步,并且延迟小于该值。 也可以通过另外一个选项--chunk-size禁止动态调整,即每次固定拷贝 1k 行,如果指定则默认1000行,且比 chunk-time 优先生效。 3.1 原表上不能有触发器存在 这个很容易理解,pt-osc会在原表上创建3个触发器,而一个表上不能同时有2个相同类型的触发器,为简单通用起见,只能一棍子打死。 所以如果要让它支持有触发器存在的表也是可以实现的,思路就是:先找到原表触发器定义;重写原表触发器;最后阶段将原表触发器定义应用到新表。 3.2 通过触发器写数据到临时新表,会不会出现数据不一致或异常 如果update t1,触发update t2,但这条数据还没copy到t2,不就有异常了吗?后台通过打开general_log,看到它创建的触发器: 在原表上update,新临时表上是replace into整行数据,所以达到有则更新,无则插入。同时配合后面的 insert ignore,保证这条数据不会因为重复而失败。 3.3 为什么外键那么特殊 假设 t1 是要修改的表,t2 有外键依赖于 t1,_t1_new 是 alter t1 产生的新临时表。 这里的外键不是看t1上是否存在外键,而是作为子表的 t2。主要问题在 rename t1 时,t1“不存在”导致t2的外键认为参考失败,不允许rename。 pt-osc提供--alter-foreign-keys-method选项来决定怎么处理这种情况: 涉及的主要方法在 pt-online-schema-change 文件的 determine_alter_fk_method, rebuild_constraints, swap_tables三个函数中。 开发规范决定,即使表间存在外键参考关系,也不通过表定义强制约束。 3.4 在使用之前需要对磁盘容量进行评估 使用OSC会使增加一倍的空间,包括索引 而且在 Row Based Replication 下,还会写一份binlog。不要以为使用--set-vars去设置 sql_log_bin=0,因为在这个session级别,alter语句也要在从库上执行,除非你对从库另有打算。 篇幅有限,后面再介绍下这款工具部署教程和怎么去使用,感兴趣的朋友可以关注一下~概述
1、pt-osc工作过程
2、常用选项说明
3、 使用限制
6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETE ON `confluence`.`sbtest3`
FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new` WHERE `confluence`.`_sbtest3_new`.`id` <=> OLD.`id`
6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_upd` AFTER UPDATE ON `confluence`.`sbtest3`
FOR EACH ROW REPLACE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`)
6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_ins` AFTER INSERT ON `confluence`.`sbtest3`
FOR EACH ROW REPLACE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`)
并且copy操作是:
6165 Query INSERT LOW_PRIORITY IGNORE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`)
SELECT `id`, `k`, `c`, `pad` FROM `confluence`.`sbtest3` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4692805')) AND ((`id` <= '4718680'))
LOCK IN SHARE MODE /*pt-online-schema-change 46459 copy nibble*/
4、使用 pt-osc原生 5.6 online ddl相比,如何选择