作者：翟硕
爱可生 DBA 团队成员，负责公司 DMP 产品的运维和客户 MySQL 问题的处理。擅长数据库故障处理。对数据库技术和 python 有着浓厚的兴趣。
一、gh-ost 介绍
gh-ost 是 GitHub 发布的一款用于 MySQL 的无触发器在线模式迁移解决方案。它是可测试的，并提供暂停，动态控制/重新配置，审计和许多操作特权。它在整个迁移过程中，对主服务器产生的工作量很少，与已迁移表上的现有工作分离。
gh-ost 与所有现有的在线模式更改工具都以类似的方式操作：它们以与原始表相似的方式创建幽灵表，将数据从原始表缓慢且增量地复制到幽灵表，同时应用正在进行的更改（INSERT，DELETE，UPDATE）到幽灵表。最后，在适当的时候，它用幽灵表替换了原始表。gh-ost 使用相同的模式。但是，它与所有现有工具的不同之处在于不使用触发器。取而代之的是，gh-ost 使用二进制日志流捕获表的更改，然后将其异步应用到幽灵表。
gh-ost 承担一些其他工具留给数据库执行的任务。gh-ost 可以更好地控制迁移过程；可以真正暂停它；可以真正将迁移的写入负载与主服务器的工作负载分离。此外，它还提供了许多可操作的特权，使其更安全、可信赖且易于使用。
二、gh-ost 的三种模式架构
2.1 连接从库，在主库转换
在主库上创建 _xxx_gho（和原表表结构一致）、_xxx_ghc（记录变更日志），并修改 _xxx_gho 表结构； 从 slave 上读取二进制日志事件，将变更应用到主库上的 _xxx_gho 表； 在主库上读源表的数据写入 _xxx_gho 表中； 在主库上完成表切换。
2.2 连接主库，在主库转换
需要使用 –allow-on-master 选项：
在主库上创建 _xxx_gho、_xxx_ghc，并修改 _xxx_gho 表结构；
从主库上读取二进制日志事件，将变更应用到主库上的 _xxx_gho 表；
在主库上读源表的数据写入 _xxx_gho 表中；
在主库上完成表切换。
2.3 在从库上测试和转换
三、下载安装
3.1 下载
[]
3.2 安装
使用 rpm 命令进行安装# 使用rpm安装[root@10-186-61-20 ~]# rpm -ivh gh-ost-1.0.49-1.x86_64.rpmPreparing... ################################# [100%]Updating / installing...1:gh-ost-1:1.0.49-1 ################################# [100%]# 验证是否安装成功[root@10-186-61-20 ~]# gh-ost --version1.0.49
四、使用
4.1 常用参数介绍
需要安装 nmap-ncat 包来使用 nc 命令
使用方式为 echo “命令” | nc -U socket文件名，例如：echo status | nc -U sbtest1.gh-ost.socket
[]Copy: 19733607/19731368 100.0%; Applied: 3425325; Backlog: 863/1000; Time: 5h18m48s(total), 5h18m48s(copy); streamer: mysql_bin.000083:192819590; Lag: 0.01s, State: throttled, 10.186.61.96:3306 replica-lag=3.686724s; ETA: due
4.2.2 sup
[]Copy: 19833607/19731368 100.5%; Applied: 3479988; Backlog: 1000/1000; Time: 5h22m21s(total), 5h22m21s(copy); streamer: mysql_bin.000084:12427624; Lag: 1.61s, State: migrating; ETA: due
4.2.3 coordinates
[]mysql_bin.000131:10563681
4.2.4 可动态修改的参数
chunk-size=<newsize> # Set a new chunk-sizedml-batch-size=<newsize> # Set a new dml-batch-sizenice-ratio=<ratio> # Set a new nice-ratio, immediate sleep after each row-copy operation, float (examples: 0 is aggressive, 0.7 adds 70% runtime, 1.0 doubles runtime, 2.0 triples runtime, ...)critical-load=<load> # Set a new set of max-load thresholdsmax-lag-millis=<max-lag> # Set a new replication lag thresholdreplication-lag-query=<query> # Set a new query that determines replication lag (no quotes)max-load=<load> # Set a new set of max-load thresholdsthrottle-query=<query> # Set a new throttle-query (no quotes)throttle-http=<URL> # Set a new throttle URLthrottle-control-replicas=<replicas> # Set a new comma delimited list of throttle control replicas
[]4.2.5 强制节流和取消节流
throttle # Force throttlingno-throttle # End forced throttling (other throttling may still apply)
4.2.6 取消推迟转换
unpostpone # Bail out a cut-over postpone; proceed to cut-over4.2.7 立刻停止进程
panic # panic and quit without cleanup[root@10-186-61-103 tmp]# echo help | nc -U sbtest1.gh-ost.socketavailable commands:status # Print a detailed status messagesup # Print a short status messagecoordinates # Print the currently inspected coordinateschunk-size=<newsize> # Set a new chunk-sizedml-batch-size=<newsize> # Set a new dml-batch-sizenice-ratio=<ratio> # Set a new nice-ratio, immediate sleep after each row-copy operation, float (examples: 0 is aggressive, 0.7 adds 70% runtime, 1.0 doubles runtime, 2.0 triples runtime, ...)critical-load=<load> # Set a new set of max-load thresholdsmax-lag-millis=<max-lag> # Set a new replication lag thresholdreplication-lag-query=<query> # Set a new query that determines replication lag (no quotes)max-load=<load> # Set a new set of max-load thresholdsthrottle-query=<query> # Set a new throttle-query (no quotes)throttle-http=<URL> # Set a new throttle URLthrottle-control-replicas=<replicas> # Set a new comma delimited list of throttle control replicasthrottle # Force throttlingno-throttle # End forced throttling (other throttling may still apply)unpostpone # Bail out a cut-over postpone; proceed to cut-overpanic # panic and quit without cleanuphelp # This message- use '?' (question mark) as argument to get info rather than set. e.g. "max-load=?" will just print out current max-load
4.3 使用示例
本示例模拟更改字段数据类型，将 sbtest1 表的 c 字段由 char(120) 改为varchar(200)。采用连接从库，在主库转换的模式架构。
CPU：4C 内存：4G
操作系统版本：CentOS 7.5.1804
MySQL版本：5.7.29
MySQL架构：主从复制 主库：10.186.61.88:3306 从库：10.186.61.101:3306 gh-ost版本：1.0.4
4.3.2 准备工作
mysql> select @@global.binlog_format,@@log_slave_updates;+------------------------+---------------------+| @@global.binlog_format | @@log_slave_updates |+------------------------+---------------------+| ROW | 1 |+------------------------+---------------------+1 row in set (0.00 sec)
show slave status\G1. row ***************************省略其他信息Replicate_Do_DB::::::-- 省略其他信息
mysql> desc sbtest1;+-------+-----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-----------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || k | int(11) | NO | MUL | 0 | || c | char(120) | NO | | | || pad | char(60) | NO | | | |+-------+-----------+------+-----+---------+----------------+4 rows in set (0.00 sec)
mysql> GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE,SUPER, REPLICATION SLAVE on *.* TO ghost@'%' identified by 'ghost';Query OK, 0 rows affected, 1 warning (0.01 sec)
[][]user=ghostpassword=ghost
4.3.3 开始执行更改操作
[]modify c varchar(200) not null default ""[root@10-186-61-88 ~]# gh-ost --conf=ghost.conf --database=sbtest --table=sbtest1 \--alter='modify c varchar(200) not null default ""' --max-load=Threads_running=100 \--critical-load=Threads_running=1000 --critical-load-interval-millis=100 \--chunk-size=100000 --throttle-control-replicas="10.186.61.101:3306" \--max-lag-millis=1500 --host=10.186.61.101 --port=3306 --verbose \--default-retries=120 --dml-batch-size=100 \--assume-rbr --assume-master-host=10.186.61.88 \--panic-flag-file=/tmp/sbtest1.panic \--postpone-cut-over-flag-file=/tmp/sbtest1.postpone \--throttle-flag-file=/tmp/sbtest1.throttle \--timestamp-old-table \--serve-socket-file=/tmp/sbtest1.gh-ost.socket2020-07-24 15:06:28 INFO starting gh-ost 1.0.492020-07-24 15:06:28 INFO Migrating `sbtest`.`sbtest1`2020-07-24 15:06:28 INFO connection validated on 10.186.61.101:33062020-07-24 15:06:28 INFO User has SUPER, REPLICATION SLAVE privileges, and has ALL privileges on `sbtest`.*2020-07-24 15:06:28 INFO binary logs validated on 10.186.61.101:33062020-07-24 15:06:28 INFO Inspector initiated on 10-186-61-101:3306, version 5.7.29-log2020-07-24 15:06:28 INFO Table found. Engine=InnoDB2020-07-24 15:06:28 INFO Estimated number of rows via EXPLAIN: 48144272020-07-24 15:06:28 INFO Master forced to be 10.186.61.88:33062020-07-24 15:06:28 INFO log_slave_updates validated on 10.186.61.101:33062020-07-24 15:06:28 INFO connection validated on 10.186.61.101:33062020-07-24 15:06:28 INFO Connecting binlog streamer at mysql_bin.000008:95041262[2020/07/24 15:06:28] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql 10.186.61.101 3306 ghost false false <nil> false UTC true 0 0s 0s 0 false}[2020/07/24 15:06:28] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql_bin.000008, 95041262)[2020/07/24 15:06:28] [info] binlogsyncer.go:203 register slave for master server 10.186.61.101:3306[2020/07/24 15:06:28] [info] binlogsyncer.go:723 rotate to (mysql_bin.000008, 95041262)2020-07-24 15:06:28 INFO rotate to next log from mysql_bin.000008:0 to mysql_bin.0000082020-07-24 15:06:28 INFO connection validated on 10.186.61.88:33062020-07-24 15:06:28 INFO connection validated on 10.186.61.88:33062020-07-24 15:06:28 INFO will use time_zone='SYSTEM' on applier2020-07-24 15:06:28 INFO Examining table structure on applier2020-07-24 15:06:28 INFO Applier initiated on 10-186-61-88:3306, version 5.7.29-log2020-07-24 15:06:28 INFO Dropping table `sbtest`.`_sbtest1_ghc`2020-07-24 15:06:28 INFO Table dropped2020-07-24 15:06:28 INFO Creating changelog table `sbtest`.`_sbtest1_ghc`2020-07-24 15:06:28 INFO Changelog table created2020-07-24 15:06:28 INFO Creating ghost table `sbtest`.`_sbtest1_gho`2020-07-24 15:06:28 INFO Ghost table created2020-07-24 15:06:28 INFO Altering ghost table `sbtest`.`_sbtest1_gho`2020-07-24 15:06:28 INFO Ghost table altered2020-07-24 15:06:28 INFO Created postpone-cut-over-flag-file: /tmp/sbtest1.postpone2020-07-24 15:06:28 INFO Waiting for ghost table to be migrated. Current lag is 0s2020-07-24 15:06:28 INFO Intercepted changelog state GhostTableMigrated2020-07-24 15:06:28 INFO Handled changelog state GhostTableMigrated2020-07-24 15:06:28 INFO Chosen shared unique key is PRIMARY2020-07-24 15:06:28 INFO Shared columns are id,k,c,pad2020-07-24 15:06:28 INFO Listening on unix socket file: /tmp/sbtest1.gh-ost.socket2020-07-24 15:06:28 INFO Migration min values: [1]2020-07-24 15:06:28 INFO Migration max values: [5000000]2020-07-24 15:06:28 INFO Waiting for first throttle metrics to be collected2020-07-24 15:06:28 INFO First throttle metrics collected# Migrating `sbtest`.`sbtest1`; Ghost table is `sbtest`.`_sbtest1_gho`# Migrating 10-186-61-88:3306; inspecting 10-186-61-101:3306; executing on 10-186-61-88# Migration started at Fri Jul 24 15:06:28 +0800 2020# chunk-size: 100000; max-lag-millis: 1500ms; dml-batch-size: 100; max-load: Threads_running=100; critical-load: Threads_running=1000; nice-ratio: 0.000000# throttle-flag-file: /tmp/sbtest1.throttle# throttle-additional-flag-file: /tmp/gh-ost.throttle# throttle-control-replicas count: 1# postpone-cut-over-flag-file: /tmp/sbtest1.postpone [set]# panic-flag-file: /tmp/sbtest1.panic# Serving on unix socket: /tmp/sbtest1.gh-ost.socket2020-07-24 15:06:28 INFO Row copy complete# Migrating `sbtest`.`sbtest1`; Ghost table is `sbtest`.`_sbtest1_gho`# Migrating 10-186-61-88:3306; inspecting 10-186-61-101:3306; executing on 10-186-61-88# Migration started at Fri Jul 24 15:06:28 +0800 2020# chunk-size: 100000; max-lag-millis: 1500ms; dml-batch-size: 100; max-load: Threads_running=100; critical-load: Threads_running=1000; nice-ratio: 0.000000# throttle-flag-file: /tmp/sbtest1.throttle# throttle-additional-flag-file: /tmp/gh-ost.throttle# throttle-control-replicas count: 1# postpone-cut-over-flag-file: /tmp/sbtest1.postpone [set]# panic-flag-file: /tmp/sbtest1.panic# Serving on unix socket: /tmp/sbtest1.gh-ost.socketCopy: 0/4814427 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql_bin.000008:95044050; Lag: 0.03s, State: migrating; ETA: N/ACopy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql_bin.000008:95044050; Lag: 0.03s, State: migrating; ETA: due2020-07-24 15:06:28 INFO New table structure followsCREATE TABLE `_sbtest1_gho` (`id` int(11) NOT NULL AUTO_INCREMENT,`k` int(11) NOT NULL DEFAULT '0',`c` varchar(200) NOT NULL DEFAULT '',`pad` char(60) NOT NULL DEFAULT '',PRIMARY KEY (`id`),KEY `k_1` (`k`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4[2020/07/24 15:06:28] [info] binlogsyncer.go:164 syncer is closing...2020-07-24 15:06:28 INFO Closed streamer connection. err=<nil>2020-07-24 15:06:28 INFO Dropping table `sbtest`.`_sbtest1_ghc`[2020/07/24 15:06:28] [error] binlogstreamer.go:77 close sync with err: sync is been closing...[2020/07/24 15:06:28] [info] binlogsyncer.go:179 syncer is closed2020-07-24 15:06:28 INFO Table dropped2020-07-24 15:06:28 INFO Dropping table `sbtest`.`_sbtest1_gho`2020-07-24 15:06:28 INFO Table dropped2020-07-24 15:06:28 INFO Done migrating `sbtest`.`sbtest1`2020-07-24 15:06:28 INFO Removing socket file: /tmp/sbtest1.gh-ost.socket2020-07-24 15:06:28 INFO Tearing down inspector2020-07-24 15:06:28 INFO Tearing down applier2020-07-24 15:06:28 INFO Tearing down streamer2020-07-24 15:06:28 INFO Tearing down throttler# Done
gh-ost --conf=ghost.conf --database=sbtest --table=sbtest1 \--alter='modify c varchar(200) not null default ""' \--max-load=Threads_running=100 \--critical-load=Threads_running=1000 \--critical-load-interval-millis=100 --chunk-size=100000 \--throttle-control-replicas="10.186.61.101:3306" \--max-lag-millis=1500 --host=10.186.61.101 \--port=3306 --verbose --default-retries=120 \--dml-batch-size=100 --assume-rbr \--assume-master-host=10.186.61.88 \--panic-flag-file=/tmp/sbtest1.panic \--postpone-cut-over-flag-file=/tmp/sbtest1.postpone \--throttle-flag-file=/tmp/sbtest1.throttle \--timestamp-old-table \--serve-socket-file=/tmp/sbtest1.gh-ost.socket --execute
执行过程中会有如下的日志打印，代表的含义如下：
Copy：从原表 copy row 的进度，三列分别代表已 cpoy 的行、预估行数、进度
Applied：表示从二进制日志中读取并应用到幽灵表上的行数
Backlog：当前处理二进制日志的积压数量
Time：执行时间
streamer：当前处理到的二进制日志的位置
Lag：延时时间
State：状态（migrating：正常迁移，throttled：限流中，postponing cut-over：等待转换）
ETA：预估的完成耗时
Copy: 300000/4814427 6.2%; Applied: 0; Backlog: 0/1000; Time: 6s(total), 6s(copy); streamer: mysql_bin.000008:116441176; Lag: 1.72s, State: throttled, lag=1.824079s; ETA: 1m30sCopy: 300000/4814427 6.2%; Applied: 0; Backlog: 0/1000; Time: 7s(total), 7s(copy); streamer: mysql_bin.000008:133260367; Lag: 1.42s, State: migrating; ETA: 1m45s
当状态成为 postponing cut-over 后，我们就可以进行下一步的转换环节，转换方式有两种，第一种是删除创建的标志文件，标志文件为 –postpone-cut-over-flag-file 选项定义的。第二种是执行交互式命令，这里我们采用第二种。
Copy: 5000000/5000000 100.0%; Applied: 0; Backlog: 0/1000; Time: 18m0s(total), 1m49s(copy); streamer: mysql_bin.000011:254596764; Lag: 0.02s, State: postponing cut-over; ETA: dueCopy: 5000000/5000000 100.0%; Applied: 0; Backlog: 0/1000; Time: 18m30s(total), 1m49s(copy); streamer: mysql_bin.000011:254786245; Lag: 0.02s, State: postponing cut-over; ETA: du
[]Copy: 5000000/5000000 100.0%; Applied: 0; Backlog: 0/1000; Time: 20m27s(total), 1m49s(copy); streamer: mysql_bin.000011:255529733; Lag: 0.02s, State: postponing cut-over; ETA: due2020-07-24 15:28:53 INFO Grabbing voluntary lock: gh-ost.2088.lock2020-07-24 15:28:53 INFO Setting LOCK timeout as 6 seconds2020-07-24 15:28:53 INFO Looking for magic cut-over table2020-07-24 15:28:53 INFO Creating magic cut-over table `sbtest`.`_sbtest1_20200724150824_del`2020-07-24 15:28:53 INFO Magic cut-over table created2020-07-24 15:28:53 INFO Locking `sbtest`.`sbtest1`, `sbtest`.`_sbtest1_20200724150824_del`2020-07-24 15:28:53 INFO Tables locked2020-07-24 15:28:53 INFO Session locking original & magic tables is 20882020-07-24 15:28:53 INFO Writing changelog state: AllEventsUpToLockProcessed:15955757333518931702020-07-24 15:28:53 INFO Waiting for events up to lock2020-07-24 15:28:53 INFO Intercepted changelog state AllEventsUpToLockProcessed2020-07-24 15:28:53 INFO Handled changelog state AllEventsUpToLockProcessed2020-07-24 15:28:54 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:15955757333518931702020-07-24 15:28:54 INFO Done waiting for events up to lock; duration=696.107491ms# Migrating `sbtest`.`sbtest1`; Ghost table is `sbtest`.`_sbtest1_gho`# Migrating 10-186-61-88:3306; inspecting 10-186-61-101:3306; executing on 10-186-61-88# Migration started at Fri Jul 24 15:08:24 +0800 2020# chunk-size: 100000; max-lag-millis: 1500ms; dml-batch-size: 100; max-load: Threads_running=100; critical-load: Threads_running=1000; nice-ratio: 0.000000# throttle-flag-file: /tmp/sbtest1.throttle# throttle-additional-flag-file: /tmp/gh-ost.throttle# throttle-control-replicas count: 1# postpone-cut-over-flag-file: /tmp/sbtest1.postpone [set]# panic-flag-file: /tmp/sbtest1.panic# Serving on unix socket: /tmp/sbtest1.gh-ost.socketCopy: 5000000/5000000 100.0%; Applied: 0; Backlog: 0/1000; Time: 20m29s(total), 1m49s(copy); streamer: mysql_bin.000011:255542872; Lag: 0.02s, State: migrating; ETA: due2020-07-24 15:28:54 INFO Setting RENAME timeout as 3 seconds2020-07-24 15:28:54 INFO Session renaming tables is 20842020-07-24 15:28:54 INFO Issuing and expecting this to block: rename /* gh-ost */ table `sbtest`.`sbtest1` to `sbtest`.`_sbtest1_20200724150824_del`, `sbtest`.`_sbtest1_gho` to `sbtest`.`sbtest1`2020-07-24 15:28:54 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)2020-07-24 15:28:54 INFO Checking session lock: gh-ost.2088.lock2020-07-24 15:28:54 INFO Connection holding lock on original table still exists2020-07-24 15:28:54 INFO Will now proceed to drop magic table and unlock tables2020-07-24 15:28:54 INFO Dropping magic cut-over table2020-07-24 15:28:54 INFO Releasing lock from `sbtest`.`sbtest1`, `sbtest`.`_sbtest1_20200724150824_del`2020-07-24 15:28:54 INFO Tables unlocked2020-07-24 15:28:54 INFO Tables renamed2020-07-24 15:28:54 INFO Lock & rename duration: 721.156968ms. During this time, queries on `sbtest1` were blocked2020-07-24 15:28:54 INFO Looking for magic cut-over table[2020/07/24 15:28:54] [info] binlogsyncer.go:164 syncer is closing...2020-07-24 15:28:54 INFO Closed streamer connection. err=<nil>2020-07-24 15:28:54 INFO Dropping table `sbtest`.`_sbtest1_ghc`[2020/07/24 15:28:54] [error] binlogstreamer.go:77 close sync with err: sync is been closing...[2020/07/24 15:28:54] [info] binlogsyncer.go:179 syncer is closed2020-07-24 15:28:54 INFO Table dropped2020-07-24 15:28:54 INFO Am not dropping old table because I want this operation to be as live as possible. If you insist I should do it, please add `--ok-to-drop-table` next time. But I prefer you do not. To drop the old table, issue:2020-07-24 15:28:54 INFO -- drop table `sbtest`.`_sbtest1_20200724150824_del`2020-07-24 15:28:54 INFO Done migrating `sbtest`.`sbtest1`2020-07-24 15:28:54 INFO Removing socket file: /tmp/sbtest1.gh-ost.socket2020-07-24 15:28:54 INFO Tearing down inspector2020-07-24 15:28:54 INFO Tearing down applier2020-07-24 15:28:54 INFO Tearing down streamer2020-07-24 15:28:54 INFO Tearing down throttler# Done
mysql> desc sbtest1;+-------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+----------------+|| k | int(11) | NO | MUL | 0 | ||| pad | char(60) | NO | | | |+-------+--------------+------+-----+---------+----------------+4 rows in set (0.00 sec)
验证表数据是否正常 删除旧表
mysql> show tables;+-----------------------------+| Tables_in_sbtest |+-----------------------------+| _sbtest1_20200724150824_del || sbtest1 |+-----------------------------+2 rows in set (0.00 sec)mysql> drop table _sbtest1_20200724150824_del;Query OK, 0 rows affected (0.03 sec)
清理残留的标志文件
[]rm: remove regular empty file ‘sbtest1.postpone’? y
参考：
https://github.com/github/gh-ost/tree/master/doc