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

Diese Anleitung wird nicht weiter aktualisiert. Bitte wechselt auf die Versionen 5.7 oder 8.0

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

Sämtliche Einstellungen sind von Dir dann natürlich nochmals zu überprüfen, insbesondere Caches und Speicherverbrauch. Dabei kann dann auch das Tool mysqltuner.pl helfen. Mein Beispiel ist auf ein Hot-Dataset von 2GB InnoDB Daten ausgelegt.

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/