MySQL gh-ost test and analysis 02/23 Update SLTechnology News&Howtos

MySQL gh-ost test and analysis

2025-02-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article mainly introduces "MySQL gh-ost Test and Analysis". In daily operation, I believe many people have doubts about MySQL gh-ost test and analysis. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "MySQL gh-ost Test and Analysis". Next, please follow the editor to study!

Environment

Master: 10.0.0.11 vm1 3307

Slave: 10.0.0.12 vm2 3307

Download & & install

Download the compiled binary package directly from https://github.com/github/gh-ost/releases.

Such as: gh-ost-binary-linux-20160815105421.tar.gz

Upload to a server, such as slave.

Decompress.

Second test

Test 1: connect to the slave library and make changes in the master library.

This is the default way for gh-ost to work.

Connect to the slave library, and the row data is read and written on the master library.

Read the binary log of the slave library and apply the changes to the master library

Collecting table format, field & index, number of rows and other information from the library

Read internal change events (such as heartbeat events) from the library

Switch tables in the main library

By changing the table structure on the master library from the slave library, the old and temporary tables exist in all master and slave libraries until the new and old tables of the master library are switched.

Note: this method is based on the changes made by the table structure and table data of the master database, so after the changes are completed, the data of the slave database will be consistent with that of the master database.

* * *

Example: connect to the slave library vm2, and add the column Create_priv enum ('nasty journal Y') CHARACTER SET utf8 NOT NULL DEFAULT' N' to the test.t1 table.

Before the change

Main library T1 table on vm1

Mysql > select * from T1

+-+ +

+-+ +

| |% | root | Y | Y | Y | Y |

| | 10.0.0.% | repl | N | N | N | N | N |

| | 127.0.0.1 | root | Y | Y | Y | Y | Y |

| |:: 1 | root | Y | Y | Y | Y | Y |

| | localhost | | N | N | N | N |

| | localhost | mysql.sys | N | N | N | N | N |

| | localhost | root | Y | Y | Y | Y | Y |

| | localhost | zabbix | N | N | N | N | N |

+-+ +

8 rows in set (0.00 sec)

From the library T1 table on vm2

Mysql > select * from T1

+-+ +

+-+ +

| |% | root | Y | Y | Y | Y |

| | 127.0.0.1 | root | Y | Y | Y | Y | Y |

| |:: 1 | root | Y | Y | Y | Y | Y |

| | localhost | | N | N | N | N |

| | localhost | mysql.sys | N | N | N | N | N |

| | localhost | root | Y | Y | Y | Y | Y |

| | localhost | zabbix | N | N | N | N | N |

+-+ +

7 rows in set (0.00 sec)

Note that the T1 table of the slave library is one row less than the T1 table of the master library.

# #

Start the change, and the output is as follows:

[root@vm2 ~] #. / gh-ost-- conf=/etc/my.cnf-- port=3307-- host=vm2-- database= "test"-- table= "T1"-- alter= "add Create_priv enum" ('Numanju 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 Droppping table `test`.` _ t1ggho`

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`.` _ t1ggho`

2016-08-16 15:28:41 INFO Ghost table created

2016-08-16 15:28:41 INFO Altering ghost table `test`.` _ t1ggho`

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`.` _ t1gho`

# 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: 0swap 7. 0%; Applied: 0; Backlog: 0ap100; Time: 0s (total), 0s (copy); streamer: mysql_bin.000007:437697; ETA: nameA

2016-08-16 15:28:42 INFO Row copy complete

Copy: 0x7%; Applied: 0; Backlog: 0ap100; Time: 1s (total), 1s (copy); streamer: mysql_bin.000007:438790; ETA: NameA

Copy: 8 7 114.3%; Applied: 0; Backlog: 0Accord 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`.` _ t1del`

2016-08-16 15:28:43 INFO Magic cut-over table created

2016-08-16 15:28:43 INFO Locking `test`.`t1`, `test`.` _ t1del`

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: 8ax 7 114.3%; Applied: 0; Backlog: 1ax 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`.` _ t1gho`

# 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: 0Accord 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`.` _ t1del`, `test`.` _ t1gho`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`.` _ t1del`

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`.` _ t1del`

2016-08-16 15:28:44 INFO Done migrating `test`.`t1`

2016-08-16 15:28:44 INFO Done

After Chan

View table structure and data

Main library T1 table on vm1

Mysql > select * from T1

+-+

+-+

| |% | root | Y | Y | Y | Y | N |

| | 10.0.0.% | repl | N | 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 | N |

| | localhost | mysql.sys | N | N | N | N | N | N |

| | localhost | root | Y | Y | Y | Y | N |

| | localhost | zabbix | N | N | N | N | N | N |

+-+

8 rows in set (0.00 sec)

From the library T1 table on vm2

Mysql > select * from T1

+-+

+-+

| |% | root | Y | Y | Y | Y | N |

| | 10.0.0.% | repl | N | 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 | N |

| | localhost | mysql.sys | N | N | N | N | N | N |

| | localhost | root | Y | Y | Y | Y | N |

| | localhost | zabbix | N | N | N | N | N | N |

+-+

8 rows in set (0.00 sec)

You can see that new columns have been added to the T1 table on the master-slave library, and the master-slave data has become 8, which is the same as the number of data items in the original master library.

Test 2: connect to the main library

If you do not have a slave library, or do not want to use the slave library, you can operate directly on the master library. Gh-ost will do all operations directly on the main library. You need to keep an eye on replication delays.

The binary log of your main library must be in RBR format.

In this mode you must specify the-- allow-on-master parameter

* * *

Example: delete the Create_priv column of table test.t1.

Before the change

View table structure and data

Main library T1 table on vm1

Mysql > select * from T1

+-+

+-+

| |% | root | Y | Y | Y | Y | N |

| | 10.0.0.% | repl | N | 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 | N |

| | localhost | mysql.sys | N | N | N | N | N | N |

| | localhost | root | Y | Y | Y | Y | N |

| | localhost | zabbix | N | N | N | N | N | N |

+-+

8 rows in set (0.00 sec)

From the library T1 table on vm2

Mysql > select * from T1

+-+

+-+

| |% | root | Y | Y | Y | Y | N |

| | 10.0.0.% | repl | N | 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 | N |

| | localhost | mysql.sys | N | N | N | N | N | N |

| | localhost | root | Y | Y | Y | Y | N |

| | localhost | zabbix | N | N | N | N | N | N |

+-+

8 rows in set (0.00 sec)

Start the change, and the output is as follows:

[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 Droppping table `test`.` _ t1ggho`

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`.` _ t1ggho`

2016-08-16 15:35:06 INFO Ghost table created

2016-08-16 15:35:06 INFO Altering ghost table `test`.` _ t1ggho`

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`.` _ t1gho`

# 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: 0swap 8%; Applied: 0; Backlog: 0apace 100; Time: 0s (total), 0s (copy); streamer: mysql_bin.000026:585540; ETA: nameA

[MySQL] 2016-08-16 15:35:07 statement.go:27: Invalid Connection

Copy: 0Accord 8%; Applied: 0; Backlog: 0Accord 100; Time: 1s (total), 1s (copy); streamer: mysql_bin.000026:585968; ETA: NameA

2016-08-16 15:35:07 INFO Row copy complete

Copy: 8: 100%; 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`.` _ t1del`

2016-08-16 15:35:07 INFO Magic cut-over table created

2016-08-16 15:35:07 INFO Locking `test`.`t1`, `test`.` _ t1del`

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: 8Accord 8 100.0%; Applied: 0; Backlog: 1CM100; 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`.` _ t1gho`

# 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: 100%; 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`.` _ t1del`, `test`.` _ t1gho`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`.` _ t1del`

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`.` _ t1del`

2016-08-16 15:35:08 INFO Done migrating `test`.`t1`

2016-08-16 15:35:08 INFO Done

After Chan

Main library T1 table on vm1

Mysql > select * from T1

+-+ +

+-+ +

| |% | root | Y | Y | Y | Y |

| | 10.0.0.% | repl | N | N | N | N | N |

| | 127.0.0.1 | root | Y | Y | Y | Y | Y |

| |:: 1 | root | Y | Y | Y | Y | Y |

| | localhost | | N | N | N | N |

| | localhost | mysql.sys | N | N | N | N | N |

| | localhost | root | Y | Y | Y | Y | Y |

| | localhost | zabbix | N | N | N | N | N |

+-+ +

8 rows in set (0.00 sec)

From the library T1 table on vm2

Mysql > select * from T1

+-+ +

+-+ +

| |% | root | Y | Y | Y | Y |

| | 10.0.0.% | repl | N | N | N | N | N |

| | 127.0.0.1 | root | Y | Y | Y | Y | Y |

| |:: 1 | root | Y | Y | Y | Y | Y |

| | localhost | | N | N | N | N |

| | localhost | mysql.sys | N | N | N | N | N |

| | localhost | root | Y | Y | Y | Y | Y |

| | localhost | zabbix | N | N | N | N | N |

+-+ +

8 rows in set (0.01 sec)

Test 3: migrating / testing from the library

This mode performs a migration operation from the library. Gh-ost simply connects to the master library, after which all operations are performed from the slave library

No changes will be made to the main library. During the whole operation, gh-ost will control the speed to ensure that the data can be synchronized in time from the library.

-- migrate-on-replica indicates that gh-ost will migrate directly from the library. Table switching can be performed even during the replication run phase.

-- test-on-replica indicates that the migration operation is just to test that replication will stop before switching, then switch, and then switch back, and your original table will eventually be the original table. Both tables are saved and the copy operation is stopped. You can perform test operations such as consistency checking on these two tables.

* * *

Example: delete the Delete_priv column of the test.t1 table from the library. Only change the table structure in the slave library, and then switch the new table in the slave library, that is, the master library is the old table structure and the slave library is the new table structure.

Before the change

Main library T1 table on vm1

Mysql > select * from T1

+-+ +

+-+ +

| |% | root | Y | Y | Y | Y |

| | 10.0.0.% | repl | N | N | N | N | N |

| | 127.0.0.1 | root | Y | Y | Y | Y | Y |

| |:: 1 | root | Y | Y | Y | Y | Y |

| | localhost | | N | N | N | N |

| | localhost | mysql.sys | N | N | N | N | N |

| | localhost | root | Y | Y | Y | Y | Y |

| | localhost | zabbix | N | N | N | N | N |

+-+ +

8 rows in set (0.00 sec)

From the library T1 table on vm2

Mysql > select * from T1

+-+ +

+-+ +

| |% | root | Y | Y | Y | N |

| | 10.0.0.% | repl | N | 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 | N |

| | localhost | root | Y | Y | Y | N |

+-+ +

7 rows in set (0.00 sec)

Start the change, and the output is as follows:

[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 Droppping table `test`.` _ t1ggho`

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`.` _ t1ggho`

2016-08-16 15:54:15 INFO Ghost table created

2016-08-16 15:54:15 INFO Altering ghost table `test`.` _ t1ggho`

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`.` _ t1gho`

# 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: 0swap 7. 0%; Applied: 0; Backlog: 0ap100; Time: 0s (total), 0s (copy); streamer: mysql_bin.000007:489090; ETA: nameA

Copy: 0x7%; Applied: 0; Backlog: 0ap100; Time: 1s (total), 1s (copy); streamer: mysql_bin.000007:489906; ETA: NameA

2016-08-16 15:54:16 INFO Row copy complete

Copy: 7Accord 7 100.0%; Applied: 0; Backlog: 0Accord 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`.` _ t1ghr`

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: 7Accord 7 100.0%; Applied: 0; Backlog: 1CM100; 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`.` _ t1gho`

# 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: 7Accord 7 100.0%; Applied: 0; Backlog: 0Accord 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`.` _ t1ghr`, `test`.` _ t1gho`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`.` _ t1ghr`

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`.` _ t1gghr`

2016-08-16 15:54:17 INFO Done migrating `test`.`t1`

2016-08-16 15:54:17 INFO Done

After Chan

Main library T1 table on vm1

Mysql > select * from T1

+-+ +

+-+ +

| |% | root | Y | Y | Y | Y |

| | 10.0.0.% | repl | N | N | N | N | N |

| | 127.0.0.1 | root | Y | Y | Y | Y | Y |

| |:: 1 | root | Y | Y | Y | Y | Y |

| | localhost | | N | N | N | N |

| | localhost | mysql.sys | N | N | N | N | N |

| | localhost | root | Y | Y | Y | Y | Y |

| | localhost | zabbix | N | N | N | N | N |

+-+ +

8 rows in set (0.00 sec)

From the library T1 table on vm2

Mysql > select * from T1

+-+

+-+

| |% | root | Y | Y | Y |

| | 10.0.0.% | repl | N | N | N | N |

| | 127.0.0.1 | root | Y | Y | Y |

| |:: 1 | root | Y | 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)

As you can see, this approach is only changing from the library, based on the structure and data of the old tables in the library.

* * *

Example:-- test-on-replica indicates that the migration operation is only for testing. Replication will stop before switching, then switch, and then switch back. Your original table is still the original table in the end. Both tables are saved, and replication is stopped during this process. You can perform test operations such as consistency checking on these two tables.

Before the change

Main library T1 table on vm1

Mysql > select * from T1

+-+

+-+

| |% | root | Y | Y | Y |

| | 10.0.0.% | repl | N | N | N | N |

| | 127.0.0.1 | root | Y | Y | Y |

| |:: 1 | root | Y | 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)

From the library T1 table on vm2

Mysql > select * from T1

+-+

+-+

| |% | root | Y | Y | Y |

| | 10.0.0.% | repl | N | N | N | N |

| | 127.0.0.1 | root | Y | Y | Y |

| |:: 1 | root | Y | 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

Note that the T1 table of the slave library is one row less than the T1 table of the master library.

# #

Start the change, and the output is as follows:

[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 Droppping table `test`.` _ t1ggho`

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`.` _ t1ggho`

2016-08-16 16:09:37 INFO Ghost table created

2016-08-16 16:09:37 INFO Altering ghost table `test`.` _ t1ggho`

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`.` _ t1gho`

# 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: 0swap 8%; Applied: 0; Backlog: 0apace 100; Time: 0s (total), 0s (copy); streamer: mysql_bin.000007:515905; ETA: nameA

Copy: 0Accord 8%; Applied: 0; Backlog: 0Accord 100; Time: 1s (total), 1s (copy); streamer: mysql_bin.000007:516333; ETA: NameA

2016-08-16 16:09:38 INFO Row copy complete

Copy: 8: 100%; 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`.` _ t1ght`

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: 8Accord 8 100.0%; Applied: 0; Backlog: 1CM100; 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`.` _ t1gho`

# 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: 100%; 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`.` _ t1it`, `test`.` _ t1gho`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`.` _ t1ght`

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`.` _ t1ght`

2016-08-16 16:09:39 INFO Done migrating `test`.`t1`

2016-08-16 16:09:39 INFO Done

After Chan

Main library on vm1

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | T1 |

+-+

1 row in set (0.00 sec)

Mysql > select * from T1

+-+

+-+

| |% | root | Y | Y | Y |

| | 10.0.0.% | repl | N | N | N | N |

| | 127.0.0.1 | root | Y | Y | Y |

| |:: 1 | root | Y | 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)

Slave library on vm2:

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | _ t1_gho |

| | T1 |

+-+

2 rows in set (0.00 sec)

Mysql > select * from T1

+-+

+-+

| |% | root | Y | Y | Y |

| | 10.0.0.% | repl | N | N | N | N |

| | 127.0.0.1 | root | Y | Y | Y |

| |:: 1 | root | Y | 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

+-+

+-+

| |% | 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

Three special parameters

-- postpone-cut-over-flag-file=/tmp/ghost.postpone.flag

Delay switching, gh-ost will automatically switch as long as the / tmp/ghost.postpone.flag file does not exist, and continue to monitor data updates if it does.

-- allow-on-master

Change the switch directly in the main library.

-- panic-flag-file=/tmp/ghost.panic.flag

If the file / tmp/ghost.panic.flag exists, gh-ost interrupts the switch directly, stops the process, and does not clean up any data created by the process.

At this point, the study of "MySQL gh-ost Test Analysis" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.