MySQL parameters & configuration sample file

The following is a sample my.cnf configuration file. It includes some common and uncommon parameters, along with occasional comments.

You can use this file, and tune as appropriate for your machine, operating system, and application needs.

In particular, note the high values set for the InnoDB related parameters, below, and change them to match your system and application’s requirements. You have been warned.

[client]
#password = [your_password]
port = 3306
# The following location is not the default location for many
# pre-packaged utilities. For example, mytop or mysqlreport
# (or any Perl or Python based client, for that matter), will
# NOT look in the /tmp path for the mysql.sock file.
# The value is set to common location by your RPM or DEB
# package on GNU/Linux, yet defaults to the following in the
# .tgz mysql-server downlaod files.
socket = /tmp/mysql.sock

default-character-set=utf8

[mysqld]

# For a new installation, best to use a strict sql_mode:

sql_mode=TRADITIONAL
datadir= [path to mysql data]
# I prefer working case-sensitive. However, some 3rd party tools
# demand case insensitivity. If you’re working with such a tool,
# uncomment the following line.
#lower_case_table_names=1

# 5-10 minutes timeout is usually more than required for idle
# connections. If a connection can be idle for so long, then
# creating a new connection is usually very cheap. This applies
# for many common web based applications. However, depending
# on your application, you may wish to set this parameter to a
# higher value.
wait_timeout=300

# The following is unfortunately still used on many instances. If
# you need backwards compatability, you may need to uncomment
# the following line.
#old_passwords=1

# generic configuration options
port = 3306
# Make sure this is the same location as in the [client] section
socket = /tmp/mysql.sock

back_log = 50
max_connect_errors = 10

# Resources:
# The following values are liberal. You may reduce them
# according to your needs.
# NOTE: verify that your OS’s open files limit is at least as
# specified here.
# I usually set ‘ulimit -n’ to allow for 8192 files to mysql.
open_files_limit = 8000
# The following is usually cheap, so go ahead and set a high value.
table_cache = 2048
# Connections take memory, so don;t be too liberal here. A common,
# well tuned application, using proper connection pools, shouldn’t
# require more than 1000 concurrent connections.
max_connections = 500

# Memory:
# When required, the following parameters will be used to allocate
# more memory. The value specified is always the amount of memory
# allocated, regardless of the real need.
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
# The following value sets an upper limit only: MySQL will only
# allocate as much as required. Setting this parameters to a high
# value may be required if you’re handling very large statement,
# such as queries reading/writing BLOBs.
max_allowed_packet = 16M

# Even on a very busy server, a thread cache of 32 threads is
# usually enough to make for good thread recycling. I rarely go
# above this number.
thread_cache_size = 32
thread_concurrency = 8

# Query cache:
# Make sure you really need the query cache. I’ve seen many
# applications where the query cache almost utterly unused
# (due to frequent invalidation). Check for your hit:insert ratio.
# A query cache type of value “2” (DEMAND) is a more programmatic
# oriented setting, where most queries are not cached, but a few,
# recognized to benefit from the query cache, are executed with
# the SQL_CACHE flag.
query_cache_type = 1
query_cache_size = 32M
query_cache_limit = 2M

ft_min_word_len = 4
# I usually go with InnoDB, which is ACID compliant and crash-safe.
default_table_type = InnoDB

thread_stack = 192K

transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M

# Logs
# Having log-bin enabled allows for replication and for point in time
# recovery. It’s a good idea to always have binary logs at hand.
# binary logs can also be reviewed to see what kind of modifications
# are made on your database (the general query log also logs
# SELECTs and other queries, which do not appear in the binary log)
# I usually name the logs after my host name. When dealing with
# replication, having master and slave logs named after the machines
# on which they are running makes for clearer distinction between
# them.
log-bin=mymachine-bin
relay-log = mymachine-relay-bin
relay-log-index = mymachine-relay-bin.index
# Since a slave can suddenly be promoted to Master’s role, I always
# take care that it also has the binary logs prepared.
log_slave_updates
log-error=mymachine-mysql-error.log
log_slow_queries
long_query_time = 2
log-queries-not-using-indexes
# Automatically remove binary logs. Make sure this number is not too
# high and not too low for you, depending on your backups frequency
# and maximum slave lag.
expire_logs_days=7

# Replication related settings:
# Unique server identification number between 1 and 2^32-1.
server-id = [a unique number]
# Setting the report_host allows for SHOW SLAVE HOSTS on the master
# to see which slaves are connected.
report_host=[machine’s ip]
# Limit the total size of the relay logs: set this value to a high enough
# value. When the slave can’t keep up with the master, the relay logs
# fill up. If no limit is set, all disk space can be consumed.
# If a value too low is set, the master’s binary logs cannot (shouldn’t)
# be purged, and so the master gets to have a disk space problem. So
# provide a high enough value, and monitor your disk space!
relay_log_space_limit=10G

# MyISAM:
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover = force,backup

concurrent_insert=2

# INNODB:

innodb_file_per_table
# Your machine’s memory capacity dictates many of the following
# values. ***Do NOT take these values as they are. Change them***
innodb_buffer_pool_size = 10G
innodb_additional_mem_pool_size = 16M
innodb_data_file_path = ibdata1:50M:autoextend
innodb_file_io_threads = 4
# Keep the following one commented, unless you need to recover
# from disaster.
#innodb_force_recovery=4
innodb_thread_concurrency = 8
# Set “1” for full ACID compliance. Set “2” for IO performance gain
# (will only flush transaction log to disk once per second, instead
# of at each commit). If you have battery backed disk cache ,set
# to “1” and enjoy both worlds.
innodb_flush_log_at_trx_commit = 1
# Keep the following one commented, unless you really have an
# emergency. Fast shutdown makes for slower start up time.
#innodb_fast_shutdown
innodb_log_buffer_size = 8M
# The default here is 5M, which is really too low for our modern
# machines.
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
# Consult The manual. The desired values for the following
# parameter change according to your OS type, OS version and
# hardware types. Benchmark after changing it!
#innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 64M

default-character-set=utf8