重新配置默认的MySQL服务器以更有效地运行。

以下是我们在带有MySQL 5.5的Debian Wheezy服务器上使用的my.cnf文件的内容。
服务器有2GB的内存。

MySQL数据根/var/lib/mysql安装在单独的驱动器上。

# The MySQL 5.5 database server configuration file
# Server with 2GB of 内存 + 1GB swap
# By: www.theitroad.local
## Global total buffers (mysqltuner) include:
#   key_buffer_size
#   innodb_buffer_pool_size
#   innodb_additional_mem_pool_size
#   innodb_log_buffer_size
#   tmp_table_size
#   query_cache_size
## Per-thread buffers (mysqltuner) include:
#   read_buffer_size
#   read_rnd_buffer_size
#   sort_buffer_size
#   join_buffer_size
#   thread_stack
#   binlog_cache_size
[mysqld]
# *** Basic Settings ***
user                    = mysql
pid_file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc_messages_dir         = /usr/share/mysql
skip_external_locking
symbolic_links          = 0
bind_address            = 127.0.0.1
# *** Tuning ***
myisam_recover          = BACKUP
max_connections         = 100
wait_timeout            = 3600
interactive_timeout     = 3600
# nofile is set to 2048 for mysql user in limits.conf 
open_files_limit	= 2048
table_open_cache	= 1024
max_allowed_packet	= 16M
# Max size to which user-created MEMORY tables are permitted to grow
# Make these equal
max_heap_table_size     = 256M
tmp_table_size          = 256M
# *** Fine Tuning ***
key_buffer_size	 	= 128M
join_buffer_size 	= 2M
sort_buffer_size	= 2M
read_buffer_size 	= 2M
read_rnd_buffer_size	= 256K
thread_stack		= 256K
thread_cache_size       = 8
# *** Query Cache Configuration ***
query_cache_type	= 1
query_cache_limit	= 4M
query_cache_size        = 64M
# *** Logging ***
# Be aware that this log type is a performance killer
general_log_file        = /var/log/mysql/mysql.log
general_log             = 0
#log_slow_queries	= /var/log/mysql/mysql-slow.log
#long_query_time        = 2
#log_queries_not_using_indexes
# *** Replication and Binlogs ***
# Binlogs are not enabled
server_id		= 1
#log_bin		= /var/log/mysql/mysql-bin.log
expire_logs_days	= 7
max_binlog_size         = 1G
# *** InnoDB ***
innodb_file_per_table   	= 1
innodb_flush_method      	= fsync
innodb_flush_log_at_trx_commit 	= 1
innodb_log_file_size     	= 32M
innodb_buffer_pool_size 	= 128M
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_instances 	= 1
innodb_log_buffer_size		= 10M
innodb_lock_wait_timeout 	= 100
innodb_data_file_path    	= ibdata1:16M:autoextend:max:2048M
[mysqldump]
quick
quote_names
max_allowed_packet = 16M
[isamchk]
key_buffer	= 16M
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
!includedir /etc/mysql/conf.d/

Mysqltuner：

$mysqltuner
...
[--] Total buffers: 586.0M global + 6.5M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.2G (60% of installed 内存)
...