gh-ost 使用记录

0.warn:表结构变更之前最好是mysqldump下单表数据及结构，方便回滚

mysqldump -u $用户名 -p -h ip --single-transaction  --set-gtid-purged=OFF --master-data=2 --databases $database --tables $table >xxx_bak.sql

1.启动后台任务，进入后台任务

screen -R 任务名

#查看所有后台进程

screen -ls  

#退出此后台任务

ctrl a+d

#重新进入后台任务

screen -r 任务ID/任务名称

2.执行gh-ost Online DDL 变更，期间不会锁表，但是正式转换幽灵表时会检测MDL锁

gh-ost --database=test   --table=a_test   --conf=/root/test.conf --host=172.16.201.179 --aliyun-rds=true -replica-server-id=99999 --max-load=Threads_running=100  --critical-load=Threads_running=1000 --critical-load-interval-millis=100 --chunk-size=100000 --port=3306 --verbose --default-retries=120 --dml-batch-size=100 --assume-rbr --panic-flag-file=/tmp/sbtest1.panic --postpone-cut-over-flag-file=/tmp/sbtest1.postpone --throttle-flag-file=/tmp/sbtest1.throttle --timestamp-old-table -allow-on-master    --serve-socket-file=/tmp/sbtest4.gh-ost.socket  --alter="add column name_new varchar(255) after name" --execute

ctrl a+d 退出当前后台任务

#查看当前后台任务copy进度

[root@iZ2ze0soem5oe1tpm6dcaxZ ~]# echo "status" |nc -U /tmp/sbtest4.gh-ost.socket
# Migrating `test`.`a_test`; Ghost table is `test`.`_a_test_gho`
# Migrating 172.16.201.179:3306; inspecting 172.16.201.179:3306; executing on iZ2ze0soem5oe1tpm6dcaxZ
# Migration started at Mon Sep 25 11:04:22 +0800 2023
# 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 
# postpone-cut-over-flag-file: /tmp/sbtest1.postpone [set]
# panic-flag-file: /tmp/sbtest1.panic
# Serving on unix socket: /tmp/sbtest4.gh-ost.socket
Copy: 2771/2771 100.0%; Applied: 0; Backlog: 0/1000; Time: 42s(total), 1s(copy); streamer: mysql-bin.007879:404105946; Lag: 0.01s, State: postponing cut-over; ETA: due
 

当State: postponing cut-over时，执行

echo "unpostpone" |nc -U /tmp/sbtest4.gh-ost.socket 确认转换幽灵表与原表

screen -r 任务ID/任务名称 重新进入任务后台

# Serving on unix socket: /tmp/sbtest4.gh-ost.socket
Copy: 2771/2771 100.0%; Applied: 0; Backlog: 0/1000; Time: 2m38s(total), 1s(copy); streamer: mysql-bin.007879:408344862; Lag: 0.01s, State: migrating; ETA: due
2023-09-25 11:07:01 INFO Setting RENAME timeout as 3 seconds
2023-09-25 11:07:01 INFO Session renaming tables is 2318922
2023-09-25 11:07:01 INFO Issuing and expecting this to block: rename /* gh-ost */ table `test`.`a_test` to `test`.`_a_test_20230925110422_del`, `test`.`_a_test_gho` to `test`.`a_test`
2023-09-25 11:07:01 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2023-09-25 11:07:01 INFO Checking session lock: gh-ost.2318249.lock
2023-09-25 11:07:01 INFO Connection holding lock on original table still exists
2023-09-25 11:07:01 INFO Will now proceed to drop magic table and unlock tables
2023-09-25 11:07:01 INFO Dropping magic cut-over table
2023-09-25 11:07:01 INFO Releasing lock from `test`.`a_test`, `test`.`_a_test_20230925110422_del`
2023-09-25 11:07:01 INFO Tables unlocked
2023-09-25 11:07:01 INFO Tables renamed
2023-09-25 11:07:01 INFO Lock & rename duration: 1.007233071s. During this time, queries on `a_test` were blocked
2023-09-25 11:07:01 INFO Looking for magic cut-over table
[2023/09/25 11:07:01] [info] binlogsyncer.go:164 syncer is closing...
[2023/09/25 11:07:01] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
2023-09-25 11:07:01 INFO Closed streamer connection. err=<nil>
2023-09-25 11:07:01 INFO Dropping table `test`.`_a_test_ghc`
[2023/09/25 11:07:01] [info] binlogsyncer.go:179 syncer is closed
2023-09-25 11:07:01 INFO Table dropped
2023-09-25 11:07:01 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:
2023-09-25 11:07:01 INFO -- drop table `test`.`_a_test_20230925110422_del`
2023-09-25 11:07:01 INFO Done migrating `test`.`a_test`
2023-09-25 11:07:01 INFO Removing socket file: /tmp/sbtest4.gh-ost.socket
2023-09-25 11:07:01 INFO Tearing down inspector
2023-09-25 11:07:01 INFO Tearing down applier
2023-09-25 11:07:01 INFO Tearing down streamer
2023-09-25 11:07:01 INFO Tearing down throttler
# Done

完成转换，库表校验

如果无法完成转换，进入后台任务查看日志