MySQL DDL Online Schema Change—gh-ost介绍

MySQL轻松学

发布于 2019-12-05 18:01:40

1.4K0

发布于 2019-12-05 18:01:40

文章被收录于专栏：MYSQL轻松学

Online DDL方案及优缺点：

方式	优点	缺点
先在从库执行，然后进行主从切换	适用于计划内的切换；	步骤较多，需要做主从切换；如果开启gtid，从库执行需要设置sql_log_bin=off避免主从切换时异常
MySQL自身Online DDL	无需借助额外工具，部分DDL操作不会创建临时表、不会造成DML阻塞；	需要mysql5.6以上版本；MySQL5.6的Online DDL不是真正的Online DDL失败的话回滚时间长；如果表的DML操作比较多，需要设置更大的缓存空间；
pt-online-schema-change	不会造成DML阻塞；加锁时间短，只在最后rename阶段加瞬间锁	需要创建触发器和临时表；需要更大的存储空间；添加唯一索引可能造成数据丢失
gh-ost	对主库影响较小；无需创建触发器;有暂停功能；	需要开启binlog并设置row模式；对binlog保留时间要求较高；模拟从库单线程应用较慢；需要更大的存储空间

gh-ost定义：

gh-ost是针对MySQL对主库影响很小，无trigger的online schema change解决方案。采用消费binlog的方式来代替trigger方式，并将同步信息存储到临时表中。

gh-ost特性：

可以测试、随时暂停、动态控制/重新配置、审计和其他操作。

gh-ost原理：

经历四个阶段：

1、校验阶段:

检查有没有外键和触发器
检查表的主键信息
预估行数
检查是否主库或从库，是否开启log_slave_updates，以及binlog信息
检查gho和del结尾的临时表是否存在
创建ghc结尾的表，存数据迁移的信息，以及binlog信息等

2、初始化阶段：

初始化stream的连接，添加binlog的监听

3、迁移阶段：

创建_gho结尾的临时表，执行DDL在_gho结尾的临时表上
开启事务，按照主键id把源表数据写入到gho结尾的表上，再提交，以及应用binlog

/* 进度计算 */
/* 方法1：通过explain获取大概数据 */
explain select * from `test`.`t` where 1=1;
/* 方法2：通过select count(*)获取一个准确值 */
select count(*) from `test`.`t`;
/* 获取最值 */
/* 最小值 */
elect /* gh-ost test.t */ `id` from `test`.`t` order by id` asc limit 1；
/* 最大值 */
select /* gh-ost test.t */ `id` from `test`.`t` order by id` dasc limit 1;
/* 剩余数据检查 */
/* 计算第一个chunk */
select /* gh-ost test.t */ `id` from `test`.`t` where `id` >= 1 and `id` <= 2000 order by `id` asc limit 1 offset 999;
/* 最后一个chunk如果不足1000，那么上面sql查询为空，这时运行：*/
select /* gh-ost test.t */ `id`                   
from (                                   
     select `id` from `test`.`t`
     where `id` > 1900 and `id` <= 2000 order by `id` asc limit 1000
     ) select_osc_chunk                   
order by `id` desc limit 1;
/* copy原始数据 */
insert ignore into `test`.`_t_gho` select from `test`.`t` force index (`PRIMARY`) where `id` >=1 and `id` <= 1000 lock in share mode;

数据迁移过程sql映射关系

	源表操作	新表操作
copy数据	select	insert ignore into
binlog应用	insert	replace into
	update	update全行
	delete	delete

binlog是最权威的，gh-ost的原则是以binlog优先,所以无论任何顺序下,数据都是和binlog保持一致。

insert操作如果copy数据在后，会insert ignore into。如果应用binlog在后，会replace into。
update/delete操作对已copy过的数据，出现对原表的update/delete操作。会通过应用binlog的update，对这条记录列全部覆盖更新，所以不会有累加的问题。对未copy过的数据，出现对原表的update/delete操作。新表数据还不存在，应用binlog为空操作，会等copy迁移。

4、cut-over阶段：

lock源表
rename表：rename源表 to 源_del表，_gho表 to 源表。
清理_ghc表。

流程图：

gh-ost三种模式：

a. 连接从库，在主库执行

gh-ost默认执行方式，查看从库情况，并获取主库信息并连到主库，对主库侵入最小。

步骤为：

1）在主库上创建_xxx_gho、_xxx_ghc，并修改_xxx_gho表结构；

2）从slave上读取binlog日志事件，将变更应用到主库上的_xxx_gho表；

3）在主库上读源表的数据写入_xxx_gho表中:insert into ignore...select；

4）在主库上完成表切换；

b. 连接主库，在主库执行

参数--allow-master-master：如果担心从库延迟，可以直接读取主库binlog，并在主库执行。步骤同上。

c. 连接从库，在从库执行

参数--test-on-replica：在从库上测试gh-ost，包括在从库上数据迁移(migration)，数据迁移完成后stop slave，源表和ghost表立刻交换而后立刻再交换回来，最终相当于源表没被改过。继续保持stop slave，使你可以对比两张表。如果不想stop slave，则可以再添加参数：--test-on-replica-skip-replica-stop

gh-ost通用方式：

gh-ost \
--user=“xxxx" \
--password=“xxxxx" \
--host=1.1.1.1 \
--port=3306 \
--database="test" \
--table="t" \
--alter="engine=innodb" \
--switch-to-rbr \
--cut-over-lock-timeout-seconds=1 \
--initially-drop-old-table \
--initially-drop-ghost-table \
--initially-drop-socket-file \
--ok-to-drop-table \
--approve-renamed-columns \
--max-load='Threads_running=100,Threads_connected=500' \
--default-retries=3600 \
--allow-on-master \
--execute

gh-ost常用参数说明：

--allow-on-master	直接读取主库binlog
--approve-renamed-column	是否允许进行列重命名
--alter	改表语句--alter="engine=innodb"
--switch-to-rbr	将binlog转换成row格式
--cut-over-lock-timeout-seconds	cut-over操作超时时长，也就是MDL锁持有时长
--critical-load	数据库压力阈值设定，例如Threads_running=100,Threads_connected=500
--exact-rowcount	是否通过执行select count(*) 统计数据行数
--initially-drop-ghost-table	检查并删除已经存在的ghost表
--initially-drop-old-table	检查并删除已经存在的旧表
--initially-drop-socket-file	强制删除已经存在的socket文件
--ok-to-drop-table	操作结束后删除旧表
--default-retries	cut-over重试次数
--postpone-cut-over-flag-file	该文件存在则不进行cut-over操作，一直保持数据同步
---panic-flag-file‍‍	当此文件存在时则立刻终止所有操作，并且不做任何清理操作
--serve-socket-file	使用socket监听请求，可以在命令运行后更改相应的参数
--chunk-size	每次迁移数据chunk大小，可以设置为100-100000，默认为1000
--max-lag-millis	允许的最大延迟（毫秒）,超过将被限制