gg修改器2021免root_GG修改器2018

大家好，今天小编为大家分享关于gg修改器2021免root_GG修改器2018的内容，赶快来一起来看看吧。

一 背景

我们在日常的数据库运维中.如果让你在线执行DDL的时候.是不是都会担心会不会长时间阻塞DML啊?或者DDL因为copy原因长时间阻塞啊?虽然从MySQL5.6版本开始就加入了online DDL 但是直到8.0版本才基本完善.大家要明确一个概念online DDL并不是代表就不会锁表了.除非8.0的instant模式.这个模式现在的适用范围也很小.inplace方式在mit阶段都会有可能阻塞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 plete
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/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 plete
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 也会不时地暂停，以便从库的数据可以保持最新。

--test-on-replica 表明操作只是为了测试目的。在进行最终的切换操作之前，复制会被停止。原始表和临时表会相互切换，再切换回来，最终相当于原始表没被动过。主从复制暂停的状态下，你可以检查和对比这两张表中的数据

四 总结

这篇文章主要分享了onlineDDL工具gh-ost的工作原理和使用方法.

以上就是关于gg修改器2021免root_GG修改器2018的全部内容，感谢大家的浏览观看，如果你喜欢本站的文章可以CTRL+D收藏哦。