专栏首页MYSQL轻松学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 \
--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

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

本文分享自微信公众号 - MYSQL轻松学(learnmysql),作者:Liang

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-11-22

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • insert事务产生duplicate key error引发的死锁分析

    先看程序报错: 2017-06-12 21:18:40.856 [ForkJoinPool.commonPool-worker-12] ERROR com....

    MySQL轻松学
  • MGR 的主要优点

    MGR(Mysql Group Replication)是5.7版本新加的特性,是一个MySQL插件。

    MySQL轻松学
  • MySQL系列优化(一)

    MYSQL优化是一个非常大的课题,这篇文章主要介绍了跟MYSQL相关的4个方面,如果想深入研究可以查下相关资料。 ---- 一、服务器级别优化 二、操作系统级别...

    MySQL轻松学
  • yii2 modal弹窗之ActiveForm ajax表单异步验证

    前面我们讲述了yii2中如何使用modal以及yii2 gridview列表内更新操作如何使用modal的问题,本以为modal要告一段落可以开始新的话题了,但...

    botkenni
  • 从0到1:PostCSS 插件开发最佳实践

    前阵子为了满足工作上的一个需求开发了一个PostCSS 插件,后来也将这个插件提交给PostCSS 官方并得到认可。在这篇文章中笔者将记录开发过程中遇到的一些问...

    Jeff
  • 如何使用Flume采集Kafka数据写入HBase

    Fayson
  • 带你认识 flask ajax 异步请求

    迄今为止,在我遵循的传统服务器端模型中,有一个客户端(由用户驱动的Web浏览器)向应用服务器发出HTTP请求。请求可以简单地请求HTML页面,例如当你单击“个人...

    公众号---志学Python
  • 基于Flink商品实时推荐系统项目【大数据及算法】

    介绍: 基于Flink实现的商品实时推荐系统。flink统计商品热度,放入redis缓存,分析日志信息,将画像标签和实时记录放入Hbase。在用户发起推荐请求后...

    用户1410343
  • 腾讯云一句话识别-iOS SDK

    AppID、SecretID 和 SecretKey等个人信息填入,否则无法运行Demo(需要在语音识别控制台开通服务,并进入API 密钥管理页面新建密钥,生成...

    许岳操
  • mysql 内连接,连续两次使用同一张表,自连接

    select * from class INNER JOIN student on class.id=student.classId

    hotqin888

扫码关注云+社区

领取腾讯云代金券