Mysql installieren und konfigurieren
Einleitung mysql
Mysql ist unter Linux neben postgres das am häufigsten genutzten Datenbank-Systeme. Mysql ist als Opensource Projekt im Jahr 1994 entstanden und gehört mittlerweile zu Oracle. Obwohl es zu mysql seit geraumer Zeit auch kommerzielle Angebote gibt, ist die Datenbank-Software mysql selbst immer noch ein Opensource-Produkt. Das bedeutet, daß es von jedermann herunter geladen und genutzt werden kann.
https://de.wikipedia.org/wiki/MySQL
Homepage: https://www.mysql.com/de/
Da es nach der Übernahme von Oracle jedoch zu Konflikten zwischen dem kommerziellen Anspruch und der Opensource-Gemeinde kam, hat sich im Jahr 2012 mit mariadb ein so genannter Fork (~Abspaltung) von mysql gebildet, der wieder vollkommen quelloffen entwickelt wird und so mehr dem Opensource Gedanken entspricht.
https://de.wikipedia.org/wiki/MariaDB
Homepage: https://mariadb.org/
Im nachfolgenden Tutorial erklären wir Schritt für Schritt die Installation von mariadb auf einem Linux-Server mit Centos sowie die Absicherung und Inbetriebnahme der Datenbank-Software.
Hinweis: Wir verwenden CentOS als Basis für unsere Installation. Mysql bzw. mariadb lassen sich aber genauso auf Debian, Ubuntu und praktisch allen anderen Linux-Servern mit dem jeweiligen Paketmanager installieren. Sobald die Erst-Installation unter Linux erledigt ist, können Sie diese Anleitung dennoch Schritt für Schritt abarbeiten.
Vorüberlegungen zu mysql
Wer mysql einfach mal so ausprobieren möchte, muss sich um Geschwindigkeit von Abfragen und Performance im Allgemeinen vermutlich keine Gedanken machen.
Sofern Sie allerdings mysql produktiv einsetzen möchten, so sollten wir einige wenige Gedanken über das System-Design machen.
Hauptspeicher / RAM
In Sachen Hauptspeicher (RAM) sind praktisch alle Datenbanksysteme extrem „hungrig“. Da macht mysql keine Ausnahme. Die generelle Regel lautet: Je mehr desto beser.
Festplatten
Wer seinen mysql Server auf einer dedizierten Hardware betreibt, der tut gut daran dem mysql Dienst eine separate Festplatte zuzuweisen. Ideal sind mehrere, eher kleine Festplatten, die über ein Raid10 (Verbund aus gespiegelten Platten) an einem RAID-Controller angeschlossen sind.
Wer mysql virtuell betreibt, der sollte zumindest das mysql-Datenverzeichnis /var/lib/mysql auf einer separaten Partition einrichten. Wie das geht, zeigen wir weiter unten.
Linux-Server vorbereiten
Zur Vorbereitung unserer mysql Installation deaktivieren wir zuerst temporär SELinux und schalten ebenfalls den eingebauten firewalld Dienst aus.
1 | Mysql01# systemctl disable firewalld |
In der Datei /etc/sysconfig/selinux ändern Sie die Zeile „SELINUX=enforcing“ auf „SELINUX=disabled“
Anschließend loggen wir uns per ssh auf dem Server an und aktualisieren alle Software-Pakete mit „yum –y update“. Das dauert je nach Hardware und Internetanbindung ein paar Minuten. Anschließend rebooten Sie den Server.
Mysql auf eine separate Partition bzw. Festplatte legen
Wer wie weiter oben angesprochen mysql bzw. mariadb produktiv einsetzen möchte, der sollte das Verzeichnis /var/lib/mysql auf ein separates Filesystem legen.
In unserem (virtuellen) Beispiel haben wir eine zweite Festplatte /dev/sdb erstellt.
Auf dieser erstellen wir mit fdisk /dev/sdb die neue Partition /dev/sdb1 und speichern fdisk mit „w“ ab.
1 2 3 4 5 6 | [root@mysql01 ~]# fdisk /dev/sdb Welcome to fdisk (util-linux 2.23.2). Changes will remain in memory only, until you decide to write them. Be careful before using the write command. Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0x8f093467. |
Befehl (m für Hilfe): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e Erweiterte
Select (default p): p
Partitionsnummer (1-4, default 1): 1
Erster Sektor (2048-125829119, Vorgabe: 2048):
Benutze den Standardwert 2048
Last Sektor, +Sektoren or +size{K,M,G} (2048-125829119, Vorgabe: 125829119):
Benutze den Standardwert 125829119
Partition 1 of type Linux and of size 60 GiB is set
Befehl (m für Hilfe): w
Die Partitionstabelle wurde verändert!
Rufe ioctl() um Partitionstabelle neu einzulesen.
Synchronisiere Platten.
Anschließend formatieren wir die neue Partition z.B. mit dem xfs Filesystem
1 2 3 4 5 6 7 8 9 10 | [root@mysql01 ~]# mkfs.<a class="wpil_keyword_link" href="https://www.biteno.de/was-ist-xfs/" title="xfs" data-wpil-keyword-link="linked">xfs</a> /dev/sdb1 meta-data=/dev/sdb1 isize=512 agcount=4, agsize=3932096 blks = sectsz=512 attr=2, projid32bit=1 = crc=1 finobt=0, sparse=0 data = bsize=4096 blocks=15728384, imaxpct=25 = sunit=0 swidth=0 blks naming =version 2 bsize=4096 ascii-ci=0 ftype=1 log =Internes Protokoll bsize=4096 blocks=7679, version=2 = sectsz=512 sunit=0 blks, lazy-count=1 realtime =keine extsz=4096 blocks=0, rtextents=0 |
Last but not least legen wir einen Eintrag in der /etc/fstab an, damit das zusätzliche Filesystem bei einem Reboot auch ordentlich gemounted wird, bevor der mariadb bzw. mysqld Dienst startet.
Ergänzen Sie die Datei /etc/fstab um die folgende Zeile
1 | /dev/sdb1 /var/lib/mysql xfs defaults 0 0 |
Anschließend legen wir den notwendigen Mountpoint (Einhängepunkt für die Partition) noch manuell an und setzen die Berechtigungen:
1 2 | [root@mysql01 ~]# mkdir /var/lib/mysql [root@mysql01 lib]# chown –R mysql.mysql /var/lib/mysql/ |
Mysql unter Linux installieren
Die eigentliche Installation von mariadb (bzw. mysql) übernimmt der Befehl „yum install mariadb-server mariadb“. Im Schlepptau werden ca 2 Dutzend Perl-Pakete mit installiert.
1 | [root@mysql01 ~]# yum –y install mariadb mariadb-server |
Nun müssen wir den Dienst mariadb noch aktivieren und anschließend starten:
1 2 3 4 | [root@mysql01 ~]# systemctl enable mariadb Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service. [root@mysql01 ~]# service mariadb start Redirecting to /bin/systemctl start mariadb.service |
Erste Anmeldung an mariadb / mysql
Um nun „interaktiv“ zu prüfen ob mariadb wirklich funktioniert, loggen wir uns einmal als Benutzer root an mariadb an.
In der Standard-Installation ist der Benutzer „root“ in mariadb/mysql bereits angelegt. Ein Passwort hat der Benutzer (noch) nicht.
Syntax: mysql –u<Benutzer> -p<Passwort>
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [root@mysql01 ~]# mysql -uroot Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.56-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) |
MariaDB [(none)]> exit
Der Befehl “show databases” zeigt uns die bestehenden Datenbanken an. Dies sind neben der Standard-Datenbank „mysql“ die Datenbanken „information_schema“ und „performance_schema“ sowie „test“.
mysql / mariadb absichern
Im Moment kann man sich noch ganz ohne Passwort an mysql anmelden. Ebenfalls sind Anmeldungen von entfernten Rechnern über das Netzwerk möglich. Das möchten wir dauerhaft so nicht belassen.
Ein Weg mysql auf einen Rutsch deutlich sicherer zu machen ist der Aufruf der Anpassungs-Routine „mysql_secure_installation“.
Mit diesem Skript können Sie
- Ein Passwort für den Benutzer root setzen
- Die Test-Datenbank entfernen
- Den Zugriff über das Netzwerk unterbinden
Beantworten Sie einfach die gestellten Fragen mit y[es] oder n[o]. Nach Abschluss des Skripts wird die mysql-Datenbank, welche die Benutzer enthält neu geladen und Sie können sich mit ihrem gerade erstellten Passwort wieder anmelden.
1 2 3 4 5 6 | [root@mysql01 ~]# mysql_secure_installation … In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. |
Enter current password for root (enter for none):
OK, successfully used password, moving on…
Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation.
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.
Remove anonymous users? [Y/n] y
… Success!
Normally, root should only be allowed to connect from ‚localhost‘. This ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
… Success!
By default, MariaDB comes with a database named ‚test‘ that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment.
Remove test database and access to it? [Y/n] y
– Dropping test database…
… Success!
– Removing privileges on test database…
… Success!
Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
Reload privilege tables now? [Y/n] y
… Success!
Cleaning up…
All done! If you’ve completed all of the above steps, your MariaDB installation should now be secure.
(…)
Wir prüfen nun ob die Anmeldung als Benutzer root immer noch ohne Passwort möglich ist (Das sollte es nicht ,mehr sein).
1 2 | [root@mysql01 ~]# mysql -uroot ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) |
Die Fehlermeldung ist ausnahmsweise einmal „positiv“ im Sinne von so gewollt. Dafür sollte die Anmeldung mit „mysql –uroot –p<ihr Passwort> klappen
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [root@mysql01 ~]# mysql -uroot -p Enter password: ç hier Passwort eingeben Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 5.5.56-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) |
Wir sehen: Die Anmeldung ist weiter mit Passwort möglich und außerdem ist die Datenbank „test“ ebenfalls verschwunden.
mysql / mariadb: Benutzer verwalten
Zwei administrative Aufgaben kommen beim Betrieb von mysql bzw. mariadb immer wieder vor: Neue Benutzer anlegen und von bestehenden Benutzern das Passwort zurücksetzen. Daneben gilt es „mal eben so“ eine neue Datenbank anzulegen.
Hinweis: Alle Beispiele gehen davon aus, daß Sie sich zuerst mit „mysql –uroot –p“ an mysql anmelden.
Eine neue (leere) Datenbank in mysql anlegen
Eine neue und leere Datenbank legen Sie mit dem Befehl „create database <DATENBANKNAME>;“ an.
Beispiel:
1 | create database sbtest; |
Der obige Befehlt erstellt die leere Datenbank „sbtest“.
Neuen Benutzer unter mysql anlegen
Die Syntax unter mysql zum Anlegen eines neuen Benutzers ist:
1 2 3 |
Username und Password ersetzen Sie mit dem echten Benutzer-Namen sowie einem möglichst sicheren Passwort. Mit „HOST“ ist dabei der Rechner gemeint, von dem aus der Anwender zugreifen darf.
Sofern hier nur der Zugriff über den Rechner erlaubt ist, auf dem mysql läuft, so verwenden Sie „localhost“ . Alternativ können Sie bei HOST auch eine IP-Adresse verwenden. Ebenso gehen normale Hostnamen, die sich über das DNS auflösen lassen.
Beispiel: Wir legen einen Benutzer mit Namen „max“ an, der von überall aus zugreifen darf. Sein Passwort lautet „Imitoguli835“.
1 2 3 | use mysql; create user 'max'@'*' identified by 'Imitoguli835'; flush privileges; |
Auf Datenbanken zugreifen (dürfen)
Damit der frisch angelegte Benutzer auch das Recht hat, eine bestehende Datenbank zu bearbeiten, muss ihm mit dem „Grant“ Befehl dazu die Berechtigung erteilt werden. Die Syntax dazu lautet:
1 | Grant ALL on DATABASE.TABLE TO 'USERNAME'@'HOST'; |
Beispiel: Wir gewähren dem Benutzer „Max“ den Zugriff auf die Datenbank „sbtest“ – aber nur wenn er sich über den mysql-Server anmeldet (also über den Localhost).
1 2 3 | use mysql; grant all on sbtest.* to 'max'@'localhost'; flush privileges; |
Noch ein Beispiel: Wir gewähren unserem Testbenutzer „Max“ den Zugriff auf alle (!) Datenbanken, egal von wo aus er zugreift.
1 2 3 | use mysql; grant all on *.* to 'max'@'*'; flush privileges; |
Der Befehl “flush privileges;“ schreibt alle Berechtigungen in mysql zurück und lädt die aktualisierten Berechtigungen neu. Erst danach greift das neue Rechtesystem für den gerade veränderten mysql-Benutzer.
Von einem bekannten mysql Benutzer das Passwort zurück setzen
Wenn ein Benutzer sein Passwort für mysql nicht mehr weiß, dann kann es der mysql-Administrator mit dem folgendem Befehl zurück setzen:
1 2 | use mysql; update user set password=PASSWORD('NEUESPASSWORT') where User='BENUTZERNAME'; flush privileges; |
Wichtig: ‚User‘ wird wirklich mit großem ‚U‘ geschrieben.
Die Begriffe „NEUESPASSWORT“ und „BENUTZERNAME“ ersetzen Sie natürlich durch ein wirklich sicheres Passwort und den echten Benutzernamen.
Mysql Benchmark
Um mysql nun ein wenig unter Last zu setzen, installieren wir den Benchmark-Test sysbench. Dafür benötigen wir allerdings zuerst das EPEL-Repository, das wir zuerst installieren.
1 2 | yum –y install epel-release yum -y install sysbench |
Da sysbench eine Testdatenbank (die meistens sbtest heißt) benötigt legen wir die Datenbank „sbtest“ an und außerdem gleich einen passenden Benutzer „testdb“, der sich lokal auf dem Server an mariadb anmelden darf.
1 2 3 | mysql –uroot –p –e "create database sbtest;" mysql –uroot –p –e “grant all on sbtest.* to testdb@localhost identified by 'test123';" mysql –uroot –p –e "flush privileges;" |
Sysbench besteht in der Regel aus zwei aufeinaderfolgenden Aktionen. Im ersten Schritt (der mit ‚prepare‘ endet) werden die Tabellen und ggf. Inhalte angelegt, auf die beim eigentlichen Durchlauf des Benchmarks zugegriffen wird. Der eigentliche Benchmark-Befehlt endet auf „run“.
Was konkret wie geprüft und getestet wird, ist bei sysbench seit einiger Zeit in vordefinierten Skripten fest gelegt. Diese liegen nach der Installation von sysbench unter /usr/share/sysbench/ .
Beispiel:
Der nachfolgende erste Befehl bereitet die leere Datenbank sbtest mit 4 Tabellen vor.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | sysbench /usr/share/sysbench/oltp_common.lua --threads=4 --mysql-host=localhost --mysql-user=testdb --mysql-password=test123 --mysql-port=3306 --tables=4 --db-driver=mysql --table-size=100000 prepare sysbench 1.0.9 (using system LuaJIT 2.0.4) Initializing worker threads... Creating table 'sbtest2'... Creating table 'sbtest1'... Creating table 'sbtest3'... Creating table 'sbtest4'... Inserting 100000 records into 'sbtest3' Inserting 100000 records into 'sbtest1' Inserting 100000 records into 'sbtest4' Inserting 100000 records into 'sbtest2' Creating a secondary index on 'sbtest1'... Creating a secondary index on 'sbtest4'... Creating a secondary index on 'sbtest2'... Creating a secondary index on 'sbtest3'... |
Der anschließende Aufruf startet den allgemeinen Lese/Schreiben Benchmark für 60 Sekunden und gibt alle 5 Sekunden eine Statistik aus.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | sysbench /usr/share/sysbench/oltp_read_write.lua --threads=4 --events=0 --time=60 --mysql-host=localhost --mysql-user=testdb --mysql-password=test123 --mysql-port=3306 --tables=4 --table-size=100000 --range_selects=off --db-driver=mysql --db-ps-mode=disable --report-interval=5 run sysbench 1.0.9 (using system LuaJIT 2.0.4) Running the test with following options: Number of threads: 4 Report intermediate results every 5 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 5s ] thds: 4 tps: 164.51 qps: 2643.40 (r/w/o: 1653.13/432.57/557.71) lat (ms,95%): 95.81 err/s: 0.00 reconn/s: 0.00 [ 10s ] thds: 4 tps: 185.81 qps: 2973.73 (r/w/o: 1858.08/528.62/587.03) lat (ms,95%): 95.81 err/s: 0.00 reconn/s: 0.00 [ 15s ] thds: 4 tps: 257.17 qps: 4114.80 (r/w/o: 2571.75/764.93/778.12) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 4 tps: 232.42 qps: 3718.67 (r/w/o: 2324.17/743.45/651.05) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00 [ 25s ] thds: 4 tps: 256.43 qps: 4102.95 (r/w/o: 2564.34/850.91/687.69) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 4 tps: 199.19 qps: 3185.91 (r/w/o: 1991.95/679.58/514.39) lat (ms,95%): 89.16 err/s: 0.00 reconn/s: 0.00 [ 35s ] thds: 4 tps: 237.75 qps: 3805.25 (r/w/o: 2377.53/823.84/603.88) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 4 tps: 243.02 qps: 3888.26 (r/w/o: 2430.16/852.06/606.04) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00 [ 45s ] thds: 4 tps: 234.79 qps: 3756.71 (r/w/o: 2347.94/830.78/577.99) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 4 tps: 239.43 qps: 3830.93 (r/w/o: 2394.33/851.92/584.68) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00 [ 55s ] thds: 4 tps: 276.78 qps: 4422.67 (r/w/o: 2763.79/995.93/662.95) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 4 tps: 274.40 qps: 4396.27 (r/w/o: 2748.04/995.22/653.01) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00 |
SQL statistics:
queries performed:
read: 140130
write: 46750
other: 37328
total: 224208
transactions: 14013 (233.52 per sec.)
queries: 224208 (3736.38 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0050s
total number of events: 14013
Latency (ms):
min: 3.48
avg: 17.12
max: 440.18
95th percentile: 68.05
sum: 239970.51
Threads fairness:
events (avg/stddev): 3503.2500/12.74
execution time (avg/stddev): 59.9926/0.00
Ein Hinweis zum Schluss: Wenn Sie mit sysbench einen gänzlich anderen Performance-Test durchführen wollen, so müssen Sie zuerst die Tabellen innerhalb der Datenbank sbtest löschen. Ansonsten erhalten Sie von sysbench eine Fehlermeldung.
Das Löschen der Tabellen erfolgt mit „drop table <tablename>;“. Schneller ist es aber die ganze Datenbank „sbtest“ zu löschen und anschließend leer wieder anzulegen.
1 2 | Drop database sbtest; Create database sbtest; |
Fazit zu mysql
Die Installation und erste Einrichtung von mysql ist für geübte Linux-Administratoren in wenigen Minuten erledigt und auch für Gelegenheits-Admins kein Hexenwerk. Wer durch das Tutorial auf den Geschmack gekommen ist, der sollte sich ein wenig mehr mit dem Thema Performance von mysql bzw. mariadb beschäftigen.
Wie man die Konfigurations-Datei ( /etc/my.cnf ) anpasst und so mysql weiter optimiert, erklären wir in einem weiteren Tutorial.
- Über den Autor
- Aktuelle Beiträge
Matthias Böhmichen ist Geschäftsführer des IT-Dienstleisters Biteno GmbH. Neben seinem Job blogt und schreibt er auf den Webseiten der Biteno GmbH über technische und unternehmerische Themen.
