Run MySQL migrations safely

A migration is a versioned schema change. Apply migrations in order and record successful versions in the database, so every environment can answer “which changes have already run here?”

The risky part is timing. A deployment is not instantaneous: for a while, old and new application code run against the same database. A migration that instantly renames a column breaks every old instance still querying the old name — and breaks the rollback too, because the old code cannot find its column anymore.

The safe pattern is expand and contract. For a risky rename, add the new column first, deploy code that supports both versions, backfill data, switch reads, then remove the old column in a later release:

-- expand: old code keeps working, new code can start
ALTER TABLE notes ADD COLUMN headline VARCHAR(200);

Backfill existing rows in bounded, restartable steps:

UPDATE notes
SET headline = title
WHERE headline IS NULL
LIMIT 1000;

Run it repeatedly until it reports 0 rows affected. The WHERE headline IS NULL condition makes the backfill idempotent: if it stops halfway, running it again continues instead of redoing finished rows. Only after every reader uses the new column does a later migration drop the old one — the contract step.

MySQL-specific rules

Two behaviors matter more in MySQL than elsewhere.

First, MySQL data-definition statements such as CREATE TABLE, ALTER TABLE, and DROP TABLE cause an implicit commit. You cannot make a multi-statement schema migration atomic by wrapping it in START TRANSACTION — MySQL commits at each DDL statement anyway. Plan every migration assuming it can stop between statements, and test what happens when it stops halfway through.

Second, the cost of ALTER TABLE depends on the table and the operation. Some changes are instant metadata updates; others rebuild the whole table and block writes for the duration. Check how MySQL executes each ALTER TABLE on the table size and version you actually run — rehearse against a restored copy of production data, and you get the real duration for free.

Back up first, make each step restartable, and never let a migration be the first time a statement touches production.