gh-ost: Triggerless Schema Migrations via Binlog Streaming

1. From triggers to binlogs: the core idea behind gh-ost

gh-ost, short for GitHub's Online Schema Change, was built at GitHub after pt-online-schema-change repeatedly hit limits on very large, write-heavy tables. Instead of installing synchronous triggers on the original table like pt-osc does, gh-ost behaves toward the MySQL server exactly like a regular replica: it establishes a normal replication connection and reads the full stream of change events from the binary log through it.

gh-ost then applies these change events asynchronously to a separate ghost table, in parallel with the actual copy of existing data. The key advantage: the original application barely notices any of this, because no extra triggers run on the original table and every regular write stays exactly as fast as before the migration.

2. How binlog streaming works as change data capture

With row-based replication, that is binlog_format=ROW, MySQL writes a complete before and after image for every changed row into the binary log. gh-ost connects through the normal replication protocol, registers as a replica with its own server ID, and receives the same event stream a regular replica server would. From that stream, gh-ost filters out only the events relevant to the table being migrated.

Each filtered event is internally translated into a matching write on the ghost table: an insert event becomes an insert, an update event becomes an update, a delete event becomes a delete. Because this process runs fully asynchronously relative to the actual application write, it adds no extra latency to the application itself, at most a small delay before a change also lands in the ghost table.

# gh-ost requires binlog_format=ROW as a prerequisite
mysql -e "SHOW VARIABLES LIKE 'binlog_format';"

# Dry run against the target table, no change to production data
gh-ost --host=db-primary.internal --database=magento --table=catalog_product_entity --alter="ADD INDEX idx_sku_type (sku, type_id)" --dry-run

3. Architecture comparison to pt-online-schema-change

The core difference between gh-ost and pt-online-schema-change is where the extra work happens. With pt-osc it happens synchronously in the same transaction as every application write, because a trigger has to run before the actual commit. With gh-ost it happens fully decoupled, in a separate process that reads and processes the binary log at some point after the commit.

In practice, that decoupling means a table with several thousand writes per second, such as a session or event log table in a high-traffic Magento shop, feels no extra write overhead per request with gh-ost. The tradeoff is more complexity in the tool itself and a usually small delay before changes from the binary log actually land in the ghost table.

4. Prerequisites: binlog format, GTID and privileges

The hard requirement is binlog_format=ROW, statement or mixed based replication is not sufficient since gh-ost relies on the full before and after row images. The database user gh-ost connects with needs at least the REPLICATION SLAVE and REPLICATION CLIENT privileges, plus the usual DML and DDL privileges on the target database.

Before any production run, a test run via --test-on-replica is strongly recommended: gh-ost runs the entire migration on a dedicated replica, stops replication there and automatically compares the results between the original and ghost table for consistency, with zero impact on the production primary.

5. The practical workflow: test run, dry run, execute

A sensible sequence starts with a --test-on-replica run against a dedicated replica, followed by a --dry-run against the primary that performs every check without any real migration. Only then follows the actual run with --execute, ideally outside peak hours, though gh-ost's throttling generally makes it safe to run during normal load as well.

During execution, gh-ost continuously reports status information: rows copied, estimated time remaining, current throughput and current replication lag. This output can also be queried interactively through a Unix socket interface, which is valuable especially for migrations without direct terminal access, for example when started from a CI job.

# Test run on a dedicated replica, no impact on the primary server
gh-ost --host=db-replica.internal --database=magento --table=sales_order_grid --alter="ADD COLUMN priority TINYINT DEFAULT 0" --test-on-replica

# Production run with an interactive control channel over a Unix socket
gh-ost --host=db-primary.internal --database=magento --table=sales_order_grid --alter="ADD COLUMN priority TINYINT DEFAULT 0" --serve-socket-file=/tmp/gh-ost.sales_order_grid.sock --execute

6. Throttling and the interactive control channel

gh-ost continuously monitors replication lag on every configured replica throughout the migration. If lag exceeds the threshold defined with --max-lag-millis, gh-ost automatically throttles the copy process until the replicas have caught up again. A deliberate slowdown factor can also be set with --nice-ratio, which permanently takes regular application load into account even without an actual lag breach.

Through the same Unix socket interface, a running migration can be throttled manually at any time with the throttle command, or sped back up with no-throttle, without restarting the process. That is especially valuable during unexpected load spikes during business hours, for example a sudden campaign driving an unusually high order volume.

7. The cutover moment in detail

At the end of the migration, gh-ost offers two cutover strategies. The default, --cut-over=atomic, uses a special locking mechanism that ensures no writes to the old table are lost during the switch by briefly blocking all writes while the last pending binlog events are still being applied. That block typically lasts a fraction of a second up to a few seconds.

The alternative --cut-over=two-step works more conservatively with two separate steps and tolerates certain connection interruptions during the switch, at the cost of taking somewhat longer overall. For most Magento migrations, the atomic cutover is the preferred choice as long as the network connection between the gh-ost process and the database server stays stable.

8. Limits and pitfalls

The most important limitation of gh-ost concerns foreign keys: the tool does not support tables with foreign key relationships at all by default and aborts once it detects them, unless --discard-foreign-keys is explicitly set, which removes the foreign keys entirely on the new table. On heavily interlinked Magento tables with many referencing foreign keys, that is a significant practical difference from pt-online-schema-change, which at least handles foreign keys in a configurable way.

Other limitations: a table needs a primary key or at least one unique, non-nullable index as the basis for the copy process, and on rows that change extremely frequently during the migration, remaining runtime can stretch out because the same rows have to be reapplied from the binary log multiple times. Triggers on the original table that themselves have DDL-relevant side effects are also incompatible with the ghost approach.

-- Check for existing foreign key references before migrating
SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'catalog_product_entity';

9. When gh-ost is the right choice over pt-osc or native DDL

gh-ost shows its strength mainly on tables with very high, constant write load, such as quote_item, sales_order_grid or custom log tables in high-traffic Magento shops, where the trigger overhead of pt-online-schema-change would actually be noticeable. When fine-grained throttling control and a particularly safe, tested cutover mechanism matter most, gh-ost is often the preferred choice.

For tables with foreign keys where fully removing the references is not an option, pt-online-schema-change often remains the more practical solution. And for any operation with native ALGORITHM=INSTANT or INPLACE support that is unlikely to hit row log capacity limits, MySQL's own built-in online DDL stays the simplest and fastest option of all.

10. Summary

11. FAQ: gh-ost: Triggerless Migrations