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
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[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.xfs /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
17
18
[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)]&gt; show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]&gt; 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
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[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)]&gt; 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
use mysql;
create user 'USERNAME'@'HOST' identified by 'PASSWORD';
flush privileges;

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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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.