Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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"
share|improve this question
    
is this a virtual instance (ec2 or similar?) –  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

3 Answers 3

up vote 0 down vote accepted

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)
share|improve this answer

(Re-) configure your TZ with

tzsetup
share|improve this answer
    
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": freebsd.org/cgi/… –  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 7:14

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

date -u

Does that match MySQL?

share|improve this answer
    
Yes it matches. –  Phliplip Jun 20 '11 at 11:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.