我们在日常的数据库运维中.如果让你在线执行DDL的时候.是不是都会担心会不会长时间阻塞DML啊?或者DDL因为copy原因长时间阻塞啊?虽然从MySQL5.6版本开始就加入了online DDL 但是直到8.0版本才基本完善.大家要明确一个概念online DDL并不是代表就不会锁表了.除非8.0的instant模式.这个模式现在的适用范围也很小.inplace方式在prepare和commit阶段都会有可能阻塞DML的.那么我们选择的pt-osc或者gh-ost就不会锁表吗?也不是.只是会更温柔.所以无论是选择哪种方式.都尽量避开业务高峰期去执行.实在迫不得已建议大家使用gh-ost 这个工具相比于pt-osc更温柔,限制也更少.
推荐大家看一下我这篇对pt-osc和gh-ost的性能对比文章
二 gh-ost介绍gh-ost 是 GitHub 发布的一款用于 MySQL 的无触发器在线模式迁移解决方案。它是可测试的，并提供暂停，动态控制/重新配置，审计和许多操作特权。它在整个迁移过程中，对主服务器产生的工作量很少，与已迁移表上的现有工作分离。
gh-ost 与所有现有的在线模式更改工具都以类似的方式操作：它们以与原始表相似的方式创建幽灵表，将数据从原始表缓慢且增量地复制到幽灵表，同时应用正在进行的更改（INSERT，DELETE，UPDATE）到幽灵表。最后，在适当的时候，它用幽灵表替换了原始表。gh-ost 使用相同的模式。但是，它与所有现有工具的不同之处在于不使用触发器。取而代之的是，gh-ost 使用二进制日志流捕获表的更改，然后将其异步应用到幽灵表。
gh-ost 承担一些其他工具留给数据库执行的任务。gh-ost 可以更好地控制迁移过程；可以真正暂停它；可以真正将迁移的写入负载与主服务器的工作负载分离。此外，它还提供了许多可操作的特权，使其更安全、可信赖且易于使用。
三 gh-ost模式介绍
三种工作模式
3.1 连接从库,在主库转换这种模式是gh-ost的默认模式.
1 先连接到从库,获取到主库信息及表的数据行数
2021-07-26 18:32:56 INFO connection validated on 10.2.4.178:3307
2021-07-26 18:32:56 INFO User has ALL privileges
2021-07-26 18:32:56 INFO binary logs validated on 10.2.4.178:3307
2021-07-26 18:32:56 INFO Restarting replication on 10.2.4.178:3307 to make sure binlog settings apply to replication thread
2021-07-26 18:32:56 INFO Inspector initiated on slowquery:3307, version 5.7.28-log
2021-07-26 18:32:56 INFO Table found. Engine=InnoDB
2021-07-26 18:32:56 INFO Estimated number of rows via EXPLAIN: 59256
2021-07-26 18:32:56 INFO Recursively searching for replication master
2021-07-26 18:32:56 INFO Master found to be 10.2.4.178:3306
2 开始监控从库的binlog,并以当前的从库为主库开启binlog复制
2021-07-26 18:32:56 INFO Connecting binlog streamer at backups.000006:317378064
[2021/07/26 18:32:56] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql 10.2.4.178 3307 ghost false false <nil> false UTC true 0 0s 0s 0 false}
[2021/07/26 18:32:56] [info] binlogsyncer.go:354 begin to sync binlog from position (backups.000006, 317378064)
[2021/07/26 18:32:56] [info] binlogsyncer.go:203 register slave for master server 10.2.4.178:3307
[2021/07/26 18:32:56] [info] binlogsyncer.go:723 rotate to (backups.000006, 317378064)
2021-07-26 18:32:56 INFO rotate to next log from backups.000006:0 to backups.000006
3 连接到主库.创建对应的日志表及转换表
2021-07-26 18:32:56 INFO connection validated on 10.2.4.178:3306
2021-07-26 18:32:56 INFO connection validated on 10.2.4.178:3306
2021-07-26 18:32:56 INFO will use time_zone='SYSTEM' on applier
2021-07-26 18:32:56 INFO Examining table structure on applier
2021-07-26 18:32:56 INFO Applier initiated on slowquery:3306, version 5.7.28-log
2021-07-26 18:32:56 INFO Dropping table `increment`.`_test1_gho`
2021-07-26 18:32:56 INFO Table dropped
2021-07-26 18:32:56 INFO Dropping table `increment`.`_test1_del`
2021-07-26 18:32:56 INFO Table dropped
2021-07-26 18:32:56 INFO Dropping table `increment`.`_test1_ghc`
2021-07-26 18:32:56 INFO Table dropped
2021-07-26 18:32:56 INFO Creating changelog table `increment`.`_test1_ghc`
2021-07-26 18:32:56 INFO Changelog table created
2021-07-26 18:32:56 INFO Creating ghost table `increment`.`_test1_gho`
2021-07-26 18:32:56 INFO Ghost table created
2021-07-26 18:32:56 INFO Altering ghost table `increment`.`_test1_gho`
2021-07-26 18:32:56 INFO Ghost table altered
2021-07-26 18:32:56 INFO Waiting for ghost table to be migrated. Current lag is 0s
4 获取原表的最大和最小值.在主库开始转换
2021-07-26 18:32:56 INFO As instructed, I'm issuing a SELECT COUNT(*) on the table. This may take a while
2021-07-26 18:32:56 INFO Migration min values: [1]
2021-07-26 18:32:56 INFO Migration max values: [60000]
2021-07-26 18:32:56 INFO Waiting for first throttle metrics to be collected
2021-07-26 18:32:56 INFO First throttle metrics collected
# Migrating `increment`.`test1`; Ghost table is `increment`.`_test1_gho`
Copy: 0/59256 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: backups.000006:317380569; Lag: 0.03s, State: migrating; ETA: N/A
2021-07-26 18:32:56 INFO Exact number of rows via COUNT: 60000
Copy: 0/60000 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: backups.000006:317384833; Lag: 0.03s, State: migrating; ETA: N/A
Copy: 30000/60000 50.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 2s(copy); streamer: backups.000006:321225527; Lag: 0.23s, State: migrating; ETA: 2s
2021-07-26 18:32:59 INFO Row copy complete
Copy: 60000/60000 100.0%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 2s(copy); streamer: backups.000006:325064699; Lag: 0.23s, State: migrating; ETA: due
5 修改mysql的锁等时间.并锁定原表同时应用从库的binlog到表中
2021-07-26 18:32:59 INFO Setting LOCK timeout as 6 seconds
2021-07-26 18:32:59 INFO Looking for magic cut-over table
2021-07-26 18:32:59 INFO Creating magic cut-over table `increment`.`_test1_del`
2021-07-26 18:32:59 INFO Magic cut-over table created
2021-07-26 18:32:59 INFO Locking `increment`.`test1`, `increment`.`_test1_del`
2021-07-26 18:32:59 INFO Tables locked
2021-07-26 18:32:59 INFO Session locking original & magic tables is 594
2021-07-26 18:32:59 INFO Writing changelog state: AllEventsUpToLockProcessed:1627295579226030036
2021-07-26 18:32:59 INFO Waiting for events up to lock
2021-07-26 18:32:59 INFO Intercepted changelog state AllEventsUpToLockProcessed
2021-07-26 18:32:59 INFO Handled changelog state AllEventsUpToLockProcessed
# Migrating `increment`.`test1`; Ghost table is `increment`.`_test1_gho`
# Migrating slowquery:3306; inspecting slowquery:3307; executing on slowquery
6 rename原表和转换表
2021-07-26 18:33:00 INFO Setting RENAME timeout as 3 seconds
2021-07-26 18:33:00 INFO Session renaming tables is 592
2021-07-26 18:33:00 INFO Issuing and expecting this to block: rename /* gh-ost */ table `increment`.`test1` to `increment`.`_test1_del`, `increment`.`_test1_gho` to `increment`.`test1`
2021-07-26 18:33:00 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2021-07-26 18:33:00 INFO Checking session lock: gh-ost.594.lock
2021-07-26 18:33:00 INFO Connection holding lock on original table still exists
7 最后,删除掉日志表,完成并退出
2021-07-26 18:33:00 INFO Will now proceed to drop magic table and unlock tables
2021-07-26 18:33:00 INFO Dropping magic cut-over table
2021-07-26 18:33:00 INFO Releasing lock from `increment`.`test1`, `increment`.`_test1_del`
2021-07-26 18:33:00 INFO Tables unlocked
2021-07-26 18:33:00 INFO Tables renamed
2021-07-26 18:33:00 INFO Lock & rename duration: 983.664218ms. During this time, queries on `test1` were blocked
2021-07-26 18:33:00 INFO Looking for magic cut-over table
[2021/07/26 18:33:00] [info] binlogsyncer.go:164 syncer is closing...
2021-07-26 18:33:00 INFO Closed streamer connection. err=<nil>
2021-07-26 18:33:00 INFO Dropping table `increment`.`_test1_ghc`
[2021/07/26 18:33:00] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2021/07/26 18:33:00] [info] binlogsyncer.go:179 syncer is closed
2021-07-26 18:33:00 INFO Table dropped
2021-07-26 18:33:00 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:
2021-07-26 18:33:00 INFO -- drop table `increment`.`_test1_del`
2021-07-26 18:33:00 INFO Done migrating `increment`.`test1`
执行过程图解:
默认执行过程的图解
语句执行:
gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=100 \
--chunk-size=10000 \
--throttle-control-replicas="10.2.4.178:3307" \
--max-lag-millis=1500 \
--user="ghost" \
--password="123" \
--host=10.2.4.178 \
--port=3307 \
--database="increment" \
--table="test1" \
--verbose \
--alter='modify name varchar(100) not null default ""' \
--switch-to-rbr \
--allow-on-master \
--cut-over=default \
--exact-rowcount \
--concurrent-rowcount \
--default-retries=120 --initially-drop-ghost-table --initially-drop-old-table --execute
参数解释:
--max-load 控制当主库的活跃连接数超过25个时就停止转换
用法:'Threads_running=100,Threads_connected=500'
--critical-load 用法和--max-load一样
--chunk-size 每次取多少行进行插入转行表 allowed range: 100-100,000
--throttle-control-replicas 从库的地址及端口.多个的话用逗号分隔
--max-lag-millis 从库的延迟时间超过这个值的话就停止gh-ost 单位是ms
--switch-to-rbr gh-ost依赖于row模式复制.如果你的是staetment模式的话就使用这个参数.如果确定自己的
就是row模式的话可以使用这个参数-assume-rbr
--allow-on-master 表示允许在主库操作.
--cut-over=default 这是gh-ost在最后rename时候的算法 大家想了解的话去GitHub查看https://github.com/github/gh-ost/blob/master/doc/cut-over.md
--exact-rowcount 执行之前.计算表行数
--concurrent-rowcount 配合上边的参数.在copy的过程中展示copy进度
3.2 连接主库.在主库执行并切换
和默认模式相比.就是二进制日志的拉取是在主库拉取.其他过程一致
[root@slowquery ~]# gh-ost \
> --max-load=Threads_running=25 \
> --critical-load=Threads_running=1000 \
> --chunk-size=10000 \
> --max-lag-millis=1500 \
> --user="ghost" \
> --password="123" \
> --host=10.2.4.178 \
> --allow-on-master \
> --database="increment" \
> --table="test1" \
> --verbose \
> --alter='modify name varchar(200) not null default ""' \
> --assume-rbr \
> --allow-master-master \
> --allow-on-master \
> --cut-over=default \
> --initially-drop-ghost-table \
> --initially-drop-old-table \
> --exact-rowcount \
> --concurrent-rowcount \
> --default-retries=120 \
> --execute
2021-07-26 19:27:22 INFO starting gh-ost 1.0.49
2021-07-26 19:27:22 INFO Migrating `increment`.`test1`
2021-07-26 19:27:22 INFO connection validated on 10.2.4.178:3306
2021-07-26 19:27:22 INFO User has SUPER, REPLICATION SLAVE privileges, and has ALL privileges on `increment`.*
2021-07-26 19:27:22 INFO binary logs validated on 10.2.4.178:3306
2021-07-26 19:27:22 INFO Inspector initiated on slowquery:3306, version 5.7.28-log
2021-07-26 19:27:22 INFO Table found. Engine=InnoDB
2021-07-26 19:27:22 INFO Estimated number of rows via EXPLAIN: 59256
2021-07-26 19:27:22 INFO Recursively searching for replication master
2021-07-26 19:27:22 INFO Master found to be slowquery:3306
2021-07-26 19:27:22 INFO log_slave_updates validated on 10.2.4.178:3306
2021-07-26 19:27:22 INFO connection validated on 10.2.4.178:3306
2021-07-26 19:27:22 INFO Connecting binlog streamer at mysql-bin.000009:427741954
[2021/07/26 19:27:22] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql 10.2.4.178 3306 ghost false false <nil> false UTC true 0 0s 0s 0 false}
[2021/07/26 19:27:22] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql-bin.000009, 427741954)
[2021/07/26 19:27:22] [info] binlogsyncer.go:203 register slave for master server 10.2.4.178:3306
2021-07-26 19:27:22 INFO rotate to next log from mysql-bin.000009:0 to mysql-bin.000009
[2021/07/26 19:27:22] [info] binlogsyncer.go:723 rotate to (mysql-bin.000009, 427741954)
2021-07-26 19:27:22 INFO connection validated on 10.2.4.178:3306
2021-07-26 19:27:22 INFO connection validated on 10.2.4.178:3306
2021-07-26 19:27:22 INFO will use time_zone='SYSTEM' on applier
2021-07-26 19:27:22 INFO Examining table structure on applier
2021-07-26 19:27:22 INFO Applier initiated on slowquery:3306, version 5.7.28-log
2021-07-26 19:27:22 INFO Dropping table `increment`.`_test1_gho`
2021-07-26 19:27:22 INFO Table dropped
2021-07-26 19:27:22 INFO Dropping table `increment`.`_test1_del`
2021-07-26 19:27:22 INFO Table dropped
2021-07-26 19:27:22 INFO Dropping table `increment`.`_test1_ghc`
2021-07-26 19:27:22 INFO Table dropped
2021-07-26 19:27:22 INFO Creating changelog table `increment`.`_test1_ghc`
2021-07-26 19:27:22 INFO Changelog table created
2021-07-26 19:27:22 INFO Creating ghost table `increment`.`_test1_gho`
2021-07-26 19:27:22 INFO Ghost table created
2021-07-26 19:27:22 INFO Altering ghost table `increment`.`_test1_gho`
2021-07-26 19:27:22 INFO Ghost table altered
2021-07-26 19:27:22 INFO Intercepted changelog state GhostTableMigrated
2021-07-26 19:27:22 INFO Waiting for ghost table to be migrated. Current lag is 0s
2021-07-26 19:27:22 INFO Handled changelog state GhostTableMigrated
2021-07-26 19:27:23 INFO Chosen shared unique key is PRIMARY
2021-07-26 19:27:23 INFO Shared columns are id,k,c,pad,name,ghost
2021-07-26 19:27:23 INFO Listening on unix socket file: /tmp/gh-ost.increment.test1.sock
2021-07-26 19:27:23 INFO As instructed, counting rows in the background; meanwhile I will use an estimated count, and will update it later on
2021-07-26 19:27:23 INFO As instructed, I'm issuing a SELECT COUNT(*) on the table. This may take a while
2021-07-26 19:27:23 INFO Migration min values: [1]
2021-07-26 19:27:23 INFO Migration max values: [60000]
2021-07-26 19:27:23 INFO Waiting for first throttle metrics to be collected
2021-07-26 19:27:23 INFO First throttle metrics collected
# Migrating `increment`.`test1`; Ghost table is `increment`.`_test1_gho`
# Migrating slowquery:3306; inspecting slowquery:3306; executing on slowquery
# Migration started at Mon Jul 26 19:27:22 0800 2021
# chunk-size: 10000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=25; critical-load: Threads_running=1000; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.increment.test1.sock
Copy: 0/59256 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000009:427744525; Lag: 0.01s, State: migrating; ETA: N/A
2021-07-26 19:27:23 INFO Exact number of rows via COUNT: 60000
Copy: 0/60000 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000009:427749035; Lag: 0.10s, State: migrating; ETA: N/A
Copy: 30000/60000 50.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 2s(copy); streamer: mysql-bin.000009:433508963; Lag: 0.10s, State: migrating; ETA: 2s
2021-07-26 19:27:25 INFO Row copy complete
Copy: 60000/60000 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 2s(copy); streamer: mysql-bin.000009:437478208; Lag: 0.10s, State: migrating; ETA: due
2021-07-26 19:27:25 INFO Grabbing voluntary lock: gh-ost.1174.lock
2021-07-26 19:27:25 INFO Setting LOCK timeout as 6 seconds
2021-07-26 19:27:25 INFO Looking for magic cut-over table
2021-07-26 19:27:25 INFO Creating magic cut-over table `increment`.`_test1_del`
2021-07-26 19:27:25 INFO Magic cut-over table created
2021-07-26 19:27:25 INFO Locking `increment`.`test1`, `increment`.`_test1_del`
2021-07-26 19:27:25 INFO Tables locked
2021-07-26 19:27:25 INFO Session locking original & magic tables is 1174
2021-07-26 19:27:25 INFO Writing changelog state: AllEventsUpToLockProcessed:1627298845618434245
2021-07-26 19:27:25 INFO Intercepted changelog state AllEventsUpToLockProcessed
2021-07-26 19:27:25 INFO Handled changelog state AllEventsUpToLockProcessed
2021-07-26 19:27:25 INFO Waiting for events up to lock
Copy: 60000/60000 100.0%; Applied: 0; Backlog: 1/1000; Time: 3s(total), 2s(copy); streamer: mysql-bin.000009:439271232; Lag: 0.10s, State: migrating; ETA: due
2021-07-26 19:27:26 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:1627298845618434245
2021-07-26 19:27:26 INFO Done waiting for events up to lock; duration=953.324255ms
# Migrating `increment`.`test1`; Ghost table is `increment`.`_test1_gho`
# Migrating slowquery:3306; inspecting slowquery:3306; executing on slowquery
# Migration started at Mon Jul 26 19:27:22 0800 2021
# chunk-size: 10000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=25; critical-load: Threads_running=1000; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.increment.test1.sock
Copy: 60000/60000 100.0%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 2s(copy); streamer: mysql-bin.000009:439273731; Lag: 0.10s, State: migrating; ETA: due
2021-07-26 19:27:26 INFO Setting RENAME timeout as 3 seconds
2021-07-26 19:27:26 INFO Session renaming tables is 1175
2021-07-26 19:27:26 INFO Issuing and expecting this to block: rename /* gh-ost */ table `increment`.`test1` to `increment`.`_test1_del`, `increment`.`_test1_gho` to `increment`.`test1`
2021-07-26 19:27:26 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2021-07-26 19:27:26 INFO Checking session lock: gh-ost.1174.lock
2021-07-26 19:27:26 INFO Connection holding lock on original table still exists
2021-07-26 19:27:26 INFO Will now proceed to drop magic table and unlock tables
2021-07-26 19:27:26 INFO Dropping magic cut-over table
2021-07-26 19:27:26 INFO Releasing lock from `increment`.`test1`, `increment`.`_test1_del`
2021-07-26 19:27:26 INFO Tables unlocked
2021-07-26 19:27:26 INFO Tables renamed
2021-07-26 19:27:26 INFO Lock & rename duration: 996.102516ms. During this time, queries on `test1` were blocked
2021-07-26 19:27:26 INFO Looking for magic cut-over table
[2021/07/26 19:27:26] [info] binlogsyncer.go:164 syncer is closing...
2021-07-26 19:27:26 INFO Closed streamer connection. err=<nil>
[2021/07/26 19:27:26] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2021/07/26 19:27:26] [info] binlogsyncer.go:179 syncer is closed
2021-07-26 19:27:26 INFO Dropping table `increment`.`_test1_ghc`
2021-07-26 19:27:26 INFO Table dropped
2021-07-26 19:27:26 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:
2021-07-26 19:27:26 INFO -- drop table `increment`.`_test1_del`
2021-07-26 19:27:26 INFO Done migrating `increment`.`test1`
2021-07-26 19:27:26 INFO Removing socket file: /tmp/gh-ost.increment.test1.sock
2021-07-26 19:27:26 INFO Tearing down inspector
2021-07-26 19:27:26 INFO Tearing down applier
2021-07-26 19:27:26 INFO Tearing down streamer
2021-07-26 19:27:26 INFO Tearing down throttler
# Done
3.3 在从库上测试和转换
这种模式会在从库上做修改。gh-ost 仍然会连上主库，但所有操作都是在从库上做的，不会对主库产生任何影响。在操作过程中，gh-ost 也会不时地暂停，以便从库的数据可以保持最新。
--migrate-on-replica 选项让 gh-ost 直接在从库上修改表。最终的切换过程也是在从库正常复制的状态下完成的。
--test-on-replica 表明操作只是为了测试目的。在进行最终的切换操作之前，复制会被停止。原始表和临时表会相互切换，再切换回来，最终相当于原始表没被动过。主从复制暂停的状态下，你可以检查和对比这两张表中的数据四 总结
这篇文章主要分享了onlineDDL工具gh-ost的工作原理和使用方法.
