MySQL gh-ost测试分析
发布时间：2021-12-17 10:00:55 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍MySQL gh-ost测试分析，在日常操作中，相信很多人在MySQL gh-ost测试分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答MySQL gh-ost测试分析的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ 环境 mast
|
这篇文章主要介绍“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 statusG *************************** 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 statusG *************************** 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测试分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！ （编辑：昌吉站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐