Schema changes without downtime on MySQL

Most schema changes can be applied to a busy MySQL database without taking the application down. The ones that cannot are usually not the ones people expect, and the difference is worth knowing before you write the migration rather than during it.

Three classes of change

MySQL 8.0 offers three algorithms for ALTER TABLE, and the one it picks determines whether your change is free or expensive.

You do not have to guess which one you will get. Ask for the cheap one explicitly, and let MySQL refuse:

-- Fails immediately if it cannot be done instantly, rather than-- quietly rebuilding a 200 GB table during your deployment window.ALTER TABLE `orders`  ADD COLUMN `channel` varchar(20) NULL,  ALGORITHM = INSTANT;

What is instant, and what is not

• Instant: adding a nullable column at the end of the table, dropping a column, renaming a column, changing a column's default, adding or removing a virtual generated column.
• In place: adding or dropping a secondary index, adding a foreign key when checks are off, changing whether an index is visible.
• Full rebuild: changing a column type, adding a column at a specific position, changing the primary key, changing character set or ROW_FORMAT, converting to a partitioned table.

The practical lesson is that adding a column is nearly free provided you let it go at the end of the table. Insisting on AFTER some_column for tidiness turns a metadata update into a full copy. Column order is not worth an outage.

Renaming a column without breaking anything

The rename itself is instant. The problem is not the database, it is that during a rolling deployment both the old and the new version of your application are running at once, and they disagree about the column's name. A single-step rename breaks one of them.

The way through it is to expand, migrate, then contract — four deployments, none of which has a moment where the running code and the schema disagree:

1. 1

   Add the new column

   Add customer_reference alongside the existing cust_ref. Nothing reads it yet. Instant, and invisible to the application.

2. 2

   Write to both, read from the old

   Deploy code that writes both columns and still reads the old one. Backfill the new column for existing rows in batches.

3. 3

   Read from the new

   Deploy code that reads the new column. Both columns are still being written, so a rollback to the previous version remains safe.

4. 4

   Stop writing the old one, then drop it

   Once no running version references it — and you are confident you will not roll back that far — drop the old column. Instant.

It looks like a lot of ceremony for a rename. It is, and it is still cheaper than an outage. The same shape works for splitting a column, changing a type, or moving a value to another table.

When a rebuild is unavoidable

Sometimes you genuinely need to change a column type on a very large table. Two options remain, and both keep the table available:

• An online schema change tool such as pt-online-schema-change or gh-ost, which builds a shadow copy, keeps it in sync with triggers or the binary log, and swaps it in at the end.
• Expand and contract again — add a new column of the new type, dual-write, backfill in batches, switch reads, then drop the original. More application work, but no external tooling and no shadow table.

Rehearse it

Whatever the plan, run it once against a restored backup of production at production scale. That rehearsal tells you the real duration, and it is the only step in this article that cannot be replaced by reading. Generate the script, apply it to a copy, time it, then decide.