Example InnoDB my.cnf file for high end GNU/Linux machines

#----------------------------------------------------------------
#
# my.cnf file
#
#
 
# See:
#
# http://dev.mysql.com/doc/refman/5.1/en/server-options.html
# http://dev.mysql.com/doc/refman/5.1/en/option-files.html
#
 
# You can also dump all the variables set for mysqld with:
#
#   mysqld --verbose --help
#
 
#----------------------------------------------------------------
 
[client]
 
# These options apply to all client applications
 
# Port and the socket
port                              = 3306
socket                            = /tmp/mysqld.sock
#password                          = my_password
 
 
# Default character set to utf-8
default_character_set             = utf8
 
 
#----------------------------------------------------------------
 
[safe_mysqld]
 
# Log file 
err_log                           = /home/poplar/mysql/logs/mysql.err
 
 
#----------------------------------------------------------------
 
[mysqld]
 
 
# Skip options
#skip_bdb
#skip_innodb
skip_locking
#skip_networking
 
 
 
# Server ID must be unique to allow for replication
server_id                         = 9000
 
# User name to run as
user                              = mysql
 
# Port and the socket
port                              = 3306
socket                            = /tmp/mysqld.sock
 
# Bind to a specific address, otherwise listen to all addresses
#bind_address                     = 127.0.0.1
 
 
 
# Maximum number of connections 
max_connections                   = 2048
 
# Connection backlog, raise this if we run out of
# connections (128 is the linux default)
back_log = 128
 
# Maximum number of connection error per host 
max_connect_errors                = 1000
 
# Connection timeout
connect_timeout                   = 2
 
# Timeout for inactive connections
wait_timeout                      = 60
 
# Maximum packet length, no single MySQL statement can be longer than this
max_allowed_packet                = 16M
 
# Network buffer length (I think this is the linux default)
net_buffer_length                 = 8K
 
 
 
# Set the default character set to utf8
default_character_set             = utf8
 
# Set the server character set
character_set_server              = utf8
 
# Set the default collation to utf8_general_ci
default_collation                 = utf8_general_ci
 
# Set the names to utf8 when a client connects
init_connect                      = 'SET NAMES utf8; SET sql_mode = STRICT_TRANS_TABLES'
 
 
 
# Server directories
basedir                           = /usr/local/mysql
datadir                           = /home/poplar/mysql/data
tmpdir                            = /home/poplar/mysql/tmp
 
# Language file location
language                          = /usr/local/mysql/share/english
 
# Error log file (need dash in variable name)
log-error                         = /home/poplar/mysql/logs/mysqld.err
 
# Process ID file (need dash in variable name)
pid-file                          = /var/run/mysqld/mysqld.pid
 
# Log slow queries, time threshold set by 'long_query_time', 
log_slow_queries                  = /home/poplar/mysql/logs/slow-queries.log
log_output                        = FILE					# 5.1 only
long_query_time                   = 5
 
# Log queries which don't use indices in the slow query log
log_long_format
 
# Enable this to get a log of all the statements coming from a client,
# this should be used for debugging only as it generates a lot of stuff
# very quickly
#log                               = /home/poplar/mysql/logs/queries.log
 
# Binary log and replication log file names prefix
log_bin                           = /home/poplar/mysql/binary-logs/server1-bin
relay_log                         = /home/poplar/mysql/binary-logs/server1-relay-bin
 
# Binary log format, see:
# http://dev.mysql.com/doc/refman/5.1/en/replication-row-based.html
# http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html
binlog_format                     = row					# 5.1 only
 
# Binary log cache size
binlog_cache_size                 = 1M
 
 
 
# Skip automatic replication start up, replication will have to be 
# started manually with 'start slave' once the server is started
skip_slave_start
 
# Make the slave read-only
#read_only
 
# Select which replication errors to skip, see
# http://dev.mysql.com/doc/refman/5.1/en/replication-options.html
#slave_skip_errors                = 1062
 
# Select which databases/tables to ignore during replication, see
# http://dev.mysql.com/doc/refman/5.1/en/replication-options.html
#replicate_ignore_db              =
#replicate_ignore_table           =
#replicate_do_db                  =
#replicate_do_table               =
 
 
 
# Number of open tables at any one time
table_cache                       = 4096
 
# Join buffer size for index-less joins
join_buffer_size                  = 8M
 
# Maximum size for in memory temporary tables, anything
# larger gets spun out to disc
tmp_table_size                    = 64M
 
# Sort buffer size for ORDER BY and GROUP BY queries, data
# gets spun out to disc if it does not fit
sort_buffer_size                  = 8M
 
 
 
# Thread cache size, concurrency and stack
thread_cache_size                 = 64
thread_concurrency                = 8
thread_stack                      = 192K
 
 
 
# Query cache, disabled for now
query_cache_size                  = 0
query_cache_type                  = 1
query_cache_limit                 = 2M
 
 
 
# Default table storage engine when creating new tables 
# (comment out when running mysql_install_db)
default_storage_engine            = InnoDB
 
 
# Default transaction isolation level, levels available are:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# see: http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html
transaction_isolation             = REPEATABLE-READ
 
 
 
# MyISAM options, see:
# http://dev.mysql.com/doc/refman/5.1/en/myisam-start.html
key_buffer_size                   = 256M
read_buffer_size                  = 2M
read_rnd_buffer_size              = 8M
myisam_sort_buffer_size           = 128M
bulk_insert_buffer_size           = 64M
myisam_max_sort_file_size         = 10G
myisam_repair_threads             = 2
#myisam_recover_options           = DEFAULT
 
 
 
# InnoDB options, see:
# http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html
 
 
# Data directory, and data file
innodb_data_home_dir              = /home/poplar/mysql/data
innodb_data_file_path             = ibdata1:10M:autoextend
 
# Use one file per table
innodb_file_per_table
 
# Buffer pool size
innodb_buffer_pool_size           = 2G			# 4GB RAM
#innodb_buffer_pool_size           = 10G		# 16GB RAM
innodb_additional_mem_pool_size   = 32M
 
# Transaction log location and sizes
innodb_log_group_home_dir         = /home/poplar/mysql/innodb-logs
innodb_log_files_in_group         = 4
innodb_log_file_size             = 128M			# 4GB RAM
#innodb_log_file_size              = 512M		# 16GB RAM
innodb_log_buffer_size            = 8M
 
# Percentage of unwritten dirty pages not to exceed
innodb_max_dirty_pages_pct        = 80
 
# Transaction commit policy
innodb_flush_log_at_trx_commit    = 1
 
# Timeout to wait for a lock before rolling back a transaction
innodb_lock_wait_timeout          = 50
 
# Flush method
innodb_flush_method               = O_DIRECT
 
# Number of concurrent threads to run 
innodb_thread_concurrency         = 32
 
# Autoinc lock mode ('consecutive' lock mode), see:
# http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
innodb_autoinc_lock_mode = 1
 
# Prevent extra locking, we can only use this if we have row 
# level replication, see 'binlog_format'
innodb_locks_unsafe_for_binlog					# 5.1 only
 
# Enable fast innodb shutdown (skip full purge and insert buffer merge)
innodb_fast_shutdown              = 1
#innodb_fast_shutdown              = 0
 
# Dont delay insert, update and delete operations when purge 
# operations are lagging
innodb_max_purge_lag              = 0
#innodb_max_purge_lag              = 1
 
# Force recovery mode, do not mess with unless you really know
# what you are doing, see:
# http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
#innodb_force_recovery             = 4
 
 
#----------------------------------------------------------------
 
[mysqldump]
 
# Enable quick dumping
quick
 
# Set the max allowed packet size
max_allowed_packet                = 16M
 
 
#----------------------------------------------------------------
 
[mysql]
 
# Disable tab completion in mysql
#no_auto_rehash
 
 
#----------------------------------------------------------------
 
[myisamchk]
 
# Set the buffer sizes used by myisamchk when checking/rebuilding
# databases
key_buffer                        = 256M
sort_buffer                       = 256M
read_buffer                       = 64M
write_buffer                      = 64M
 
 
#----------------------------------------------------------------
 
[mysqlhotcopy]
 
# No clue
interactive_timeout
 
 
#----------------------------------------------------------------
 
 

Current Tags

You must be logged in to tag this tool