If you have a problem with SELECT NOW()
returning values in UTC and not your local time, you have to
tell the server your current time zone. The same applies if
UNIX_TIMESTAMP()
returns the
wrong value. This should be done for the environment in which
the server runs; for example, in
mysqld_safe or
mysql.server. See
Section 2.13, “Environment Variables”.
You can set the time zone for the server with the
--timezone=
option to mysqld_safe. You can also set it
by setting the timezone_name
TZ
environment variable
before you start mysqld.
The allowable values for
--timezone
or
TZ
are system-dependent. Consult your
operating system documentation to see what values are
acceptable.
User Comments
Let me try to help clarify with a howto:
First, set things up:
log in as root.
stop mysql (/etc/rc.d/init.d/mysql stop)
start mysql in ignore mode (/usr/bin/safe_mysqld
--skip-grant-tables &)
now, change the password for root user:
mysql -p
<enter root password? if requested, or don't use
the -p option>
use mysql;
update user set password=password('newpass');
quit
/etc/rc.d/init.d/mysql stop # Stop the insecure
server
/etc/rc.d/init.d/mysql start # restart it in
normal mode
Ok,now root's password is 'newpass'. Now, to
change password for user 'joe_user', do this as
root:
mysqladmin -u joe_user password newpass -p
(enter root password - newpass in this case)
Repeat for all users (bug please don't use
'newpass' as the password!).
I wish I had found a way to do this without having
to type the new password on the command line, but
I could not. Hopefully someone else will figure
it out and comment here...
Hope this saves someone the time *I* spent on
this!
rc
I just downloaded the Linux 3.23.49a RPMs and the
default location for the creation of mysql.sock is
NOT /tmp !! It's actually in /var/lib/mysql. This is
quite serious because not only doesn't this match
all the online docs and mysqld man page, it also
causes a problem if MySQL isn't running when you
configure PHP because the latter defaults to
/tmp/mysql.sock and if you then try to connect
locally to mysql via PHP, it'll fail.
I was having trouble getting MySQL to recognize my Unix/FreeBSD server's timezone. I kept reading on this site that I needed to set the TZ environmental variable.
After a couple of hours of frustration trying to find out how you define an environmental variable (which I'm no closer to solving than I was before all this started), I discovered that upon restart, MySQL seemed to be getting the timezone information from ~/etc/localtime on the server.
I overwrote that localtime file with the one for my timezone as provided by my servers' host, restarted MySQL, and had no problems; MySQL picked up my time zone perfectly.
Just for gits and shiggles, I tried a few other time zones (PST/PDT, MST/MDT, CST/CDT, EST/EDT, Fiji's, Kabul's, Baghdad's, Tokyo's, Honolulu's, UTC, GMT, etc). Just as what happened with my local time zone (which is CST/CDT), MySQL recognized and used the new time zone.
Hopefully this will help someone in a similar situation as mine in the future!
Thats smart Brent Butler I have the same problem I want to change the time zone of mysql (ver. 4.1.2), but when I opened the file /etc/localtime, it has following mess:
TZif?V^G?ðW%·?WçuðY^NÔ^C^B^C^B^C^B^C^B^C^B^C^B^C^B^C^B^C^B^C^B^C^B^C^Bÿÿ£^L
Please tell me what should I modify it to, to change it to the time zone of Pakistan, thank you.
Kamal Siddiqi--
I haven't the slightest clue what information is contained within these files, but on my particular server, they were all located in this directory:
~/usr/share/zoneinfo
Upon exploring that directory, I found files for all major timezones within the United States and a slew of others, including Karachi, Pakistan. I believe they are all a function of FreeBSD, but I'm honestly not sure.
Since I only barely have a grasp as to how all this functions, I would recommend "Googling" your way to your solution. To see some of these time zones in action, take a visit to http://www.brentbutler.com/timezones.html
On RedHat systems, the /etc/localtime file is normally a link to your time zone file in /usr/share/zoneinfo.
You should use dateconfig or redhat-config-time to change since other files may need to be updated (like /etc/sysconfig/time), but with regards to the localtime file, it's as simple as:
rm -f /etc/localtime
ln -s /usr/share/zoneinfo/America/New_York /etc/localtime
BTW, these are binary files, not text.
Note: The contents of the timezone file is not human-editable. It's a description to the computer how it should behave for timezones, with leap years Daylight Saving Time, etc etc.
One never edits those files. One does occasionally change the symlink to point to a different timezone-data file (or copy it, or hard-link it).
For the benefit of anyone wanting to set the timezone to UTC, on a Macintosh, you can go through the symbolic link method:
$ sudo bash
$ rm -f /etc/localtime
$ ln -s /usr/share/zoneinfo/UCT /etc/localtime
, or you can use the "System Preferences"/"Date & Time"/"Time Zone" panel, and set the 'Nearest City' to UTC.
Add your own comment.