How to adjust the value of Seconds_Behind_Master in MySQL replication.

Seconds_Behind_Master variable in MySQL replicate is usually used to determine how long the replicated database lagging from its master. You can get this value by using SHOW SLAVE STATUS command or by using MySQL Workbench this value is shown in TIME column of the SQL THREAD process.

You can see that sometime this value is non zero even the replica has done all SQL statements replicated from master. As described in MySQL document, this value is not accurate due to the calculation of this value is related to speed of the network, time stamp in binary log and the system clock.

If MySQL replication has been setup in high speed network environment, you can adjust the seconds_behind_master value by adjust system clock of master and slave to be equal.

For Linux system, adjust time by ntpdate command.

#ntpdate pool.ntp.org
14 Jul 09:03:53 ntpdate[28609]: step time server 176.31.45.67 offset 2.289779 sec

After updating system time in both master and slave machine, the value of Seconds_Behind_Master should be zero after slave done all SQL statement.