gh-ost使用手冊
gh-ost實戰運用
一、安裝步驟
1、環境
go版本:1.10.3 gh-ost版本:1.0.46
2、安裝go語言
# 安裝go依賴包 yum install bison ed gawk gcc libc6-dev make -y # 配置go環境變數 vim ~/.bashrc export GOROOT=/usr/local/go export PATH=$PATH:$GOROOT/bin export GOPATH=/usr/local/go/src/github.com/github/gh-ost # 使環境變數生效 source ~/.bashrc # 解壓go安裝包 安裝包在10.135.2.217:data/online/software/go1.10.3.linux-amd64.tar.gz tar -zxvf go1.10.3.linux-amd64.tar.gz -C /usr/local/
3、安裝gh-ost
安裝包在:10.135.2.217:data/online/software/gh-ost-binary-linux-20180527215024.tar.gz tar -zxvf gh-ost-binary-linux-20180527215024.tar.gz -C /usr/local ln -s /usr/local/gh-ost /usr/bin/gh-ost
二、主庫模式
1、常用命令
gh-ost \ --max-load=Threads_running=16 \ --critical-load=Threads_running=32 \ --chunk-size=1000\ --initially-drop-old-table \ --initially-drop-ghost-table \ --initially-drop-socket-file \ --ok-to-drop-table \ --host="10.249.5.39" \ --port=3306 \ --user="dbadmin" \ --password="12345" \ --assume-rbr \ --allow-on-master \ --assume-master-host=10.249.5.39:3306 \ --database="gh_ost" \ --table="gh_01" \ --alter="add column c4 varchar(50) not null default ''" \ --panic-flag-file=/tmp/ghost.panic.flag \ --serve-socket-file=/tmp/ghost.sock \ --verbose \ --execute
2、常用引數解釋
-allow-on-master 預設是在從庫上應用binlog,如果直接在主庫上執行,必須設定該引數 -max-load string string是狀態表達式,當設定多個狀態值,用逗號分隔,如'Threads_running=100,Threads_connected=500',當超過該值,遷移暫停等待 -critical-load string 與max-load不同的是,當超過該值,遷移直接停止並退出 -chunk-size int 每次從原表迭代遷移資料的行數(允許值:100-100000) (預設1000) -initially-drop-ghost-table 在本次操作前刪除可能存在的ghost表(可能之前留下的),預設如果存在就中斷 -initially-drop-old-table 在本次操作前刪除old表(可能之前沒有刪除),預設如果存在中斷 -initially-drop-socket-file 刪除已存在的socket檔案 -ok-to-drop-table DDL完成後自動刪除old表 -panic-flag-file string 當指定該引數後,如果建立該檔案,gh-ost立刻中斷退出,不會清理產生的臨時表和檔案 -exact-rowcount 精確的統計表資料行數而不是預估,即使不準確只是影響進度的計算,實際copy行數是由最大值和最小值確定,與其無關。 -serve-socket-file string socket檔案 -assume-rbr 顯示告訴gh-ost日誌格式是row格式,如果沒有該引數,gh-ost每次都會設定row格式並重啟複製,需要使用者有super許可權 -assume-master-host 顯示告訴gh-ost master地址,如果不提供,gh-ost會根據從庫查到master -host、-port gh-ost預設是作為slave的連線資訊,如果使用slave應用日誌,這裡填寫slave的主機資訊;如果直接在master上執行,這裡就填寫master資訊,並且必須存在allow-on-master引數,否則就報錯退出。
3、輸出日誌分析
GH-OST會輸出一些關鍵詳細資訊,讓你瞭解整個遷移過程。當然,你可以控制輸出級別。
–verbose:常用,有用的輸出,而不是一切。 –debug:輸出所有一切。
開始輸出如下:
2018-08-07 14:17:11 INFO starting gh-ost 1.0.46 2018-08-07 14:17:11 INFO Migrating `darren`.`t4` 2018-08-07 14:17:11 INFO connection validated on 10.249.5.39:3306 2018-08-07 14:17:11 INFO User has ALL privileges 2018-08-07 14:17:11 INFO binary logs validated on 10.249.5.39:3306 2018-08-07 14:17:11 INFO Restarting replication on 10.249.5.39:3306 to make sure binlog settings apply to replication thread 2018-08-07 14:17:11 INFO Inspector initiated on shvm-5-39.58os.org:3306, version 5.7.21-log 2018-08-07 14:17:11 INFO Table found. Engine=InnoDB 2018-08-07 14:17:11 INFO Estimated number of rows via EXPLAIN: 58707 2018-08-07 14:17:11 INFO Recursively searching for replication master 2018-08-07 14:17:11 INFO Master found to be shvm-5-39.58os.org:3306 2018-08-07 14:17:11 INFO log_slave_updates validated on 10.249.5.39:3306 2018-08-07 14:17:11 INFO connection validated on 10.249.5.39:3306 2018/08/07 14:17:11 binlogsyncer.go:79: [info] create BinlogSyncer with config {99999 mysql 10.249.5.39 3306 dbadminfalse false <nil>} 2018-08-07 14:17:11 INFO Connecting binlog streamer at shvm-5-39.000040:337570954 2018/08/07 14:17:11 binlogsyncer.go:246: [info] begin to sync binlog from position (shvm-5-39.000040, 337570954) 2018/08/07 14:17:11 binlogsyncer.go:139: [info] register slave for master server 10.249.5.39:3306 2018/08/07 14:17:11 binlogsyncer.go:573: [info] rotate to (shvm-5-39.000040, 337570954) 2018-08-07 14:17:11 INFO rotate to next log name: shvm-5-39.000040 2018-08-07 14:17:11 INFO connection validated on 10.249.5.39:3306 2018-08-07 14:17:11 INFO connection validated on 10.249.5.39:3306 2018-08-07 14:17:11 INFO will use time_zone='SYSTEM' on applier 2018-08-07 14:17:11 INFO Examining table structure on applier 2018-08-07 14:17:11 INFO Applier initiated on shvm-5-39.58os.org:3306, version 5.7.21-log 2018-08-07 14:17:11 INFO Dropping table `darren`.`_t4_gho` 2018-08-07 14:17:11 INFO Table dropped 2018-08-07 14:17:11 INFO Dropping table `darren`.`_t4_del` 2018-08-07 14:17:11 INFO Table dropped 2018-08-07 14:17:11 INFO Dropping table `darren`.`_t4_ghc` 2018-08-07 14:17:11 INFO Table dropped 2018-08-07 14:17:11 INFO Creating changelog table `darren`.`_t4_ghc` 2018-08-07 14:17:11 INFO Changelog table created 2018-08-07 14:17:11 INFO Creating ghost table `darren`.`_t4_gho` 2018-08-07 14:17:11 INFO Ghost table created 2018-08-07 14:17:11 INFO Altering ghost table `darren`.`_t4_gho` 2018-08-07 14:17:11 INFO Ghost table altered 2018-08-07 14:17:11 INFO Intercepted changelog state GhostTableMigrated 2018-08-07 14:17:11 INFO Waiting for ghost table to be migrated. Current lag is 0s 2018-08-07 14:17:11 INFO Handled changelog state GhostTableMigrated 2018-08-07 14:17:11 INFO Chosen shared unique key is PRIMARY 2018-08-07 14:17:11 INFO Shared columns are id,name,c1,c2,c4,c5,c6 2018-08-07 14:17:11 INFO Listening on unix socket file: /tmp/ghost.sock 2018-08-07 14:17:11 INFO Migration min values: [1] 2018-08-07 14:17:11 INFO Migration max values: [58597] 2018-08-07 14:17:11 INFO Waiting for first throttle metrics to be collected 2018-08-07 14:17:11 INFO First throttle metrics collected # Migrating `darren`.`t4`; Ghost table is `darren`.`_t4_gho` # Migrating shvm-5-39.58os.org:3306; inspecting shvm-5-39.58os.org:3306; executing on shvm-5-39.58os.org # Migration started at Tue Aug 07 14:17:11 +0800 2018 # chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=25; critical-load: Threads_running=64; nice-ratio: 0.000000 # throttle-additional-flag-file: /tmp/gh-ost.throttle # panic-flag-file: /tmp/ghost.panic.flag # Serving on unix socket: /tmp/ghost.sock
這些資訊是GH-OST相對自我解釋,他們大多表示一切順利。你將主要關注遷移並瞭解其是否順利進行。一旦遷移實際開始,你將看到如下輸出。
Copy: 0/58707 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: shvm-5-39.000040:337574146; State: migrating; ETA: N/A Copy: 0/58707 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: shvm-5-39.000040:337581355; State: migrating; ETA: N/A Copy: 27000/58707 46.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 2s(copy); streamer: shvm-5-39.000040:338201054; State: migrating; ETA: 2s Copy: 58000/58707 98.8%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 3s(copy); streamer: shvm-5-39.000040:338912890; State: migrating; ETA: 0s 2018-08-07 14:17:14 INFO Row copy complete
進度提示
Copy: 27000/58707 46.0%;58707指需要遷移總行數,27000指已經遷移的行數,46%指遷移完成的百分比。 Applied: 0,指在二進位制日誌中處理的event數量。在上面的例子中,遷移表沒有流量,因此沒有被處理日誌event。 Backlog: 0/1000,表示我們在讀取二進位制日誌方面表現良好,在二進位制日誌佇列中沒有任何積壓(Backlog)事件。 Backlog: 7/1000,當複製行時,在二進位制日誌中積壓了一些事件,並且需要應用。 Backlog: 1000/1000,表示我們的1000個事件的緩衝區已滿(程式寫死的1000個事件緩衝區,低版本是100個),此時就注意binlog寫入量非常大,gh-ost處理不過來event了,可能需要暫停binlog讀取,需要優先應用緩衝區的事件。 streamer: shvm-5-39.000040:338912890;表示當前已經應用到binlog檔案位置
狀態提示
每隔一定時間會列印友好提示: # Migrating `darren`.`t4`; Ghost table is `darren`.`_t4_gho` # Migrating shvm-5-39.58os.org:3306; inspecting shvm-5-39.58os.org:3306; executing on shvm-5-39.58os.org # Migration started at Tue Aug 07 14:17:11 +0800 2018 # chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=25; critical-load: Threads_running=64; nice-ratio: 0.000000 # throttle-additional-flag-file: /tmp/gh-ost.throttle # panic-flag-file: /tmp/ghost.panic.flag # Serving on unix socket: /tmp/ghost.sock
三、從庫模式
1、常用命令
gh-ost \ --max-load=Threads_running=16 \ --critical-load=Threads_running=32 \ --chunk-size=1000\ --initially-drop-old-table \ --initially-drop-ghost-table \ --initially-drop-socket-file \ --ok-to-drop-table \ --host="10.249.5.39" \ --port=3307 \ --user="dbadmin" \ --password="12345" \ --assume-rbr \ --allow-on-master \ --assume-master-host=10.249.5.39:3306 \ --database="gh_ost" \ --table="gh_01" \ --alter="add column c4 varchar(50) not null default ''" \ --panic-flag-file=/tmp/ghost.panic.flag \ --serve-socket-file=/tmp/ghost.sock \ --verbose \ --execute
四、測試模式
gh-ost \ --test-on-replica \ --max-load=Threads_running=16 \ --critical-load=Threads_running=32 \ --chunk-size=1000\ --initially-drop-old-table \ --initially-drop-ghost-table \ --initially-drop-socket-file \ --host="10.249.5.39" \ --port=3307 \ --user="dbadmin" \ --password="12345" \ --assume-rbr \ --database="gh_ost" \ --table="gh_01" \ --alter="add column c4 varchar(50) not null default ''" \ --panic-flag-file=/tmp/ghost.panic.flag \ --serve-socket-file=/tmp/ghost.sock \ --verbose \ --execute
引數說明
--test-on-replica 在從庫上執行遷移,但不進行最後的cut-over,並最後會停止複製執行緒,供測試人員進行資料對比 --migrate-on-replica 直接在從庫上遷移並cut-over,複製執行緒不會停止
五、暫停、恢復、終止、延遲切換
gh-ost的--serve-socket-file檔案用來監聽請求,比如可以動態調整效能方面引數,也可以進行暫停、恢復gh-ost執行緒。
#暫停 echo throttle | socat - /tmp/ghost.sock #恢復 echo no-throttle | socat - /tmp/ghost.sock #終止 對應panic-flag-file引數檔案,當tmp目錄存在該檔案立即停止 touch /tmp/ghost.panic.flag #延遲切換(cut-over階段) --postpone-cut-over-flag-file=/tmp/ghost.postpone.flag 當設定該引數時cut-over一直延遲切換,直到你刪除該檔案才進行切換 #動態調整效能引數 echo chunk-size=100 | socat - /tmp/ghost.sock