MySQL Online DDL 工具 gh-ost 使用示例 -- cnDBA.cn

1 gh-ost 说明

在之前的博客中，我们介绍了MySQL的Online DDL，如下：

MySQL 5.7 Online DDL 说明
https://www.cndba.cn/dave/article/131460http://www.cndba.cn/dave/article/131461

gh-ost 就是第三方的online ddl工具，其不依赖于触发器,而是通过模拟从库,在row binlog中获取增量变更,再异步应用到ghost表。

官网下载地址：

https://github.com/github/gh-ost/releaseshttp://www.cndba.cn/dave/article/131461

我们这里直接下载rpm包，然后安装：

[dave@www.cndba.cn:/software]# rpm -ivh gh-ost-1.1.5-1.x86_64.rpm 
准备中...                          ################################# [100%]
正在升级/安装...
   1:gh-ost-1:1.1.5-1                 ################################# [100%]
[dave@www.cndba.cn:/software]# gh
gh-ost       ghostscript  
[dave@www.cndba.cn:/software]# gh-ost --help

关于gh-ost的使用限制直接参考之前online DDL的博客。

DDL 的过程如下：
① 检查有没有外键和触发器。
② 检查表的主键信息。
③ 检查是否主库或从库，是否开启log_slave_updates，以及binlog信息
④ 检查gho和del结尾的临时表是否存在
⑤ 创建ghc结尾的表，存数据迁移的信息，以及binlog信息等
—-以上校验阶段
⑥ 初始化stream的连接,添加binlog的监听
—-以下迁移阶段
⑥ 创建gho结尾的临时表，在gho结尾的临时表上执行DDL
⑦ 开启事务，按照主键id把源表数据写入到gho结尾的表上，再提交，以及binlog apply。
—-以下cut-over阶段
⑧ lock源表，rename 表：rename 源表 to 源_del表，gho表 to 源表。
⑨ 清理ghc表。http://www.cndba.cn/dave/article/131461

注意：

pt-osc是多线程处理的，DDL执行速度较快。
gh-ost是模拟“从”的单线程应用，极端的情况下，DDL执行较慢。

2 操作示例

2.1 命令帮助

可以通过gh-ost —help 命令查看。

主要参数如下：

1.-allow-master-master: 是否允许gh-ost运行在双主复制架构中，一般与-assume-master-host参数一起使用
2.-allow-nullable-unique-key:允许gh-ost在数据迁移依赖的唯一键可以为NULL，默认为不允许为NULL的唯一键。如果数据迁移(migrate)依赖的唯一键允许NULL值，则可能造成数据不正确，请谨慎使用。
3.-allow-on-master:允许gh-ost直接运行在主库上。默认gh-ost连接的从库。
4.-alter string:DDL语句
5.-assume-master-host string:为gh-ost指定一个主库，格式为”ip:port”或者”hostname:port”。在这主主架构里比较有用，或则在gh-ost发现不到主的时候有用。
6.-assume-rbr:确认gh-ost连接的数据库实例的binlog_format=ROW的情况下，可以指定-assume-rbr，这样可以禁止从库上运行stop slave,start slave,执行gh-ost用户也不需要SUPER权限。
7.-chunk-size int:在每次迭代中处理的行数量(允许范围：100-100000)，默认值为1000。
8.-concurrent-rowcount:该参数如果为True(默认值)，则进行row-copy之后，估算统计行数(使用explain select count(*)方式)，并调整ETA时间，否则，gh-ost首先预估统计行数，然后开始row-copy。
9.-conf string:gh-ost的配置文件路径。
10.-critical-load string:一系列逗号分隔的status-name=values组成，当MySQL中status超过对应的values，gh-ost将会退出。-critical-load Threads_connected=20,Connections=1500，指的是当MySQL中的状态值Threads_connected>20,Connections>1500的时候，gh-ost将会由于该数据库严重负载而停止并退出。
11.-critical-load-hibernate-seconds int :负载达到critical-load时，gh-ost在指定的时间内进入休眠状态。它不会读/写任何来自任何服务器的任何内容。
12.-critical-load-interval-millis int:当值为0时，当达到-critical-load，gh-ost立即退出。当值不为0时，当达到-critical-load，gh-ost会在-critical-load-interval-millis秒数后，再次进行检查，再次检查依旧达到-critical-load，gh-ost将会退出。
13.-cut-over string:选择cut-over类型:atomic/two-step，atomic(默认)类型的cut-over是github的算法，two-step采用的是facebook-OSC的算法。
14.-cut-over-exponential-backoff
15.-cut-over-lock-timeout-seconds int:gh-ost在cut-over阶段最大的锁等待时间，当锁超时时，gh-ost的cut-over将重试。(默认值：3)
16.-database string:数据库名称。
17.-default-retries int:各种操作在panick前重试次数。(默认为60)
18.-dml-batch-size int:在单个事务中应用DML事件的批量大小（范围1-100）（默认值为10）
19.-exact-rowcount:准确统计表行数(使用select count(*)的方式)，得到更准确的预估时间。
20.-execute:实际执行alter&migrate表，默认为noop，不执行，仅仅做测试并退出，如果想要ALTER TABLE语句真正落实到数据库中去，需要明确指定-execute
21.-exponential-backoff-max-interval int
22.-force-named-cut-over:如果为true，则'unpostpone | cut-over'交互式命令必须命名迁移的表
23.-heartbeat-interval-millis int:gh-ost心跳频率值，默认为500
24.-initially-drop-ghost-table:gh-ost操作之前，检查并删除已经存在的ghost表。该参数不建议使用，请手动处理原来存在的ghost表。默认不启用该参数，gh-ost直接退出操作。
25.-initially-drop-old-table:gh-ost操作之前，检查并删除已经存在的旧表。该参数不建议使用，请手动处理原来存在的ghost表。默认不启用该参数，gh-ost直接退出操作。
26.-initially-drop-socket-file:gh-ost强制删除已经存在的socket文件。该参数不建议使用，可能会删除一个正在运行的gh-ost程序，导致DDL失败。
27.-max-lag-millis int:主从复制最大延迟时间，当主从复制延迟时间超过该值后，gh-ost将采取节流(throttle)措施，默认值：1500s。
28.-max-load string:逗号分隔状态名称=阈值，如：'Threads_running=100,Threads_connected=500'. When status exceeds threshold, app throttles writes
29.-migrate-on-replica:gh-ost的数据迁移(migrate)运行在从库上，而不是主库上。
30.-nice-ratio float:每次chunk时间段的休眠时间，范围[0.0…100.0]。0：每个chunk时间段不休眠，即一个chunk接着一个chunk执行；1：每row-copy 1毫秒，则另外休眠1毫秒；0.7：每row-copy 10毫秒，则另外休眠7毫秒。
31.-ok-to-drop-table:gh-ost操作结束后，删除旧表，默认状态是不删除旧表，会存在_tablename_del表。
32.-panic-flag-file string:当这个文件被创建，gh-ost将会立即退出。
33.-password string :MySQL密码
34.-port int ：MySQL端口，最好用从库
35.-postpone-cut-over-flag-file string：当这个文件存在的时候，gh-ost的cut-over阶段将会被推迟，数据仍然在复制，直到该文件被删除。
36.-skip-foreign-key-checks:确定你的表上没有外键时，设置为'true'，并且希望跳过gh-ost验证的时间-skip-renamed-columns ALTER
37.-switch-to-rbr:让gh-ost自动将从库的binlog_format转换为ROW格式。
38.-table string:表名
39.-throttle-additional-flag-file string:当该文件被创建后，gh-ost操作立即停止。该参数可以用在多个gh-ost同时操作的时候，创建一个文件，让所有的gh-ost操作停止，或者删除这个文件，让所有的gh-ost操作恢复。
40.-throttle-control-replicas string:列出所有需要被检查主从复制延迟的从库。
41.-throttle-flag-file string:当该文件被创建后，gh-ost操作立即停止。该参数适合控制单个gh-ost操作。-throttle-additional-flag-file string适合控制多个gh-ost操作。
42.-throttle-query string:节流查询。每秒钟执行一次。当返回值=0时不需要节流，当返回值>0时，需要执行节流操作。该查询会在数据迁移(migrated)服务器上操作，所以请确保该查询是轻量级的。
43.-timestamp-old-table:在旧表名中使用时间戳。这会使旧表名称具有唯一且无冲突的交叉迁移。
44.-user string :MYSQL用户

2.2 单实例上DDL（主库）

在单个实例（主库）使用go-ost时需要添加—allow-on-master参数，并启用ROW模式。

我们给ustc库的dave 表加一列，完整的SQL如下：

ALTER TABLE dave ADD COLUMN url varchar(200);

[root@gbase03:~]# gh-ost -max-load=Threads_running=20 -critical-load=Threads_running=100 -chunk-size=2000 -user="root" -password="Qft3pRB7Z6Yf2SjB" -host="127.0.0.1" -port 3308 -allow-on-master -database="ustc" -table="dave" -alter="ADD COLUMN url varchar(200)" -cut-over=default -exact-rowcount -concurrent-rowcount -default-retries=120  -timestamp-old-table -assume-rbr -panic-flag-file=/tmp/ghost.panic.flag -execute

输出内容：

[2023/10/17 14:40:21] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql 127.0.0.1 3308 root    false false <nil> false UTC true 0 0s 0s 0 false}
[2023/10/17 14:40:21] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql-bin.000020, 274)
[2023/10/17 14:40:21] [info] binlogsyncer.go:203 register slave for master server 127.0.0.1:3308
[2023/10/17 14:40:21] [info] binlogsyncer.go:723 rotate to (mysql-bin.000020, 274)
# Migrating `ustc`.`dave`; Ghost table is `ustc`.`_dave_gho`
# Migrating gbase03:3308; inspecting gbase03:3308; executing on gbase03
# Migration started at Tue Oct 17 14:40:21 +0800 2023
# chunk-size: 2000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=20; critical-load: Threads_running=100; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle 
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.ustc.dave.sock
Copy: 0/7 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000020:3059; Lag: 0.05s, HeartbeatLag: 0.05s, State: migrating; ETA: N/A
Copy: 0/7 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000020:7515; Lag: 0.03s, HeartbeatLag: 0.05s, State: migrating; ETA: N/A
Copy: 7/7 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000020:8910; Lag: 0.03s, HeartbeatLag: 0.06s, State: migrating; ETA: due
Copy: 7/7 100.0%; Applied: 0; Backlog: 1/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000020:14897; Lag: 0.03s, HeartbeatLag: 0.05s, State: migrating; ETA: due
# Migrating `ustc`.`dave`; Ghost table is `ustc`.`_dave_gho`
# Migrating gbase03:3308; inspecting gbase03:3308; executing on gbase03
# Migration started at Tue Oct 17 14:40:21 +0800 2023
# chunk-size: 2000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=20; critical-load: Threads_running=100; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle 
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.ustc.dave.sock
Copy: 7/7 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000020:15386; Lag: 0.03s, HeartbeatLag: 0.06s, State: migrating; ETA: due
Copy: 7/7 100.0%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 1s(copy); streamer: mysql-bin.000020:20057; Lag: 0.03s, HeartbeatLag: 0.05s, State: migrating; ETA: due
[2023/10/17 14:40:25] [info] binlogsyncer.go:164 syncer is closing...
[2023/10/17 14:40:25] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2023/10/17 14:40:25] [info] binlogsyncer.go:179 syncer is closed
# Done
[root@gbase03:~]#

操作过程中会生成两个中间状态的表：_b_ghc,_b_gho：

_b_ghc 是记录 gh-ost 执行过程的表；
_b_gho 是目标表，也即应用 ddl 语句的幽灵表。

进行 cut-over 切换幽灵表和原表时，默认不删除幽灵表。因为如果是对大表操作，直接删除上百 G 会对磁盘 IO 有一定的影响。可以通过如下2个选型控制：

—ok-to-drop-table:gh-ost操作结束后，删除旧表，默认状态是不删除旧表，会存在_tablename_del表。
—timestamp-old-table：最终rename的时候表名会加上时间戳后缀，每次执行的时候都会生成一个新的表名。

2.3 主从复制环境执行DDL

如果存在从库或者只读实例，需要控制 gh-ost 工具不产生太大同步延迟，那么可以添加两个参数：-max-lag-millis 和 -throttle-control-replicas。 http://www.cndba.cn/dave/article/131461

gh-ost -max-load=Threads_running=20 -critical-load=Threads_running=100  -max-lag-millis=5000 -throttle-control-replicas="192.168.56.107:3308"  -chunk-size=2000 -user="root" -password="Qft3pRB7Z6Yf2SjB" -host="192.168.56.106" -port 3308  -allow-on-master -allow-master-master  -database="ustc" -table="cndba"  -alter="engine=innodb" -cut-over=default  -exact-rowcount -concurrent-rowcount -default-retries=120 -timestamp-old-table -assume-rbr -panic-flag-file=/tmp/ghost.panic.flag -execute

相关参数说明：

1.max-load=Threads_running=20         超过20个client在执行SQL查询时,暂停Online DDL操作
2.critical-load=Threads_running=100   超过100个client在执行SQL查询时,中断Online DDL操作
3.chunk-size=2000                     每一次同步操作处理2000行数据
4.max-lag-millis=5000                 控制延迟时间，延迟超过阈值会等待延迟进入阈值范围内继续
5.throttle-control-replicas="ip:port" 指定从库地址，多个地址可以用逗号分隔
6.allow-on-master                     允许在主库执行Online DDL相关的所有操作
7.alter                               Online DDL的操作,仅需要部分alter语句(方括号部分)
8.例：alter table sbtest.sbtest1 [add column t int not NULL]
9.cut-over=default                    数据同步完成后自动进行镜像表与源表的切换
10.exact-rowcount                      精确计算行数,提供更准确的进度
11.timestamp-old-table                 使用时间戳来命名旧表
12.assume-rbr                          跳过重启slave线程与row format检查,设置后无需super权限
13.panic-flag-file                     创建该文件后,会强制中断Online DDL操作

[dave@www.cndba.cn:~]# gh-ost -max-load=Threads_running=20 -critical-load=Threads_running=100  -max-lag-millis=5000 -throttle-control-replicas="192.168.56.107:3308"  -chunk-size=2000 -user="root" -password="Qft3pRB7Z6Yf2SjB" -host="192.168.56.106" -port 3308  -allow-on-master -allow-master-master  -database="ustc" -table="cndba"  -alter="engine=innodb" -cut-over=default  -exact-rowcount -concurrent-rowcount -default-retries=120 -timestamp-old-table -assume-rbr -panic-flag-file=/tmp/ghost.panic.flag -execute

输出日志：

[2023/10/17 15:52:30] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql 192.168.56.106 3308 root    false false <nil> false UTC true 0 0s 0s 0 false}
[2023/10/17 15:52:30] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql-bin.000003, 271363)
[2023/10/17 15:52:30] [info] binlogsyncer.go:203 register slave for master server 192.168.56.106:3308
[2023/10/17 15:52:30] [info] binlogsyncer.go:723 rotate to (mysql-bin.000003, 271363)
# Migrating `ustc`.`cndba`; Ghost table is `ustc`.`_cndba_gho`
# Migrating gbase01:3308; inspecting gbase01:3308; executing on gbase01
# Migration started at Tue Oct 17 15:52:30 +0800 2023
# chunk-size: 2000; max-lag-millis: 5000ms; dml-batch-size: 10; max-load: Threads_running=20; critical-load: Threads_running=100; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle 
# throttle-control-replicas count: 1
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.ustc.cndba.sock
Copy: 0/1 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000003:274143; Lag: 0.03s, HeartbeatLag: 0.03s, State: migrating; ETA: N/A
Copy: 1/1 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000003:278878; Lag: 0.02s, HeartbeatLag: 0.04s, State: migrating; ETA: due
Copy: 1/1 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000003:278878; Lag: 0.02s, HeartbeatLag: 0.05s, State: migrating; ETA: due
# Migrating `ustc`.`cndba`; Ghost table is `ustc`.`_cndba_gho`
# Migrating gbase01:3308; inspecting gbase01:3308; executing on gbase01
# Migration started at Tue Oct 17 15:52:30 +0800 2023
# chunk-size: 2000; max-lag-millis: 5000ms; dml-batch-size: 10; max-load: Threads_running=20; critical-load: Threads_running=100; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle 
# throttle-control-replicas count: 1
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.ustc.cndba.sock
Copy: 1/1 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000003:285097; Lag: 0.02s, HeartbeatLag: 0.04s, State: migrating; ETA: due
Copy: 1/1 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000003:285097; Lag: 0.02s, HeartbeatLag: 0.04s, State: migrating; ETA: due
[2023/10/17 15:52:32] [info] binlogsyncer.go:164 syncer is closing...
[2023/10/17 15:52:32] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2023/10/17 15:52:32] [info] binlogsyncer.go:179 syncer is closed
# Done
[dave@www.cndba.cn:~]#

2.4 操作事项：终止、暂停、限速、删除临时表

命令启动后有会升成2个文件，可以通过这2个文件来控制gh-ost的运行：

# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.ustc.cndba.sock

—panic-flag-file：标示文件终止运行。
—serve-socket-file：使用socket监听请求，操作者可以在命令运行后更改相应的参数。
创建socket文件进行监听，通过接口进行参数调整，当执行操作的过程中发现负载、延迟上升了，不得不终止操作，重新配置参数，如 chunk-size，然后重新执行操作命令，可以通过scoket接口进行动态调整。

2.4.1 暂停

echo throttle | socat - /tmp/gh-ost.[tablename].sock

2.4.2 恢复

echo no-throttle | socat - /tmp/gh-ost.[tablename].sock

2.4.3 停止

touch /tmp/ghost.panic.[tablename].flag

2.4.4 运行中查看状态

echo status | socat - /tmp/gh-ost.[tablename].sock
echo “chunk-size=?” | socat - /tmp/gh-ost.[tablename].sockhttp://www.cndba.cn/dave/article/131461http://www.cndba.cn/dave/article/131461

2.4.5 运行中调整限速

echo chunk-size=1500 | socat - /tmp/gh-ost.[tablename].sock
echo max-lag-millis=200 | socat - /tmp/gh-ost.[tablename].sock
echo max-load=Thread_running=30 | socat - /tmp/gh-ost.[tablename].sockhttp://www.cndba.cn/dave/article/131461

2.4.6 删除临时表

不建议在生产cut-over阶段立马删除原表，需考虑IO影响（不能添加参数ok-to-drop-table）；需在低峰期对原表做硬连接，循环清理遗留原表_[tablename]_del；

mysql> show tables like '%dave%';
+--------------------------+
| Tables_in_ustc (%dave%)  |
+--------------------------+
| _dave_20231017144021_del |
| dave                     |
+--------------------------+
2 rows in set (0.00 sec)

方法1：分批删除

如果源表有非常多的数据，尽量分批次删除。

delete from table tablename_old limit 5000;
或者在业务空闲时段用truncate table tablename_old清空表数据之后再 drop 表。http://www.cndba.cn/dave/article/131461

方法2：使用硬链接（快速删除大表）

将主+从上的原表做硬连接（集群实例数量较多建议用蓝鲸）http://www.cndba.cn/dave/article/131461

ln /data/mysql/[dbname]/_[tablename]_del.ibd /data/mysql/[dbname]/_[tablename]_del.ibd.hdlk
#在主库上drop
drop table _[tablename]_del;

2.4.7 注意可用磁盘空间

gh-ost 的镜像表包含源表的所有数据，会额外占用一倍的磁盘。所以对于大表，务必注意可用磁盘空间的情况。

签到成功

CNDBA社区