常用GHOST模板 GHOST常用參數： GHOST建議： GHOST所有參數： Usage of gh-ost: -allow-master-master 當運行MM雙主模式時需要顯示設置。 -allow-nullable-unique-key 設置是否運行唯一鍵列中使用NULL值，使用ghost ...
常用GHOST模板
##================================================## mysql_ip="127.0.0.1" mysql_port=3358 ghost_user="root" ghost_password="123.com" ghost_database="test1" ghost_table="tb002" ghost_command="ADD C4 INT COMMENT '測試列'" ./gh-ost \ --allow-on-master \ --max-load="Threads_running=256" \ --critical-load="Threads_running=512" \ --chunk-size=1000 \ --initially-drop-old-table \ --initially-drop-ghost-table \ --initially-drop-socket-file \ --timestamp-old-table \ --force-table-names="" \ --host="${mysql_ip}" \ --port="${mysql_port}" \ --user="${ghost_user}" \ --password="${ghost_password}" \ --database="${ghost_database}" \ --table="${ghost_table}" \ --alter="${ghost_command}" \ --verbose \ --execute
GHOST常用參數：
1、如果希望直接讀取主庫BINLOG，需要指定allow-on-master參數 2、如果想避免上次操作失敗產生的臨時表導致新操作失敗，建議使用initially-drop-ghost-table和initially-drop-socket-file參數 3、如果希望GHOST操作完成後刪除源表，使用ok-to-drop-table參數 4、如果希望GHOST操作完成後保留源表，建議使用timestamp-old-table參數且不使用ok-to-drop-table參數 5、使用參數max-load來控制主庫繁忙時暫停GHOST運行 6、使用參數critical-load來控制主庫繁忙時終止GHOST運行 7、使用參數throttle-flag-file或throttle-query來控制GHOST暫停運行 8、使用參數panic-flag-file來控制GHOST停止運行 9、使用參數throttle-control-replicas來控制GHOST對從庫複製延遲的影響
GHOST建議：
1、避免同一時間允許多個GHOST 2、請勿使用GHOST來創建唯一索引 3、建議從主庫讀取BINLOG，執行效率較高 4、建議設置critical-load和max-load時僅考慮Threads_running的值，使用Threads_connected容易因連接暴漲導致GHOST暫停或中止 5、在磁碟空間足夠的條件下，使用timestamp-old-table來保存源表，一方面立即刪除源表會造成IO壓力，另一方面防止執行中出現BUG或誤操作。
GHOST所有參數：
Usage of gh-ost: -allow-master-master 當運行MM雙主模式時需要顯示設置。 -allow-nullable-unique-key 設置是否運行唯一鍵列中使用NULL值，使用ghost創建唯一索引，存在數據丟失風險，慎用！ -allow-on-master 當使用主節點的BINLOG來操作時，需要設置該值為ON。 -alter string 修改命令，不包含ALTER TABLE部分。 -approve-renamed-columns ALTER 當使用ghost進行修改列名操作，需要設置該值 -ask-pass 提升輸入密碼 -assume-master-host string 在雙主或多主複製場景下，設置以哪個主節點的BINLOG為主。 -assume-rbr 假定使用基於ROW格式的複製模式 -check-flag Check if another flag exists/supported. This allows for cross-version scripting. Exits with 0 when all additional provided flags exist, nonzero otherwise. You must provide (dummy) values for flags that require a value. Example: gh-ost --check-flag --cut-over-lock-timeout-seconds --nice-ratio 0 -chunk-size int 設置每個批次複製數據的行數，取值範圍100至100000，預設為1000 -concurrent-rowcount (with --exact-rowcount), when true (default): count rows after row-copy begins, concurrently, and adjust row estimate later on; when false: first count rows, then start row copy (default true) 設置對拷貝記錄的計數方式。 -conf string 配置文件的路徑 -critical-load string Comma delimited status-name=threshold, same format as --max-load. When status exceeds threshold, app panics and quits 設置GHOST運行的最大閾值，當超過該閾值後，終止GHOST運行。 -critical-load-hibernate-seconds int When nonzero, critical-load does not panic and bail out; instead, gh-ost goes into hibernate for the specified duration. It will not read/write anything to from/to any server 當該值不為0時，如果達到critical-load設置的閾值，會休眠指定時間，然後再次檢查。 -critical-load-interval-millis int When 0, migration immediately bails out upon meeting critical-load. When non-zero, a second check is done after given interval, and migration only bails out if 2nd check still meets critical load 設置達到critical-load設置的閾值後的檢查間隔。 -cut-over string choose cut-over type (default|atomic, two-step) (default "atomic") 設置切換源表和新表表名的操作方式，預設為自動 -cut-over-lock-timeout-seconds int Max number of seconds to hold locks on tables while attempting to cut-over (retry attempted when lock exceeds timeout) (default 3) 設置在cut-over時獲取表鎖的超時時間。 -database string database name (mandatory) 資料庫名 -debug debug mode (very verbose) DEBUG模式 -default-retries int Default number of retries for various operations before panicking (default 60) 預設重試次數 -discard-foreign-keys DANGER! This flag will migrate a table that has foreign keys and will NOT create foreign keys on the ghost table, thus your altered table will have NO foreign keys. This is useful for intentional dropping of foreign keys 設置是否丟棄外鍵，謹慎設置。 -dml-batch-size int batch size for DML events to apply in a single transaction (range 1-100) (default 10) -exact-rowcount actually count table rows as opposed to estimate them (results in more accurate progress estimation) -execute actually execute the alter & migrate the table. Default is noop: do some tests and exit 設置運行GHOST操作，預設不執行，僅檢查並退出。 -force-named-cut-over When true, the 'unpostpone|cut-over' interactive command must name the migrated table -force-table-names string table name prefix to be used on the temporary tables 設置臨時表的首碼 -heartbeat-interval-millis int how frequently would gh-ost inject a heartbeat value (default 100) 設置檢查心跳時間間隔 -help Display usage -hooks-hint string arbitrary message to be injected to hooks via GH_OST_HOOKS_HINT, for your convenience -hooks-path string directory where hook files are found (default: empty, ie. hooks disabled). Hook files found on this path, and conforming to hook naming conventions will be executed -host string MySQL hostname (preferably a replica, not the master) (default "127.0.0.1") -initially-drop-ghost-table Drop a possibly existing Ghost table (remains from a previous run?) before beginning operation. Default is to panic and abort if such table exists 設置是否刪除已存在的ghost表，預設不刪除，如果ghost表存在，則立即退出。 -initially-drop-old-table Drop a possibly existing OLD table (remains from a previous run?) before beginning operation. Default is to panic and abort if such table exists 設置是否刪除已存在的OLD表，預設不刪除，如果存在，則立即退出。 -initially-drop-socket-file Should gh-ost forcibly delete an existing socket file. Be careful: this might drop the socket file of a running migration! 設置是否刪除已有的socket文件 -master-password string MySQL password on master, if different from that on replica. Requires --assume-master-host -master-user string MySQL user on master, if different from that on replica. Requires --assume-master-host -max-lag-millis int replication lag at which to throttle operation (default 1500) 設置複製延遲閾值，如果複製延遲超過該閾值，則暫停GHOST操作。 -max-load string Comma delimited status-name=threshold. e.g: 'Threads_running=100,Threads_connected=500'. When status exceeds threshold, app throttles writes 設置GHOST允許閾值，如果超過該值，則暫停GHOST操作。 -migrate-on-replica Have the migration run on a replica, not on the master. This will do the full migration on the replica including cut-over (as opposed to --test-on-replica) -nice-ratio float force being 'nice', imply sleep time per chunk time; range: [0.0..100.0]. Example values: 0 is aggressive. 1: for every 1ms spent copying rows, sleep additional 1ms (effectively doubling runtime); 0.7: for every 10ms spend in a rowcopy chunk, spend 7ms sleeping immediately after 設置顯示GHOST進度的格式 -ok-to-drop-table Shall the tool drop the old table at end of operation. DROPping tables can be a long locking operation, which is why I'm not doing it by default. I'm an online tool, yes? 設置GHOST完成後是否刪除OLD表，對於超大表，刪除表操作會持續很長時間，並且造成性能問題，建議將表刪除操作放到業務低峰期操作。 -panic-flag-file string when this file is created, gh-ost will immediately terminate, without cleanup 當GHOST檢查到該文件存在時，會立即退出而不做任何清理。 -password string MySQL password -port int MySQL port (preferably a replica, not the master) (default 3306) -postpone-cut-over-flag-file string while this file exists, migration will postpone the final stage of swapping tables, and will keep on syncing the ghost table. Cut-over/swapping would be ready to perform the moment the file is deleted. 當GHOST檢查到該文件存在時，會將切換表名操作延期，直到該文件被刪除。 -quiet quiet -replica-server-id uint server id used by gh-ost process. Default: 99999 (default 99999) 指定GHOST操作實例的server-id -replication-lag-query string Deprecated. gh-ost uses an internal, subsecond resolution query 已棄用。 -serve-socket-file string Unix socket file to serve on. Default: auto-determined and advertised upon startup 指定實例使用的socket文件路徑 -serve-tcp-port int TCP port to serve on. Default: disabled -skip-foreign-key-checks set to 'true' when you know for certain there are no foreign keys on your table, and wish to skip the time it takes for gh-ost to verify that 忽略外鍵檢查 -skip-renamed-columns ALTER in case your ALTER statement renames columns, gh-ost will note that and offer its interpretation of the rename. By default gh-ost does not proceed to execute. This flag tells gh-ost to skip the renamed columns, i.e. to treat what gh-ost thinks are renamed columns as unrelated columns. NOTE: you may lose column data 忽略列名修改操作 -stack add stack trace upon error -switch-to-rbr let this tool automatically switch binary log format to 'ROW' on the replica, if needed. The format will NOT be switched back. I'm too scared to do that, and wish to protect you if you happen to execute another migration while this one is running 使用GHOST工具修改複製格式為ROW模式 -table string table name (mandatory) 修改表的表名 -test-on-replica Have the migration run on a replica, not on the master. At the end of migration replication is stopped, and tables are swapped and immediately swap-revert. Replication remains stopped and you can compare the two tables for building trust 在複製節點上進行測試 -test-on-replica-skip-replica-stop When --test-on-replica is enabled, do not issue commands stop replication (requires --test-on-replica) -throttle-additional-flag-file string operation pauses when this file exists; hint: keep default, use for throttling multiple gh-ost operations (default "/tmp/gh-ost.throttle") 當該文件存在時，GHOST操作會暫停，預設值為/tmp/gh-ost.throttle -throttle-control-replicas string List of replicas on which to check for lag; comma delimited. Example: myhost1.com:3306,myhost2.com,myhost3.com:3307 設置要複製延遲的從庫 -throttle-flag-file string operation pauses when this file exists; hint: use a file that is specific to the table being altered 當該文件存在時，GHOST操作會暫停 -throttle-http string when given, gh-ost checks given URL via HEAD request; any response code other than 200 (OK) causes throttling; make sure it has low latency response 基於HTTP方式來決定GHOST暫停 -throttle-query string when given, issued (every second) to check if operation should throttle. Expecting to return zero for no-throttle, >0 for throttle. Query is issued on the migrated server. Make sure this query is lightweight 基於QUERY方式來決定GHOST暫停 -timestamp-old-table Use a timestamp in old table name. This makes old table names unique and non conflicting cross migrations GHOST操作完成後，將老表設置帶有時間戳 -tungsten explicitly let gh-ost know that you are running on a tungsten-replication based topology (you are likely to also provide --assume-master-host) tungsten-replication是一種異構複製技術，如MySQL到MongoDB的複製 -user string MySQL user -verbose verbose 設置該參數會導致GHOST輸出更多詳細信息 -version Print version & exitView Code
