Any risks in updating the system time on a mysql 5.5 server?

Question

We just discovered one of our mysql systems has not been running ntp, with the result that the time has drifted a bit.

If I enable ntp, and synchronize the time while the system is running are there any possible problems? The master is currently about 4 minutes slow, and a slave is about 3:45 behind.

Answer 1

With the changing of the clock times on the Master

1. there might be loss of data for the slave
2. a slave may replay a query it already processed

Rather than risk either of these extremes, you should schedule a brief downtime.

Here is what you should do:

STEP 01: Stop all writes to the Master

SET GLOBAL read_only = 1;
FLUSH TABLES WITH READ LOCK;

STEP 02: Make sure are writes have stopped on the Master

SHOW MASTER STATUS;

You may see something like this

mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.005956 | 59702913 |              | dba              |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

Run this several times and make sure nothing is running

STEP 03: Set the clock time on Master (and Slave if needed)

STEP 04: On the Slave, run this several times

SHOW SLAVE STATUS\G

Make sure

• Seconds_Behind_Master is 0
• Read_Master_Log_File is not changing
• Relay_Master_Log_File is not changing
• Exec_Master_Log_Pos is not changing

STEP 04 : Run the following on the Slave

STOP SLAVE;
CHANGE MASTER TO master_log_file='bin-log.000001',master_log_pos=4;

STEP 05 : On the Master, run

RESET MASTER;

This will erase all your binlogs on the Master and start with a new one

STEP 06: On the Slave,

START SLAVE; DO SLEEP(5); SHOW SLAVE STATUS\G

Make sure Seconds_Behind_Master is 0

STEP 07: On the Master,

UNLOCK TABLES;

That's it.

It's better to do this that to just flip the clock on a live system

I have suggested this about 1.5 years ago (Risk of changing clocktime on MySQL server host)