1

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"
Improve this question
2
  • is this a virtual instance (ec2 or similar?)
    – Mike
    Jun 20, 2011 at 12:41
  • No, it's a dedicated box running FreeBSD 8.2 as only OS
    – Phliplip
    Jun 20, 2011 at 13:50

3 Answers 3

Reset to default
0

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

date -u

Does that match MySQL?

Improve this answer
1
  • Yes it matches.
    – Phliplip
    Jun 20, 2011 at 11:09
0

(Re-) configure your TZ with

tzsetup
Improve this answer
4
  • 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, 2011 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": freebsd.org/cgi/…
    – Henk
    Jun 20, 2011 at 15:39
  • Has this anything to do with the difference in MySQL?
    – Phliplip
    Jun 20, 2011 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, 2011 at 7: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)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .