MySQL is 2 hours behind system time

Question

Have a FreeBSD box with MySQL server on it.

The system time is correct - For Denmark (Europe/Copenhagen) currently summertime.

# date
Fri Jun 17 12:09:56 CEST 2011

The MySQL server has timezone set to SYSTEM

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)

And the time is 2 hours behind

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2011-06-17 10:12:24 |
+---------------------+
1 row in set (0.00 sec)

How can I get this into sync?

Update

The box also uses NTP syncronization.

/etc/rc.conf

ntpd_enable="YES"
ntpd_sync_on_start="YES"

is this a virtual instance (ec2 or similar?) – Mike Jun 20 '11 at 12:41 — Mike, Jun 20 '11 at 12:41
No, it's a dedicated box running FreeBSD 8.2 as only OS – Phliplip Jun 20 '11 at 13:50 — Phliplip, Jun 20 '11 at 13:50

score 0 · Answer 1 · answered Jun 17 '11 at 10:31

0

It looks to me like MySQL is reporting UTC, and date is reporting your local time. Try:

date -u

Does that match MySQL?

answered Jun 17 '11 at 10:31

Flimzy

2,375
17
26

Yes it matches. – Phliplip Jun 20 '11 at 11:09

score 0 · Answer 2 · answered Jun 17 '11 at 10:34

0

(Re-) configure your TZ with

tzsetup

answered Jun 17 '11 at 10:34

Henk

1,321
11
24

Is this just to set the timezone on the system? Because then it's the same as copying timezone files to /etc/localtime `cp /usr/share/zoneinfo/Europe/Copenhagen /etc/localtime` – Phliplip Jun 20 '11 at 11:10
True, but from the man-page: "the tzsetup utility also determines whether any adjustment is necessary for systems where the hardware clock does not keep UTC": http://www.freebsd.org/cgi/man.cgi?query=tzsetup&apropos=0&sektion=0&manpath=FreeBSD+8.2-RELEASE&format=html – Henk Jun 20 '11 at 15:39
Has this anything to do with the difference in MySQL? – Phliplip Jun 20 '11 at 19:31
Well... "date -u" matches time in MySQL, so I think your CMOS clock is set to UTC. You might check the BIOS as well. – Henk Jun 21 '11 at 07:14

score 0 · Accepted Answer · edited Apr 13 '17 at 12:14

0

I found this to be the correct answer.

Add the following line to /etc/my.cnf in the [mysqld] section and restart:

default-time-zone='Europe/Copenhagen'

Now the MySQL time shows correct.

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2011-06-21 13:26:40 |
+---------------------+
1 row in set (0.00 sec)

edited Apr 13 '17 at 12:14

Community

1

answered Jun 21 '11 at 11:31

Phliplip

531
8
21

MySQL is 2 hours behind system time

3 Answers3