Why MySQL Replication Lags During Long ALTERs and MyISAM Locks—and How to Fix It

Recently the author encountered several master‑slave replication delay issues in MySQL and decided to document the findings.

Scenario 1: The primary server runs a long‑running ALTER statement that takes about ten minutes; the replica reproduces the same ALTER, also taking ten minutes, during which replication lag continuously grows.

Scenario 2: The primary performs heavy INSERT/UPDATE/DELETE on a MyISAM table. Because MyISAM uses table‑level locks, any SELECT on that table creates a lock that blocks the replica’s SQL thread, leading to lag.

Both cases share the same root cause: the problematic SQL statements execute slowly or become blocked on the replica, causing the replication process to fall behind. The usual remedy is to schedule such “problematic SQL” during off‑peak windows, following DBA best practices.

If the delay occurs during normal business hours, one can isolate the lagging replica, shield it from client traffic, allow it to catch up, then reintegrate it and repeat the process with other replicas until the primary is fully caught up.

Another type of lag arises when the replica stalls while writing to the binary log, often indicated by high system I/O wait. In this situation, adjust transaction group size and log‑flush policies according to the workload, or upgrade storage hardware to improve performance.