Online MySQL migration with gh-ost without downtime: step-by-step guide

Why migrating a MySQL table without gh-ost stops the service

The most expensive migration is the one that looks harmless: add a column, change a type, rebuild an index. The tool sends ALTER TABLE to a table with hundreds of millions of rows, and during the preparation stage the query acquires a metadata lock.

While it is held, not only writes to that table but ordinary reads queue up: connections pile up, response time grows severalfold, and the application starts returning errors before the administrator notices the cause.

Then the arithmetic kicks in. If the operation requires rebuilding the table, the database reads and writes a copy of all rows — overhead grows linearly with volume, and hours of work turn into a night window that nobody guarantees.

On replicas, the change is replayed sequentially and with delay, so reads from replicas return stale data, and rollback of an already started rebuild without losses is impossible.

Online schema migration solves this by splitting the work. gh-ost reads the binary log of the primary node, creates a shadow copy of the table in the new form, and moves rows in small batches, automatically choosing the pace based on server load and replica lag.

Changes that arrive during the transfer are applied from the log, so the copy stays current. The switch is performed by atomic rename — the table lock is held for fractions of a second, not hours.

The process can be paused, resumed, and checked at any moment; the original table remains until the result is confirmed and serves as a rollback point.

Next we break down how this is handled in practice: the order of steps, replica lag control, and the criteria by which the switch is safe.

What changes in practice: lock time and failure class

Switching to an online schema changes not "convenience" but measurable parameters: write lock time, service downtime during migration, migration rollback window.

gh-ost does not hold a blocking ALTER on the production table — it builds a copy alongside and transfers changes from the replication log into it, and the write lock is reduced to a short switch at the very end.

On tables with hundreds of millions of rows, copying runs for hours, and all this time the service accepts writes.

Load on the primary and replica behavior

The main work happens in a separate ghost table: reading rows in batches, writing in chunks, pauses between them. The primary gets additional load from copying, but it is limited explicitly — by rows per second and the replica load threshold.

If replica lag rises above the specified threshold, the migration pauses itself and continues when the replicas have caught up. Replica lag becomes a manageable value instead of a production incident.

Rollback window and the eliminated failure class

Until the switch is performed, rollback is stopping the process and dropping the ghost table: the production table is untouched, and the data in it is current. After the switch, rollback is a reverse swap using the saved schema, and it also does not require downtime.

A whole class of failures goes away: locks during ALTER, migration failing halfway due to timeout, replica degradation under load. Instead of "migration succeeded or everything crashed," there is a manageable process with a predictable rollback window.

Comparison of approaches: gh-ost, triggers, and table rebuild

The migration method is chosen by load profile, not table size: what matters is how long the operation can be held and what happens on interruption. Below are five routes and the limitations that make one of them impossible right away.

Where the trigger-based approach breaks

Tables without a primary key, frequent updates to one row, and already existing triggers: write overhead doubles, and copy lag grows faster than it catches up.

Where gh-ost requires verification

Foreign keys, wide rows, and servers with the replication log disabled. Before launch, the schema, write rate, and space for the shadow table are checked — otherwise the process will have to be stopped and restarted.

Stages of online migration: from load measurement to traffic switch

The order of work in online migration is not arbitrary: gh-ost creates a shadow table, copies rows into it in batches, and simultaneously applies changes from the replication log, so an error in the schema or queries surfaces already under production load.

The database migration plan is built so that each risk is removed before the table switch, not after.

Preparation and checks

1. Audit of schema and queries — record index composition, foreign keys, triggers, and table access in code; look for anything the tool does not transfer automatically.
2. Load measurement — capture the read and write profile, daily peak, and table growth rate; from this data calculate the expected copy time and acceptable replica lag threshold.
3. Test migration run — on a restored copy of production data, run the full cycle, including rollback and query plan checks before and after.

Launch, switch, and control

• Start copying — gh-ost starts with automatic throttling by replica lag and primary node load.
• Queue monitoring — track transfer speed and replica delay; if the threshold is exceeded, the process pauses without affecting reads.
• Table switch — the rename is performed by one atomic operation with a brief write lock, then the old name remains available for rollback.
• Post-migration control — compare row checksums, compare response for key queries, and keep the old table until metrics return to a steady baseline.

Migration artifacts: configurations, replication schemas, runbooks

Migration ends not at the traffic switch moment, but when the repository contains a set by which another engineer can repeat the task, and the on-call shift can roll it back without the author.

Therefore we store all artifacts next to the launch configuration and update them in the same change as the run itself — otherwise migration documentation diverges from reality on the first incident.

• Launch parameters: connection endpoint, source replica, load and lag thresholds, check intervals, automatic pause conditions — with an explanation of why exactly this value was chosen.
• Data flow diagram: how the process reads the change log from the replica, writes to the shadow table, and performs the final switch; separately mark the points where migration can be safely interrupted.
• Replication diagram: "primary — replicas" topology, acceptable lag, pre-start check method, and the role of each replica in the process.
• Migration service tables: full list of created objects, their purpose, lifetime, and owner responsible for deletion; access policy — the application and on-call do not write to them.
• Rollback runbook: signs at which the run is stopped, cancellation order, what to do with already switched traffic, and how much time is allotted for the decision.
• Instructions for the on-call shift: which metrics to watch, which deviations to consider alarming, and at what moment to escalate to the responsible engineer.
• Run log: test results on the copy, actual values versus planned, recorded discrepancies, and conclusion on rerun.

Each item is reviewed before start, not after: a rollback runbook nobody read is useless at the moment of failure.

Case: planned migration of a large table without service downtime

An orders table with several hundred million rows was migrated to a new schema on a live service: traffic runs around the clock, and a regular ALTER blocks writes for the entire copy duration.

We switched to gh-ost — the tool reads the change log and transfers rows in batches, so the production table remains available for both writes and reads.

What did not go according to plan

An hour before the switch, the replica could not keep up with the change stream. Instead of switching on schedule, we stopped the step and let the replica catch up with the source — no downtime was needed.

After this case, we added a threshold to the process: while lag is above the specified value, the switch does not start.

Rollback was tested in advance: the old table remains in place, return is one operation, without restore from backup. On the next migration, this reduced preparation and allowed the work to be done without a maintenance window.

What to do if the replica lags at the moment of switch?

Replica lag is the most frequent scenario in which online migration stops being safe. While gh-ost transfers rows to the shadow table, replicas receive the same event stream but apply it slower than the primary.

If reads are switched to a lagging replica, the application will see a state before some changes. Not all rows are lost, but individual ones — such discrepancies are hardest to diagnose.

Signs of lag are visible in advance: log apply delay grows, a queue of unprocessed events accumulates, and the write peak at the switch moment adds a spike.

The built-in indicator lies on long transactions — a service heartbeat table updated by gh-ost in a separate thread is more reliable.

Before the switch, we set hard conditions: the delay below the threshold holds continuously for the entire observation window, the event queue is empty, and there is no lag on any replica under traffic.

Separately, we compare row counts and checksums by blocks between the shadow and original table — this confirms data integrity, not just the fact that copying finished.

Migration rollback is planned in advance: gh-ost leaves the original table, so return is stopping the process and a reverse rename, and traffic returns to the previous schema in seconds. Artifacts cannot be deleted before verification, otherwise there will be nowhere to roll back to.

If lag continues to grow despite load limiting, we do not switch: we look for the bottleneck — disk, network, heavy queries on the replica — and return to the transfer after it is eliminated.

Technical doubts: locks, rollback, and replica lag

Engineering doubts around online migration usually come down to four questions: does the process hold a lock on the production table, what happens on connection loss, what privileges are needed on the primary, and will replicas diverge during parallel transfer. Below are short answers for each point and the environment requirements we check before start.

What happens if the migration process is interrupted?

The operation stops at the current stage: the shadow table and auxiliary objects are dropped, and the source data remains untouched. The run can be repeated — before start, the synchronization state is checked again, and the incomplete copy does not go into production.

Is direct access to the primary server required?

Yes: writing changes and switching go through the primary, so privileges are needed to create tables, triggers, and rename. Replicas are used only for lag control; separate accounts for them are not required.

Can several tables be migrated at the same time?

Yes, but parallel launch across many tables creates competition for disk operations and accelerates replica lag. It is safer to carry out the transfer in waves of one or two tables, checking replication delay between stages.

Let's discuss your migration: schema, risks, work plan

The introductory conversation takes about an hour and is to the point: we review your schema, data volume, and availability requirements to see where the transfer may affect live traffic.

Based on the results, you receive not general promises, but a migration work plan with stages, owners, and stop points.

• Migration scope assessment: table sizes, number of replicas, peak write rate, and share of long transactions — the transfer pace depends on this.
• gh-ost mode: transfer via replica or from the primary node, load limits, lag threshold at which the process pauses itself.
• Migration window: when we perform the final switch, how long we keep the original table as insurance, and how we return traffic back.
• Roles and communication: who confirms the start, whom we escalate to on deviation, and where we keep the step-by-step action log.
• Success criteria: checksums match, replica lag is normal, and application response time has not increased.
• Failure procedure: stop the transfer, return traffic to the original table, analyze the log, and schedule a rerun.

We agree in advance what we consider a failure and by what sign we roll back — then the decision is made based on facts, not in a hurry. To discuss migration concretely, describe the task: we will return with a scope estimate, work plan, and team composition.