MySQL DDL Online Schema Change—gh-ost介绍
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数据 | 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 \
--executegh-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 | 允许的最大延迟（毫秒）,超过将被限制 |