MySQL在线ddl汇总

1.pt-osc

1.1原理与优缺点

• 创建新表，表结构与原表相同
• alter新表 在原表上创建insert、delete、update触发器
• 拷贝旧表数据到新表，同时通过触发器将增量数据同步到新表
• 如果原表有外键约束，处理外键
• 原表命名为old表 新表命名为原表 删除old表

CREATE TRIGGER `pt_osc_sbtest_sbtest2_del` 
AFTER DELETE ON `sbtest`.`sbtest2` 
FOR EACH ROW DELETE IGNORE FROM `sbtest`.`_sbtest2_new`
 WHERE `sbtest`.`_sbtest2_new`.`id` <=> OLD.`id`;
 
CREATE TRIGGER `pt_osc_sbtest_sbtest2_upd` 
AFTER UPDATE ON `sbtest`.`sbtest2` 
FOR EACH ROW REPLACE INTO `sbtest`.`_sbtest2_new` (`id`, `k`, `c`, `pad`, `ptosc`) 
VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`, NEW.`ptosc`);

CREATE TRIGGER `pt_osc_sbtest_sbtest2_ins`
AFTER INSERT ON `sbtest`.`sbtest2` 
FOR EACH ROW REPLACE INTO `sbtest`.`_sbtest2_new` (`id`, `k`, `c`, `pad`, `ptosc`) 
VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`, NEW.`ptosc`);
           

pt-osc同时处理全量和增量数据，即一边拷表一边回放增量DML，其所用拷表语句如下：

INSERT LOW_PRIORITY IGNORE INTO `sbtest`.`_sbtest2_new` (`id`, `k`, `c`, `pad`, `ptosc`) 
SELECT `id`, `k`, `c`, `pad`, `ptosc` FROM `sbtest`.`sbtest2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 115039 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
           

由于采用触发器方式，需要解决增量回放与全量拷贝乱序问题。pt-osc通过如下方式解决：

在拷表select时需要进行当前读（lock in shared mode）并与insert组成一个事务，避免快照读导致增量的delete操作丢失。场景如下：

如果是快照读，且在读id=x之前，业务执行了delete操作，则触发器先执行了空操作，导致新表中仍存在需删除掉的id=x记录;

如果与insert不是一个事务，存在相似问题；

触发器将增量update和insert均转换为replace into用于防止增量回放时因数据不存在而报错。

拷表insert时采用‘LOW_PRIORITY IGNORE’也是类似的考虑，防止全量旧数据覆盖增量新数据。

根据pt-osc上述实现，可以发现其存在的使用约束至少包括：

需要该表存在主键：因为进行每个select+insert事务拆分时用到了‘FORCE INDEX(PRIMARY)’；

需要确保表上没有触发器，否则会导致触发器冲突；

在使用过程中，会出现因存在触发器导致与业务事务冲突死锁的问题。

1. 原表上要有 primary key 或 unique index，因为当执行该工具时会创建一个 DELETE 触发器来更新新表；

注意：一个例外的情况是 –alter 指定的子句中是在原表中的列上创建 primary key 或 unique index，这种情况下将使用这些列用于 DELETE 触发器。

2. 不能使用 rename 子句来重命名表；

1. 列不能通过删除 + 添加的方式来重命名，这样将不会 copy 原有列的数据到新列；
2. 如果要添加的列是 not null，则必须指定默认值，否则会执行失败；
3. 删除外键约束（DROP FOREIGN KEY constraint_name），外键约束名前面必须添加一个下划线 ‘_’，即需要指定名称 _constraint_name，而不是原始的 constraint_name；

1.2 使用

-- 安装 yum 仓库
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
-- 安装 percona toolkit
yum install percona-toolkit -y
           

#创建用户
GRANT SELECT, INSERT, UPDATE, DELETE, \
    CREATE, DROP, PROCESS, REFERENCES, \ 
    INDEX, ALTER, SUPER, LOCK TABLES, \
    REPLICATION SLAVE, TRIGGER 
ON *.* TO 'ptosc'@'%'

#检查要变更的表上是否有主键或非空唯一键
#检查是否有其他表外键引用该表
select * from information_schema.key_column_usage where referenced_table_schema='testdb' and referenced_table_name='sbtest1'\G
#检查是否有触发器
select * from information_schema.triggers where event_object_schema='testdb' and event_object_table='sbtest1'\G
#若有，则需指定 –preserve-triggers 选项，且在 percona tool 3.0.4 起，对于 MySQL 5.7.2 以上，支持原表上有触发器，建议使用前在测试环境进行测试。
#执行 dry run
pt-online-schema-change --print --statistics \
    --progress time,30 --preserve-triggers --user=ptosc \
    --password=ptosc --alter 'modify c varchar(200) not null default ""' \
    h=127.0.1.1,P=3306,D=testdb,t=sbtest1 \
    --pause-file=/tmp/aa.txt --max-load=threads_running=100,threads_connected=200 \
    --critical-load=threads_running=1000  --chunk-size=1000 \
    --alter-foreign-keys-method auto --dry-run
           

–print：打印工具执行的 SQL 语句。

–statistics：打印统计信息。

–pause-file：当指定的文件存在时，终止执行。

–max-load：超过指定负载时，暂定执行。

–critical-load：超过指定负载时，终止执行。

–chunck-size：指定每次复制的行数。

–alter-foreign-keys-method：指定外键更新方式。

–progress：copy 进度打印的频率。
-[no]check-unique-key-change 控制添加唯一索引时是否会清理重复数据
           

pt-osc

2.gh-ost

• 先连接到主库上，根据alter语句创建所需的新表；
• 作为一个“备库”连接到其中一个真正的备库上，一边在主库上拷贝已有的数据到新表，一边从备库上拉取增量数据的binlog；
• 然后不断的把 binlog 应用回主库；

• cut-over是最后一步，锁住主库的源表，等待binlog 应用完毕，然后替换gh-ost表为源表。

  由于使用单线程回放binlog来替换触发器，所以增量DML回放效率不如触发器，因为pt-osc的增量回放并发度是与业务DML并发度相同的，是多线程的。

2.1优缺点

• 实现层面，gh-ost对业务负载敏感度会远高于pt-osc。

• 在使用gh-ost工具增加唯一索引时，如没检查数据唯一性，变更后会遇到数据丢失问题

  gh-ost迁移旧表数据时使用

  insert ignore into xxx

• 存在null值的字段添加not null约束，任何模式下，如果之前存在空值，则会把空值改为0（隐形默认值），存在业务涵义上的风险

  

  从上图可以看到，在某些场景下，可能发生 gh-ost 开始捕获 DML 操作后的二进制日志，但是之前的二进制事务并没有提交！

在上图的案例中，步骤1 addDMLEventsListener 将会捕获记录5以后发生的日志。

然而，在步骤2 ReadMigrationRangeValues 中，获取 min、max的值将会是1、4。

这是因为由于

after_sync

半同步模式，记录5对应的事务还未提交（如网络原因，或从机宕机等场景），记录5对于 gh-ost 中的函数 ReadMigrationRangeValues 是不可见的。

因此，步骤3、4只会插入记录1-4，以及回放记录5之后的所有日志，但会丢失记录5。

既然知道了原因，那么修复就变得非常简单了。只需要在获取 min、max的边界值的时候通过一致性读取即可

SELECT MIN(UK),MAX(UK) FROM xxxLOCK IN SHARE MODE;

通过 LOCK IN SHARE MODE，即便发生上述 after_sync 半同步等待问题，则在函数 ReadMigrationRangeValues 执行过程中，需要等待上述事务提交才能完成边界值的获取。

这时，边界值就会变为1、5，从而不会导致数据的丢失。

对于DDL操作的灵活度掌控，可暂停，可动态修改参数；DBA可以根据执行情况来快速调整预设的参数，可快可慢，实现DDL操作性能和对业务影响的平衡；

更为稳健的切表控制：将-cut-over-lock-timeout-seconds和-default-retries 配合使用，可以对切表进行灵活的控制。避免pt-osc切表异常导致对业务造成严重影响；

3. MySQL8.0 online ddl

Online DDL有复制阻塞问题：在MySQL多线程并行复制框架下，从库回放DDL操作时排他性的，也就是说DDL操作独立为一个group，只有该DDL操作执行完才能回放后续的DML，如果DDL操作需花费2小时，那么复制延迟至少为2小时；

相关参数：

调整参数加快索引创建速度

innodb_ddl_threads

innodb_ddl_threads #创建二级索引时的并行线程数量
innodb_ddl_buffer_size #指定进行并行 DDL 操作时能够使用的 buffer 大小
innodb_parallel_read_threads #扫描聚簇索引的并行线程