Investigating Sudden MySQL Replication Lag Caused by Time Synchronization Issues

As a DBA, you may have dealt with master‑slave replication lag; this article shares a recent production case where the lag exhibited a distinctive, abrupt jump pattern.

The monitoring graph shows two key characteristics: the lag curve rises and falls sharply like a step function, and the peak value remains constant.

The lag metric is obtained from Seconds_Behind_Master . To verify the pattern, the author collected this value every second for five minutes using the following command:

# Observe the curve; capture 300 samples over 5 minutes
for i in {1..300}
do
    echo `date` `mysql -S /data/mysql/3306/data/mysqld.sock -uxxx -pxxx -e 'show slave status\G' | grep Second` >> /tmp/second.log
    sleep 1
done

Examining /tmp/second.log confirms that Seconds_Behind_Master repeatedly jumps between 0 and 71 seconds.

Investigation revealed that the slave’s system clock differed from the master’s by about 71 seconds, matching the maximum observed lag value.

According to MySQL documentation, the IO thread subtracts the time difference when calculating Seconds_Behind_Master , but this subtraction assumes the time offset remains unchanged after the IO thread starts.

Therefore, a likely cause of the sudden lag jumps is that the slave’s clock was corrected (e.g., via NTP) after the IO thread had already started, leading to mis‑calculation.

To resolve the issue, the simplest fix is to restart the slave’s IO thread so it recomputes the time offset. However, the optimal solution is to first synchronize the clocks of all servers in the cluster, then restart the IO thread. Before adjusting server time, consider:

Whether the application relies on system‑time functions; changing the server clock may affect in‑flight transactions.

The direction of the correction (forward vs. backward) and its impact on time‑dependent fields such as create_time and update_time .

If the offset is large, perform gradual adjustments rather than a single jump to minimize business impact.

For time‑sensitive business logic, temporarily stop application connections or set the database to read‑only while correcting time and restarting the IO thread.

Reference: MySQL 5.7 – SHOW SLAVE STATUS