Safe Online Schema Changes in MySQL

Introduction¶

ALTER TABLE on a large, busy table can block reads and writes long enough to cause an outage. MySQL 8 made many changes instant; the rest need an online schema change tool or a careful multi-step rollout.

The three algorithms¶

MySQL picks (or you specify) an algorithm:

• INSTANT — metadata-only, milliseconds, no table rebuild. Since 8.0.12: adding a column (8.0.29+ anywhere in the row order), adding a virtual column, renaming a column, adding/dropping a column default, changing ENUM/SET values by appending, dropping an index.
• INPLACE — rebuilds or modifies the table within InnoDB, usually allowing concurrent DML. Adding a secondary index, adding a foreign key, changing ROW_FORMAT. Faster than COPY, but still I/O-heavy on big tables and takes a brief metadata lock at start and end.
• COPY — builds a whole new table and copies every row, blocking writes (and sometimes reads) the entire time. Changing a column data type, dropping a PK, converting charset. This is the one that causes outages.

ALTER TABLE orders ADD COLUMN notes TEXT, ALGORITHM=INSTANT, LOCK=NONE;
-- fails loudly if it can't be done instantly, instead of silently doing COPY

Always specify ALGORITHM= and LOCK=NONE so an unexpectedly expensive change errors rather than locking prod.

When you need a tool¶

For a COPY change (type change, PK change) on a table too big to lock:

pt-online-schema-change (Percona Toolkit)¶

pt-online-schema-change \
  --alter "MODIFY COLUMN total DECIMAL(12,2) NOT NULL" \
  --execute \
  --max-load "Threads_running=40" \
  --critical-load "Threads_running=80" \
  D=shop,t=orders

It creates a shadow table with the new schema, adds triggers to keep it in sync, copies rows in chunks (throttling on load), then atomically renames. Caveats: needs room for a full table copy, adds trigger overhead during the run, and tables with existing triggers need --preserve-triggers (8.0) or can't be done.

gh-ost (GitHub)¶

Triggerless — reads the binlog to replay changes onto the shadow table. Lower production impact, pausable/throttleable interactively, but requires row-based binlog and a replica (or careful primary config).

The expand/contract pattern¶

Even with a tool, schema and code must be compatible during the rollout (old and new code run simultaneously). For a column rename total → amount:

1. Expand: add amount, backfill it, add a trigger or app dual-write so both stay in sync. Deploy code that writes both, reads total.
2. Deploy code that reads amount.
3. Contract: a later release drops total (now unused) and the sync.

Never: ALTER TABLE ... CHANGE total amount ... in one shot while the running app still selects total.

For a NOT NULL addition:

1. Add the column nullable with a default.
2. Backfill existing rows in batches.
3. Once all rows are populated and code always sets it, ALTER ... MODIFY ... NOT NULL (INSTANT in 8.0.29+ if a default exists).

Verification and troubleshooting¶

-- Will this ALTER be instant? Try it and let it fail:
ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT;

-- What's holding a metadata lock?
SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME = 'orders';
SHOW PROCESSLIST;                     -- "Waiting for table metadata lock"

• ALTER stuck on "Waiting for table metadata lock" — a long-running transaction (even a SELECT left open) holds a shared MDL. Find it in information_schema.innodb_trx / performance_schema.metadata_locks and end it. The ALTER also blocks all new queries behind it — kill the ALTER if prod is stalling.
• ALGORITHM=INSTANT rejected — that change can't be instant in your version. Check the docs for your exact MySQL version; upgrade or use a tool.
• pt-osc "Cannot chunk the table" — no suitable unique index; it needs one to iterate. Add one first (that add may itself be INPLACE).
• Replication lag spike during the copy — the tool's chunk copy floods the binlog. Lower chunk size, raise the throttle, run during a low period.
• Disk fills mid-migration — the shadow table doubles the table's size. Check free space first; clean up the leftover _tablename_new / _tablename_old if it aborts.
• Foreign keys break with pt-osc — child tables referencing the altered table need --alter-foreign-keys-method; test on staging.

Related tools and reading¶

• On-site: JSON formatter.
• Related posts: MySQL replication basics, Blue-green vs canary deployments.