GH-Ost User Manual

GH-Ost practical application I. Installation Steps

1. Environment

Go version: 1.10.3gh-Ost version: 1.0.46

2. Install the go Language

# Install go dependency package Yum install bison ed gawk GCC libc6-dev make-y # configure go environment variable Vim ~ /. Bashrcexport goroot =/usr/local/goexport Path = $ path: $ goroot/binexport gopath =/usr/local/go/src/github.com/github/gh-ost# make the environment variable effective source ~ /. Bashrc # unzip the go installation package in 10.135.2.217: Data/online/software/go1.10.3.linux-amd64.tar.gz tar-zxvf go1.10.3.linux-amd64.tar.gz-C/usr/local/

3. Install GH-ost

Installation Package in: 10.135.2.217: Data/online/software/gh-ost-binary-linux-20180527215024.tar.gztar-zxvf gh-ost-binary-linux-20180527215024.tar.gz-C/usr/localln-S/usr/local/GH-Ost/usr/bin/GH-ost

Ii. master database mode

1. Common commands

gh-ost    --max-load=Threads_running=16    --critical-load=Threads_running=32    --chunk-size=1000     --initially-drop-old-table    --initially-drop-ghost-table    --initially-drop-socket-file    --ok-to-drop-table    --host="10.249.5.39"    --port=3306    --user="dbadmin"    --password="12345"    --assume-rbr    --allow-on-master    --assume-master-host=10.249.5.39:3306    --database="gh_ost"    --table="gh_01"    --alter="add column c4 varchar(50) not null default ‘‘" \      --panic-flag-file=/tmp/ghost.panic.flag    --serve-socket-file=/tmp/ghost.sock    --verbose    --execute

2. Common Parameter Interpretation

-By default, allow-on-master applies BINLOG to the slave database. If it is executed directly on the master database, you must set this parameter-max-load string to a status expression, when multiple Status values are set, separate them with commas (,), for example, 'threads _ running = 100, threads_connected = 100'. If this value is exceeded, migration pause wait-critical-load string is different from Max-load. When this value is exceeded, stop migration directly and exit-chunk-size int the number of rows that are migrated from the original table each time (allowed value: 100-100000) (default: 1000) -initially-drop-ghost-table: before this operation, delete the possible ghost table (which may have been left before ), -initially-drop-old-table is interrupted by default. The old table is deleted before this operation (it may not have been deleted before). By default,-initially-drop-Soc is interrupted. After ket-file deletes an existing socket file-OK-to-drop-Table DDL, it automatically deletes the old table-panic-flag-file string. If this parameter is specified, if the file is created, GH-Ost immediately terminates and exits. The generated temporary tables and files-Exact-rowcount are not cleared to accurately count the number of rows in the table rather than the estimated number of rows, even if it is not accurate, it only affects the calculation of the progress. The actual number of copy rows is determined by the maximum value and the minimum value, and is irrelevant to it. -Serve-socket-file string socket file-assume-RBR shows that GH-Ost log format is row format. If this parameter is not specified, GH-Ost sets the row format each time and restarts the replication. the user needs to have the super permission-assume-master-host to display and tell GH-Ost Master Address. If no, GH-ost will check the master-host and-port GH-Ost from the slave database as the slave connection information by default. If the slave Application log is used, enter the server information of the slave; if you run the command directly on the master, enter the master information and the allow-on-master parameter must exist. Otherwise, an error is returned and the system exits.

3. Output Log Analysis

The GH-OST will output some key details for you to understand the entire migration process. Of course, you can control the output level.

-Verbose: Common, useful output, not everything. -Debug: Output everything.

The output is as follows:

2018-08-07 14:17:11 INFO starting gh-ost 1.0.462018-08-07 14:17:11 INFO Migrating `darren`.`t4`2018-08-07 14:17:11 INFO connection validated on 10.249.5.39:33062018-08-07 14:17:11 INFO User has ALL privileges2018-08-07 14:17:11 INFO binary logs validated on 10.249.5.39:33062018-08-07 14:17:11 INFO Restarting replication on 10.249.5.39:3306 to make sure binlog settings apply to replication thread2018-08-07 14:17:11 INFO Inspector initiated on shvm-5-39.58os.org:3306, version 5.7.21-log2018-08-07 14:17:11 INFO Table found. Engine=InnoDB2018-08-07 14:17:11 INFO Estimated number of rows via EXPLAIN: 587072018-08-07 14:17:11 INFO Recursively searching for replication master2018-08-07 14:17:11 INFO Master found to be shvm-5-39.58os.org:33062018-08-07 14:17:11 INFO log_slave_updates validated on 10.249.5.39:33062018-08-07 14:17:11 INFO connection validated on 10.249.5.39:33062018/08/07 14:17:11 binlogsyncer.go:79: [info] create BinlogSyncer with config {99999 mysql 10.249.5.39 3306 dbadmin   false false <nil>}2018-08-07 14:17:11 INFO Connecting binlog streamer at shvm-5-39.000040:3375709542018/08/07 14:17:11 binlogsyncer.go:246: [info] begin to sync binlog from position (shvm-5-39.000040, 337570954)2018/08/07 14:17:11 binlogsyncer.go:139: [info] register slave for master server 10.249.5.39:33062018/08/07 14:17:11 binlogsyncer.go:573: [info] rotate to (shvm-5-39.000040, 337570954)2018-08-07 14:17:11 INFO rotate to next log name: shvm-5-39.0000402018-08-07 14:17:11 INFO connection validated on 10.249.5.39:33062018-08-07 14:17:11 INFO connection validated on 10.249.5.39:33062018-08-07 14:17:11 INFO will use time_zone=‘SYSTEM‘ on applier2018-08-07 14:17:11 INFO Examining table structure on applier2018-08-07 14:17:11 INFO Applier initiated on shvm-5-39.58os.org:3306, version 5.7.21-log2018-08-07 14:17:11 INFO Dropping table `darren`.`_t4_gho`2018-08-07 14:17:11 INFO Table dropped2018-08-07 14:17:11 INFO Dropping table `darren`.`_t4_del`2018-08-07 14:17:11 INFO Table dropped2018-08-07 14:17:11 INFO Dropping table `darren`.`_t4_ghc`2018-08-07 14:17:11 INFO Table dropped2018-08-07 14:17:11 INFO Creating changelog table `darren`.`_t4_ghc`2018-08-07 14:17:11 INFO Changelog table created2018-08-07 14:17:11 INFO Creating ghost table `darren`.`_t4_gho`2018-08-07 14:17:11 INFO Ghost table created2018-08-07 14:17:11 INFO Altering ghost table `darren`.`_t4_gho`2018-08-07 14:17:11 INFO Ghost table altered2018-08-07 14:17:11 INFO Intercepted changelog state GhostTableMigrated2018-08-07 14:17:11 INFO Waiting for ghost table to be migrated. Current lag is 0s2018-08-07 14:17:11 INFO Handled changelog state GhostTableMigrated2018-08-07 14:17:11 INFO Chosen shared unique key is PRIMARY2018-08-07 14:17:11 INFO Shared columns are id,name,c1,c2,c4,c5,c62018-08-07 14:17:11 INFO Listening on unix socket file: /tmp/ghost.sock2018-08-07 14:17:11 INFO Migration min values: [1]2018-08-07 14:17:11 INFO Migration max values: [58597]2018-08-07 14:17:11 INFO Waiting for first throttle metrics to be collected2018-08-07 14:17:11 INFO First throttle metrics collected# Migrating `darren`.`t4`; Ghost table is `darren`.`_t4_gho`# Migrating shvm-5-39.58os.org:3306; inspecting shvm-5-39.58os.org:3306; executing on shvm-5-39.58os.org# Migration started at Tue Aug 07 14:17:11 +0800 2018# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=25; critical-load: Threads_running=64; nice-ratio: 0.000000# throttle-additional-flag-file: /tmp/gh-ost.throttle # panic-flag-file: /tmp/ghost.panic.flag# Serving on unix socket: /tmp/ghost.sock

The information is relative self-explanatory to the GH-OST, and most of them indicate that everything goes well. You will focus on migration and check whether the migration is successful. Once the migration starts, you will see the following output.

Copy: 0/58707 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: shvm-5-39.000040:337574146; State: migrating; ETA: N/ACopy: 0/58707 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: shvm-5-39.000040:337581355; State: migrating; ETA: N/ACopy: 27000/58707 46.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 2s(copy); streamer: shvm-5-39.000040:338201054; State: migrating; ETA: 2sCopy: 58000/58707 98.8%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 3s(copy); streamer: shvm-5-39.000040:338912890; State: migrating; ETA: 0s2018-08-07 14:17:14 INFO Row copy complete

Progress prompt

Copy: 27000/58707 46.0%; 58707 indicates the total number of rows to be migrated, 27000 indicates the number of rows already migrated, and 46% indicates the percentage of completed migration. Applied: 0 indicates the number of events processed in binary logs. In the preceding example, the Migration table has no traffic, so no log event is processed. Backlog: 0/1000 indicates that we have performed well in reading binary logs, and there are no backlog events in the binary log queue. Backlog: 7/1000. When copying a row, some events are accumulated in the binary log and need to be applied. Backlog: 1000/1000, indicating that the buffer of our 1000 events is full (the buffer of the 1000 events written by the program, and the buffer of the earlier version is 100). Note that the BINLOG write volume is very large, GH-Ost cannot process the event. You may need to suspend the BINLOG reading and apply the events in the buffer zone first. Streamer: shvm-5-39.000040: 338912890; indicates that the BINLOG file location has been applied currently

Status prompt

A friendly prompt will be printed at a certain time: # migrating 'darren '. 't4 '; ghost table is 'darren '. '_ t4_gho' # migrating shvm-5-39.58os.org: 3306; inspecting shvm-5-39.58os.org: 3306; executing on shvm-5-39.58os.org # migration started at Tue Aug 07 14:17:11 + 0800 2018 # Chunk-size: 1000; max-lag-millis: 1500 ms; DML-batch-size: 10; max-load: threads_running = 25; critical-load: threads_running = 64; nice-ratio: 0.000000 # throttle-Additional-flag-file:/tmp/gh-ost.throttle # Panic-flag-file:/tmp/ghost. panic. flag # serving on UNIX socket:/tmp/ghost. sock

Iii. Slave database mode

1. Common commands

gh-ost    --max-load=Threads_running=16    --critical-load=Threads_running=32    --chunk-size=1000     --initially-drop-old-table    --initially-drop-ghost-table    --initially-drop-socket-file    --ok-to-drop-table    --host="10.249.5.39"    --port=3307    --user="dbadmin"    --password="12345"    --assume-rbr    --allow-on-master    --assume-master-host=10.249.5.39:3306    --database="gh_ost"    --table="gh_01"    --alter="add column c4 varchar(50) not null default ‘‘"    --panic-flag-file=/tmp/ghost.panic.flag    --serve-socket-file=/tmp/ghost.sock    --verbose    --execute

Iv. Test Mode

gh-ost    --test-on-replica    --max-load=Threads_running=16    --critical-load=Threads_running=32    --chunk-size=1000     --initially-drop-old-table    --initially-drop-ghost-table    --initially-drop-socket-file    --host="10.249.5.39"    --port=3307    --user="dbadmin"    --password="12345"    --assume-rbr    --database="gh_ost"    --table="gh_01"    --alter="add column c4 varchar(50) not null default ‘‘"    --panic-flag-file=/tmp/ghost.panic.flag    --serve-socket-file=/tmp/ghost.sock    --verbose    --execute

Parameter description

-- Test-on-replica executes the migration from the slave database, but does not perform the final cut-over, and stops the replication thread, for testing personnel to compare data -- migrate-on-replica is directly migrated from the slave database and cut-over, the replication thread will not stop

5. Pause, resume, terminate, and delay Switching

The -- serve-socket-file of GH-Ost is used to listen to requests. For example, you can dynamically adjust performance parameters, or pause or resume the GH-Ost thread.

# Pause echo throttle | socat-/tmp/ghost. sock # recover echo no-Throttle | socat-/tmp/ghost. sock # terminate the corresponding panic-flag-file parameter file. When the tmp directory exists, the file immediately stops touch/tmp/ghost. panic. flag # Cut-over stage -- postpone-cut-over-flag-file =/tmp/ghost. postpone. flag when this parameter is set, cut-over always delays switching until you delete the file. # dynamically adjust the performance parameter echo chunk-size = 100 | socat-/tmp/ghost. sock

GH-Ost User Manual