How do you change the schema of a large MySQL table without downtime?

Summary

Check whether the change can run with ALGORITHM=INSTANT or INPLACE (online DDL) in MySQL 8; otherwise use an online schema-change tool (gh-ost or pt-online-schema-change) that copies the table in the background and swaps it in. Always test on a copy and watch replication lag.

Longer Answer

• INSTANT (8.0.12+, extended in 8.0.29): adding a column (also in the middle since 8.0.29), dropping a column, renaming a column or changing a default only changes metadata, so it is immediate.
• INPLACE: adding a secondary index, many other changes; concurrent DML is allowed, but the operation still takes brief metadata locks and replicas apply it serially (lag).
• COPY: rebuilds the table and blocks writes; changing a column's data type usually needs it.

Specify ALGORITHM=INSTANT or INPLACE, LOCK=NONE explicitly so MySQL errors instead of silently falling back to a blocking copy. Beware metadata locks: a long-running query on the table makes the ALTER wait, and everything queued behind the ALTER waits too, which can take the application down. Set lock_wait_timeout low for DDL.

Key Points

• INSTANT for metadata-only changes.
• INPLACE allows concurrent DML.
• COPY blocks writes; use gh-ost/pt-osc.
• Watch metadata locks and replica lag.