MySQL cnf configuration script for the power user's dev developer machine

my.cnf

[client]
user=root
password=root
socket=/var/mysql/mysql.sock

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# server_id = .....
# socket = /tmp/mysql.sock
socket = /var/mysql/mysql.sock

character-set-server=utf8
default-storage-engine=InnoDB
bind-address = 0.0.0.0

wait_timeout=10800
net_read_timeout = 7200
net_write_timeout = 7200

## Security
# safe-user-create prevents autocreating users with the GRANT statement
# unless the user has the insert privilege on mysql.user
safe-user-create = 1

lower_case_table_names=2
# explicit_defaults_for_timestamp=TRUE
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

# By default, MyISAM tables will INSERT into deleted row space before
# appending to the end of the disk. In exchange for saving this trivial
# amount of space, once a row gets deleted, only one insert operation
# may occur at a time until holes are filled. Setting concurrent_insert
# to 2 stops this silly behavior, at the cost of wasting a bit of disk
# space, for a significant performance improvement in MyISAM tables.
# Drupal 7 shouldn't be using MyISAM.
concurrent_insert = 2

myisam_sort_buffer_size=64M
table_definition_cache=4096
table_open_cache=16384

thread_cache_size = 128

max_connections = 256
open_files_limit = 65535

key_buffer_size=64M
key_cache_block_size=4K

sort_buffer_size=3M
read_buffer_size=2M
read_rnd_buffer_size=64M
join_buffer_size=8M

bulk_insert_buffer_size=512M

# 1 = default on, 0 = no query_chache, 2 = use SQL_CACHE switch in SQL-statement
query_cache_type = 1

tmp_table_size=256M
max_heap_table_size=256M
query_cache_size=64M
query_cache_limit=6M

max_allowed_packet=1024M

# innodb_file_per_table
# innodb_file_format=barracuda
innodb_thread_concurrency=0
innodb_strict_mode=1
innodb_log_file_size=512M
innodb_log_buffer_size=4M
innodb_buffer_pool_size=4G
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 3600
# Reduce contention. Set to 4+ in MySQL 5.5+
innodb_buffer_pool_instances = 4
# innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_support_xa=0

# Logging
slow_query_log=1
slow_query_log_file=/tmp/mysql-slow.log
long_query_time=5
log_queries_not_using_indexes=1