Zero-downtime schema change on a 1 TB MySQL table
- Context
- A cross-service integration needed new columns on a 1 TB, 500M+ row MySQL table. At that size, a table-rebuilding
ALTER TABLEmeans hours of heavy I/O, replica lag and a metadata-lock risk at the end. - Action
- Used Alibaba Cloud DMS lock-free schema change: a shadow table with the new columns, filled in batches and kept in sync from the binlog, then an atomic
RENAMEcutover that needs only a brief metadata lock. - Result
- Zero downtime and no maintenance window. Reads and writes continued throughout the copy, and the integration shipped on schedule.
- Stack
- MySQL · Alibaba Cloud DMS · binlog-based online schema change
Read the full write-up
A native ALTER TABLE that rebuilds a 1 TB, 500M+ row MySQL table runs for hours: heavy I/O, replicas that only start the change after the primary finishes, and an exclusive metadata lock at the end that a single long transaction can stall, blocking every query behind it. Instead I used Alibaba Cloud DMS lock-free schema change. DMS creates a shadow table with the new schema, copies the existing rows in batches, replays ongoing writes from the binlog, then performs an atomic RENAME cutover. Apart from short shared locks on each copy batch, the only blocking step is a brief metadata lock for the swap.
Reads and writes continued normally through the copy and catch-up. Zero downtime, no maintenance window, and the service integration shipped on schedule.