Designing an Online DDL Work Order System for MySQL

The article, authored by a senior DBA, introduces the challenges faced by MySQL DBAs when handling DDL operations, especially on large tables, and explains why such operations are often painful.

It reviews MySQL's DDL mechanisms (copy vs inplace, online DDL introduced in 5.6) and highlights the limitations of online DDL, including cases where operations are not metadata‑only (NOMM) and the impact on latency, locks, and I/O.

The author compares two popular online schema‑change tools, gh‑ost and pt‑osc, enumerating their drawbacks such as trigger‑related deadlocks, network sensitivity, binlog requirements, and inability to avoid data copying for large tables.

Based on this analysis, a detailed design for an automated “online DDL work‑order” system is presented. The workflow includes SQL formatting, two‑stage parsing (pre‑validation and OMM detection), rule‑based DDL auditing, dynamic tool selection, monitoring of MDL, connections, and disk usage, atomic execution, API‑driven control (pause, resume, cancel), and post‑operation cleanup.

The article also lists practical implementation questions, such as handling comments during formatting, detecting OMM‑compatible changes, supporting MySQL 8.0 instant‑add‑column, keyword handling, and building a robust SQL parser.

Finally, the author provides links to the open‑source project on Gitee and encourages readers to explore the code and contribute.