MySQL DDL Online Schema Change—gh-ost介绍

Online DDL方案及优缺点：

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映射关系

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表。
  复制

流程图：

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常用参数说明：