• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

Online DDL 工具 gh-ost原理(二)

MySQL SEian.G 5年前 (2019-12-15) 2514次浏览 已收录 0个评论
文章目录[隐藏]

接上一篇文章Online DDL 工具 gh-ost实战(一),介绍了gh-ost的实际使用案例,本文介绍一下gh-osh的相关原理;

gh-ost 放弃了触发器,使用 binlog 来同步。gh-ost 作为一个伪装的备库,可以从主库/备库上拉取 binlog,过滤之后重新应用到主库上去,相当于主库上的增量操作通过 binlog 又应用回主库本身,不过是应用在幽灵表上。

Online DDL 工具 gh-ost原理(二)

gh-ost 首先连接到主库上,根据 alter 语句创建幽灵表,然后作为一个”备库“连接到其中一个真正的备库上,一边在主库上拷贝已有的数据到幽灵表,一边从备库上拉取增量数据的 binlog,然后不断的把 binlog 应用回主库。图中 cut-over 是最后一步,锁住主库的源表,等待 binlog 应用完毕,然后替换 gh-ost 表为源表。gh-ost 在执行中,会在原本的 binlog event 里面增加以下 hint 和心跳包,用来控制整个流程的进度,检测状态等。这种架构带来诸多好处,例如:

  • 整个流程异步执行,对于源表的增量数据操作没有额外的开销,高峰期变更业务对性能影响小。
  • 降低写压力,触发器操作都在一个事务内,gh-ost 应用 binlog 是另外一个连接在做。
  • 可停止,binlog 有位点记录,如果变更过程发现主库性能受影响,可以立刻停止拉binlog,停止应用 binlog,稳定之后继续应用。
  • 可测试,gh-ost 提供了测试功能,可以连接到一个备库上直接做 Online DDL,在备库上观察变更结果是否正确,再对主库操作,心里更有底。

一、三种模式架构图

Online DDL 工具 gh-ost原理(二)

1、连上从库,在主库上修改

这是gh-ost默认的工作模式,它会查看从库情况,找到集群的主库并且连接上去,对主库侵入最少,大体步骤是:

在主库上创建_xxx_gho、_xxx_ghc,并修改_xxx_gho表结构;

从slave上读取二进制日志事件,将变更应用到主库上的_xxx_gho表;

在主库上读源表的数据写入_xxx_gho表中:insert into igore….select;

在主库上完成表切换;

如果主库的二进制日志格式是 Statement,就可以使用这种模式。但从库就必须配成启用二进制日志(log_bin, log_slave_updates),还要设成 Row 格式(binlog_format=ROW),实际上 gh-ost 会在从库上帮你做这些设置。

事实上,即使把从库改成 Row 格式,这仍然是对主库侵入最少的工作模式。

2、直接主库修改

在主库上创建_xxx_gho、_xxx_ghc,并修改_xxx_gho表结构;

从主库上读取二进制日志事件,将变更应用到主库上的_xxx_gho表;

在主库上读源表的数据写入_xxx_gho表中:insert into igore….select;

在主库上完成表切换;

如果没有从库,或者不想在从库上操作,那直接用主库也是可以的。gh-ost 就会在主库上直接做所有的操作。仍然可以在上面查看主从复制延迟。

主库必须产生 Row 格式的二进制日志;

启动 gh-ost 时必须用–allow-on-master 选项来开启这种模式;

3、在从库上修改和测试

这种模式会在从库上做修改。gh-ost仍然会连上主库,但所有操作都是在从库上做的,不会对主库产生任何影响。在操作过程中,gh-ost也会不时地暂停,以便从库的数据可以保持最新。

–migrate-on-replica选项让gh-ost直接在从库上修改表。最终的切换过程也是在从库正常复制的状态下完成的。

–test-on-replica表明操作只是为了测试目的。在进行最终的切换操作之前,复制会被停止。原始表和临时表会相互切换,再切换回来,最终相当于原始表没被动过。主从复制暂停的状态下,你可以检查和对比这两张表中的数据。

三种模式各有优缺点,但我只关心缺点:

模式一的缺点,模式一会在从 DB 上面读取 binlog,可能造成数据库主从数据不一致,原因因为是主库的 binlog 没有完全在从库执行。所以个人感觉模式一有丢失数据的风险。

模式二任何操作都会在主库操作,或多或少会对主库负载造成影响,但是可以通过调整一些参数降低和时刻关注这些影响,所以个人推荐使用模式二。

模式三是偏向测试用的,这里不做过多介绍,但是模式三里有一个细节,cut-over 阶段有会 stop slave 一个操作,其实这个操作风险特别高,有时 stop slave 时间会很长,务必会对线上数据库使用造成影响,所以如果使用模式三做测试也要在线下数据库。

二、原理

Online DDL 工具 gh-ost原理(二)

1、检查数据库实例的基础信息

(1)测试db是否可连通,并且验证database是否存在

(2)确认连接实例是否正确

2019-12-07T14:43:58.641684Z     1761853 Query   select @@global.version
2019-12-07T14:43:58.641924Z     1761853 Query   select @@global.port
2019-12-07T14:43:58.642435Z     1761853 Query   select @@global.hostname, @@global.port

(3)权限验证 show grants for current_user()

2019-12-07T14:43:58.642435Z     1761853 Query   show /* gh-ost */ grants for current_user()

(4)获取binlog相关信息,包括row格式和修改binlog格式后的重启replicate

select @@global.log_bin, @@global.binlog_format

select @@global.binlog_row_image

2019-12-07T14:43:58.642435Z     1761853 Query   select @@global.log_bin, @@global.binlog_format
2019-12-07T14:43:58.642865Z     1761853 Query   select @@global.binlog_row_image

(5)原表存储引擎是否是innodb,检查表相关的外键,是否有触发器,行数预估等操作,需要注意的是行数预估有两种方式 一个是通过explain 读执行计划 另外一个是select count(*) from table ,遇到几百G的大表,后者一定非常慢。

2019-12-07T14:43:58.642865Z     1761853 Query   show /* gh-ost */ table status from `wjq` like 'employees'
2019-12-07T14:43:58.643410Z     1761853 Query   SELECT
                        SUM(REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_SCHEMA='wjq' AND TABLE_NAME='employees') as num_child_side_fk,
                        SUM(REFERENCED_TABLE_NAME IS NOT NULL AND REFERENCED_TABLE_SCHEMA='wjq' AND REFERENCED_TABLE_NAME='employees') as num_parent_side_fk
                FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                WHERE
                                REFERENCED_TABLE_NAME IS NOT NULL
                                AND ((TABLE_SCHEMA='wjq' AND TABLE_NAME='employees')
                                        OR (REFERENCED_TABLE_SCHEMA='wjq' AND REFERENCED_TABLE_NAME='employees')
                                )
2019-12-07T14:44:01.076868Z     1761853 Query   SELECT COUNT(*) AS num_triggers
                        FROM INFORMATION_SCHEMA.TRIGGERS
                        WHERE
                                TRIGGER_SCHEMA='wjq'
                                AND EVENT_OBJECT_TABLE='employees'
2019-12-07T14:44:01.080465Z     1761853 Query   explain select /* gh-ost */ * from `wjq`.`employees` where 1=1
2019-12-07T14:44:01.080957Z     1761853 Query   SELECT
      COLUMNS.TABLE_SCHEMA,
      COLUMNS.TABLE_NAME,
      COLUMNS.COLUMN_NAME,
      UNIQUES.INDEX_NAME,
      UNIQUES.COLUMN_NAMES,
      UNIQUES.COUNT_COLUMN_IN_INDEX,
      COLUMNS.DATA_TYPE,
      COLUMNS.CHARACTER_SET_NAME,
                        LOCATE('auto_increment', EXTRA) > 0 as is_auto_increment,
      has_nullable
    FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN (
      SELECT
        TABLE_SCHEMA,
        TABLE_NAME,
        INDEX_NAME,
        COUNT(*) AS COUNT_COLUMN_IN_INDEX,
        GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES,
        SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC), ',', 1) AS FIRST_COLUMN_NAME,
        SUM(NULLABLE='YES') > 0 AS has_nullable
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE
                                NON_UNIQUE=0
                                AND TABLE_SCHEMA = 'wjq'
        AND TABLE_NAME = 'employees'
      GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
    ) AS UNIQUES
    ON (
      COLUMNS.COLUMN_NAME = UNIQUES.FIRST_COLUMN_NAME
    )
    WHERE
      COLUMNS.TABLE_SCHEMA = 'wjq'
      AND COLUMNS.TABLE_NAME = 'employees'
    ORDER BY
      COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME,
      CASE UNIQUES.INDEX_NAME
        WHEN 'PRIMARY' THEN 0
        ELSE 1
      END,
      CASE has_nullable
        WHEN 0 THEN 0
        ELSE 1
      END,
      CASE IFNULL(CHARACTER_SET_NAME, '')
          WHEN '' THEN 0
          ELSE 1
      END,
      CASE DATA_TYPE
        WHEN 'tinyint' THEN 0
        WHEN 'smallint' THEN 1
        WHEN 'int' THEN 2
        WHEN 'bigint' THEN 3
        ELSE 100
      END,
      COUNT_COLUMN_IN_INDEX
2019-12-07T14:44:01.081912Z     1761853 Query   show columns from `wjq`.`employees`

2、创建binlog streamer连接到主库或者从库,添加binlog的监听

2019-12-07T14:44:01.088803Z     1761853 Query   show /* gh-ost readCurrentBinlogCoordinates */ master status
2019-12-07T14:44:01.089184Z     1761854 Quit
2019-12-07T14:44:01.093629Z     1761855 Connect root@localhost on  using TCP/IP
2019-12-07T14:44:01.093742Z     1761855 Query   SHOW GLOBAL VARIABLES LIKE 'BINLOG_CHECKSUM'
2019-12-07T14:44:01.095303Z     1761855 Query   SET @master_binlog_checksum='NONE'
2019-12-07T14:44:01.095687Z     1761853 Query   select @@global.version
2019-12-07T14:44:01.095759Z     1761855 Binlog Dump     Log: 'mysql3306.000064'  Pos: 8255879

3、创建log表_xxx_ghc和ghost表_xxx_gho并修改ghost表结构到最新

2019-12-07T14:44:01.097632Z     1761853 Query   show columns from `wjq`.`employees`
2019-12-07T14:44:01.097632Z     1761853 Query   show /* gh-ost */ table status from `wjq` like '_employees_gho'
2019-12-07T14:44:01.098412Z     1761853 Query   show /* gh-ost */ table status from `wjq` like '_employees_20191207224358_del'
2019-12-07T14:44:01.098412Z     1761853 Query   drop /* gh-ost */ table if exists `wjq`.`_employees_ghc`
2019-12-07T14:44:01.109409Z     1761853 Query   create /* gh-ost */ table `wjq`.`_employees_ghc` (
                        id bigint auto_increment,
                        last_update timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        hint varchar(64) charset ascii not null,
                        value varchar(4096) charset ascii not null,
                        primary key(id),
                        unique key hint_uidx(hint)
                ) auto_increment=256
2019-12-07T14:44:01.278469Z     1761853 Query   create /* gh-ost */ table `wjq`.`_employees_gho` like `wjq`.`employees`
2019-12-07T14:44:01.481421Z     1761853 Query   alter /* gh-ost */ table `wjq`.`_employees_gho` engine=innodb
2019-12-07T14:44:01.759806Z     1761853 Query   insert /* gh-ost */ into `wjq`.`_employees_ghc`
                                (id, hint, value)
                        values
                                (NULLIF(2, 0), 'state', 'GhostTableMigrated')
                        on duplicate key update
                                last_update=NOW(),
                                value=VALUES(value)
2019-12-07T14:44:01.794082Z     1761853 Query   insert /* gh-ost */ into `wjq`.`_employees_ghc`
                                (id, hint, value)
                        values
                                (NULLIF(0, 0), 'state at 1575729841793887773', 'GhostTableMigrated')
                        on duplicate key update
                                last_update=NOW(),
                                value=VALUES(value)
2019-12-07T14:44:01.816535Z     1761853 Query   insert /* gh-ost */ into `wjq`.`_employees_ghc`
                                (id, hint, value)
                        values
                                (NULLIF(1, 0), 'heartbeat', '2019-12-07T22:44:01.816251582+08:00')
                        on duplicate key update
                                last_update=NOW(),
                                value=VALUES(value)

4、开始迁移数据:row copy和binlog apply同时进行

(1)最小值:select /* gh-ost `wjq`.`employees` */ `emp_no` from `wjq`.`employees` order by `emp_no` asc limit 1;

2019-12-07T14:44:01.839410Z     1761858 Query   select /* gh-ost `wjq`.`employees` */ `emp_no`
                                from
                                        `wjq`.`employees`
                                order by
                                        `emp_no` asc
                                limit 1

(2)最大值:select /* gh-ost `wjq`.`employees` */ `emp_no` from `wjq`.`employees` order by `emp_no` desc limit 1

2019-12-07T14:44:01.839484Z     1761858 Query   select /* gh-ost `wjq`.`employees` */ `emp_no`
                                from
                                        `wjq`.`employees`
                                order by
                                        `emp_no` desc
                                limit 1

   3) 计算第一个chunk: select  /* gh-ost `wjq`.`employees` iteration:0 */  `emp_no` from `wjq`.`employees` where ((`emp_no` > _binary'10001') or ((`emp_no` = _binary'10001'))) and ((`emp_no` < _binary'499999') or ((`emp_no` = _binary'499999'))) order by `emp_no` asc limit 1 offset 999 2019-12-07T14:44:02.864410Z 1761859 Query select /* gh-ost `wjq`.`employees` iteration:0 */ `emp_no` from `wjq`.`employees` where ((`emp_no` > _binary'10001') or ((`emp_no` = _binary'10001'))) and ((`emp_no` < _binary'499999') or ((`emp_no` = _binary'499999')))
                                        order by
                                                `emp_no` asc
                                        limit 1
                                        offset 999

(3)循环插入数据:insert /* gh-ost `wjq`.`employees` */ ignore into `wjq`.`_employees_gho` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`, `testcol1`,
`count`) (select `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`, `testcol1`, `count` from `wjq`.`employees` force index (`PRIMARY`) where (((`emp_no` > _binary’10001′) or ((`emp_no` = _binary’10001′))) and ((`emp_no` < _binary’11000′) or ((`emp_no` = _binary’11000′)))) lock in share mode)

2019-12-07T14:44:02.864599Z     1761859 Query   START TRANSACTION
2019-12-07T14:44:02.864671Z     1761863 Connect root@localhost on wjq using TCP/IP
2019-12-07T14:44:02.865410Z     1761859 Query   SET
                        SESSION time_zone = 'SYSTEM',
                        sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')
2019-12-07T14:44:02.865410Z     1761859 Query   insert /* gh-ost `wjq`.`employees` */ ignore into `wjq`.`_employees_gho` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`, `testcol1`,
 `count`)
      (select `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`, `testcol1`, `count` from `wjq`.`employees` force index (`PRIMARY`)
        where (((`emp_no` > _binary'10001') or ((`emp_no` = _binary'10001'))) and ((`emp_no` < _binary'11000') or ((`emp_no` = _binary'11000')))) lock in share mode
      )
2019-12-07T14:44:02.868592Z     1761864 Connect root@localhost on wjq using TCP/IP
2019-12-07T14:44:02.868724Z     1761864 Query   SELECT @@max_allowed_packet
2019-12-07T14:44:02.868838Z     1761864 Query   SET autocommit=true
2019-12-07T14:44:02.868933Z     1761864 Query   SET NAMES utf8mb4
2019-12-07T14:44:02.869067Z     1761864 Query   insert /* gh-ost */ into `wjq`.`_employees_ghc`
                                (id, hint, value)
                        values
                                (NULLIF(1, 0), 'heartbeat', '2019-12-07T22:44:02.863248803+08:00')
                        on duplicate key update
                                last_update=NOW(),
                                value=VALUES(value)
2019-12-07T14:44:02.869440Z     1761863 Query   SELECT @@max_allowed_packet
2019-12-07T14:44:02.879039Z     1761853 Query   insert /* gh-ost */ into `wjq`.`_employees_ghc`
                                (id, hint, value)
                        values
                                (NULLIF(0, 0), 'copy iteration 0 at 1575729842', 'Copy: 0/299387 0.0%; Applied: 0; Backlog: 0/1000; Time: 4s(total), 1s(copy); streamer: mysql3306.000064:8258392; State: migra
ting; ETA: N/A')
                        on duplicate key update
                                last_update=NOW(),
                                value=VALUES(value)

循环到最大的id,之后依赖binlog 增量同步

需要注意的是

rowcopy过程中是对原表加上 lock in share mode,防止数据在copy的过程中被修改。这点对后续理解整体的数据迁移非常重要。因为gh-ost在copy的过程中不会修改这部分数据记录。对于解析binlog获得的 INSERT ,UPDATE,DELETE事件我们只需要分析copy数据之前log before copy 和copy数据之后 log after copy。整体的数据迁移会在后面做详细分析。

4.1、rowcopy数据和应用binlog顺序不同是否产生数据冲突

数据迁移过程中sql映射关系:

Online DDL 工具 gh-ost原理(二)

rowcopy和binlog应用各种排列组合:

数据迁移过程,涉及三个操作:A:对原表进行rowcopy;B:应用程序的DML;C:应用binlog到新表,因为DML操作才会记录binglog,所以C操作一定在B操作的后面,共有如下几种组合:

Online DDL 工具 gh-ost原理(二)Online DDL 工具 gh-ost原理(二)Online DDL 工具 gh-ost原理(二)

1.insert 操作

binlog是最权威的,gh-ost的原则是以binlog优先,所以无论任何顺序下,数据都是和binlog保持一致,如果rowcopy在后,会insert ignore,如果binlog apply在后会replace into掉。

2.update/delete 操作

对已经rowcopy过的数据,出现对原表的update/delete操作。这时候会全部通过binlog apply执行,注意binlog apply的update是对某一条记录的全部列覆盖更新,所以不会有累加的问题。

对尚未迁移的数据,出现对原表的update/delete操作。这时候对新表的binlog apply会是空操作,具体数据由rowcopy迁移。

特殊情况下:

先对原表更新完以后,rowcopy在binlog apply之前把数据迁移了过去,而在binlog event过来以后,会再次应用,这里有问题?其实结合gh-ost的binlog apply之前把数据迁移了过去,

而在binlog的sql映射规则,insert操作会被replace重新替换掉,update 会更新对应记录全部行,delete 会是空操作。最终数据还是一致的状态。

4.2、binlog同步数据何时结束?

copy完数据向_xxx_ghc写入status:AllEventsUpToLockProcessed:1533533052229905040,当binlogsyncer过滤到该值表示所有event都已应用

5、copy完成后进行原子性cut-over阶段

copy完数据之后进行原始表和影子表cut-over 切换

gh-ost的切换是原子性切换,基本是通过两个会话的操作来完成 。作者写了三篇文章解释cut-over操作的思路和切换算法。详细的思路请移步到下面的链接。

http://code.openark.org/blog/mysql/solving-the-non-atomic-table-swap-take-iii-making-it-atomic

http://code.openark.org/blog/mysql/solving-the-non-atomic-table-swap-take-ii

http://code.openark.org/blog/mysql/solving-the-facebook-osc-non-atomic-table-swap-problem

Online DDL 工具 gh-ost原理(二)

5.1) C10:

创建magic表_xxx_del,目的为了防止过快的进行rename操作和意外情况rename

对源表和magic表_xxx_del加write锁

5.2) C11…C19: 新的请求进来,关于原表的请求被blocked

5.3) C20: 执行:rename table `t4` to `_t4_del`,`_t4_gho` to `t4`;这时被阻塞,timeout:3s。(这一步只有binlog event应用完成后)

5.4) 检查是否有blocked 的RENAME请求,通过show processlist

5.5) C10:

删除magic表(只有show processlist里存在被block的rename才进行)

释放琐

不同阶段失败后如何处理:

如果5.1失败,退出程序,比如建表成功,加锁失败,退出程序,未加锁

rename请求来的时候,会话C10死掉,lock会自动释放,同时因为_xxx_del的存在rename也会失败,所有请求恢复正常

rename被blocked的时候,会话C10死掉,lock会自动释放,同样因为_xxx_del的存在,rename会失败,所有请求恢复正常

C20死掉,gh-ost会捕获不到rename,会话C10继续运行,释放lock,所有请求恢复正常

6、清理战场

关闭binlogsyncer连接

删除源表和_xxxx_ghc表

至于删除中间表 ,其实和参数有关 –initially-drop-ghost-table –initially-drop-old-table。

参考链接

https://www.cnblogs.com/mysql-dba/p/9901589.html

https://cloud.tencent.com/developer/article/1512199

https://www.cnblogs.com/zhoujinyi/p/9187421.html


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:Online DDL 工具 gh-ost原理(二)
喜欢 (4)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址