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"

Answer 1

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

date -u

Does that match MySQL?

Answer 2

(Re-) configure your TZ with

tzsetup

Answer 3

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)