Tweaks to MySQL installation

After you install one of the MySQL packages available for the Mac, there are some steps that you should do.

First, make sure your MySQL installation knows about time zones. This is important if you want to run your MySQL in the UTC time zone.

To update the mysql database time zone tables, do:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Type the password at the prompt (hit enter if you don't have one yet).

And then make sure all your date/datetime fields use the Highlander-timezone. Edit my.cnf and add:

[mysqld]
default-time-zone=utc

Second, make sure your server is using UTF-8 everywhere. Add to my.cnf:

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8

Third, set mysql to strict SQL:

[mysqld]
sql-mode="TRADITIONAL,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

Finally, make sure you use InnoDB by default:

[mysqld]
default-storage-engine=InnoDB

There are probably more tweaks to make your MySQL saner. This are the one I feel comfortable recommending.