mysql dasc,MySQL DDL Online Schema Change—gh-ost介绍

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数据selectinsert ignore into

binlog应用insertreplace into

updateupdate全行

deletedelete

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-secondscut-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-retriescut-over重试次数

--postpone-cut-over-flag-file该文件存在则不进行cut-over操作，一直保持数据同步

---panic-flag-file‍‍当此文件存在时则立刻终止所有操作，并且不做任何清理操作

--serve-socket-file使用socket监听请求，可以在命令运行后更改相应的参数

--chunk-size每次迁移数据chunk大小，可以设置为100-100000，默认为1000

--max-lag-millis允许的最大延迟(毫秒),超过将被限制