gh-ost — Triggerless Online Schema Migration for MySQL

Introduction

gh-ost is GitHub's tool for performing online schema changes on MySQL tables without using triggers. Traditional online DDL tools rely on database triggers to capture changes during migration, which adds load and creates lock contention. gh-ost instead reads the binary log stream, making migrations safer and less intrusive on production workloads.

What gh-ost Does

• Alters large MySQL tables online with zero downtime and no trigger overhead
• Streams row changes from the binary log to keep the ghost table in sync
• Supports pausing, resuming, and dynamically reconfiguring migrations mid-flight
• Validates migrations on replicas before running on the primary
• Provides an interactive command socket for real-time status and throttle control

Architecture Overview

gh-ost creates a shadow (ghost) table with the desired schema, then copies rows from the original table in small chunks. Simultaneously, it tails the MySQL binary log to capture any DML changes on the original table and replays them on the ghost table. Once the copy is complete and the ghost table is caught up, an atomic rename swaps the two tables. This trigger-free design avoids the write amplification and locking issues inherent in trigger-based tools.

Self-Hosting & Configuration

• Requires MySQL 5.7+ with ROW-based binary logging enabled
• Download pre-built binaries from the GitHub releases page
• Point gh-ost at a replica for binary log reading to offload the primary
• Configure throttle settings via the --max-load and --critical-load flags
• Use --test-on-replica to validate the full migration cycle on a non-production host

Key Features

• Trigger-free architecture that minimizes production database load
• True pause capability that completely halts all migration activity
• Deferred cut-over lets you choose exactly when the table swap happens
• Built-in throttling based on replication lag, server load, or custom queries
• Hooks system for running scripts at key migration lifecycle events

Comparison with Similar Tools

• pt-online-schema-change — trigger-based; higher write amplification on busy tables
• MySQL native online DDL — limited ALTER support; long-running for large tables
• Facebook OSC — trigger-based with Facebook-specific optimizations
• Spirit — newer Golang migration tool inspired by gh-ost with parallel copier
• Vitess OnlineDDL — integrated into Vitess sharding layer; requires Vitess stack

FAQ

Q: Does gh-ost work with MySQL 8.0? A: Yes. gh-ost supports MySQL 5.7 and 8.0 with ROW-based binary logging.

Q: Can I migrate tables with foreign keys? A: gh-ost does not support tables that are referenced by foreign keys or that reference other tables via foreign keys.

Q: How does gh-ost handle replication lag? A: It monitors replica lag and automatically throttles the migration when lag exceeds the configured threshold.

Q: What happens if the migration fails midway? A: gh-ost cleans up the ghost table and no changes are applied to the original. You can safely retry.

Sources

• https://github.com/github/gh-ost
• https://github.blog/open-source/projects-community/gh-ost-github-s-online-migration-tool-for-mysql/