kompakte MySQL my.cnf Vorlage für neue Server (MySQL-/MariaDB-/Percona-Server 5.5

Dieses Beispiel ist für kleine bis mittelgroße Setups geeignet. Die Vorlage kann einfach nach /etc/mysql/my.cnf kopiert werden. Sofern die Datenbankengine MyISAM nicht benötigt wird, kann diese über „skip-myisam“ im [mysqld]-Bereich entfernt werden.

Um die neuen Einstellungen zu aktiveren, müssen sehr wahrscheinlich einmal die InnoDB-Logfiles gelöscht werden (MySQL läßt sich sonst nicht starten). Diese werden dann beim nächsten Start der Datenbank neu angelegt. Je nach Größe kann das auch länger dauern, das Init-Script beendet sich dann gerne mit einem Fehler. Den aktuellen Status sieht man im MySQL-Logfile (bzw. /var/log/syslog). Die Logfiles werden wie folgt gelöscht:

service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

Bei Debian Wheezy: Anpassung der Systemlimits, diese Zeilen am Ende vor der letzten Zeile ergänzen:

/etc/security/limits.conf

*    soft    nofile    65535
*    hard    nofile    65535

Ab Debian Jessie, bzw. Distributionen mit Systemd, muss das Systemd unit file angepasst werden (danach „systemctl daemon-reload“ ausführen):

/lib/systemd/system/mysql.service

LimitNOFILE=infinity
LimitMEMLOCK=infinity

/etc/mysql/my.cnf

#
# The MySQL database server configuration file
# compact version from https://wiki.magenbrot.net/datenbanken/mysql/kompakte_mysql_my.cnf_vorlage_fuer_neue_server_5.5
#

[client]
port                            = 3306
socket                          = /var/run/mysqld/mysqld.sock
default_character_set           = utf8

[mysqld_safe]
socket                          = /var/run/mysqld/mysqld.sock
nice                            = 0

[mysqld]
# basic settings
user                            = mysql
pid_file                        = /var/run/mysqld/mysqld.pid
socket                          = /var/run/mysqld/mysqld.sock
bind_address                    = 127.0.0.1
port                            = 3306
basedir                         = /usr
datadir                         = /var/lib/mysql
tmpdir                          = /tmp
event_scheduler                 = 0
skip_external_locking
skip_name_resolve
#skip_myisam

# charset and collation
character_set_server            = utf8
collation_server                = utf8_general_ci

# os related
open_files_limit                = 8192                        # should be around 3x table_open_cache, also set limits.conf

# networking
max_connections                 = 250
max_allowed_packet              = 128M
connect_timeout                 = 10
wait_timeout                    = 180                         # could cause problems if too strict (default: 28800)
net_read_timeout                = 30
net_write_timeout               = 60
back_log                        = 50

# fine tuning
key_buffer_size                 = 16M
thread_stack                    = 256K
thread_cache_size               = 8
sort_buffer_size                = 2M
read_buffer_size                = 128K
read_rnd_buffer_size            = 256K
join_buffer_size                = 128K
query_cache_limit               = 1M
query_cache_size                = 16M
table_open_cache                = 256
tmp_table_size                  = 32M
max_heap_table_size             = 32M
bulk_insert_buffer_size         = 16M
auto_increment_increment        = 1
auto_increment_offset           = 1
concurrent_insert               = 2

# MyISAM options
myisam_recover_options          = FORCE,BACKUP
myisam_sort_buffer_size         = 8M

# logging
#general_log_file               = /var/log/mysql/mysql.log
#general_log                    = 1
log_error                       = /var/log/mysql/error.log
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/mysql-slow.log
long_query_time                 = 2
log_queries_not_using_indexes 	= 1

# replication settings
server_id                       = 1                           # set to 2 or higher in replication slaves
log_bin                         = mysql-bin
#binlog_format                  = statement
binlog_format                   = mixed
log_slave_updates               = false
expire_logs_days                = 3
max_binlog_size                 = 100M
binlog_cache_size               = 32K
sync_binlog                     = 1
#relay_log                      = mysqld-relay-bin            # enable on replication slaves
#read_only                      = true                        # only on replication slaves

# InnoDB options
innodb_data_home_dir            = /var/lib/mysql
innodb_log_group_home_dir       = /var/lib/mysql
innodb_table_locks              = true
innodb_lock_wait_timeout        = 60
innodb_thread_concurrency       = 9
innodb_commit_concurrency       = 0
innodb_support_xa               = true
innodb_buffer_pool_size         = 2G
innodb_buffer_pool_instances    = 2
innodb_log_file_size            = 30M
innodb_additional_mem_pool_size = 128M
innodb_data_file_path           = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit  = 1
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 8M
innodb_open_files               = 8192
innodb_file_per_table

[mysqldump]
quick
quote_names
max_allowed_packet              = 128M

[mysql]

[isamchk]
key_buffer_size                 = 16M
myisam_sort_buffer_size         = 16M
myisam_max_sort_file_size       = 2147483648
myisam_repair_threads           = 1
myisam_recover_options          = FORCE,BACKUP

# include *.cnf files, overwriting settings from here
!includedir /etc/mysql/conf.d/