MySQL slave reporting incorrect values for Seconds_Behind_Master

configurationdatabase-replicationmaster-slaveMySQLtimezone

I have a MySQL slave that when it is 0 seconds behind the master, it correctly reports 0 Seconds_Behind_Master. However, if it gets even 1 seconds behind, it reports 14401 Seconds_Behind_Master (which is 4 hours and 1 second behind).

The unix date command produces the same time on both the master and the slave, and both machines are synced with ntp.

Issuing a SELECT NOW() on both machines produces the same time. Also, the time zones are the same on both:

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CDT    | 
| time_zone        | SYSTEM | 
+------------------+--------+
2 rows in set (0.00 sec)

This isn't a huge problem for me, but it makes my nagios graphs look weird, and prevents me from having sane monitoring on that machine to monitor slave lag. Anyone know why the slave would think it's 4 more hours behind than it really is?

Best Answer

The time difference between master and slave is computed when I/O thread starts and it assumed to never change while it runs. It you had a different timezone on one of the servers and it changed without restarting MySQL, then slave is adding the incorrect difference to the report. Restart servers.

Related Topic