MySQL ALTER TABLE Cheatsheet for Developers

MySQL ALTER TABLE is deceptively simple. The syntax looks like any other SQL dialect — until you run it on a table with ten million rows and production locks up for thirty minutes.

This cheatsheet covers the MySQL-specific behaviors every developer should know: which operations lock the table, which ones are instant in MySQL 8.0, and how to run migrations safely in production without waking the on-call engineer.

Lock Behavior at a Glance

Not all ALTER TABLE statements are equal. Some lock nothing. Some allow reads but block writes. Some lock everything until the operation completes. Here is the lock behavior for common operations on InnoDB tables in MySQL 8.0:

The key insight: MySQL 8.0 made many operations instant that required full table rebuilds in 5.7. If you are still on 5.7, upgrade before your next major schema change. The difference between "zero downtime" and "hours of locking" is often just the version number.

Online DDL: The Algorithm and Lock Options

MySQL supports explicit control over how ALTER TABLE executes via the ALGORITHM and LOCK clauses:

ALTER TABLE orders
  ADD COLUMN status VARCHAR(20),
  ALGORITHM = INPLACE,
  LOCK = NONE;

ALGORITHM Options

• INSTANT — Metadata-only change. No row copies. No locks. Available in 8.0.12+ for specific operations.
• INPLACE — Rebuilds the table in the background without creating a full copy. Allows concurrent DML.
• COPY — Creates a new table, copies all rows, then swaps. Locks the table for the duration. Fallback for unsupported operations.

LOCK Options

• NONE — Allows concurrent reads and writes. Fails if the operation cannot run without locking.
• SHARED — Allows reads, blocks writes. Safer than exclusive but still impacts production.
• EXCLUSIVE — Blocks reads and writes. Default for operations that require it.

Best practice: always specify ALGORITHM = INPLACE, LOCK = NONE. If MySQL cannot satisfy the request, it will error immediately rather than silently locking your table for an hour.

Safe Patterns for Common Operations

Adding a Column

-- Safe: instant in MySQL 8.0 if added at the end
ALTER TABLE users
  ADD COLUMN preferences JSON
  ALGORITHM = INSTANT, LOCK = NONE;

-- Safer: add as nullable first, backfill, then add constraints
ALTER TABLE users
  ADD COLUMN preferences JSON NULL
  ALGORITHM = INSTANT, LOCK = NONE;

-- Backfill in application code or with a batched UPDATE
-- Then add NOT NULL if required in a later deploy

Adding an Index

-- Safe: online index creation
ALTER TABLE orders
  ADD INDEX idx_created_at (created_at)
  ALGORITHM = INPLACE, LOCK = NONE;

-- Even safer: use pt-online-schema-change for very large tables
pt-online-schema-change \
  --alter "ADD INDEX idx_created_at (created_at)" \
  --execute \
  D=production,t=orders

Changing a Column Type

-- Dangerous: full table rebuild, exclusive lock
ALTER TABLE events
  MODIFY COLUMN payload LONGTEXT;

-- Safer alternative for large tables: pt-online-schema-change
pt-online-schema-change \
  --alter "MODIFY COLUMN payload LONGTEXT" \
  --execute \
  D=production,t=events

Dropping a Column

-- Warning: rebuilds table even though the data is being removed
ALTER TABLE logs
  DROP COLUMN debug_info,
  ALGORITHM = INPLACE, LOCK = NONE;

-- For huge tables, pt-online-schema-change is often faster
-- and causes less replication lag

Adding a Foreign Key

-- Dangerous: checks every row for referential integrity
-- On a large table, this can take hours
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id);

-- Safer: add without validation first, then validate
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

-- MySQL 8.0.16+ supports NOT VALID for foreign keys
-- Then validate in a separate statement:
ALTER TABLE orders
  ALTER CONSTRAINT fk_orders_user VALIDATE;

Tools for Safe MySQL Migrations

pt-online-schema-change (Percona Toolkit)

The industry standard for large-table migrations in MySQL. It creates a shadow table, applies the ALTER, sets up triggers to sync changes, copies rows in chunks, then swaps the tables:

pt-online-schema-change \
  --alter "ADD COLUMN region VARCHAR(10)" \
  --chunk-size 1000 \
  --max-load Threads_running=25 \
  --execute \
  D=production,t=customers

It is not perfect — it adds load to the primary, can cause replication lag, and requires careful monitoring — but it is far safer than a raw ALTER TABLE on a 500GB table.

gh-ost (GitHub Online Schema Transformer)

GitHub's alternative to pt-osc. It uses the binary log instead of triggers, which reduces load on the primary and avoids trigger-related bugs:

gh-ost \
  --database=production \
  --table=customers \
  --alter="ADD COLUMN region VARCHAR(10)" \
  --execute

Both tools are essential if you run MySQL at scale. If your table is under 1GB, a direct ALTER TABLE with ALGORITHM = INPLACE is usually fine. Above 10GB, use a migration tool.

Common MySQL ALTER TABLE Mistakes

MySQL 5.7 vs 8.0: What Changed

If you are still on MySQL 5.7, here is what you are missing:

The message is clear: if schema changes are part of your regular deploy flow, upgrade to MySQL 8.0. The time savings and safety improvements are substantial.

Quick Reference: Safe Migration Checklist

1. Check your MySQL version. 8.0+ unlocks instant DDL for many operations.
2. Always specify ALGORITHM = INPLACE, LOCK = NONE and let it fail safely if unsupported.
3. For tables over 10GB, use pt-online-schema-change or gh-ost.
4. Test the migration on a replica first and measure execution time.
5. Monitor replication lag during and after the migration.
6. Never add NOT NULL without a default on a large table. Use the expand/contract pattern.

Related Reading

• How to Generate ALTER TABLE Scripts Automatically
• The Complete SQL Migration Checklist for Production
• 10 Database Schema Changes That Break Production
• Zero-Downtime Migration Guide