Understanding MySQL ALTER Operations and Online Schema Change (OSC) Tools

MySQL ALTER is a frequently used DDL operation, but its internal process—including table locking, intermediate table creation, data synchronization, and rename—can cause significant downtime, especially on large tables.

Basic workflow (single instance): create a temporary table with the same structure, apply the ALTER to it, lock the original table for reads only, copy data, then rename the temporary table to replace the original.

In a master‑slave environment, the master finishes the ALTER before the binlog is generated; the slave receives the binlog only after the ALTER completes, causing both master and slave to hang during the operation.

To avoid table locks, online schema‑change (OSC) tools are used. Common OSC tools include gh-ost , oak-online-alter-table , pt-online-schema-change (pt‑OSC), and Facebook's online schema change.

pt‑OSC usage example:

pt-online-schema-change -h xx.xx.xx.xx -uroot -P6707 -p lianjia \
  --alter='modify column test varchar(100)' \
  --execute D=lianjia_newhouse_service_customer,t=link_customer_bak

The pt‑OSC process consists of:

Creating a new intermediate table and applying the ALTER.

Creating triggers to sync INSERT/UPDATE/DELETE from the original table to the intermediate table.

Copying all data from the original table to the intermediate table.

Analyzing the new table.

Swapping the tables via RENAME .

Dropping the triggers.

MySQL 5.6+ also supports native online DDL with ALGORITHM=INPLACE or ALGORITHM=COPY and various LOCK options (NONE, SHARED, DEFAULT, EXCLUSIVE). In‑place operations allow concurrent DML for many changes (e.g., adding columns, modifying nullability), while copy‑based operations lock the table.

Limitations include the need for sufficient disk space for copy‑based ALTERs, potential replication lag in master‑slave setups, and the innodb_online_alter_log_max_size limit that can cause DB_ONLINE_LOG_TOO_BIG errors for large DML volumes.

Conclusion: Use native OSC when possible; resort to pt‑OSC for copy‑based operations on large tables, when foreign keys or triggers are present, or when finer control over speed and impact is needed. Always perform DDL during low‑traffic periods and back up data beforehand.