Change mysql server time zone

March 30, 2009 Databases No Comments

If you want mysql to have a different timezone than your server’s, here are the steps:

1) Run the following command to populate mysql’s timezone tables:

mysql_tzinfo_to_sql /usr/share/zoneinfo |mysql mysql

2) in the [mysqld] section of /etc/my.cnf, add the following line:

default-time-zone='US/Eastern'
(change US/Eastern with the desired timezone of course. It should match the format of /usr/share/zoneinfo)

3) Restart mysql

To check you mysql server’s time run the following query:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2009-03-30 16:21:20 |
+---------------------+
1 row in set (0.00 sec)

–JC

md5sum on MySQL results

March 25, 2009 Databases No Comments

This morning I read a twit about a blog post from xapbr.com (http://www.xaprb.com/blog/2009/03/25/mysql-command-line-tip-compare-result-sets/).

Pretty nifty tip on how to get a MD5 Checksum on a result set from a query. This can be very helpful when you are optimizing queries that return a very large set of results and you need to make sure they match.

mysql> pager md5sum -
PAGER set to 'md5sum -'
mysql> select * from test;
a09bc56ac9aa0cbcc659c3d566c2c7e4 -
4096 rows in set (0.00 sec)

–JC