接上一篇文章Online DDL 工具 gh-ost实战（一），介绍了gh-ost的实际使用案例，本文介绍一下gh-osh的相关原理；
gh-ost 放弃了触发器，使用 binlog 来同步。gh-ost 作为一个伪装的备库，可以从主库/备库上拉取 binlog，过滤之后重新应用到主库上去，相当于主库上的增量操作通过 binlog 又应用回主库本身，不过是应用在幽灵表上。
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，在备库上观察变更结果是否正确，再对主库操作，心里更有底。
一、三种模式架构图
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 时间会很长，务必会对线上数据库使用造成影响，所以如果使用模式三做测试也要在线下数据库。
二、原理
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映射关系：
rowcopy和binlog应用各种排列组合：
数据迁移过程，涉及三个操作：A:对原表进行rowcopy；B:应用程序的DML;C:应用binlog到新表，因为DML操作才会记录binglog，所以C操作一定在B操作的后面,共有如下几种组合:
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
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原理（二）
