Sample MySQL configuration with strict defaults

my.cnf

[client]
socket = /opt/local/var/run/mysql5/mysqld.sock

default-character-set = utf8

# XXX: how can the connection collation be set to utf8_unicode_ci?

[mysqld]
port = 3306
socket = /opt/local/var/run/mysql5/mysqld.sock

default-character-set = utf8
default-collation = utf8_unicode_ci
default-storage-engine = InnoDB

# NOTE: make sure sql-mode is disabled when running the command below
#
# see: http://dev.mysql.com/doc/mysql/en/mysql-tzinfo-to-sql.html
# run: mysql_tzinfo_to_sql5 /usr/share/zoneinfo | mysql -u root mysql
default-time-zone = UTC

# removed NO_AUTO_VALUE_ON_ZERO because of MySQL bug http://bugs.mysql.com/bug.php?id=42270
sql-mode = ANSI,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_UNSIGNED_SUBTRACTION,TRADITIONAL

key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 192K

server-id = 1

skip-locking
skip-networking

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates # do not turn this on, it silently adds a LIMIT 1000 to all SELECT clauses

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout