Using gh-ost for Online MySQL Schema Changes: Features, Modes, and Practical Tips

After a previous experience with pt-online-schema-change , the author discovered that many legacy systems use triggers, which prevents the tool from working. To solve this, a DBA colleague recommended gh-ost , an open‑source online schema change tool written in Go and maintained by GitHub.

Key characteristics of gh-ost (as listed in the original article):

No triggers Lightweight Pause‑able Dynamically controllable Auditable Testable Reliable

gh-ost tracks MySQL binary logs (preferably row‑based) and applies changes to a temporary table asynchronously, decoupling schema‑change load from normal workload. Because it does not use triggers, it avoids locking and contention on the original table.

When the binary log format is statement‑based, gh-ost can still be used by setting up a replica that converts statements to row events.

Pause and control : All write operations performed by gh-ost are generated by the tool itself, so pausing simply stops those writes. An internal status table continues to receive negligible heartbeat writes. Users can pause via a flag file or by sending commands through a TCP/unix socket, e.g.:

echo throttle | socat - /tmp/gh-ost.sock (or no‑throttle )

Additional dynamic controls include load thresholds, replication lag monitoring, and on‑the‑fly configuration changes such as:

--max-load : define a load threshold (e.g., max connections) that gh‑ost will respect.

--critical-load : abort if load exceeds this value.

--chunk-size : number of rows per transaction (default 1000).

--max-lag-millis : pause if replica lag exceeds the given milliseconds.

--throttle-control-replicas : specify which replicas to monitor for lag.

Auditing and testing : gh‑ost can report its current status, progress, and configuration via the same socket interface, providing full auditability. The --test-on-replica flag lets users run the migration on a replica, pause replication, perform a cut‑over, then revert, ensuring no production impact.

Reliability : The tool writes only to temporary tables and a small internal tracking table, keeping the original data untouched until the final cut‑over.

Operating modes :

Mode 1 – Connect to a replica, modify the master : gh‑ost reads binlog events from a replica, applies changes to the master, and performs the cut‑over on the master. The replica must have row‑based binlog enabled.

Mode 2 – Directly modify the master : Use --allow-on-master to run all steps on the master. Suitable when no replica is available, but it adds load to the master.

Mode 3 – Modify and test on a replica : With --migrate-on-replica and --test-on-replica , all operations happen on the replica, leaving the master untouched until the final cut‑over.

The author notes practical pros and cons of each mode, emphasizing data‑consistency risks in Mode 1, load impact in Mode 2, and the need for careful handling of replication pause in Mode 3.

GitHub production usage : All schema changes at GitHub are performed with gh‑ost, integrated with a ChatOps system for real‑time monitoring and control.

Getting started :

Download the binary from GitHub releases .

Typical command‑line options (each shown as code):

--max-load , --critical-load , --chunk-size , --max-lag-millis , --throttle-control-replicas , --switch-to-rbr , --initially-drop-ghost-table , --initially-drop-socket-file , --ok-to-drop-table , --host , --port , --user , --password , --database , --table , --verbose , --alter , --cut-over , --debug , --panic-flag-file , --execute , --allow-on-master , --throttle-flag-file

Testing workflow example (run on a single‑instance DB without replication parameters):

Omit --max-lag-millis , --throttle-control-replicas , --switch-to-rbr when no replica is present.

If replication is misconfigured, gh‑ost will abort with errors such as:

FATAL Replication on 192.168.1.1:3306 is broken: Slave_IO_Running: No, Slave_SQL_Running: No.

Common issues and solutions :

Binlog format must be ROW. Switch dynamically with SET GLOBAL binlog_format='ROW'; .

Tables with triggers are not supported – remove triggers before migration.

Parent‑side foreign keys are not supported – drop or disable them.

Custom MySQL ports in master‑master setups require specifying the port in -assume-master-host=192.168.1.1:3307 .

All the above information is compiled from the author’s blog and external articles (e.g., InfoQ).