Running DDL on Live MySQL Tables: Copy, In‑Place, and Instant Add Column

Background

In MySQL interviews a classic question is how to execute DDL statements on tables that are actively serving traffic. Before MySQL 5.6 a DDL acquired a write lock that blocked all reads and writes, increasing risk in production.

Copy algorithm (pre‑5.6)

Only the copy algorithm existed before MySQL 5.6. The process creates a temporary table, copies all rows from the original table A to the temporary table, then swaps the tables. During the whole copy the original table is locked for writes because the I/O‑intensive copy would lose data if new writes arrived.

In‑place (Online) algorithm (MySQL 5.6+)

MySQL 5.6 introduced the in‑place algorithm, also called Online DDL, which minimizes the duration of the write lock. The workflow is:

Build a temporary file and scan all primary‑key pages of table A .

Generate a B+‑tree from those pages and store it in the temporary file.

Record every operation on A in a row‑log during the scan.

After the temporary file is ready, replay the logged operations onto it, producing a logical copy that matches A .

Replace the original data file of A with the new temporary file.

Because the row‑log captures changes, the table can still accept INSERT/UPDATE/DELETE while the DDL runs, which is why the method is called “online”.

Example ALTER statement: ALTER TABLE A ENGINE=InnoDB, ALGORITHM=INPLACE; The algorithm still requires an initial MDL write lock, but this lock is quickly downgraded to a read lock before the data copy begins, avoiding the long exclusive lock of the copy algorithm.

Instant Add Column (MySQL 8.0)

MySQL 8.0 adds the Instant Add Column feature, which modifies only the table’s metadata without rewriting any rows.

Typical command:

ALTER TABLE A ADD COLUMN new_column INT, ALGORITHM=INSTANT;

Key characteristics:

Only metadata is changed; no temporary table or data copy is created.

The new column is stored in the data dictionary with a default value and position.

Existing rows are read as‑is; the engine supplies the default value on‑the‑fly.

Only newly inserted or updated rows physically store the new column.

Limitations:

The column must be nullable or have a default value; NOT NULL without a default is not allowed.

Cannot be UNIQUE, PRIMARY KEY, or AUTO_INCREMENT.

Can only be added at the end of the table.

Requires InnoDB with MySQL 8.0.12+ data‑page format.

Master‑Slave switch for DDL

When neither copy nor in‑place (or instant) can be used—e.g., older versions or unsupported column changes—a practical approach is to perform the DDL on a standby replica, redirect client traffic to that replica, and finally apply the change to the original master.

Best practices

Choose the DDL method based on MySQL version and business requirements.

For MySQL 8+, prefer ALGORITHM=INSTANT when adding a column at the end of the table—it avoids locks and completes in milliseconds.

For MySQL 5.6/5.7, use Online DDL by specifying ALGORITHM=INPLACE to minimise lock time.

For older versions or unsupported operations, consider applying the change on a replica first (master‑slave switch) and then promoting it.