Online Schema Changes in MySQL Without Downtime

On a table with hundreds of millions of rows, the wrong ALTER TABLE can lock writes for hours. MySQL offers several ways to avoid that.

1. Ask for INSTANT

ALTER TABLE orders ADD COLUMN gift_message VARCHAR(255) NULL, ALGORITHM=INSTANT;

Instant changes only touch table metadata: adding or dropping columns (in recent versions), changing defaults, renaming columns. If the change can't be instant, MySQL refuses instead of silently doing something slow, which is exactly what you want.

2. INPLACE with no locking

ALTER TABLE orders ADD INDEX idx_orders_status (status), ALGORITHM=INPLACE, LOCK=NONE;

Index creation rebuilds data in the background while reads and writes continue. It still uses I/O and can cause replica lag on large tables.

3. Watch out for metadata locks

Every ALTER needs a brief metadata lock at the start and end. If a long-running transaction is open on the table, the ALTER waits, and all new queries queue behind it.

SET SESSION lock_wait_timeout = 5;   -- fail fast instead of blocking everyone
SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME = 'orders';

4. Tools for heavy changes

For changes that need a full table rebuild (changing a column type, converting a primary key), use an online schema change tool. They create a new table with the new structure, copy rows in small chunks, keep it in sync and swap it in atomically:

gh-ost \
  --host=db-primary --database=shop --table=orders \
  --alter="MODIFY total DECIMAL(12,2) NOT NULL" \
  --max-load=Threads_running=25 \
  --chunk-size=1000 \
  --execute

• gh-ost (GitHub) reads changes from the binary log; no triggers on the original table; can be paused and throttled.
• pt-online-schema-change (Percona Toolkit) uses triggers to sync changes; widely used and battle-tested.

Process

• Test the exact ALTER on a production-sized copy first.
• Run big changes off-peak and monitor replica lag.
• Use expand-and-contract for renames so old and new app versions both work during deploys.