MySQL richtig konfigurieren

[Joe User]

Moin,

da viele User die MySQL-Dokumentation nicht lesen und sich über einen langsamem MySQLd wundern, stelle ich hier eine vollständige und bewährte Konfiguration (/etc/mysql/my.cnf) bereit. Diese ist für Server ab 2048MB RAM, auf denen neben MySQL 5.0/5.1 noch weitere Dienste, beispielsweise Web- und/oder Mailserver, betrieben werden, ausgelegt. Die Pfade sollten selbstverständlich an das jeweilige System angepasst werden und die Datenbanken in UTF-8 vorliegen. Ist Letzteres nicht möglich, weil es eine Client-Applikation kein UTF-8 ünterstützt, so ist utf8 jeweils durch latin1 zu ersetzen.

Code: Select all

[client]
character_sets_dir              = /usr/share/mysql/charsets
default_character_set           = utf8
port                            = 3306
socket                          = /var/run/mysqld/mysqld.sock

[mysql]
character_sets_dir              = /usr/share/mysql/charsets
default_character_set           = utf8
prompt                          = u@h [d]>_
no_auto_rehash

[mysqladmin]
character_sets_dir              = /usr/share/mysql/charsets
default_character_set           = utf8

[mysqlcheck]
character_sets_dir              = /usr/share/mysql/charsets
default_character_set           = utf8

[mysqldump]
character_sets_dir              = /usr/share/mysql/charsets
default_character_set           = utf8
max_allowed_packet              = 32M
quote_names
quick

[mysqlimport]
character_sets_dir              = /usr/share/mysql/charsets
default_character_set           = utf8

[mysqlshow]
character_sets_dir              = /usr/share/mysql/charsets
default_character_set           = utf8

[isamchk]
character_sets_dir              = /usr/share/mysql/charsets
key_buffer_size                 = 256M

[myisamchk]
character_sets_dir              = /usr/share/mysql/charsets
key_buffer_size                 = 256M

[myisampack]
character_sets_dir              = /usr/share/mysql/charsets

[mysqld_safe]
err_log                         = /var/log/mysql/mysql.err

[mysqld]
character_sets_dir              = /usr/share/mysql/charsets
character_set_server            = utf8
default_character_set           = utf8
user                            = mysql
port                            = 3306
bind_address                    = 127.0.0.1
socket                          = /var/run/mysqld/mysqld.sock
pid_file                        = /var/run/mysqld/mysqld.pid
log_error                       = /var/log/mysql/mysqld.err
log_slow_queries                = /var/log/mysql/slow-queries.log
basedir                         = /usr
datadir                         = /var/lib/mysql
tmpdir                          = /var/tmp
slave_load_tmpdir               = /var/tmp
language                        = /usr/share/mysql/english
log_bin                         = /var/lib/mysql/mysql-bin
relay-log                       = /var/lib/mysql/relay.log
relay-log-index                 = /var/lib/mysql/relay.index
relay-log-info-file             = /var/lib/mysql/relay.info
master-info-file                = /var/lib/mysql/master.info
#master_host                     = <hostname>
#master_user                     = <username>
#master_password                 = <password>
#master_port                     = 3306
#auto_increment_increment        = 10
#auto_increment_offset           = 1
server_id                       = 1
back_log                        = 50
sync_binlog                     = 0
binlog_cache_size               = 1M
max_binlog_size                 = 100M
expire_logs_days                = 7
slave_compressed_protocol       = 1
lower_case_table_names          = 1
safe_user_create                = 1
delay_key_write                 = ALL
myisam_recover                  = FORCE,BACKUP
myisam_recover_threads          = 1
key_buffer_size                 = 512M
record_buffer                   = 2M
join_buffer_size                = 2M
sort_buffer_size                = 2M
read_buffer_size                = 2M
read_rnd_buffer_size            = 8M
myisam_sort_buffer_size         = 64M
max_allowed_packet              = 32M
max_heap_table_size             = 64M
tmp_table_size                  = 64M
table_cache                     = 1024
query_cache_type                = 1
query_cache_size                = 128M
query_cache_limit               = 16M
thread_concurrency              = 8
thread_cache_size               = 16
max_connections                 = 100
ft_max_word_len                 = 20
ft_min_word_len                 = 3
long_query_time                 = 3
local_infile                    = 0
log_warnings                    = 2
log_queries_not_using_indexes
log_slave_updates
log_long_format
skip_locking
skip_name_resolve
skip_external_locking
skip_show_database
#skip_innodb
innodb_thread_concurrency       = 8
innodb_buffer_pool_size         = 512M
innodb_additional_mem_pool_size = 16M
innodb_data_home_dir            = /var/lib/mysql
innodb_log_arch_dir             = /var/lib/mysql
innodb_log_group_home_dir       = /var/lib/mysql
innodb_data_file_path           = ibdata1:2000M;ibdata2:10M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 100M
innodb_log_buffer_size          = 8M
innodb_log_files_in_group       = 2
innodb_flush_log_at_trx_commit  = 2
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 120
innodb_file_per_table

[mysqlhotcopy]
interactive_timeout


Für das weitere Tuning, sofern noch nötig, sind die Scripte http://mysqltuner.com/mysqltuner.pl und http://day32.com/MySQL/tuning-primer.sh sowie die offizielle MySQL-Dokumentation äusserst hilfreich.

Gruss,
Joe User


Letzte Aktualisierung: 30.03.2009

[captaincrunch]

Vielleicht sollte man sowas auch mal für ne echte DB (aka Postgres (SCNR)) anbieten. OutOfBound, wie wär's?

[Joe User]

Moin,

wer nicht nur eine optimierte, sondern auch sichere MySQL-Installation möchte, der sollte folgendes Script ausführen und alle Fragen (abgesehen vom MySQL-Root-Passwort) durch ein simples drücken der Return-Taste mit der Standardantwort bestätigen:

Code: Select all

/usr/bin/mysql_secure_installation

Gruss,
Joe User

[larsinho]

Danke für die conf! Eine Anmerkung allerdings: Bei mir hat das skip-name-resolve dazu geführt, dass sich kein User mehr zur DB connecten konnte, da der Zugang nur für 'localhost' erlaubt war. Also Vorsicht mit dem skip-name-resolve

[Joe User]

da der Zugang nur für 'localhost' erlaubt war.

Das ist so beabsichtigt, schliesslich soll der MySQLd nur von Admins, welche sehr genau wissen was sie machen, von Extern zugänglich gemacht werden, denn ein von Extern zugänglicher MySQLd stellt ein erhebliches Sicherheitsrisiko dar!

[burn]

Meine MySQL-Server sind auch alle an den localhost gebunden, weil ich dank PhpMyAdmin etc. nicht mehr als das brauche, aber da es immer wieder so dargestellt wird: was macht einen nach außen offenen MySQL-Server unsicherer als andere offene Server?

[Roger Wilco]

was macht einen nach außen offenen MySQL-Server unsicherer als andere offene Server?

Grundsätzlich nichts. Es gab in der (jüngeren) Vergangenheit allerdings einige ausnutzbare Sicherheitslücken, die es dem Angreifer erlaubt haben, Befehle im Kontext des MySQL-Benutzers (unter dem der mysqld läuft) auszuführen.
Desweiteren kann ein Angreifer bei schlecht oder gar nicht gewählten Passwörtern die Datenbanken der jeweiligen Benutzer verändern. Und ohne Ratelimit ist ein Bruteforce Angriff ebenfalls leicht möglich.

Selbst wenn ich von extern auf eine MySQL-Datenbank zugreifen müsste, würde ich das nicht ohne SSL-Zertifikat, VPN oder SSH-Tunneling machen. Alles andere ist einfach zu Böse[tm]. YMMV.

[venundo]

Ich wollte die Einstellungen meiner my.cnf nun mal mit den hier geposteten vergleichen und musste feststellen, dass viele Angaben in meiner Datei gar nicht vorhanden sind. Z.B. endet meine my.cnf bereits hinter

[isamchk]
key_buffer_size = 64M
sort_buffer_size = 4M

. Danach kommt rein gar nichts mehr.

Hängt das vielleicht vom OS ab? Oder müsste ich nicht vorhandene Parameter einfügen?

[Joe User]

Hängt das vielleicht vom OS ab?

Vom OS weniger, eher vom Distributoren.

Oder müsste ich nicht vorhandene Parameter einfügen?

Jupp.

[stefan2k1]

Hi,

danke für die config, was könnte man denn noch für Rechner mit 2Gig RAM anpassen an der Config ?

Danke
Stefan

[Joe User]

Man könnte ein paar *_buffer_size erhöhen und den QCache vergrössern, wenn die vorgeschlagenen Werte nicht ausreichen sollten. Andererseits habe ich, um den Supportaufwand möglichst gering zu halten, absichtlich schon recht grosszügige Werte gewählt

[memo1003]

Moin,

wer nicht nur eine optimierte, sondern auch sichere MySQL-Installation möchte, der sollte folgendes Script ausführen und alle Fragen (abgesehen vom MySQL-Root-Passwort) durch ein simples drücken der Return-Taste mit der Standardantwort bestätigen:

Code: Select all

/usr/bin/mysql_secure_installation

wenn ich das im nachhinein mache also wenn schon eine menge tabellen bestehen geht das immer noch? oder werden die tabellen dann gelöscht?

[Joe User]

wenn ich das im nachhinein mache also wenn schon eine menge tabellen bestehen geht das immer noch? oder werden die tabellen dann gelöscht?

Ja. Nein.

[in flames]

Ich wollte auch gerade mal diese Config austesten.

Nur startet dann mysql nicht mehr, die Pfade sind korrekt und stimmen mit meinen überein.

Fehlermeldung beim starten:

Starting MySQL database server: mysqld...failed.
Please take a look at the syslog.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

Auszug aus der syslog:

Nov 18 13:02:35 debian mysqld[5266]:
Nov 18 13:02:35 debian mysqld_safe[5272]: ended
Nov 18 13:02:41 debian /etc/init.d/mysql[5335]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Nov 18 13:02:41 debian /etc/init.d/mysql[5335]: ^G/usr/bin/mysqladmin: connect to server at 'localhost' failed
Nov 18 13:02:41 debian /etc/init.d/mysql[5335]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Nov 18 13:02:41 debian /etc/init.d/mysql[5335]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Nov 18 13:02:41 debian /etc/init.d/mysql[5335]:

Die mysqld.sock wird auch nicht erstellt.

Hier ist mal meine bisherige my.cnf:

http://www.bm-community.de/my.cnf

Ich bedanke mich

[philipp]

Ich wollte auch gerade mal diese Config austesten.

Nur startet dann mysql nicht mehr, die Pfade sind korrekt und stimmen mit meinen überein.

Fehlermeldung beim starten:

Starting MySQL database server: mysqld...failed.
Please take a look at the syslog.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

suche in der neuen my.cnf nach

Code: Select all

#skip-innodb

und mach ein

Code: Select all

skip-innodb

draus.

[adjustman]

und warum funktioniert das bei ihm nicht? Oder bei mir. Hab das gleiche

[n0fear]

Hatte die Datei angepasst und auf nem Server mit Plesk installed.. also die Webseiten sind nun pfeilschnell ABER Plesk admin Oberfläche will nicht mehr

ERROR: Unable to query: Can't find file: 'misc.MYI' (errno: 2) 0: /usr/local/psa/admin/plib/common_func.php3:203 psaerror(string "Unable to query: Can't find file: 'misc.MYI' (errno: 2)") 1: /usr/local/psa/admin/plib/common_func.php3:390 db_query(string "select param, val from misc") 2: /usr/local/psa/admin/plib/class.Session.php:113 get_param(string "login_timeout") 3: /usr/local/psa/admin/auto_prepend/auth.php3:139 Session->Session()


Nach Entfernen des gesammten Bereichs innodb_ rennt es... für was brauch ich die innodb denn? (dumm frag)


Frage 2: /usr/bin/mysql_secure_installation was mache ich wenn ich keinen mysql root habe sondern der default admin heist?

[Joe User]

Nach Entfernen des gesammten Bereichs innodb_ rennt es... für was brauch ich die innodb denn? (dumm frag)

Wenn Du die InnoDB-Engine nicht nutzt, für Nichts. Allerdings wird von den MySQL-Entwicklern empfohlen, die InnoDB-Engine grundsätzlich miteinzukompilieren, daher der Konfigurationsabschnitt.

Frage 2: /usr/bin/mysql_secure_installation was mache ich wenn ich keinen mysql root habe sondern der default admin heist?

Entweder den User anlegen, oder das Script anpassen, oder http://www.www.rootforum.org/forum/view ... 232#240232

[Guest]

Hallo,

ich habe seit kurzem einen Server bei strato mit folgender konfig:

Opteronâ?¢148
2 GB RAM
2x160GB Raid

SuSE 9.3
Plesk 7.5

da ich des öffteren einen zu hohen load auf dem server habe, bin auf daiese my.conf hier gestossen und wollte sie mal ausprobieren.
Das Problem ist, wenn ich wie mein vorgänger den InnoDB-Teil herausnehme funktioniert mein mail-server nicht mehr, was ich vorher schon vermutet habe, wenn ich das #skip-innodb so lasse wie es ist, startet MySQL nicht mehr.

Gibt es eine Lösung hierfür?

[braindead]

poste doch einfach mal die Error-Logs von mysql, dann kann man auch mehr sagen.

[seb]

hallihallo, bin auf den thread durch die suche gelandet...

bei mir hört die my.cnf auch schon bei

Code: Select all

[isamchk]
key_buffer = 16M

auf, das issen RootDS also nen größerer vServer mit Debian 3.1 Stable und Plesk 7.5

da einige hier schon von problemen berichten mit Plesk...
bei mir liegt die my.cnf in /etc/mysql/my.cnf

und manche sachen sind auch anders...

Code: Select all

# Instead of skip-networking the default is now to listen only on localhost which is more comatible and is not less secure.
bind-address = 127.0.0.1

also skip bräucht man garnicht mehr eintragen?

[dasilva]

Gibt es sowas auch für Windows?
Kann die Datei nicht finden.

[flo]

Gibt es sowas auch für Windows?
Kann die Datei nicht finden.

@ DaSilva:

Hier geht es nicht um Windows, aber die meisten Einträge dieser my.cnf dürften wohl auch unter Windows laufen - mehr sagt Dir das Handbuch.

Bitte gib weitere wenig nützliche Kommentare in eigene Threads, die kann man wenigstens verschieben.

flo.

[isotopp]

Gibt es sowas auch für Windows?
Kann die Datei nicht finden.

Ein mysqld sucht seine Datei in den angebenenen Verzeichnissen, wenn man ihn nicht zwingt, woanders nachzusehen. Du kannst also mal "C:ProgrammeMySQLMySQL Server 5.0binmysql.exe --help --verbose" aufrufen und dort nachsehen, wo Dein mysqld.exe seine Konfiguration sucht. Es wird my.ini und my.cnf in C:, in C:WINDOWSirgendwas und so weiter gesucht.

Wenn Du jedoch MySQL als Dienst installiert hast (Kannst Du im Dienstekontroll-Plugin nachsehen), dann solltest Du Dir mal die Eigenschaften von dem Dienst ansehen (RMB auf den Dienst, Eigenschaften). Dort findest Du den Kontrollstring für den Dienst, und da ist mit "--default-file=..." der Pfadname Deiner my.ini eingetragen. Die in mysqld.exe eingebauten Defaults werden dann nicht verwendet.

[bravesurfer]

Ich habe o.g. Konfiguration auf mehreren Root-Servern P IV 3 GHZ, 1024 MB Speicher getestet. Die Serverlast geht hierdurch jedoch deutlich in die Höhe, weshalb ich wieder auf die "Standardwerte" meiner Linux Distrubtion (Suse 9.3 / MySQL 4.1) umgestellt habe, die bezüglich Speicherzuweisung in vielen Punkten deutlich unter den o.g. Werten liegt.

Konnte jemand ähnliche Erfahrungen machen?