溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“MySQL gh-ost測試分析”，在日常操作中，相信很多人在MySQL gh-ost測試分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL gh-ost測試分析”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
環境
master: 10.0.0.11 vm1 3307
slave: 10.0.0.12 vm2 3307
一 下載&&安裝
到 https://github.com/github/gh-ost/releases直接下載編譯好的binary包。
如：gh-ost-binary-linux-20160815105421.tar.gz
上傳到服務器，如slave。
解壓。
二 測試
測試一：連接到從庫，在主庫做變更。
這是gh-ost 默認的工作方式。
連接從庫，行數據在主庫上讀寫。
讀取從庫的二進制日志，將變更應用到主庫
在從庫收集表格式，字段&索引，行數等信息
在從庫上讀取內部的變更事件（如心跳事件）
在主庫切換表
通過從庫更改主庫上的表結構，在主庫新表和舊表切換前，舊表和臨時表都存在于所有主從庫。
注：這種方式是基于主庫的表結構及表數據做的變更，所以，變更完成后，從庫的數據將會和主庫一致。
**************************************************
示例：連接從庫vm2，給test.t1表的添加列Create_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N'。
#############
變更前
#############
vm1上主庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+
| % | root | Y | Y | Y | Y |
| 10.0.0.% | repl | N | N | N | N |
| 127.0.0.1 | root | Y | Y | Y | Y |
| ::1 | root | Y | Y | Y | Y |
| localhost | | N | N | N | N |
| localhost | mysql.sys | N | N | N | N |
| localhost | root | Y | Y | Y | Y |
| localhost | zabbix | N | N | N | N |
+-----------+-----------+-------------+-------------+-------------+-------------+
8 rows in set (0.00 sec)
vm2上從庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+
| % | root | Y | Y | Y | Y |
| 127.0.0.1 | root | Y | Y | Y | Y |
| ::1 | root | Y | Y | Y | Y |
| localhost | | N | N | N | N |
| localhost | mysql.sys | N | N | N | N |
| localhost | root | Y | Y | Y | Y |
| localhost | zabbix | N | N | N | N |
+-----------+-----------+-------------+-------------+-------------+-------------+
7 rows in set (0.00 sec)
注意從庫的t1表比主庫的t1表少一行數據。
###########################
開始變更，輸出如下：
[root@vm2 ~]# ./gh-ost --conf=/etc/my.cnf --port=3307 --host=vm2 --database="test" --table="t1" --alter="add Create_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N'" --verbose --initially-drop-ghost-table --execute
2016-08-16 15:28:41 INFO starting gh-ost 1.0.8
2016-08-16 15:28:41 INFO Migrating `test`.`t1`
2016-08-16 15:28:41 INFO connection validated on vm2:3307
2016-08-16 15:28:41 INFO User has ALL privileges
2016-08-16 15:28:41 INFO binary logs validated on vm2:3307
2016-08-16 15:28:41 INFO Restarting replication on vm2:3307 to make sure binlog settings apply to replication thread
2016-08-16 15:28:41 INFO Table found. Engine=MyISAM
2016-08-16 15:28:41 INFO Estimated number of rows via EXPLAIN: 7
2016-08-16 15:28:41 INFO Master found to be vm1:3307
2016-08-16 15:28:41 INFO connection validated on vm2:3307
2016-08-16 15:28:41 INFO Registering replica at vm2:3307
2016-08-16 15:28:41 INFO Connecting binlog streamer at mysql_bin.000007:435807
2016-08-16 15:28:41 INFO rotate to next log name: mysql_bin.000007
2016-08-16 15:28:41 INFO connection validated on vm1:3307
2016-08-16 15:28:41 INFO connection validated on vm1:3307
2016-08-16 15:28:41 INFO Droppping table `test`.`_t1_gho`
2016-08-16 15:28:41 INFO Table dropped
2016-08-16 15:28:41 INFO Droppping table `test`.`_t1_ghc`
2016-08-16 15:28:41 INFO Table dropped
2016-08-16 15:28:41 INFO Creating changelog table `test`.`_t1_ghc`
2016-08-16 15:28:41 INFO Changelog table created
2016-08-16 15:28:41 INFO Creating ghost table `test`.`_t1_gho`
2016-08-16 15:28:41 INFO Ghost table created
2016-08-16 15:28:41 INFO Altering ghost table `test`.`_t1_gho`
2016-08-16 15:28:41 INFO Ghost table altered
2016-08-16 15:28:41 INFO Chosen shared unique key is PRIMARY
2016-08-16 15:28:41 INFO Shared columns are Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv
2016-08-16 15:28:41 INFO Listening on unix socket file: /tmp/gh-ost.test.t1.sock
2016-08-16 15:28:41 INFO Migration min values: [%,root]
2016-08-16 15:28:41 INFO Migration max values: [localhost,zabbix]
# Migrating `test`.`t1`; Ghost table is `test`.`_t1_gho`
# Migrating vm1:3307; inspecting vm2:3307; executing on vm2
# Migration started at Tue Aug 16 15:28:41 +0800 2016
# chunk-size: 1000; max-lag-millis: 1500ms; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.t1.sock
Copy: 0/7 0.0%; Applied: 0; Backlog: 0/100; Time: 0s(total), 0s(copy); streamer: mysql_bin.000007:437697; ETA: N/A
2016-08-16 15:28:42 INFO Row copy complete
Copy: 0/7 0.0%; Applied: 0; Backlog: 0/100; Time: 1s(total), 1s(copy); streamer: mysql_bin.000007:438790; ETA: N/A
Copy: 8/7 114.3%; Applied: 0; Backlog: 0/100; Time: 1s(total), 1s(copy); streamer: mysql_bin.000007:439243; ETA: Due
2016-08-16 15:28:43 INFO Grabbing voluntary lock: gh-ost.322.lock
2016-08-16 15:28:43 INFO Setting LOCK timeout as 6 seconds
2016-08-16 15:28:43 INFO Looking for magic cut-over table
2016-08-16 15:28:43 INFO Creating magic cut-over table `test`.`_t1_del`
2016-08-16 15:28:43 INFO Magic cut-over table created
2016-08-16 15:28:43 INFO Locking `test`.`t1`, `test`.`_t1_del`
2016-08-16 15:28:43 INFO Tables locked
2016-08-16 15:28:43 INFO Session locking original & magic tables is 322
2016-08-16 15:28:43 INFO Writing changelog state: AllEventsUpToLockProcessed
2016-08-16 15:28:43 INFO Waiting for events up to lock
Copy: 8/7 114.3%; Applied: 0; Backlog: 1/100; Time: 2s(total), 1s(copy); streamer: mysql_bin.000007:441347; ETA: Due
2016-08-16 15:28:43 INFO Done waiting for events up to lock; duration=917.995641ms
# Migrating `test`.`t1`; Ghost table is `test`.`_t1_gho`
# Migrating vm1:3307; inspecting vm2:3307; executing on vm2
# Migration started at Tue Aug 16 15:28:41 +0800 2016
# chunk-size: 1000; max-lag-millis: 1500ms; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.t1.sock
Copy: 8/7 114.3%; Applied: 0; Backlog: 0/100; Time: 2s(total), 1s(copy); streamer: mysql_bin.000007:441760; ETA: Due
2016-08-16 15:28:43 INFO Setting RENAME timeout as 3 seconds
2016-08-16 15:28:43 INFO Session renaming tables is 321
2016-08-16 15:28:43 INFO Issuing and expecting this to block: rename /* gh-ost */ table `test`.`t1` to `test`.`_t1_del`, `test`.`_t1_gho` to `test`.`t1`
2016-08-16 15:28:44 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2016-08-16 15:28:44 INFO Checking session lock: gh-ost.322.lock
2016-08-16 15:28:44 INFO Connection holding lock on original table still exists
2016-08-16 15:28:44 INFO Will now proceed to drop magic table and unlock tables
2016-08-16 15:28:44 INFO Dropping magic cut-over table
2016-08-16 15:28:44 INFO Releasing lock from `test`.`t1`, `test`.`_t1_del`
2016-08-16 15:28:44 INFO Tables unlocked
2016-08-16 15:28:44 INFO Tables renamed
2016-08-16 15:28:44 INFO Lock & rename duration: 992.719724ms. During this time, queries on `t1` were blocked
2016-08-16 15:28:44 INFO Looking for magic cut-over table
2016-08-16 15:28:44 INFO Droppping table `test`.`_t1_ghc`
2016-08-16 15:28:44 INFO Table dropped
2016-08-16 15:28:44 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:
2016-08-16 15:28:44 INFO -- drop table `test`.`_t1_del`
2016-08-16 15:28:44 INFO Done migrating `test`.`t1`
2016-08-16 15:28:44 INFO Done
#############
變更后
#############
查看表結構及數據
vm1上主庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+
| % | root | Y | Y | Y | Y | N |
| 10.0.0.% | repl | N | N | N | N | N |
| 127.0.0.1 | root | Y | Y | Y | Y | N |
| ::1 | root | Y | Y | Y | Y | N |
| localhost | | N | N | N | N | N |
| localhost | mysql.sys | N | N | N | N | N |
| localhost | root | Y | Y | Y | Y | N |
| localhost | zabbix | N | N | N | N | N |
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+
8 rows in set (0.00 sec)
vm2上從庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+
| % | root | Y | Y | Y | Y | N |
| 10.0.0.% | repl | N | N | N | N | N |
| 127.0.0.1 | root | Y | Y | Y | Y | N |
| ::1 | root | Y | Y | Y | Y | N |
| localhost | | N | N | N | N | N |
| localhost | mysql.sys | N | N | N | N | N |
| localhost | root | Y | Y | Y | Y | N |
| localhost | zabbix | N | N | N | N | N |
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+
8 rows in set (0.00 sec)
可以看到主從庫上t1表都已添加新列，并且主從數據都變為8條，和原來主庫的數據條數一樣。
測試二：連接到主庫
如果你沒有從庫，或者不想使用從庫，你可以直接在主庫上操作。gh-ost 將會直接在主庫上進行所有操作。你需要持續關注復制延遲問題。
你的主庫的二進制日志必須是 RBR 格式。
在這個模式中你必須指定 --allow-on-master 參數
**************************************************
示例：將表test.t1的Create_priv列刪除。
#############
變更前
#############
查看表結構及數據
vm1上主庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+
| % | root | Y | Y | Y | Y | N |
| 10.0.0.% | repl | N | N | N | N | N |
| 127.0.0.1 | root | Y | Y | Y | Y | N |
| ::1 | root | Y | Y | Y | Y | N |
| localhost | | N | N | N | N | N |
| localhost | mysql.sys | N | N | N | N | N |
| localhost | root | Y | Y | Y | Y | N |
| localhost | zabbix | N | N | N | N | N |
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+
8 rows in set (0.00 sec)
vm2上從庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+
| % | root | Y | Y | Y | Y | N |
| 10.0.0.% | repl | N | N | N | N | N |
| 127.0.0.1 | root | Y | Y | Y | Y | N |
| ::1 | root | Y | Y | Y | Y | N |
| localhost | | N | N | N | N | N |
| localhost | mysql.sys | N | N | N | N | N |
| localhost | root | Y | Y | Y | Y | N |
| localhost | zabbix | N | N | N | N | N |
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+
8 rows in set (0.00 sec)
#####################
開始變更，輸出如下：
[root@vm2 ~]# ./gh-ost --conf=/etc/my.cnf --port=3307 --host=vm1 --database="test" --table="t1" --alter="drop Create_priv" --allow-on-master --verbose --initially-drop-ghost-table --execute
2016-08-16 15:35:05 INFO starting gh-ost 1.0.8
2016-08-16 15:35:05 INFO Migrating `test`.`t1`
2016-08-16 15:35:05 INFO connection validated on vm1:3307
2016-08-16 15:35:05 INFO User has ALL privileges
2016-08-16 15:35:05 INFO binary logs validated on vm1:3307
2016-08-16 15:35:05 INFO Restarting replication on vm1:3307 to make sure binlog settings apply to replication thread
2016-08-16 15:35:05 INFO Table found. Engine=MyISAM
2016-08-16 15:35:06 INFO Estimated number of rows via EXPLAIN: 8
2016-08-16 15:35:06 INFO Master found to be vm1:3307
2016-08-16 15:35:06 INFO connection validated on vm1:3307
2016-08-16 15:35:06 INFO Registering replica at vm1:3307
2016-08-16 15:35:06 INFO Connecting binlog streamer at mysql_bin.000026:583342
2016-08-16 15:35:06 INFO rotate to next log name: mysql_bin.000026
2016-08-16 15:35:06 INFO connection validated on vm1:3307
2016-08-16 15:35:06 INFO connection validated on vm1:3307
2016-08-16 15:35:06 INFO Droppping table `test`.`_t1_gho`
2016-08-16 15:35:06 INFO Table dropped
2016-08-16 15:35:06 INFO Droppping table `test`.`_t1_ghc`
2016-08-16 15:35:06 INFO Table dropped
2016-08-16 15:35:06 INFO Creating changelog table `test`.`_t1_ghc`
2016-08-16 15:35:06 INFO Changelog table created
2016-08-16 15:35:06 INFO Creating ghost table `test`.`_t1_gho`
2016-08-16 15:35:06 INFO Ghost table created
2016-08-16 15:35:06 INFO Altering ghost table `test`.`_t1_gho`
2016-08-16 15:35:06 INFO Ghost table altered
2016-08-16 15:35:06 INFO Chosen shared unique key is PRIMARY
2016-08-16 15:35:06 INFO Shared columns are Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv
2016-08-16 15:35:06 INFO Listening on unix socket file: /tmp/gh-ost.test.t1.sock
2016-08-16 15:35:06 INFO Migration min values: [%,root]
2016-08-16 15:35:06 INFO Migration max values: [localhost,zabbix]
# Migrating `test`.`t1`; Ghost table is `test`.`_t1_gho`
# Migrating vm1:3307; inspecting vm1:3307; executing on vm2
# Migration started at Tue Aug 16 15:35:05 +0800 2016
# chunk-size: 1000; max-lag-millis: 1500ms; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.t1.sock
Copy: 0/8 0.0%; Applied: 0; Backlog: 0/100; Time: 0s(total), 0s(copy); streamer: mysql_bin.000026:585540; ETA: N/A
[MySQL] 2016/08/16 15:35:07 statement.go:27: Invalid Connection
Copy: 0/8 0.0%; Applied: 0; Backlog: 0/100; Time: 1s(total), 1s(copy); streamer: mysql_bin.000026:585968; ETA: N/A
2016-08-16 15:35:07 INFO Row copy complete
Copy: 8/8 100.0%; Applied: 0; Backlog: 0/100; Time: 1s(total), 1s(copy); streamer: mysql_bin.000026:587243; ETA: 0s
2016-08-16 15:35:07 INFO Grabbing voluntary lock: gh-ost.324.lock
2016-08-16 15:35:07 INFO Setting LOCK timeout as 6 seconds
2016-08-16 15:35:07 INFO Looking for magic cut-over table
2016-08-16 15:35:07 INFO Creating magic cut-over table `test`.`_t1_del`
2016-08-16 15:35:07 INFO Magic cut-over table created
2016-08-16 15:35:07 INFO Locking `test`.`t1`, `test`.`_t1_del`
2016-08-16 15:35:07 INFO Tables locked
2016-08-16 15:35:07 INFO Session locking original & magic tables is 324
2016-08-16 15:35:07 INFO Writing changelog state: AllEventsUpToLockProcessed
2016-08-16 15:35:07 INFO Waiting for events up to lock
Copy: 8/8 100.0%; Applied: 0; Backlog: 1/100; Time: 2s(total), 1s(copy); streamer: mysql_bin.000026:588615; ETA: 0s
2016-08-16 15:35:08 INFO Done waiting for events up to lock; duration=926.436868ms
# Migrating `test`.`t1`; Ghost table is `test`.`_t1_gho`
# Migrating vm1:3307; inspecting vm1:3307; executing on vm2
# Migration started at Tue Aug 16 15:35:05 +0800 2016
# chunk-size: 1000; max-lag-millis: 1500ms; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.t1.sock
Copy: 8/8 100.0%; Applied: 0; Backlog: 0/100; Time: 2s(total), 1s(copy); streamer: mysql_bin.000026:589432; ETA: 0s
2016-08-16 15:35:08 INFO Setting RENAME timeout as 3 seconds
2016-08-16 15:35:08 INFO Session renaming tables is 328
2016-08-16 15:35:08 INFO Issuing and expecting this to block: rename /* gh-ost */ table `test`.`t1` to `test`.`_t1_del`, `test`.`_t1_gho` to `test`.`t1`
2016-08-16 15:35:08 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2016-08-16 15:35:08 INFO Checking session lock: gh-ost.324.lock
2016-08-16 15:35:08 INFO Connection holding lock on original table still exists
2016-08-16 15:35:08 INFO Will now proceed to drop magic table and unlock tables
2016-08-16 15:35:08 INFO Dropping magic cut-over table
2016-08-16 15:35:08 INFO Releasing lock from `test`.`t1`, `test`.`_t1_del`
2016-08-16 15:35:08 INFO Tables unlocked
2016-08-16 15:35:08 INFO Tables renamed
2016-08-16 15:35:08 INFO Lock & rename duration: 1.027750659s. During this time, queries on `t1` were blocked
2016-08-16 15:35:08 INFO Looking for magic cut-over table
2016-08-16 15:35:08 INFO Droppping table `test`.`_t1_ghc`
2016-08-16 15:35:08 INFO Table dropped
2016-08-16 15:35:08 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:
2016-08-16 15:35:08 INFO -- drop table `test`.`_t1_del`
2016-08-16 15:35:08 INFO Done migrating `test`.`t1`
2016-08-16 15:35:08 INFO Done
#############
變更后
#############
vm1上主庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+
| % | root | Y | Y | Y | Y |
| 10.0.0.% | repl | N | N | N | N |
| 127.0.0.1 | root | Y | Y | Y | Y |
| ::1 | root | Y | Y | Y | Y |
| localhost | | N | N | N | N |
| localhost | mysql.sys | N | N | N | N |
| localhost | root | Y | Y | Y | Y |
| localhost | zabbix | N | N | N | N |
+-----------+-----------+-------------+-------------+-------------+-------------+
8 rows in set (0.00 sec)
vm2上從庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+
| % | root | Y | Y | Y | Y |
| 10.0.0.% | repl | N | N | N | N |
| 127.0.0.1 | root | Y | Y | Y | Y |
| ::1 | root | Y | Y | Y | Y |
| localhost | | N | N | N | N |
| localhost | mysql.sys | N | N | N | N |
| localhost | root | Y | Y | Y | Y |
| localhost | zabbix | N | N | N | N |
+-----------+-----------+-------------+-------------+-------------+-------------+
8 rows in set (0.01 sec)
測試三：在從庫遷移/測試
該模式會在從庫執行遷移操作。gh-ost 會簡單的連接到主庫，此后所有的操作都在從庫執行，
不會對主庫進行任何的改動。整個操作過程中，gh-ost 將控制速度保證從庫可以及時的進行數據同步
--migrate-on-replica 表示 gh-ost 會直接在從庫上進行遷移操作。即使在復制運行階段也可以進行表的切換操作。
--test-on-replica 表示 遷移操作只是為了測試在切換之前復制會停止，然后會進行切換操作，然后在切換回來，你的原始表最終還是原始表。兩個表都會保存下來，復制操作是停止的。你可以對這兩個表進行一致性檢查等測試操作。
**************************************************
示例：在從庫刪除test.t1表的Delete_priv列。只在從庫更改表結構，然后在從庫切換新表，即主庫是舊表結構，從庫是新表結構。
#############
變更前
#############
vm1上主庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+
| % | root | Y | Y | Y | Y |
| 10.0.0.% | repl | N | N | N | N |
| 127.0.0.1 | root | Y | Y | Y | Y |
| ::1 | root | Y | Y | Y | Y |
| localhost | | N | N | N | N |
| localhost | mysql.sys | N | N | N | N |
| localhost | root | Y | Y | Y | Y |
| localhost | zabbix | N | N | N | N |
+-----------+-----------+-------------+-------------+-------------+-------------+
8 rows in set (0.00 sec)
vm2上從庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+
| % | root | Y | Y | Y | N |
| 10.0.0.% | repl | N | N | N | N |
| 127.0.0.1 | root | Y | Y | Y | N |
| ::1 | root | Y | Y | Y | N |
| localhost | | N | N | N | N |
| localhost | mysql.sys | N | N | N | N |
| localhost | root | Y | Y | Y | N |
+-----------+-----------+-------------+-------------+-------------+-------------+
7 rows in set (0.00 sec)
#####################
開始變更，輸出如下：
[root@vm2 ~]# ./gh-ost --conf=/etc/my.cnf --port=3307 --host=vm2 --database="test" --table="t1" --alter="drop Delete_priv" --verbose --initially-drop-ghost-table --migrate-on-replica --execute
2016-08-16 15:54:14 INFO starting gh-ost 1.0.8
2016-08-16 15:54:14 INFO Migrating `test`.`t1`
2016-08-16 15:54:14 INFO connection validated on vm2:3307
2016-08-16 15:54:14 INFO User has ALL privileges
2016-08-16 15:54:14 INFO binary logs validated on vm2:3307
2016-08-16 15:54:14 INFO Restarting replication on vm2:3307 to make sure binlog settings apply to replication thread
2016-08-16 15:54:14 INFO Table found. Engine=MyISAM
2016-08-16 15:54:15 INFO Estimated number of rows via EXPLAIN: 7
2016-08-16 15:54:15 INFO --test-on-replica or --migrate-on-replica given. Will not execute on master vm1:3307 but rather on replica vm2:3307 itself
2016-08-16 15:54:15 INFO Master found to be vm2:3307
2016-08-16 15:54:15 INFO connection validated on vm2:3307
2016-08-16 15:54:15 INFO Registering replica at vm2:3307
2016-08-16 15:54:15 INFO Connecting binlog streamer at mysql_bin.000007:486891
2016-08-16 15:54:15 INFO connection validated on vm2:3307
2016-08-16 15:54:15 INFO connection validated on vm2:3307
2016-08-16 15:54:15 INFO Droppping table `test`.`_t1_gho`
2016-08-16 15:54:15 INFO Table dropped
2016-08-16 15:54:15 INFO rotate to next log name: mysql_bin.000007
2016-08-16 15:54:15 INFO Droppping table `test`.`_t1_ghc`
2016-08-16 15:54:15 INFO Table dropped
2016-08-16 15:54:15 INFO Creating changelog table `test`.`_t1_ghc`
2016-08-16 15:54:15 INFO Changelog table created
2016-08-16 15:54:15 INFO Creating ghost table `test`.`_t1_gho`
2016-08-16 15:54:15 INFO Ghost table created
2016-08-16 15:54:15 INFO Altering ghost table `test`.`_t1_gho`
2016-08-16 15:54:15 INFO Ghost table altered
2016-08-16 15:54:15 INFO Chosen shared unique key is PRIMARY
2016-08-16 15:54:15 INFO Shared columns are Host,User,Select_priv,Insert_priv,Update_priv
2016-08-16 15:54:15 INFO Listening on unix socket file: /tmp/gh-ost.test.t1.sock
2016-08-16 15:54:15 INFO Migration min values: [%,root]
2016-08-16 15:54:15 INFO Migration max values: [localhost,root]
# Migrating `test`.`t1`; Ghost table is `test`.`_t1_gho`
# Migrating vm2:3307; inspecting vm2:3307; executing on vm2
# Migration started at Tue Aug 16 15:54:14 +0800 2016
# chunk-size: 1000; max-lag-millis: 1500ms; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.t1.sock
Copy: 0/7 0.0%; Applied: 0; Backlog: 0/100; Time: 0s(total), 0s(copy); streamer: mysql_bin.000007:489090; ETA: N/A
Copy: 0/7 0.0%; Applied: 0; Backlog: 0/100; Time: 1s(total), 1s(copy); streamer: mysql_bin.000007:489906; ETA: N/A
2016-08-16 15:54:16 INFO Row copy complete
Copy: 7/7 100.0%; Applied: 0; Backlog: 0/100; Time: 1s(total), 1s(copy); streamer: mysql_bin.000007:490762; ETA: 0s
2016-08-16 15:54:16 INFO Grabbing voluntary lock: gh-ost.347.lock
2016-08-16 15:54:16 INFO Setting LOCK timeout as 6 seconds
2016-08-16 15:54:16 INFO Looking for magic cut-over table
2016-08-16 15:54:16 INFO Creating magic cut-over table `test`.`_t1_ghr`
2016-08-16 15:54:16 INFO Magic cut-over table created
2016-08-16 15:54:16 INFO Locking `test`.`t1`, `test`.`_t1_ghr`
2016-08-16 15:54:16 INFO Tables locked
2016-08-16 15:54:16 INFO Session locking original & magic tables is 347
2016-08-16 15:54:16 INFO Writing changelog state: AllEventsUpToLockProcessed
2016-08-16 15:54:16 INFO Waiting for events up to lock
Copy: 7/7 100.0%; Applied: 0; Backlog: 1/100; Time: 2s(total), 1s(copy); streamer: mysql_bin.000007:492522; ETA: 0s
2016-08-16 15:54:17 INFO Done waiting for events up to lock; duration=939.861214ms
# Migrating `test`.`t1`; Ghost table is `test`.`_t1_gho`
# Migrating vm2:3307; inspecting vm2:3307; executing on vm2
# Migration started at Tue Aug 16 15:54:14 +0800 2016
# chunk-size: 1000; max-lag-millis: 1500ms; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.t1.sock
Copy: 7/7 100.0%; Applied: 0; Backlog: 0/100; Time: 2s(total), 1s(copy); streamer: mysql_bin.000007:492951; ETA: 0s
2016-08-16 15:54:17 INFO Setting RENAME timeout as 3 seconds
2016-08-16 15:54:17 INFO Session renaming tables is 348
2016-08-16 15:54:17 INFO Issuing and expecting this to block: rename /* gh-ost */ table `test`.`t1` to `test`.`_t1_ghr`, `test`.`_t1_gho` to `test`.`t1`
2016-08-16 15:54:17 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2016-08-16 15:54:17 INFO Checking session lock: gh-ost.347.lock
2016-08-16 15:54:17 INFO Connection holding lock on original table still exists
2016-08-16 15:54:17 INFO Will now proceed to drop magic table and unlock tables
2016-08-16 15:54:17 INFO Dropping magic cut-over table
2016-08-16 15:54:17 INFO Releasing lock from `test`.`t1`, `test`.`_t1_ghr`
2016-08-16 15:54:17 INFO Tables unlocked
2016-08-16 15:54:17 INFO Tables renamed
2016-08-16 15:54:17 INFO Lock & rename duration: 990.379438ms. During this time, queries on `t1` were blocked
2016-08-16 15:54:17 INFO Looking for magic cut-over table
2016-08-16 15:54:17 INFO Droppping table `test`.`_t1_ghc`
2016-08-16 15:54:17 INFO Table dropped
2016-08-16 15:54:17 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:
2016-08-16 15:54:17 INFO -- drop table `test`.`_t1_ghr`
2016-08-16 15:54:17 INFO Done migrating `test`.`t1`
2016-08-16 15:54:17 INFO Done
#############
變更后
#############
vm1上主庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+
| % | root | Y | Y | Y | Y |
| 10.0.0.% | repl | N | N | N | N |
| 127.0.0.1 | root | Y | Y | Y | Y |
| ::1 | root | Y | Y | Y | Y |
| localhost | | N | N | N | N |
| localhost | mysql.sys | N | N | N | N |
| localhost | root | Y | Y | Y | Y |
| localhost | zabbix | N | N | N | N |
+-----------+-----------+-------------+-------------+-------------+-------------+
8 rows in set (0.00 sec)
vm2上從庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv |
+-----------+-----------+-------------+-------------+-------------+
| % | root | Y | Y | Y |
| 10.0.0.% | repl | N | N | N |
| 127.0.0.1 | root | Y | Y | Y |
| ::1 | root | Y | Y | Y |
| localhost | | N | N | N |
| localhost | mysql.sys | N | N | N |
| localhost | root | Y | Y | Y |
+-----------+-----------+-------------+-------------+-------------+
7 rows in set (0.00 sec)
可以看出，這種方式只在從庫變更，是基于從庫舊表的結構及數據做變更。
**************************************************
示例： --test-on-replica 表示 遷移操作只是為了測試，在切換之前復制會停止，然后會進行切換操作，最后再切換回來，你的原始表最終還是原始表。兩個表都會保存下來，在此過程復制是停止的。你可以對這兩個表進行一致性檢查等測試操作。
#############
變更前
#############
vm1上主庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv |
+-----------+-----------+-------------+-------------+-------------+
| % | root | Y | Y | Y |
| 10.0.0.% | repl | N | N | N |
| 127.0.0.1 | root | Y | Y | Y |
| ::1 | root | Y | Y | Y |
| localhost | | N | N | N |
| localhost | mysql.sys | N | N | N |
| localhost | root | Y | Y | Y |
| localhost | zabbix | N | N | N |
| vm1 | aa | N | N | N |
+-----------+-----------+-------------+-------------+-------------+
9 rows in set (0.00 sec)
vm2上從庫t1表
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv |
+-----------+-----------+-------------+-------------+-------------+
| % | root | Y | Y | Y |
| 10.0.0.% | repl | N | N | N |
| 127.0.0.1 | root | Y | Y | Y |
| ::1 | root | Y | Y | Y |
| localhost | | N | N | N |
| localhost | mysql.sys | N | N | N |
| localhost | root | Y | Y | Y |
| localhost | zabbix | N | N | N |
+-----------+-----------+-------------+-------------+-------------+
8 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: vm1
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql_bin.000026
Read_Master_Log_Pos: 599990
Relay_Log_File: vm2-relay-bin.000066
Relay_Log_Pos: 505
Relay_Master_Log_File: mysql_bin.000026
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
注意從庫的t1表比主庫的t1表少一行數據。
###########################
開始變更，輸出如下：
[root@vm2 ~]# ./gh-ost --conf=/etc/my.cnf --port=3307 --host=vm2 --database="test" --table="t1" --alter="drop Update_priv" --verbose --initially-drop-ghost-table --test-on-replica --execute
2016-08-16 16:09:37 INFO starting gh-ost 1.0.8
2016-08-16 16:09:37 INFO Migrating `test`.`t1`
2016-08-16 16:09:37 INFO connection validated on vm2:3307
2016-08-16 16:09:37 INFO User has ALL privileges
2016-08-16 16:09:37 INFO binary logs validated on vm2:3307
2016-08-16 16:09:37 INFO Restarting replication on vm2:3307 to make sure binlog settings apply to replication thread
2016-08-16 16:09:37 INFO Table found. Engine=MyISAM
2016-08-16 16:09:37 INFO Estimated number of rows via EXPLAIN: 8
2016-08-16 16:09:37 INFO --test-on-replica or --migrate-on-replica given. Will not execute on master vm1:3307 but rather on replica vm2:3307 itself
2016-08-16 16:09:37 INFO Master found to be vm2:3307
2016-08-16 16:09:37 INFO connection validated on vm2:3307
2016-08-16 16:09:37 INFO Registering replica at vm2:3307
2016-08-16 16:09:37 INFO Connecting binlog streamer at mysql_bin.000007:513706
2016-08-16 16:09:37 INFO connection validated on vm2:3307
2016-08-16 16:09:37 INFO connection validated on vm2:3307
2016-08-16 16:09:37 INFO Droppping table `test`.`_t1_gho`
2016-08-16 16:09:37 INFO Table dropped
2016-08-16 16:09:37 INFO rotate to next log name: mysql_bin.000007
2016-08-16 16:09:37 INFO Droppping table `test`.`_t1_ghc`
2016-08-16 16:09:37 INFO Table dropped
2016-08-16 16:09:37 INFO Creating changelog table `test`.`_t1_ghc`
2016-08-16 16:09:37 INFO Changelog table created
2016-08-16 16:09:37 INFO Creating ghost table `test`.`_t1_gho`
2016-08-16 16:09:37 INFO Ghost table created
2016-08-16 16:09:37 INFO Altering ghost table `test`.`_t1_gho`
2016-08-16 16:09:37 INFO Ghost table altered
2016-08-16 16:09:37 INFO Chosen shared unique key is PRIMARY
2016-08-16 16:09:37 INFO Shared columns are Host,User,Select_priv,Insert_priv
2016-08-16 16:09:37 INFO Listening on unix socket file: /tmp/gh-ost.test.t1.sock
2016-08-16 16:09:37 INFO Migration min values: [%,root]
2016-08-16 16:09:37 INFO Migration max values: [localhost,zabbix]
# Migrating `test`.`t1`; Ghost table is `test`.`_t1_gho`
# Migrating vm2:3307; inspecting vm2:3307; executing on vm2
# Migration started at Tue Aug 16 16:09:37 +0800 2016
# chunk-size: 1000; max-lag-millis: 1500ms; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.t1.sock
Copy: 0/8 0.0%; Applied: 0; Backlog: 0/100; Time: 0s(total), 0s(copy); streamer: mysql_bin.000007:515905; ETA: N/A
Copy: 0/8 0.0%; Applied: 0; Backlog: 0/100; Time: 1s(total), 1s(copy); streamer: mysql_bin.000007:516333; ETA: N/A
2016-08-16 16:09:38 INFO Row copy complete
Copy: 8/8 100.0%; Applied: 0; Backlog: 0/100; Time: 1s(total), 1s(copy); streamer: mysql_bin.000007:517586; ETA: 0s
2016-08-16 16:09:38 INFO Stopping replication
2016-08-16 16:09:38 INFO Replication stopped
2016-08-16 16:09:38 INFO Verifying SQL thread is stopped
2016-08-16 16:09:38 INFO SQL thread stopped
2016-08-16 16:09:38 INFO Replication IO thread at mysql_bin.000026:599990. SQL thread is at mysql_bin.000026:599990
2016-08-16 16:09:38 INFO Grabbing voluntary lock: gh-ost.386.lock
2016-08-16 16:09:38 INFO Setting LOCK timeout as 6 seconds
2016-08-16 16:09:38 INFO Looking for magic cut-over table
2016-08-16 16:09:38 INFO Creating magic cut-over table `test`.`_t1_ght`
2016-08-16 16:09:38 INFO Magic cut-over table created
2016-08-16 16:09:38 INFO Locking `test`.`t1`, `test`.`_t1_ght`
2016-08-16 16:09:38 INFO Tables locked
2016-08-16 16:09:38 INFO Session locking original & magic tables is 386
2016-08-16 16:09:38 INFO Writing changelog state: AllEventsUpToLockProcessed
2016-08-16 16:09:38 INFO Waiting for events up to lock
Copy: 8/8 100.0%; Applied: 0; Backlog: 1/100; Time: 2s(total), 1s(copy); streamer: mysql_bin.000007:518958; ETA: 0s
2016-08-16 16:09:39 INFO Done waiting for events up to lock; duration=958.264161ms
# Migrating `test`.`t1`; Ghost table is `test`.`_t1_gho`
# Migrating vm2:3307; inspecting vm2:3307; executing on vm2
# Migration started at Tue Aug 16 16:09:37 +0800 2016
# chunk-size: 1000; max-lag-millis: 1500ms; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.t1.sock
Copy: 8/8 100.0%; Applied: 0; Backlog: 0/100; Time: 2s(total), 1s(copy); streamer: mysql_bin.000007:519774; ETA: 0s
2016-08-16 16:09:39 INFO Setting RENAME timeout as 3 seconds
2016-08-16 16:09:39 INFO Session renaming tables is 380
2016-08-16 16:09:39 INFO Issuing and expecting this to block: rename /* gh-ost */ table `test`.`t1` to `test`.`_t1_ght`, `test`.`_t1_gho` to `test`.`t1`
2016-08-16 16:09:39 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2016-08-16 16:09:39 INFO Checking session lock: gh-ost.386.lock
2016-08-16 16:09:39 INFO Connection holding lock on original table still exists
2016-08-16 16:09:39 INFO Will now proceed to drop magic table and unlock tables
2016-08-16 16:09:39 INFO Dropping magic cut-over table
2016-08-16 16:09:39 INFO Releasing lock from `test`.`t1`, `test`.`_t1_ght`
2016-08-16 16:09:39 INFO Tables unlocked
2016-08-16 16:09:39 INFO Tables renamed
2016-08-16 16:09:39 INFO Lock & rename duration: 1.012549642s. During this time, queries on `t1` were blocked
2016-08-16 16:09:39 INFO Looking for magic cut-over table
2016-08-16 16:09:39 INFO Renaming back both tables
2016-08-16 16:09:39 INFO Droppping table `test`.`_t1_ghc`
2016-08-16 16:09:39 INFO Table dropped
2016-08-16 16:09:39 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:
2016-08-16 16:09:39 INFO -- drop table `test`.`_t1_ght`
2016-08-16 16:09:39 INFO Done migrating `test`.`t1`
2016-08-16 16:09:39 INFO Done
#############
變更后
#############
vm1上主庫
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv |
+-----------+-----------+-------------+-------------+-------------+
| % | root | Y | Y | Y |
| 10.0.0.% | repl | N | N | N |
| 127.0.0.1 | root | Y | Y | Y |
| ::1 | root | Y | Y | Y |
| localhost | | N | N | N |
| localhost | mysql.sys | N | N | N |
| localhost | root | Y | Y | Y |
| localhost | zabbix | N | N | N |
| vm1 | aa | N | N | N |
+-----------+-----------+-------------+-------------+-------------+
9 rows in set (0.00 sec)
vm2上從庫：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| _t1_gho |
| t1 |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from t1;
+-----------+-----------+-------------+-------------+-------------+
| Host | User | Select_priv | Insert_priv | Update_priv |
+-----------+-----------+-------------+-------------+-------------+
| % | root | Y | Y | Y |
| 10.0.0.% | repl | N | N | N |
| 127.0.0.1 | root | Y | Y | Y |
| ::1 | root | Y | Y | Y |
| localhost | | N | N | N |
| localhost | mysql.sys | N | N | N |
| localhost | root | Y | Y | Y |
| localhost | zabbix | N | N | N |
+-----------+-----------+-------------+-------------+-------------+
8 rows in set (0.00 sec)
mysql> select * from _t1_gho;
+-----------+-----------+-------------+-------------+
| Host | User | Select_priv | Insert_priv |
+-----------+-----------+-------------+-------------+
| % | root | Y | Y |
| 10.0.0.% | repl | N | N |
| 127.0.0.1 | root | Y | Y |
| ::1 | root | Y | Y |
| localhost | | N | N |
| localhost | mysql.sys | N | N |
| localhost | root | Y | Y |
| localhost | zabbix | N | N |
+-----------+-----------+-------------+-------------+
8 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: vm1
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql_bin.000026
Read_Master_Log_Pos: 599990
Relay_Log_File: vm2-relay-bin.000067
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000026
Slave_IO_Running: No
Slave_SQL_Running: No
三 特殊參數
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag
延遲切換，只要/tmp/ghost.postpone.flag文件不存在，gh-ost會自動切換，如果存在則繼續監控數據更新。
--allow-on-master
直接在主庫更改切換。
--panic-flag-file=/tmp/ghost.panic.flag
如果文件/tmp/ghost.panic.flag存在，gh-ost則直接中斷切換，停止進程，不會清理進程創建的任何數據。
到此，關于“MySQL gh-ost測試分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
