mysql 線上修改表結構工具 gh-ost

標籤：

 

gh-ost使用測試：

gh-ost -host=‘192.168.65.136‘ -user=root -password=‘‘ -database=‘haha‘ -chunk-size=100000  -allow-on-master -execute  -initially-drop-ghost-table -exact-rowcount --initially-drop-old-table  -alter=‘modify AreaID varchar(10)‘ -table=‘t_policyarea‘

  -allow-master-master        顯式的允許在主主叢集中運行          -allow-on-master        允許直接運行在主機上，如果沒有叢集使用這個選項，推薦用在從機上          -alter string        （必須參數）alter 語句 例如  add column_name type  ,  change column_name column_name type, modify column_name type; 等等 無需加alter table 字串   -assume-rbr        設定 ‘true‘ 當你確定使用 ‘ROW‘ binlog_format.    -chunk-size int        每一次迭代處理的行數，預設是1000 ，允許範圍（100-100000）    -concurrent-rowcount        (with --exact-rowcount), when true: count rows after row-copy begins, concurrently, and adjust row estimate later on; defaults false: first count rows, then start row copy    -conf string        設定檔    -critical-load --max-load        最大負載，設定一個閾值，超過這個閾值應用程式會自動結束， --max-load 同意義    -database string        資料庫名稱 必須欄位    -debug        debug模式    -default-retries int        在各種各樣的意外之前的重試次數 (default 60)    -exact-rowcount        copy前對row的數目進行精確的估計，以方便對執行進度精確判斷         注釋：這個沒什麼太大必要，精確統計對大表來說也是很耗時的  -execute        實際執行變更，如果不加這個選項預設都是測試然後退出，實際上並沒有改變原表結構    -host string        mysql主機名稱，最好是從機而不是主機 (default "127.0.0.1")    -initially-drop-ghost-table        如果運行了多次對同一個表修改，就會存在ghost表，使用這個選項來刪除存在的ghost表    -initially-drop-old-table        效果同上，預設是中斷停止          -initially-drop-socket-file        強行刪除現有的socket檔案。小心：這可能會刪除運行遷移的通訊端檔案！            -max-load string        命令格式 e.g: ‘Threads_running=100,Threads_connected=500‘.當伺服器達到這個負載那麼就會減少修改使用資源    -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    -ok-to-drop-table        結束後刪除舊錶，由於刪除會有一個很長時間的鎖，線上操作不建議，預設是false          -panic-flag-file string        when this file is created, gh-ost will immediately terminate, without cleanup    -password string        MYSQL伺服器密碼    -port int        mysql伺服器連接埠，均建議在從機上操作    -stack        在錯誤時添加堆疊追蹤            -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    -table string        必須參數，表名稱    -throttle-query string        每秒檢測一次，確保是輕量級的查詢，不對伺服器產生大的影響  -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)    -user string        mysql使用者    -verbose        冗餘輸出詳細資料    -version        版本號碼       -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")    -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         -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.    -quiet        quiet    -replication-lag-query string        Query that detects replication lag in seconds. Result can be a floating point (by default gh-ost issues SHOW SLAVE STATUS and reads Seconds_behind_master). If you‘re using pt-heartbeat, query would be something like: SELECT ROUND(UNIX_TIMESTAMP() - MAX(UNIX_TIMESTAMP(ts))) AS delay FROM my_schema.heartbeat    -serve-socket-file string        Unix socket file to serve on. Default: auto-determined and advertised upon startup    -serve-tcp-port int        TCP port to serve on. Default: disabled    -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        -approve-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 approves that gh-ost‘s interpretation si correct    -assume-master-host string        (optional) explicitly tell gh-ost the identity of the master. Format: some.host.com[:port] This is useful in master-master setups where you wish to pick an explicit master, or in a tungsten-replicator where gh-ost is unabel to determine the master     -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)     -heartbeat-interval-millis int        how frequently would gh-ost inject a heartbeat value (default 500)    -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    -max-lag-millis int        replication lag at which to throttle operation (default 1500)      

mysql 線上修改表結構工具 gh-ost