Migrate a Live MySQL Schema Without Downtime

For: A backend engineering lead at a 20–80-person B2B SaaS company who needs to add columns, drop indexes, or rename tables on a production MySQL database that cannot go offline — and whose last migration caused a 4-minute table lock that woke up half the company at 2 a.m.

The tool doesn't matter as much as the cutover. For a live MySQL table with millions of rows, use gh-ost if you have a replica you can read binlogs from and want throttle-aware, triggerless copying; use pt-online-schema-change if you're on a single primary or your ORM writes to the table in ways that break gh-ost's binlog assumptions. Both give you a long, safe copy phase and a short, dangerous swap. The swap is where migrations fail. Rehearse the swap under production-shaped write load, not the copy.

This is the playbook I give backend leads after their last ALTER TABLE locked a table for four minutes at 2 a.m. and paged half the company. It assumes you're on MySQL 5.7 or 8.0, InnoDB, with row-based replication and at least one replica. If you're on Aurora, most of it still applies — but Aurora's fast DDL covers more cases natively, so check that first.

When this playbook applies

• The table is large enough that a native ALTER TABLE would exceed your write-lock tolerance. In practice: anything over a few million rows or a few GB, or any table your app writes to on every request.
• The change is one of: add column, drop column, add/drop index, change column type, rename column, add foreign key, or change primary key.
• You can tolerate a sub-second write pause at cutover. If you truly cannot — for example, a payments ledger during business hours — you need application-level dual-writes, not a schema tool. That's a different post.
• You have a staging environment you can load with production-shaped write traffic. If you don't, stop and build one. Skip this and you will learn about cutover behavior in production.

Step 1: Classify the change before you pick a tool

Not every schema change needs an online tool. MySQL 8.0's ALGORITHM=INSTANT handles add-column-at-end, drop-column (8.0.29+), rename column, and a few others in metadata-only time. Check SHOW CREATE TABLE and the InnoDB online DDL matrix before reaching for anything else.

For each change, categorize it:

• Instant — metadata-only. Run it directly in a transaction with a short lock_wait_timeout. Done.
• Inplace — rebuilds the table but permits concurrent DML. Usually safe on smaller tables. Watch for replication lag — the replica will still rebuild.
• Copy — locks the table for writes. This is what killed you last time. Use gh-ost or pt-osc.

Anti-pattern: assuming every change needs gh-ost. Running gh-ost for an add-column-at-end on MySQL 8 wastes hours and adds risk. Check the algorithm first.

You'll know this step is done when you can state, on paper, which algorithm MySQL will pick for your change and why the native path doesn't work.

Step 2: Pick gh-ost or pt-osc based on how your app writes

The choice usually comes down to triggers.

pt-online-schema-change creates AFTER INSERT / UPDATE / DELETE triggers on the original table that mirror writes into a shadow table while it back-copies rows in chunks. It works anywhere, including a single primary with no replicas. But the triggers add latency to every write on the original table, and if your app already has triggers, pt-osc will refuse to run (or you'll need --preserve-triggers, which has sharp edges).

gh-ost is triggerless. It reads the binlog of a replica (or the primary), applies changes to a shadow table asynchronously, and back-copies in chunks. Because there are no triggers, write latency on the original table is unaffected. It also throttles cleanly based on replica lag. But it requires row-based binlog format (binlog_format=ROW, binlog_row_image=FULL) and a replica it can attach to — or the ability to run against the primary with --allow-on-master, which I don't recommend for busy tables.

Choose gh-ost when: you have replicas, RBR is on, and the table is hot enough that trigger overhead matters. Choose pt-osc when: you're on a single-node setup, or you need to run inside a managed service that restricts binlog access, or your team already knows Percona Toolkit and the table is not write-saturated.

Anti-pattern: running gh-ost with --allow-on-master because it "seems simpler." You've now put the binlog reader, the row copier, and production writes on the same instance. When throttling kicks in, it kicks in against itself.

You'll know this step is done when you can explain, in one sentence, why the other tool is wrong for this specific migration.

Step 3: Reproduce production write load in staging

This is the step everyone skips and everyone regrets.

Take a recent production snapshot. Restore it to a staging cluster with the same replication topology. Then replay real write traffic against it — either via mysqlbinlog replay, a shadow-traffic proxy like Ghostferry or a custom replay tool, or at minimum a sysbench workload calibrated to match your production QPS and row-modification distribution.

The load matters because gh-ost and pt-osc both behave differently under contention. A migration that takes 20 minutes on an idle staging box may take 6 hours under real load, and the cutover behavior — the part that actually breaks things — is only visible when there's a write queue to drain.

Measure three things during the staging run:

1. Copy phase duration and how often the tool throttled itself.
2. Replica lag peaks. Anything over your alerting threshold means you need a lower --chunk-size or higher --max-lag-millis.
3. The cutover window. How long was the atomic swap? What was your p99 write latency during that window? Did any connections time out?

Anti-pattern: testing on an empty staging DB and declaring success because "gh-ost finished in 3 minutes." You measured nothing that matters.

You'll know this step is done when you have a graph of write latency across the cutover window from staging, and you know what your app's connection timeout is set to (hint: check both the app pool and the load balancer).

Step 4: Harden the cutover, not the copy

Here's the insight that changes how you think about this: the copy phase is boring. It's a long, throttled, resumable background job. If it's slow, you make it slower and it still finishes. Nothing catches fire.

The cutover is the opposite. Both tools need to briefly stop writes to the original table, apply the last few binlog events (gh-ost) or drain the trigger queue (pt-osc), and atomically rename the shadow table into place. That window is typically 100ms to 3 seconds. Under production write load — where you might have hundreds of connections in flight — those seconds are enough to:

• Queue writes past the app's connection timeout, causing 500s.
• Trigger connection-pool exhaustion, cascading to unrelated endpoints.
• Exceed lock_wait_timeout on transactions holding metadata locks on the table (long-running SELECTs are the usual culprit), which will make the cutover fail and gh-ost retry — extending the window.

Harden the cutover with these controls:

• Kill long-running queries before cutover. Set --cut-over-lock-timeout-seconds low (1–3s) so a failed swap fails fast rather than holding a metadata lock. Have a script ready to KILL any query on the target table older than a few seconds.
• Widen your app's timeouts temporarily. If your DB connect/read timeout is 500ms, a 2-second cutover guarantees errors. Bump timeouts on writes to the affected table before cutover, revert after.
• Schedule cutover in a low-write window. Not the copy — the cutover. The copy can run for hours during business hours. The 3-second swap should happen when write QPS is at its daily minimum.
• Use --postpone-cut-over-flag-file with gh-ost. Let the copy finish, then trigger the cutover manually when you're ready and watching dashboards. Never let the tool decide when to swap.

Anti-pattern: "we ran gh-ost and it finished, so we're done." You're not done until you've inspected the p99 latency graph across the exact minute of the swap.

You'll know this step is done when you have a written cutover checklist that includes: kill-long-query command, timeout-widening SQL or config change, the exact touch command to trigger cutover, and a rollback plan.

Step 5: Run the migration with an observer, not just a runner

Two engineers. One drives gh-ost/pt-osc. The other watches:

• Replica lag (SHOW SLAVE STATUS / SHOW REPLICA STATUS).
• Primary write latency and QPS.
• App error rates on endpoints that touch the target table.
• Connection pool saturation.

The observer's job is to say "throttle now" or "abort." gh-ost's interactive commands (echo throttle | nc -U /tmp/gh-ost.sock) let you pause without killing the migration. Use them. If lag climbs, throttle. If the app starts erroring, throttle. The migration will wait.

Anti-pattern: running the migration in a screen session and going to lunch. The tool is designed to be babysat.

You'll know this step is done when the copy is complete, the postpone-flag file is in place, and you're staring at a green dashboard waiting to trigger the swap.

Step 6: Trigger cutover, verify, and keep the old table for a day

Remove the postpone-flag file. Watch the swap complete — usually under a second if you've done step 4. Verify:

1. SHOW CREATE TABLE shows the new schema.
2. Row counts match between the new table and the archived old table (gh-ost renames the original to _tablename_del by default).
3. Application writes succeed. Check a canary write path immediately.
4. Replica lag returns to baseline.

Do not drop the old table immediately. Keep _tablename_del for at least 24 hours. If you discover a bug — a missing index, a column with the wrong default, an ORM that expected the old shape — the old table is your fastest rollback path. gh-ost's --ok-to-drop-table is convenient but premature.

You'll know this step is done when the app has been serving production traffic against the new schema for at least one business-hours cycle with no elevated errors.

Step 7: Update the app, then drop the old table

If the migration was part of a code change — a new column the app now writes to, a renamed field — deploy the code change after the schema change is stable, and do it as an expand/contract pattern:

1. Migrate schema to add the new shape (both old and new columns exist).
2. Deploy code that writes to both old and new.
3. Backfill any historical data.
4. Deploy code that reads from the new column.
5. Deploy code that stops writing to the old column.
6. Migrate schema to drop the old column.

This is slower than a big-bang migration. It's also the only pattern that lets you roll back any single step without a data-loss incident.

You'll know this step is done when the old column is gone, no code references it, and the archived _del table has been dropped after a safe observation period.

Failure modes I've seen

Cutover fails repeatedly because a reporting query holds a metadata lock. Someone's BI tool runs a 20-minute SELECT on the target table. gh-ost tries to swap, hits lock_wait_timeout, retries, hits it again. Fix: KILL the query, or coordinate with the analytics team before cutover, or set lock_wait_timeout aggressively low so retries fail fast.

Foreign keys. Both tools handle foreign keys poorly. gh-ost has --alter-foreign-keys-strategy with several unsatisfying options. pt-osc has --alter-foreign-keys-method. Neither is great. If your table has inbound FKs, plan the migration as a multi-step operation: drop FK, migrate, re-add FK — and understand each step's implications for the referring tables.

Enum changes that reorder values. Adding an enum value at the end is fine. Reordering or removing values will silently corrupt data on tables that store the ordinal. Change enums to lookup tables before you regret it.

Replica-only migrations that forget the primary. If you migrate a replica first (some teams do this to reduce primary load), remember: when you promote it, the primary now has the old schema. You need to migrate all nodes.

ORMs that cache schema. Rails, Django, and some Node ORMs cache the column list at boot. After a column rename, the app may still reference the old name until you restart every worker. Include an app rolling-restart in your cutover checklist for rename operations.

Disk space. Both tools need roughly 2x the table's disk footprint during the copy. Check free space on the primary and every replica. A replica running out of disk mid-migration is a bad afternoon.

Live schema migrations are one of those operations where the tool is easy and the discipline is hard. The teams that do this cleanly aren't using a secret weapon — they're just paranoid about the cutover, they rehearse under real load, and they treat every migration as an expand/contract sequence rather than a single event. If you're modernizing a legacy MySQL-backed product and this playbook feels heavier than your team is currently equipped for, that's a reasonable signal to bring in help; database and platform modernization work is a fair chunk of what we do at CodeNicely, alongside product work like GimBooks where schema changes on live customer data were a weekly event.

Frequently Asked Questions

Can I run gh-ost on Amazon RDS or Aurora?

Yes on RDS MySQL, with caveats: you need binlog_format=ROW, binlog_row_image=FULL, and a replica gh-ost can attach to. On Aurora, gh-ost works but Aurora's own fast DDL covers many common operations (add column, drop column) with far less complexity — check Aurora's supported operations before reaching for gh-ost. Also confirm log_bin retention is high enough that gh-ost won't outrun the binlog window on a long migration.

How do I roll back a schema migration that's already cut over?

If you kept the archived _tablename_del table (gh-ost's default), rolling back is a rename operation: rename the current live table out of the way, rename the archived table back into place. This works cleanly only if writes since cutover haven't diverged in ways you can't reconcile — which is why the expand/contract pattern in step 7 matters. For destructive changes (dropped columns), rollback requires restoring from the archived table plus replaying binlog events since cutover.

What's the maximum table size gh-ost can handle?

There's no hard limit — gh-ost has been used on multi-terabyte tables. Practical constraints are disk space (you need ~2x the table size free), binlog retention (the migration must complete before the oldest binlog it needs is purged), and replica lag tolerance during the copy. For very large tables, tune --chunk-size down and --max-lag-millis up, and expect the copy to run for days.

Do I need gh-ost or pt-osc if I'm on MySQL 8.0 with instant DDL?

For operations MySQL 8 supports as ALGORITHM=INSTANT — add column at end, drop column (8.0.29+), rename column, add/drop virtual column — you don't need an external tool. For anything that requires a table rebuild (change column type, add index on a huge table, change primary key), you still need gh-ost or pt-osc. Always check SHOW CREATE TABLE output after a test run on staging to confirm MySQL used the algorithm you expected.

How do I know if my migration plan is safe to run in production?

The minimum bar: you've replayed production-shaped write load against a staging clone using the same tool and flags, you've measured the cutover latency window and confirmed it's shorter than your app's connection timeout, and you have a written rollback plan that doesn't require heroics. If any of those three are missing, delay. For high-stakes migrations on business-critical data — payments, healthcare records, financial ledgers — contact CodeNicely for a personalized assessment of the plan before you run it.