Mysql – How to sync MySQL’s time with system time without thesql restart

configurationMySQLtimestamptimezone

On my machine I have a service to synchronise the SYSTEM time (including Time zone), and would like the DB will synchronize with the machine time once in a while.

When I change SYSTEM time, MySQL keep giving the old time when I use CURRENT_TIMESTAMP, It synchronize it only when I restart the MySQL service.

I would like to ask: is it possible, by update some system variable, or anything else, to set the DB time, to be equal to the SYSTEM time, or any other time, without a restart of the service?

I don't want to stop the DB at all. I think that maybe a sceduled task, which run once in a while, can be set, but what should it do?

To accept answer, it must pass the following test:

# change system time, date or timezone on the machine
select CURRENT_TIMESTAMP from dual; #--still show old time
call sync_time; #or some sync date + time + timezone commands... 
select CURRENT_TIMESTAMP from dual; #--will show updated time

I just need the sync_time procedure, that will cause the last select to show the machine date and local time.

@rathishDBA answer didn't helped at all, it only changed the MySQL variables, not sync the DB time. I tried it as the 'root' user and both first and last select gave me the same unsynced result.

For example see this picture:
screen shot 

System time = 2:07
CURRENT_TIMESTAMP = 11:37

Best Answer

This solution worked for me, I hope it will work for you too:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-10-26 15:13:16 |
+---------------------+
1 row in set (0.07 sec)

mysql> SET GLOBAL time_zone = 'SYSTEM';
Query OK, 0 rows affected (0.07 sec)

mysql> show global variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | SYSTEM              |
+------------------+---------------------+
2 rows in set (0.18 sec)

mysql>

You need SUPER privilege to set this configuration value on run time to avoid system restart.

Related Question