Risk of changing clocktime on MySQL server host

Question

I have a MySQL server host that have a clock that is about 75 minutes wrong. Is there any risk in changing the hosts clock while MySQL is running?

Update: Obviously NOW() and other functions returning current time will return a different result. In my case I am thinking of spinlocks and other scary internals in MySQL.

• miracle173: what means 75 minutes wrong. your server shows the actual time + 75 minutes or actual time - 75 minutes?

Answer 1

Binary logs will record the events sequentially with whatever timestamp it has. Using any binary log around the time of a clock change makes the particular log unusable for PITR (Point-in-Time Recovery).

If the DB Server is a Slave, Replication may skip processing certain events.

If you have Binary Logging or Replication Enabled, I would do the following

• On DB1, stop writes to the database
• On DB2, run SHOW SLAVE STATUS\G multiple times to make sure there are no new commands being processed and Seconds_Behind_Master is 0
• On DB2, STOP SLAVE;
• On DB1, RESET MASTER; (Erases all binlogs)
• Stop MySQL Shutdown on DB1
• Stop MySQL Shutdown on DB2
• Set the Linux Time on DB1 and DB2 (Make sure times match)
• Start MySQL Shutdown on DB2
• Start MySQL Shutdown on DB1
• On DB1, RESET MASTER; (Erases all binlogs again)
• On DB2, CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=4;
• On DB2, START SLAVE;

The idea here is to make sure all binary logs and relay logs are time synchronized. The NOW() function could then be correctly interpreted from these logs.

CAVEAT

Be very careful about changing clock times when it may not be necessary when it comes to Daylight Saving Time. Since the clock time is written in binary logs in seconds since Jan 1, 1970, it may not be necessary. Since the clock is 75 minutes off, do this ASAP. Don't wait until DST kicks in.

• Ztyx: Thanks for this answer! I'm curious, why set MASTER_LOG_POS=4 and not MASTER_LOG_POS=1?