MySQL richtig konfigurieren

MySQL, PostgreSQL, SQLite
User avatar
Joe User
Project Manager
Project Manager
Posts: 11137
Joined: 2003-02-27 01:00
Location: Hamburg

MySQL richtig konfigurieren

Post by Joe User » 2005-08-26 19:55

Moin,

da viele User die MySQL-Dokumentation nicht oder nur oberflächlich lesen und sich über einen langsamen MySQLd wundern, stelle ich hier eine vollständige und bewährte Konfiguration (my.cnf) bereit. Diese ist für dedizierte Server ab 4096MB RAM auf denen neben MySQL 5.6/5.7 noch weitere Dienste, beispielsweise Web- und/oder Mailserver, betrieben werden ausgelegt. Die Pfade sollten selbstverständlich an das jeweilige System angepasst werden. Desweiteren ist zu beachten, dass vor dem Einspielen dieser Konfiguration ein mysqldump zwingend notwendig ist. Danach müssen auch die bisherigen InnoDB-Files gelöscht werden, diese werden beim nächsten Start automatisch neu angelegt:

Code: Select all

# Zuerst ein Backup der Datenbanken anlegen
mysqldump --defaults-file=/etc/mysql/my.cnf --master-data=2 --delete-master-logs --flush-logs --add-locks --create-options --allow-keywords --complete-insert --triggers --routines --events --order-by-primary --set-gtid-purged=OFF --tz-utc --hex-blob --lock-all-tables --all-databases -uroot -p > /tmp/mysqldump.sql

# MySQL stoppen

# Pfade selbst anpassen
# Die InnoDB-Files löschen
rm -f /var/lib/mysql/ibdata[0-9]
rm -f /var/lib/mysql/ib_logfile[0-9]
rm -f /var/lib/mysql/ib_buffer_pool
rm -f /var/lib/mysql/ibtmp[0-9]

# my.cnf anlegen/anpassen
Aus Sicherheitsgründen beschränken wir das Anlegen temporärer Tabellen und die Operationen "LOAD DATA ... INFILE" und "SELECT ... INTO OUTFILE" auf das Verzeichnis /var/lib/mysql_secure und andere temporäre Operationen auf das Verzeichnis /var/lib/mysql_tmpdir welche wir nun anlegen:

Code: Select all

mkdir -p /var/lib/mysql
chmod 0755 /var/lib/mysql
chown mysql:mysql /var/lib/mysql

mkdir -p /var/lib/mysql_secure
chmod 0750 /var/lib/mysql_secure
chown mysql:mysql /var/lib/mysql_secure

mkdir -p /var/lib/mysql_tmpdir
chmod 0750 /var/lib/mysql_tmpdir
chown mysql:mysql /var/lib/mysql_tmpdir
my.cnf:

Code: Select all

[client]
port                            = 3306
socket                          = /tmp/mysql.sock

[mysql]
prompt                          = \u@\h [\d]>\_
no_auto_rehash

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
bind-address                    = 127.0.0.1
basedir                         = /usr
datadir                         = /var/lib/mysql
tmpdir                          = /var/lib/mysql_tmpdir
slave-load-tmpdir               = /var/lib/mysql_tmpdir
secure-file-priv                = /var/lib/mysql_secure
log-bin                         = /var/lib/mysql/mysql-bin
log-output                      = TABLE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
relay-log-recovery              = 1
general-log                     = 0
general-log-file                = /var/lib/mysql/general.log
slow-query-log                  = 1
slow-query-log-file             = /var/lib/mysql/slow-query.log
default_authentication_plugin   = mysql_native_password
default_password_lifetime       = 0
server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 16M
expire_logs_days                = 30
enforce-gtid-consistency        = 1
gtid-mode                       = ON
safe-user-create                = 1
lower_case_table_names          = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options          = FORCE,BACKUP
net_retry_count                 = 16384
open_files_limit                = 32768
table_open_cache                = 16384
table_definition_cache          = 8192
max_allowed_packet              = 64M
key_buffer_size                 = 256M
myisam_sort_buffer_size         = 16M
bulk_insert_buffer_size         = 64M
join_buffer_size                = 512K
sort_buffer_size                = 4M
read_buffer_size                = 256K
read_rnd_buffer_size            = 512K
max_heap_table_size             = 256M
tmp_table_size                  = 256M
query_cache_type                = 0
query_cache_size                = 0
long_query_time                 = 0.05
innodb_thread_concurrency       = 8
innodb_buffer_pool_size         = 2G
innodb_buffer_pool_dump_pct     = 100
innodb_data_home_dir            = /var/lib/mysql
innodb_log_group_home_dir       = /var/lib/mysql
innodb_data_file_path           = ibdata1:1G;ibdata2:1G;ibdata3:128M:autoextend
innodb_temp_data_file_path      = ibtmp1:128M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_flush_log_at_timeout     = 2
innodb_flush_log_at_trx_commit  = 2
innodb_disable_sort_file_cache  = 1
innodb_write_io_threads         = 4
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
innodb_max_dirty_pages_pct      = 0
skip-symbolic-links
#skip-name-resolve

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick
MySQLd wieder starten und warten bis die InnoDB-Files fertig erzeugt sind.

Datenbank-Backup zurückspielen:

Code: Select all

# Backup zurückspielen
mysql -uroot -p < /tmp/mysqldump.sql

# mysql_upgrade ausführen
mysql_upgrade --force

Für das weitere Tuning, sofern noch nötig, sind die Scripts mysqltuner.pl und tuning-primer.sh sowie die offizielle MySQL 5.7 Dokumentation äusserst hilfreich.

Gruss,
Joe User


Letzte Aktualisierung: 08.04.2017
Last edited by Joe User on 2016-09-17 00:43, edited 16 times in total.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

captaincrunch
Userprojekt
Userprojekt
Posts: 7066
Joined: 2002-10-09 14:30
Location: Dorsten

Re: MySQL richtig konfigurieren

Post by captaincrunch » 2005-08-26 20:35

Vielleicht sollte man sowas auch mal für ne echte DB (aka Postgres (SCNR)) anbieten. OutOfBound, wie wär's? ;)
DebianHowTo
echo "[q]sa[ln0=aln256%Pln256/snlbx]sb729901041524823122snlbxq"|dc

User avatar
Joe User
Project Manager
Project Manager
Posts: 11137
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL richtig konfigurieren

Post by Joe User » 2005-08-27 19:40

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
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

larsinho
Posts: 14
Joined: 2004-04-14 20:21

Re: MySQL richtig konfigurieren

Post by larsinho » 2005-08-27 20:15

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 :wink:

User avatar
Joe User
Project Manager
Project Manager
Posts: 11137
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL richtig konfigurieren

Post by Joe User » 2005-08-27 20:21

Larsinho wrote: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!
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

burn
Posts: 14
Joined: 2003-02-05 22:34
Location: Hannover

Re: MySQL richtig konfigurieren

Post by burn » 2005-09-10 02:30

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
Administrator
Administrator
Posts: 5924
Joined: 2004-05-23 12:53

Re: MySQL richtig konfigurieren

Post by Roger Wilco » 2005-09-10 12:48

Burn wrote: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
Posts: 4
Joined: 2005-09-23 18:44
Location: Geilenkirchen

Re: MySQL richtig konfigurieren

Post by venundo » 2005-10-04 14:56

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?

User avatar
Joe User
Project Manager
Project Manager
Posts: 11137
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL richtig konfigurieren

Post by Joe User » 2005-10-04 15:25

Venundo wrote:Hängt das vielleicht vom OS ab?
Vom OS weniger, eher vom Distributoren.
Venundo wrote:Oder müsste ich nicht vorhandene Parameter einfügen?
Jupp.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

stefan2k1
Posts: 24
Joined: 2003-09-09 21:01

Re: MySQL richtig konfigurieren

Post by stefan2k1 » 2005-10-09 22:32

Hi,

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

Danke
Stefan

User avatar
Joe User
Project Manager
Project Manager
Posts: 11137
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL richtig konfigurieren

Post by Joe User » 2005-10-10 10:21

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 ;)
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

memo1003
Posts: 24
Joined: 2005-07-08 23:34

Re: MySQL richtig konfigurieren

Post by memo1003 » 2005-11-09 16:56

Joe User wrote: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?

User avatar
Joe User
Project Manager
Project Manager
Posts: 11137
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL richtig konfigurieren

Post by Joe User » 2005-11-09 17:43

memo1003 wrote: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.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

in flames
Posts: 70
Joined: 2003-12-24 22:33

Re: MySQL richtig konfigurieren

Post by in flames » 2005-11-18 13:23

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
Posts: 52
Joined: 2003-06-19 19:36
Location: Hannover

Re: MySQL richtig konfigurieren

Post by philipp » 2005-12-10 02:10

In Flames wrote: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
Posts: 1132
Joined: 2003-03-26 23:29
Location: SA

Re: MySQL richtig konfigurieren

Post by adjustman » 2005-12-17 23:37

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

n0fear
Posts: 6
Joined: 2005-12-03 17:43

Re: MySQL richtig konfigurieren

Post by n0fear » 2006-01-12 18:33

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?

User avatar
Joe User
Project Manager
Project Manager
Posts: 11137
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL richtig konfigurieren

Post by Joe User » 2006-01-12 20:28

n0fear wrote: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.
n0fear wrote: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.rootforum.org/forum/viewtop ... 232#240232
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

Anonymous

Re: MySQL richtig konfigurieren

Post by Anonymous » 2006-01-18 21:43

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
Posts: 250
Joined: 2002-10-22 09:49
Location: vorm Rechner

Re: MySQL richtig konfigurieren

Post by braindead » 2006-01-19 22:00

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

seb
Posts: 24
Joined: 2002-09-06 05:16

Re: MySQL richtig konfigurieren

Post by seb » 2006-01-21 14:11

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
Posts: 22
Joined: 2006-03-09 20:30

Windows?

Post by dasilva » 2006-03-11 13:46

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

flo
Posts: 2223
Joined: 2002-07-28 13:02
Location: Berlin

Re: Windows?

Post by flo » 2006-03-11 14:33

DaSilva wrote: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.

User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin

Re: Windows?

Post by isotopp » 2006-03-11 21:03

DaSilva wrote: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
Posts: 170
Joined: 2003-05-08 12:17
Location: Stuttgart

Re: MySQL richtig konfigurieren

Post by bravesurfer » 2006-04-11 16:21

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?