LinuxZone.Org

Linux Blog

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

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

This week I had a customer ask me if MySQL or the file system on his server had a 4GB file limitation because he was getting a ‘table full’ type error on one of his large tables. I found posts ab out it  everywhere on Google, including MySQL website (http://dev.mysql.com/doc/refman/5.0/en/full-table.html) but I am blogging it anyway.

Apparently it is just an easily fixable file pointer limitation with MySQL 4.x.

Here is the fix:

alter table_name MAX_ROWS = 10000000; (this can take a while)

To fix it for new tables, add this to you /etc/my.cnf:

myisam_data_pointer_size=6

This will allow tables to have up to a 256TB size limit. The default value is 4 which allows up to 4GB.

Check them out. They are awesome!
http://xkcd.com/327/

Here is how to set some global variables in mySQL on the fly without having to restart the service.

To increase max_connections from 100 to 250 for example, run

mysql> set global max_connections=250;

Check using

mysql> show global variables;

Make sure to add it to /etc/my.cnf if you want the change to persist across restarts.