Online MySQL schema migration: where production breaks
Changing a table structure without downtime seems routine until ALTER meets live traffic. MySQL holds table metadata until the rebuild finishes: while millions of rows are rewritten, incoming queries wait for the lock to be released. Service degradation under load begins before the operation itself completes.
Table locks in production are only the first facet. A long rebuild keeps a transaction open and bloats the undo log, while timeouts during a long rebuild disrupt replication: lag grows, and some traffic reads stale data. The user sees not a migration error but a slowed-down service.
A separate class of failures is data divergence on replicas. A rebuild via a shadow table and triggers moves rows in chunks, and a chunk lost during the switch or a unique-key conflict remains only in the old table version. The migration looks successful, while a row-count check shows divergence.
The pt-online-schema-change tool removes most of these risks: it copies the table in chunks, monitors replica lag, and switches data by renaming.
But behavior is determined by parameters — chunk size, pause on lag, permissible disk load; set at random, they can bring the database down harder than a single ALTER.
Online MySQL schema migration is a controlled process, not a button press: it is preceded by measurements, a rehearsal on a copy, and a rollback plan. We examine exactly where production breaks and how that is closed.
What a careful table rebuild without downtime provides
A regular ALTER TABLE on a large table builds a queue of queries behind the metadata lock: while the rebuild is running, reads and writes wait, and on a loaded service this looks like a spike in client timeouts.
Rebuilding tables without stopping the service works differently: Percona Toolkit creates a shadow copy with the target structure, moves rows in chunks, and synchronizes changes via triggers while the application continues writing to the original table.
A lock remains only during the final switch — a short rename that does not have time to accumulate a query queue.
This is exactly the elimination of lock-related failures: the class of errors where the application waits for table release longer than its timeout allows does not occur at all.
The maintenance window shrinks from hours of planned downtime to a few seconds, and only provided that the switch fits the schedule.
Predictable work time is provided by controlled transfer speed: a pause is maintained between chunks, and when lag on the replica grows, the process pauses automatically.
Reduced load on database replicas is achieved by the same mechanism — replicas do not lag behind the master, so reports and analytics continue to read current data rather than catching up to the source after the work is complete.
At the output, the client receives a table with the target structure, a rollback plan prepared before launch, and a transfer log: number of rows, pause points, duration of the final swap.
Timelines for a large table are calculated in advance based on volume and transfer speed, rather than discovered during downtime — this is what turns migration from a risk into planned work.
Ways to rebuild a table: comparison of approaches and limitations
The choice of method is determined by three parameters: table size, permissible write unavailability time, and the nature of the load.
An online rebuild via an intermediate copy — pt-online-schema-change from Percona Toolkit — creates a table with the new structure alongside, copies rows in chunks, and attaches triggers to the source, so application changes land in both tables at once.
In the final step, the names are swapped, and the write lock fits within milliseconds — usually neither users nor background jobs notice it.
The approach's boundaries are also specific. A table without a primary or unique key is not processed — this rules out some historical tables.
The copy takes as much space as the source, and under heavy writes, catching up on changes can lag behind the triggers: then the work is interrupted and moved to a maintenance window.
| Approach | When it fits | Limitations |
|---|---|---|
| Online rebuild via an intermediate copy | There is a primary key, writes cannot be stopped, the table runs under 24/7 load | The copy doubles space, no unique key — it does not start, under heavy writes catch-up lags |
| Transfer to a new table with backfill | Key type changes, partitioning scheme changes, or the structure is rewritten deeply | Requires code that reads both tables and time for reconciliation; application switchover is coordinated |
| Planned maintenance window | Small or medium table, agreed downtime is available | Writes stop, some queries receive an error, a prepared snapshot is needed for rollback |
A practical rule: up to several million rows and moderate writes — use the online method; a deep structural change — transfer to a new table followed by reconciliation; small tables are cheaper and more predictable to rebuild in a planned window, when writes stop for minutes.
Work stages: from load audit to database handover
The order of work is built from the load, not from the convenience of the window: while the table is open for writes, the change competes for locks, space, and replica throughput.
Each stage ends with a verifiable result, so the decision to proceed is made based on numbers, not a feeling.
- Database load audit. We capture a profile over several days: peak hours, share of write operations, heaviest queries, replica lag, size and growth rate of target tables. The output is a window in which intervention is safe.
- Work execution plan. We determine what change is needed, whether the table is rebuilt entirely or altered in place, whether there is enough disk space, and how foreign keys and triggers will behave. This is also where duration estimate appears.
- Migration setup. The pt-online-schema-change tool transfers data in chunks, so we set chunk size, permissible replica lag, emergency stop threshold, and lock control. Incorrect settings slow the database more than the change itself.
- Trial run. We run it on a copy of the production database with real volume: measuring time, trigger behavior, and impact on replicas. This gives actual numbers instead of estimates.
- Rollback point. We fix the method and time of return: the original table remains untouched, a snapshot is taken, and the return fits the window. We test rollback before the production launch, not after.
- Data integrity reconciliation. We compare row counts and checksums before and after, and verify indexes and foreign keys. The new schema goes into operation only if they match completely.
- Database handover. We provide parameters, schema, repeat-change procedure, and monitoring thresholds by which the on-call engineer will see a problem before users do.
Describe your database and task — we will return with an estimate and a plan for these stages.
What remains with the team: delivery scope and documentation
The work ends not at the moment of a successful migration, but when the client's team can repeat it without us.
Therefore, the delivery includes not only the run itself but also configurations, table relationship diagrams, and procedures — the things by which the next engineer will follow the same path and get the same result.
- Working configurations: data copy parameters, permissible server load thresholds, lock wait limits, and the list of excluded tables — as files alongside the migrations in the project.
- Table dependency diagrams: which foreign keys, triggers, and parent-child relationships affect the order of work, what to transfer first, and what to leave until last.
- Access rules: a separate account with minimally sufficient privileges, exactly what is needed to create triggers and switch, and what remains inaccessible by default.
- Repeat work procedure: a step-by-step order for the next migration — from estimating table size and the work window to checking replication and removing auxiliary objects.
- Rollback documentation: how to return to the previous schema if the switch did not go through, which objects to delete, and within what timeframe the state returns to the original.
- Run report: processing time for each table, replication lag, number of retries, and points where engineer intervention was required.
- Verification scripts: row count and checksum reconciliation before and after, so the result is confirmed by data, not words.
Case study: migrating a large table to a new structure without downtime
Bottleneck: rebuild under lock
A table with 180 million rows handled several thousand operations per second, and a regular change of the key field type with index rebuild held it fully under lock.
The nearest window offered about 40 minutes of write unavailability, adjacent teams would have had to stop background jobs, and some queries still timed out — around 0.3% of operations were lost and handled manually.
How it went and how it ended
We used pt-online-schema-change from Percona Toolkit: the tool creates a shadow copy of the table, attaches triggers to changes, and transfers rows in chunks, and when replication lag grows, it throttles itself.
Copying ran in parallel with production traffic, without a downtime window — the evening peak passed without degradation, and the switch to the new structure took fractions of a second, with a lock only for the table swap.
After the transition, we verified checksums for all transferred chunks: zero discrepancies, and return to the original table remained available until the end of reconciliation.
| Metric | Before | After |
|---|---|---|
| Write unavailability | ~40 minutes | fractions of a second |
| Lost operations | ~0.3% | 0 |
| Data reconciliation | sample-based, manual | checksums for all chunks |
| Behavior at peak | background jobs stopped | migration throttled itself |
| Rollback | restore from a copy | return to the original table |
What to do if MySQL already has replicas and triggers?
An already deployed environment with replicas does not prevent migration but changes the order of work.
The tool creates a copy of the table with the new structure, fills it in chunks, synchronizes edits with triggers, and at the end replaces the original with an atomic rename — replicas receive the same sequence from the log.
Existing triggers are the first snag: by default, the tool stops if the table already has triggers, so as not to break someone else's logic.
The --preserve-triggers option transfers their bodies to the new table, but check that they do not explicitly reference the old name — after the swap, such references will become broken.
Foreign keys determine the cost and the risk window.
The rebuild_constraints mode rebuilds child constraints — slower, but without a gap where references point to a vanished table; drop_swap is faster but creates such a window, and with cascade-delete relationships it requires separate verification. When keys point both inward and outward, the change is run on a copy of the entire environment.
A separate risk is replication filters: if the auxiliary copy table falls under them, it will not exist on the replica, the swap will appear “successful,” and the structures will diverge, so the configuration is checked before the start, and the --max-lag threshold pauses copying during severe lag.
Until the schema change reaches all replicas, the application must not read the new columns — on a lagging replica they do not exist yet, hence delayed reads of old data and errors.
We work with one change per pass: run, swap, verify the structure on the master and each replica, then the next.
Technical concerns: questions asked before the start
Before rebuilding a large table on a production database, the same set of questions comes up: how much will live traffic suffer, what happens on a break, how foreign keys and triggers will behave, and what confirms the result. Below are the answers usually needed before agreeing on the window.
Will a table rebuild bring down the database?
Copying proceeds in chunks with pauses between them. Thresholds are set for the number of active queries and replica lag: if exceeded, copying stops and waits until the database frees up. Production gets an increase in load, not a drop in response time.
What happens if the work is interrupted?
In Percona Toolkit, on stop or error the tool removes its triggers and temporary table, the original remains untouched, and it can be resumed from the stop point. Only the final table switch is irreversible — it is kept short and done in an agreed window.
What about foreign keys and triggers?
Child tables are switched in a separate step, otherwise foreign keys will continue to point to the old one. Triggers that were attached to the original table do not fire during copying — their logic is transferred in advance, otherwise some changes are lost.
How do we make sure no data was lost?
After the switch, we compare row counts and checksums by ranges, and separately the records changed during copying. We investigate discrepancies before dropping the temporary table: while it is in place, rollback is still possible.
What if there are multiple database replicas?
Replicas do not inherit the rebuild — the schema on each copy is brought in line with its own pass. Either we repeat the migration on each one, or rebuild the copy from a backup and catch it up with the change log, if the lag is acceptable.
Let's discuss the task: we'll send a work plan and estimate
To come back with a work stage plan and timeline estimate, we need inputs about your database — not an abstract “there is a server with MySQL,” but specifics: target tables and their size, storage engine, presence of foreign keys, triggers, and views, replication topology, and the low-load window.
The more precise the initial data, the fewer surprises in production: it shows in advance where a table rebuild will be required, and where a switch via rename is enough.
What you get after analyzing the task:
- A work stage plan with the order of actions: preparation, trial run on a copy, migration, data integrity verification, rollback point fixation.
- A timeline estimate broken down by stage with a note where a business coordination window is needed.
- A rollback scheme: which metadata we keep and how to return to the original table if checks fail.
- A list of risks for your configuration: long locks, increased replica load, lag of subordinate nodes.
- A set of support metrics: replication delay, number of active connections, processing speed of copied rows.
- A draft procedure for the on-call shift: what to do when alerts trigger and when to stop the transfer.
We perform verification on your environment — on a copy of the production database or a stand of the same configuration, so that the run result carries forward without assumptions or recalculations.
Describe the task: which tables we touch, what the peak-hour load is, and whether there is a window for the switch. We will return with a stage plan and timeline estimate.