MySQL工具之gh-ost原理解析

GrowthDBA 2021-09-04

7008

“知其然，不知其所以然，所以飘飘然。”

完整学习一个知识，还是要了解原理，不管是从技术深度还是能力提升上，都有一定帮助。同时只有深入了解后，才能看出优秀开源工具的设计精妙之处，并且出了问题也可以快速定位问题，运用工具时更加得心应手。今天就来深入学习了解一下gh-ost的原理。

前言

上篇文章MySQL工具之gh-ost介绍了gh-ost的参数、操作模式、使用方法、核心特性（暂停、动态修改参数进行流控）等，今天主要从源码方面入手解释gh-ost的执行过程、数据迁移、切换细节设计。

准备工作：

因为数据库没有开启general log，所以，为了实验顺利进行下去，我们先把数据库的general log打开。

mysql> SET GLOBAL log_output=file;
mysql> SET GLOBAL general_log_file='/mysql/mysql3307/general.log';
mysql> SET GLOBAL general_log=ON;
复制

这样，指定目录下就会生成一个general.log文件。

开启后，我们再使用gh-ost对表`employees`.`employees_test`表进行一次重建。

原理解读

执行过程（通过general log的输出来观察）

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

a. 测试数据库的连通性，做一些简单配置，获取数据库版本、端口、主机名信息；

b. 校验当前用户权限；

c. 获取binlog的一些配置信息（开启状态、binlog格式、binlog的行镜像）；

d. 查看表的状态（存储引擎、行格式等）；

e. 检查表相关的外键信息；

f. 检查是否有触发器；

g. 表行数预估；

h. 确定共享主键；

i. 获取表的字段信息；

# a. 测试数据库的连通性，做一些简单配置，获取数据库版本、端口、主机名信息
2021-09-02T16:22:55.149411+08:00    365 Connect ghost@172.23.6.223 on employees using TCP/IP
2021-09-02T16:22:55.149832+08:00    365 Query SET autocommit=true
2021-09-02T16:22:55.150115+08:00    365 Query  SET NAMES utf8mb4
2021-09-02T16:22:55.150473+08:00    365 Query  select @@global.version
2021-09-02T16:22:55.150970+08:00    365 Query  select @@global.port
2021-09-02T16:22:55.151314+08:00    365 Query  select @@global.hostname, @@global.port

# b. 校验当前用户权限
2021-09-02T16:22:55.151621+08:00    365 Query  show /* gh-ost */ grants for current_user()

# c. 获取binlog的一些配置信息（开启状态、binlog格式、binlog的行镜像）
2021-09-02T16:22:55.151953+08:00    365 Query  select @@global.log_bin, @@global.binlog_format
2021-09-02T16:22:55.152160+08:00    365 Query  select @@global.binlog_row_image

# d. 查看表的状态（存储引擎、行格式等）
2021-09-02T16:22:55.152485+08:00    365 Query  show /* gh-ost */ table status from `employees` like 'employees_test'

# e. 检查表相关的外键信息
2021-09-02T16:22:55.153993+08:00    365 Query  SELECT
      SUM(REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_SCHEMA='employees' AND TABLE_NAME='employees_test') as num_child_side_fk,
      SUM(REFERENCED_TABLE_NAME IS NOT NULL AND REFERENCED_TABLE_SCHEMA='employees' AND REFERENCED_TABLE_NAME='employees_test') as num_parent_side_fk
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
        REFERENCED_TABLE_NAME IS NOT NULL
        AND ((TABLE_SCHEMA='employees' AND TABLE_NAME='employees_test')
          OR (REFERENCED_TABLE_SCHEMA='employees' AND REFERENCED_TABLE_NAME='employees_test')
        )

# f. 检查是否有触发器
2021-09-02T16:22:55.162290+08:00    365 Query  SELECT COUNT(*) AS num_triggers
      FROM INFORMATION_SCHEMA.TRIGGERS
      WHERE
        TRIGGER_SCHEMA='employees'
        AND EVENT_OBJECT_TABLE='employees_test'

# g. 表行数预估
2021-09-02T16:22:55.163104+08:00    365 Query  explain select /* gh-ost */ * from `employees`.`employees_test` where 1=1

# h. 确定共享主键
2021-09-02T16:22:55.163804+08:00    365 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 = 'employees'
        AND TABLE_NAME = 'employees_test'
      GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
    ) AS UNIQUES
    ON (
      COLUMNS.COLUMN_NAME = UNIQUES.FIRST_COLUMN_NAME
    )
    WHERE
      COLUMNS.TABLE_SCHEMA = 'employees'
      AND COLUMNS.TABLE_NAME = 'employees_test'
    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

# i. 获取表的字段信息
2021-09-02T16:22:55.165275+08:00    365 Query  show columns from `employees`.`employees_test`
复制

2、模拟Slave，获取当前的Postion位点信息，创建binlog steamer监听binlog

a. 查看主库状态；

b. 创建连接；

c. 设置master_binlog_checksum='NONE'；

d. 指定binlog位点信息，创建监听；

# a. 查看主库状态
2021-09-02T16:22:55.168551+08:00    365 Query show /* gh-ost readCurrentBinlogCoordinates */ master status

# b. 创建连接
2021-09-02T16:22:55.169753+08:00    367 Connect  ghost@172.23.6.223 on  using TCP/IP

# c. 设置master_binlog_checksum='NONE'
2021-09-02T16:22:55.169963+08:00    367 Query  SHOW GLOBAL VARIABLES LIKE 'BINLOG_CHECKSUM'
2021-09-02T16:22:55.170842+08:00    367 Query  SET @master_binlog_checksum='NONE'

# d. 指定binlog位点信息，创建监听
2021-09-02T16:22:55.171407+08:00    367 Binlog Dump Log: 'mysql-bin.000012'  Pos: 194
复制

3、创建日志记录表_employees_test_ghc和幽灵表_employees_test_gho，并且执行ALTER语句将幽灵表变更为目标表结构。同时，gh-ost会将核心步骤记录到_employees_test_ghc表中。

a. 创建日志记录表_employees_test_ghc；

b. 创建幽灵表；

c. 将DDL语句在幽灵表上执行，使幽灵表变成目标表结构；

d. 核心步骤开始录入日志记录表_employees_test_ghc；

# a. 创建日志记录表_employees_test_ghc
2021-09-02T16:22:55.175452+08:00    365 Query drop /* gh-ost */ table if exists `employees`.`_employees_test_ghc`
2021-09-02T16:22:55.177621+08:00    365 Query  create /* gh-ost */ table `employees`.`_employees_test_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

# b. 创建幽灵表
2021-09-02T16:22:55.226074+08:00    365 Query  create /* gh-ost */ table `employees`.`_employees_test_gho` like `employees`.`employees_test`

# c. 将DDL语句在幽灵表上执行，使幽灵表变成目标表结构
2021-09-02T16:22:55.264914+08:00    365 Query  alter /* gh-ost */ table `employees`.`_employees_test_gho` engine=innodb

# d. 核心步骤开始录入日志记录表_employees_test_ghc
2021-09-02T16:22:55.311458+08:00    365 Query  insert /* gh-ost */ into `employees`.`_employees_test_ghc`
        (id, hint, value)
      values
        (NULLIF(2, 0), 'state', 'GhostTableMigrated')
      on duplicate key update
        last_update=NOW(),
        value=VALUES(value)
复制

4、数据迁移，通过INSERT IGNORE INTO _employees_test_gho SELECT * FROM employees_test;拷贝数据的方式进行拷贝。获取当前最大和最小主键值，根据命令行传参chunk获取数据INSERT到幽灵表里面。

a. 获取最小键值；

b. 获取最大键值；

c. 获取第一个chunk（迭代器）；

d. 循环插入数据到目标表；

# a. 获取最小主键值
2021-09-02T16:22:55.325197+08:00    365 Query select /* gh-ost `employees`.`employees_test` */ `emp_no`
        from
          `employees`.`employees_test`
        order by
          `emp_no` asc
        limit 1

# b. 获取最大主键值
2021-09-02T16:22:55.325577+08:00    370 Query  select /* gh-ost `employees`.`employees_test` */ `emp_no`
        from
          `employees`.`employees_test`
        order by
          `emp_no` desc
        limit 1

# c. 获取第一个chunk（迭代器）
2021-09-02T16:22:56.327727+08:00    370 Query  select  /* gh-ost `employees`.`employees_test` iteration:0 */
            `emp_no`
          from
            `employees`.`employees_test`
          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

# d. 循环插入数据到目标表
2021-09-02T16:22:56.330424+08:00    370 Query  insert /* gh-ost `employees`.`employees_test` */ ignore into `employees`.`_employees_test_gho` (`emp_no`, `birth_date`, `first_name`, `last_name`, `ge
nder`, `hire_date`)
      (select `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` from `employees`.`employees_test` 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
      )
2021-09-02T16:22:56.362458+08:00    370 Query  COMMIT
复制

注意：row-copy过程是对原表加上lock in share mode，防止数据在copy过程中被修改。因为gh-ost在copy的过程中不会修改这部分数据。对于后续增量解析binlog获得的INSERT、UPDATE、DELETE events我们只需要分析copy数据之前的log before copy和copy数据之后的log after copy。

5、增量应用binlog迁移数据

核心代码在gh-ost/go/sql/builder.go中，这里主要做DML转换的解释，当然还有其他函数做辅助工作，比如数据库，表名校验以及语法完整性校验。

解析到DELETE语句转换为DELETE语句：

func BuildDMLDeleteQuery(databaseName, tableName string, tableColumns, uniqueKeyColumns *ColumnList, args []interface{}) (result string, uniqueKeyArgs []interface{}, err error) {
（...代码省略...）
  result = fmt.Sprintf(`
      delete * gh-ost %s.%s */
        from
          %s.%s
        where
          %s
    `, databaseName, tableName,
    databaseName, tableName,
    equalsComparison,
  )
  return result, uniqueKeyArgs, nil
}
复制

解析到INSERT语句对应转换为REPLACE INTO语句：

func BuildDMLInsertQuery(databaseName, tableName string, tableColumns, sharedColumns, mappedSharedColumns *ColumnList, args []interface{}) (result string, sharedArgs []interface{}, err error) {
（...代码省略...）
  result = fmt.Sprintf(`
      replace * gh-ost %s.%s */ into
        %s.%s
          (%s)
        values
          (%s)
    `, databaseName, tableName,
    databaseName, tableName,
    strings.Join(mappedSharedColumnNames, ", "),
    strings.Join(preparedValues, ", "),
  )
  return result, sharedArgs, nil
}
复制

解析到UPDATE语句对应转换为UPDATE语句：

func BuildDMLUpdateQuery(databaseName, tableName string, tableColumns, sharedColumns, mappedSharedColumns, uniqueKeyColumns *ColumnList, valueArgs, whereArgs []interface{}) (result string, sharedArgs, uniqueKeyArgs []interface{}, err error) {
（...代码省略...）
  result = fmt.Sprintf(`
       update * gh-ost %s.%s */
           %s.%s
        set
          %s
        where
           %s
     `, databaseName, tableName,
    databaseName, tableName,
    setClause,
    equalsComparison,
  )
  return result, sharedArgs, uniqueKeyArgs, nil
}
复制

数据迁移的数据一致性分析：

gh-ost做DDL变更期间对原表和幽灵表的操作有三种：

1、对原表的row-copy（我们用A来代替）；

2、业务上对原表的DML操作（我们用B来代替）；

3、对幽灵表的增量apply binlog操作（我们用C来代替）。

数据迁移过程中的SQL映射关系：

我们都知道，binlog是基于DML操作产生的，因此对幽灵表的增量apply binlog一定是在对原表的DML之后。

共有如下几种排列组合：

A → B → C：数据先copy到幽灵表

B → C → A：数据还未copy到幽灵表，先应用DML的binlog、incremental apply binlog

B → A → C：数据还未copy到幽灵表，先应用DML的binlog

说明：

①INSERT操作：binlog是最权威的，gh-ost的原则是以binlog优先，所以无论任何顺序下，数据都是和binlog保持一致，如果row-copy在后，会insert ignore，如果incremental apply binlog在后会replace into掉。

②UPDATE/DELETE操作：对已经row-copy过的数据，出现对原表的update/delete操作。这时候会全部通过incremental apply binlog执行，注意incremental apply binlog的update是对某一条记录的全部列覆盖更新，所以不会有累加的问题。对尚未迁移的数据，出现对原表的update/delete操作。这时候对新表的incremental apply binlog会是空操作，具体数据由row-copy迁移。

③特殊情况下：先对原表更新完以后，row-copy在incremental apply binlog之前把数据迁移了过去，而在binlog event过来以后，会再次应用，这里有问题？其实结合gh-ost的incremental apply binlog之前把数据迁移了过去，而在binlog的sql映射规则，insert操作会被replace重新替换掉，update会更新对应记录全部行，delete会是空操作。最终数据还是一致的状态。

通过上面的几种组合操作的分析，我们可以看到数据最终是一致的。尤其是当row-copy结束之后，只剩下incremental apply binlog，情况更简单。

小提示

MySQL中常用的三种插入数据方法的差异：

1、INSERT INTO ...，表示插入数据，数据库会检查主键（Primary Key），如果重复会报错；

2、REPLACE INTO ...，表示插入替换数据，需求表中如果有主键（Primary Key），或者唯一索引（Unique Key），如果数据已经存在，则用新数据替换，如果数据不存在则和INSERT INTO ...效果一样；

3、INSERT IGNORE ...，表示如果表中已存在相同的记录，则忽略新插入的数据。

6、原始表与幽灵表的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

其切换原理是基于MySQL内部机制：被lock table阻塞之后，执行rename的优先级高于DML，也即先执行rename table，然后执行DML。

作者在第一个连接的文章中举了个例子：

例子讲解：假设gh-ost操作的会话是C10到C20，其他业务的DML请求的会话是C1-C9、C11-C19、C21-C29。

1、C1..C9：对tbl表正常进行DML操作

2、C10：创建magic表，防止提前RENAME表，导致数据丢失：
        CREATE TABLE tbl_old (id int primary key) COMMENT='magic-be-here'

3、C10：执行锁表操作：
        LOCK TABLES tbl WRITE, tbl_old WRITE
4、C11..C19：新传入的DML或SELECT请求，因表tbl表上有LOCK而被阻塞

5、C20：执行RENAME操作
        RENAME TABLE tbl TO tbl_old, ghost TO tbl
C20的RENAME因为C10的表锁而等待，但是RENAME TABLE请求优先级会高于前面步骤会话C11..C19、C1..C9的DML请求

6、C21..C29：新传入的DML或SELECT请求，但由于C10的LOCK TABLE和C20的RENAME TABLE被阻塞，在队列中等待

7、C10：检查会话C20的RENAME是否已应用（在进程列表中查找被阻止的RENAME）

8、C10：基于步骤7，执行DROP TABLE操作
        DROP TABLE tbl_old
删除命令执行完成，tbl仍然被锁定，所有DML请求仍然被阻止。

9、C10：解锁表
        UNLOCK TABLES
会话C20的RENAME命令第一个被执行，ghost表替换为tbl表。其他会话C1..C9、C11..C19、C21..C29的请求可以操作新表tbl。
复制

再来看一下真实执行的情况：

# a. 创建_del表，防止提前RENAME表，导致数据丢失
2021-09-02T16:23:08.489086+08:00 365 Query create * gh-ost */ table `employees`.`_employees_test_20210902162255_del` (
      id int auto_increment primary key
    ) engine=InnoDB comment='ghost-cut-over-sentry'

# b. 执行LOCK TABLES xxx WRITE
2021-09-02T16:23:08.526005+08:00 372 Query lock * gh-ost */ tables `employees`.`employees_test` write, `employees`.`_employees_test_20210902162255_del` write
（...日志省略...）

# c. 设置锁等待时间并执行RNAME操作
2021-09-02T16:23:09.488681+08:00 373 Query set session lock_wait_timeout:=1
2021-09-02T16:23:09.488814+08:00 365 Query select id
      from information_schema.processlist
      where
        id != connection_id()
        and 373 in (0, id)
        and state like concat('%', 'metadata lock', '%')
        and info like concat('%', 'rename', '%')
2021-09-02T16:23:09.488924+08:00 373 Query rename * gh-ost */ table `employees`.`employees_test` to `employees`.`_employees_test_20210902162255_del`, `employees`.`_employees_test_gho` to `employees`.`employees_test`
2021-09-02T16:23:09.489622+08:00 365 Query select is_used_lock('gh-ost.372.lock')

# d. 删除_del表
2021-09-02T16:23:09.489910+08:00 372 Query drop * gh-ost */ table if exists `employees`.`_employees_test_20210902162255_del`

# e. 执行UNLOCK TABLES
2021-09-02T16:23:09.506443+08:00 372 Query unlock tables
2021-09-02T16:23:09.506597+08:00 372 Query ROLLBACK
2021-09-02T16:23:09.523310+08:00 373 Query ROLLBACK
2021-09-02T16:23:09.523513+08:00 365 Query show * gh-ost */ table status from `employees` like '_employees_test_20210902162255_del'
2021-09-02T16:23:09.524421+08:00 365 Quit
2021-09-02T16:23:09.625093+08:00 372 Query drop * gh-ost */ table if exists `employees`.`_employees_test_ghc`
2021-09-02T16:23:09.641160+08:00 373 Quit
复制

※ 此处设计的精妙之处：（重点）

1、创建_del表是为了防止cut-over提前执行，导致表数据丢失。

2、同一个会话先执行LOCK TABLES ... WRITE之后还是可以执行FROP操作的。

3、无论RENAME TABLE和DML操作谁先执行，被阻塞后RENAME TABLE总是优先于DML被执行。

如果cut-over过程的各个环节执行失败会发生什么？

其实除了安全，什么都不会发生。

* 如果C10的CREATE TABLE `tbl_old`失败，gh-ost程序退出。
* 如果C10的加锁语句失败，gh-ost程序退出，因为表还未被锁定，DML请求可以正常进行。
* 如果C10在C20执行RENAME之前出现异常
    A. C10持有的锁被释放，查询C1-C9，C11-C19的请求可以立即在原表tbl执行。
    B. 因为`tbl_old`表存在，C20的RENAME TABLE tbl to `tbl_old`会失败。
    C. 整个操作都失败了，但没有什么可怕的事情发生，有些查询被阻止了一段时间，我们需要重试。
* 如果C10在C20执行rename被阻塞时失败退出，与上述类似，锁释放，则C20执行RENAME操作因为tbl_old表存在而失败，所有请求恢复正常。
* 如果C20异常失败，gh-ost会捕获不到RENAME，会话C10继续运行，释放LOCK，所有请求恢复正常。
* 如果C10和C20都失败了，没问题：LOCK被清除，RENAME锁被清除。C1-C9，C11-C19，C21-C29可以在tbl上正常执行。
复制

整个过程对应用程序的影响：

应用程序对表的写操作被阻止，直到交换幽灵表成功或直到操作失败。如果成功，则应用程序继续在新表上进行操作。如果切换失败，应用程序继续继续在原表上进行操作。

对复制的影响：

Slave因为binlog文件中不会复制LOCK语句，只能应用RENAME语句进行原子操作，对复制无损。

7、处理收尾工作：

最后一部分操作其实和具体参数有一定关系。最重要必不可少的是：

* 关闭binlogsyncer连接
* 删除_ghc日志表
* 至于中间表，其实和参数有关：
--initially-drop-ghost-table（gh-ost操作之前，检查并删除已经存在的ghost表。该参数不建议使用，请手动处理原来存在的ghost表。默认不启用该参数，gh-ost直接退出操作）
--initially-drop-old-table（gh-ost操作之前，检查并删除已经存在的旧表。该参数不建议使用，请手动处理原来存在的ghost表。默认不启用该参数，gh-ost直接退出操作）
复制

小结

今天从gh-ost详细的执行过程中分析了一下gh-ost原理。尤其是对cut-over设计思路之精妙，原子操作，任何异常都不会对业务有严重影响。文末再来回顾一下cut-over的整个过程。

参考资料

爱可生开源社区公众号-杭州有赞DBA杨奇龙-《gh-ost原理剖析》

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

end

mysql

文章转载自GrowthDBA，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL工具之gh-ost原理解析

评论

相关阅读