Perform an InnoDB Online DDL to Add a Column Without Blocking Writes in MySQL 8.0

Desired outcome

Add a new column to an existing InnoDB table while the table continues to accept INSERT, UPDATE, and DELETE statements, minimizing downtime for applications.

Prerequisites

• MySQL Server version 5.6 or later (the steps assume MySQL 8.0).
• The table uses the InnoDB storage engine.
• Sufficient free disk space for the online DDL log and undo logs.
• MySQL user with either the SUPER privilege or the ALTER privilege on the target table.
• Access to the server’s error log and, if using replication, to the replica’s SHOW SLAVE STATUS output.

Procedure

1. Check the current online DDL log size limit. Run this query on the primary server (requires SELECT on performance_schema or access to SHOW VARIABLES):

   SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size';

   The value is in bytes; a typical default is 64 MiB (67108864). Note it for later adjustment if needed.
2. Determine the change you want to make. For this guide we add an INT column named status_code after an existing column created_at in table orders in database sales.
3. Run the ALTER TABLE statement with online DDL options. Execute with a user that has ALTER (or SUPER) privilege:

   ALTER TABLE sales.orders
     ADD COLUMN status_code INT AFTER created_at,
     ALGORITHM=INPLACE,
     LOCK=NONE;

   The ALGORITHM=INPLACE tells InnoDB to use the online DDL mechanism; LOCK=NONE requests no table lock (if the operation cannot meet this, MySQL will upgrade the lock automatically and report it).
4. Monitor the operation while it runs. You can watch for increasing lock wait times or log usage:
   • Using Performance Schema (requires SELECT on performance_schema):

     SELECT EVENT_NAME, TIMER_WAIT
     FROM performance_schema.events_statements_current
     WHERE SQL_TEXT LIKE 'ALTER TABLE sales.orders%';

   • Or inspect InnoDB metrics:

     SELECT NAME, COUNT
     FROM information_schema.INNODB_METRICS
     WHERE NAME LIKE 'online_ddl%';

     Watch the online_ddl_log_wait_time metric; if it approaches the limit set by innodb_online_alter_log_max_size, the operation may fall back to a copying algorithm.
5. If the log size is insufficient, adjust it temporarily. You can increase the limit without restarting the server:

   SET GLOBAL innodb_online_alter_log_max_size = 134217728; -- 128 MiB

   Then retry the ALTER TABLE. Remember that this change affects all concurrent online DDL operations.

Expected checks

1. Verify the column was added:

   SHOW CREATE TABLE sales.orders\G

   Look for the line defining status_code INT in the output.
2. Check the MySQL error log for any online DDL warnings or failures (replace /var/log/mysql/error.log with your actual path):

   grep -i 'online dd' /var/log/mysql/error.log

   No recent failures should appear.
3. If you use replication, confirm the replica is not lagging excessively:

   SHOW SLAVE STATUS\G

   Ensure Seconds_Behind_Master stays low during and after the DDL; a temporary spike is normal but should return to zero.

Recovery options

If the operation rolls back or falls back to a copying algorithm:

• Increase innodb_online_alter_log_max_size (as shown above) and retry the same ALTER TABLE … ALGORITHM=INPLACE, LOCK=NONE statement.
• If the table is very large and the log limit cannot be raised enough, you can explicitly request a copy‑based algorithm as a fallback:

  ALTER TABLE sales.orders
    ADD COLUMN status_code INT AFTER created_at,
    ALGORITHM=COPY,
    LOCK=NONE;

  This will block writes only for the short period needed to acquire the metadata lock, then proceed with a table copy.
• Always ensure you have a recent logical or physical backup before attempting schema changes, especially on production systems.

Limitations and practical verification

• Online DDL requires adequate undo log space; if the server runs out of undo tablespace, the operation will fail with an error like Undo tablespace is full. Monitor undo usage via SHOW ENGINE INNODB STATUS\G under the TRANSACTIONS section.
• On replicas, the applier thread must process the online DDL log; heavily delayed replicas may see a temporary increase in Seconds_Behind_Master while the log is applied. Verify replication continuity after the change as described in the checks.
• The LOCK=NONE request is a best‑effort hint; if the alteration truly requires a table rebuild (e.g., changing a column’s data type to a larger size), MySQL will automatically upgrade the lock and you will see a message in the error log indicating a fallback to ALGORITHM=INPLACE, LOCK=SHARED or LOCK=EXCLUSIVE.