这篇文章主要介绍“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 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 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 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 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测试分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
