Schema Migrations in MySQL

How to run MySQL schema migrations safely: ALTER TABLE behavior, online DDL, large table migrations, and how to use pt-online-schema-change or gh-ost.

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →

Schema changes on a live MySQL database range from trivial (adding a nullable column to a small table) to dangerous (modifying a column on a 500GB table that handles 50K writes per second). The difference is whether and how long MySQL locks the table.

How ALTER TABLE Works

MySQL’s ALTER TABLE has two modes depending on the operation and version:

Rebuilding DDL (the old behavior): MySQL creates a new table with the new schema, copies all rows, swaps the tables, and drops the original. The table is locked for writes for the entire duration — minutes to hours on large tables.

Online DDL: Many operations can run without blocking reads or writes. MySQL performs the change in-place or rebuilds the table in the background while allowing concurrent DML. To probe what a specific operation will do, add ALGORITHM and LOCK clauses explicitly — MySQL will reject the statement if it can’t satisfy the requested constraints:

-- Fail fast if the operation would require any locking
ALTER TABLE orders ADD COLUMN notes TEXT, LOCK=NONE;

Online DDL Lock Modes

The ALGORITHM and LOCK clauses control how MySQL executes the change:

ALGORITHM	Behavior
INSTANT	Changes only metadata — no table rebuild, no row reads. Available in MySQL 8.0 for some operations.
INPLACE	Modifies the table in-place with minimal locking. Rows may not be rebuilt.
COPY	Creates a new table, copies all rows. Slowest; required for some operations.

LOCK	Behavior
NONE	No lock — reads and writes proceed normally during the change
SHARED	Read lock — reads proceed, writes block
EXCLUSIVE	Full lock — reads and writes block

-- Request a specific algorithm; fails if MySQL can't do it that way
ALTER TABLE orders
    ADD COLUMN notes TEXT,
    ALGORITHM=INSTANT;

-- Fail if the operation would require a write lock
ALTER TABLE orders
    ADD COLUMN notes TEXT,
    LOCK=NONE;

What’s Fast vs. What’s Slow

Operations that use ALGORITHM=INSTANT (MySQL 8.0) are nearly instantaneous regardless of table size:

• Adding a nullable column at the end of the table
• Adding/dropping a virtual generated column
• Changing a column’s default value

Operations that require ALGORITHM=COPY block writes for the full duration:

• Changing a column’s data type
• Converting character sets
• Adding a full-text index on some versions

Most index additions use ALGORITHM=INPLACE, LOCK=NONE — the table rebuild happens in the background and DML proceeds. Adding an index on a 100GB table takes time but doesn’t block your application.

Large Table Migrations in Production

For operations that require ALGORITHM=COPY on tables too large to lock, use an external tool:

pt-online-schema-change (Percona Toolkit)

pt-online-schema-change \
  --alter "ADD COLUMN notes TEXT" \
  --execute \
  D=mydb,t=orders

pt-osc creates a shadow table, copies rows in batches, uses triggers to replicate ongoing writes, then swaps the tables. No application downtime.

gh-ost (GitHub)

gh-ost \
  --user=root --host=localhost \
  --database=mydb --table=orders \
  --alter="ADD COLUMN notes TEXT" \
  --execute

gh-ost uses the binary log instead of triggers, which is more reliable under high write load. It’s the tool of choice for high-traffic tables.

Both tools require the table to have a primary key.

Migration Tools and Version Control

Track schema changes in version-controlled migration files. Common tools:

Tool	Language	Style
Flyway	Java/any	SQL-based; versioned files
Liquibase	Java/any	XML/YAML/SQL; change sets
golang-migrate	Go	SQL-based; numbered files
Alembic	Python	Python or SQL; auto-generates from models

Each tool maintains a migrations table in the database to track which migrations have run, so reruns are safe.

Frequently Asked Questions

Can I add a column without locking the table?

Adding a nullable column at the end uses ALGORITHM=INSTANT and completes in milliseconds regardless of table size. For other column positions, use ALGORITHM=INPLACE, LOCK=NONE. If neither works, use pt-osc or gh-ost.

How do I check if an ALTER TABLE will lock my table?

Add ALGORITHM=INPLACE, LOCK=NONE to the statement. MySQL will execute it without locking if it can, or return an error if it can’t — either way you get a definitive answer without guessing:

-- If this succeeds, the index add won't lock the table
ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;

Test on a copy of the table first when you’re unsure about large tables in production.

Should I run migrations inside a transaction?

DDL statements (ALTER TABLE, CREATE INDEX, DROP TABLE) cause an implicit commit in MySQL — they can’t be rolled back. Running them inside a BEGIN/COMMIT block doesn’t protect you. Plan your rollback strategy at the application level (dual-write, feature flags) rather than relying on transaction rollback.

Troubleshooting

Problem	Solution
ALTER TABLE blocking production traffic	Use ALGORITHM=INSTANT if available, or pt-osc/gh-ost for large tables
ERROR 1846: ALGORITHM=INSTANT is not supported	Operation requires a table rebuild — use ALGORITHM=INPLACE or an online tool
pt-osc failing with trigger errors	Table already has triggers — use gh-ost (binary log based, no triggers)
Migration ran twice	Migration tool’s tracking table out of sync — check the schema version table
Long-running ALTER holding lock	Kill it with KILL <query_id> and use an online tool instead

See also

• MySQL Backup Strategies — always back up before a migration
• Transactions in MySQL — which migration operations are transactional in MySQL
• Table Partitioning in MySQL — partitioning changes are schema migrations too