Using gh-ost for Online MySQL Schema Migration – Introduction, Architecture, Installation, and Practical Usage

1. Introduction to gh-ost gh-ost is an open‑source, trigger‑less online schema migration tool for MySQL released by GitHub. It creates a ghost table, copies data incrementally while applying binlog changes, and finally swaps the tables with minimal load on the primary server. Compared with traditional tools, gh‑ost does not use triggers; it reads the binary log stream and applies changes asynchronously, offering pause, dynamic reconfiguration, audit logs, and fine‑grained control.

2. Three Architecture Modes

gh‑ost supports three deployment patterns:

Connect to replica, migrate on master – the default mode. gh‑ost connects to the replica to discover the primary, then performs the migration on the primary with minimal intrusion.

Connect directly to master – use the --allow-on-master flag. All operations are executed on the primary.

Test and migrate on replica – use --migrate-on-replica for a dry‑run and --test-on-replica to test without affecting the primary; the actual cut‑over happens after replication is paused.

3. Download and Installation

# wget https://github.com/github/gh-ost/releases/download/v1.0.49/gh-ost-1.0.49-1.x86_64.rpm

# rpm -ivh gh-ost-1.0.49-1.x86_64.rpm

# gh-ost --version

After installation, verify the version output (e.g., 1.0.49 ).

4. Usage – Common Parameters and Interactive Commands

Key parameters include --max-load , --critical-load , --chunk-size , --throttle-control-replicas , and --max-lag-millis . gh‑ost also creates a Unix domain socket for interactive control. Example interactive commands (sent via nc -U socket ) are:

status – detailed migration status.

sup – short status.

coordinates – current binlog position.

chunk-size=<newsize> , dml-batch-size=<newsize> , etc. – adjust parameters on the fly.

throttle / no-throttle – force or release throttling.

unpostpone – trigger the cut‑over when migration is paused.

panic – abort immediately without cleanup.

help – list all commands.

5. End‑to‑End Example – Changing a Column Type

The example migrates table sbtest1 in database sbtest , converting column c from CHAR(120) to VARCHAR(200) using the "connect to replica, migrate on master" mode.

Preparation steps:

Verify replica binlog format: SELECT @@global.binlog_format, @@log_slave_updates; (should be ROW and 1 ).

Ensure the target table has a primary key or a non‑null unique key.

Create a user with the required privileges (e.g., GRANT ALTER, CREATE, DELETE, … ON *.* TO ghost@'%' IDENTIFIED BY 'ghost'; ) and store credentials in ghost.conf .

Execution (run inside a screen session):

# gh-ost \
  --conf=ghost.conf \
  --database=sbtest \
  --table=sbtest1 \
  --alter='modify c varchar(200) not null default ""' \
  --max-load=Threads_running=100 \
  --critical-load=Threads_running=1000 \
  --critical-load-interval-millis=100 \
  --chunk-size=100000 \
  --throttle-control-replicas="10.186.61.101:3306" \
  --max-lag-millis=1500 \
  --host=10.186.61.101 \
  --port=3306 \
  --verbose \
  --default-retries=120 \
  --dml-batch-size=100 \
  --assume-rbr \
  --assume-master-host=10.186.61.88 \
  --panic-flag-file=/tmp/sbtest1.panic \
  --postpone-cut-over-flag-file=/tmp/sbtest1.postpone \
  --throttle-flag-file=/tmp/sbtest1.throttle \
  --timestamp-old-table \
  --serve-socket-file=/tmp/sbtest1.gh-ost.socket \
  --execute

During execution gh‑ost logs metrics such as:

Copy : rows copied vs. estimated total.

Applied : rows applied from binlog.

Backlog : pending binlog events.

Time : total and copy time.

Lag : replication lag.

State : migrating , throttled , or postponing cut‑over .

ETA : estimated time to finish.

When the state becomes postponing cut‑over , issue echo "unpostpone" | nc -U sbtest1.gh-ost.socket to trigger the atomic rename. gh‑ost then creates a temporary cut‑over table, locks the original and temporary tables, performs the rename, releases the lock, and cleans up socket files.

After migration, verify the new schema with DESC sbtest1; , drop the temporary cut‑over table, and remove any flag files.

6. References

Further reading is available in the official gh‑ost documentation repository: https://github.com/github/gh-ost/tree/master/doc .