Mysql different time on different servers

Question

I have 2 server on which they have different time, I'd like to know how to make they both have the same time. Here's my time difference:

SELECT @@global.time_zone, @@session.time_zone; -- this is my correct time
SELECT now()

it returns the following:

    SELECT @@global.time_zone, @@session.time_zone;
    SELECT now();

+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set

+---------------------+
| now()               |
+---------------------+
| 2015-12-15 07:04:15 |
+---------------------+
1 row in set

and on the other server i got this (incorrect time or different to the other):

    SELECT @@global.time_zone, @@session.time_zone;
SELECT now();
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set

+---------------------+
| now()               |
+---------------------+
| 2015-12-15 13:02:05 |
+---------------------+
1 row in set

Is that possible, considering that correct server is physically on Peru and the other is on Spain?. I have Linux on both server and i want to know exactly what is suppose to modify on my config(if any). I also have access to my servers by ssh, but I'm interested to make it right on my second server.

You should have either an internal ntp server or configure one of those publicly available. But this is a operating system level setting. — Marged, Dec 15 '15 at 12:07
This won't be sufficient for configuring ntp. Are you talking about the _timezone_ (6 hours difference) or the actual _time_ (4 versus 2 minutes) ? — Marged, Dec 15 '15 at 12:17
Are those two servers in the same fisical location or in different countries? — Jorge Campos, Dec 15 '15 at 12:20
Then that is your problem which isn't a problem. Your dates are related to the operating system date time. You should set your mysql timezone to the one you need. @vasfed gave you an answer that explain better. — Jorge Campos, Dec 15 '15 at 12:28
This is a question for http://serverfault.com/ . You need to fix this promptly, I strongly suggest you set all your servers, globally, to UTC, and use the zoneinfo stuff (https://www.iana.org/time-zones) built in to MySQL (https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html) to handle local time, especially because you have both Northern and Southern Hemisphere daylight saving time to contend with. — O. Jones, Dec 15 '15 at 12:29

score 0 · Accepted Answer · edited May 23 '17 at 11:52

0

I dont think that clock may be runaway that much, so system timezone must be different on servers, plus there's about 2 minute difference, if it's not due to commands being run not at the same moment then one or both of the servers probably does not have ntpd running and correcting system clock.

If its only the system timezone - then change it, or if you can change only the mysql's one.

If you cannot control even that - set session timezone, anyway this does not change the timestamps stored, only how they are displayed and parsed.

See cheatsheet here: Should MySQL have its timezone set to UTC?

edited May 23 '17 at 11:52

Community

1
1

answered Dec 15 '15 at 12:26

Vasfed

18,013
10
47
53

Without ntp clock synchronization, server clocks can indeed drift by minutes. – O. Jones Dec 15 '15 at 12:30
Vasfed, there are some log sentences that i have to transfer from one server to another, so what I'd like to do is when there's an insert command with some now() function inside it, this match the same time, because on the correct server I will have a time and when i run the script on the other server I'll have actually another time and they both with no have coincidence. – user2570870 Dec 15 '15 at 12:32
Clocks can easily drift even more (once encountered about 15 minutes), but not 6 hours (assuming it was set correctly at some relatively recent time, not when dinosaurs roamed the planet) – Vasfed Dec 15 '15 at 12:32
@user2570870 then setting same system-wide timezone and ntp synchronization is a way to go – Vasfed Dec 15 '15 at 12:33
How do i do that and what do you mean by ntp (sorry for my ignorance) – user2570870 Dec 15 '15 at 12:36
`ntp` stands for 'network time protocol', and there's system service `ntpd` (in linuxes, other system have their substitutions) which synchronises system clock to a remote precision clock. When drift is detected it does that by making clock run faster or slower, so there's no interruption in timeline – Vasfed Dec 15 '15 at 12:41
Vasfed, then changing my clock server will solve my problem? because i just dont know how to make the change to have the other server on the correct time (as the first one) – user2570870 Dec 15 '15 at 12:51
There're two problems actually: different timezone and clock drift. To solve the former it's enough to change it on one of the servers, but to solve the second - servers must be both synchronized to precision time. Actually it's not that complicated - all modern distros have ntpd package, most of them even run it automatically on install with sensible defaults - all you have is to install (for example `sudo apt-get install ntpd` for ubuntu/debian). – Vasfed Dec 15 '15 at 12:57

Mysql different time on different servers

1 Answers1