Skip to main content

MySQL

1. Benutzerverwaltung

1.1 Benutzer anzeigen

sql
-- einfache Übersicht
SELECT User, Host
FROM mysql.user;

-- inkl. Authentifizierungsinformationen (nicht direkt bearbeiten!)
SELECT User, Host, plugin, authentication_string
FROM mysql.user;

Hinweis: Die Tabelle mysql.user sollte nur gelesen werden.
Benutzer immer mit CREATE USER, ALTER USER und GRANT verwalten.

1.2 Benutzer anlegen und Berechtigungen vergeben

Anmeldung als Administrator:

bash
mysql -u root -p

Datenbank anlegen:

sql
CREATE DATABASE DBNAME
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Benutzer nur lokal (localhost):

sql
CREATE USER 'DB_USER'@'localhost'
  IDENTIFIED BY 'DB_PASS';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
ON DBNAME.* TO 'DB_USER'@'localhost';

Benutzer mit Zugriff von überall (nicht empfohlen):

sql
CREATE USER 'DB_USER'@'%'
  IDENTIFIED BY 'DB_PASS';

GRANT ALL PRIVILEGES
ON DBNAME.* TO 'DB_USER'@'%';

Wichtig: Seit MySQL 8 wird ein Benutzer nicht mehr implizit über
GRANT ... IDENTIFIED BY angelegt: zuerst CREATE USER, dann GRANT.

1.3 Benutzeranmeldung

Lokal:

bash
mysql -u DB_USER -p DBNAME

Anmeldung an einem entfernten Host:

bash
mysql -u superadmin -p -h intranet-prod-mysql.irgendwas.com confluencedbtemp

1.4 Benutzer löschen

sql
DROP USER IF EXISTS 'DB_USER'@'localhost';
DROP USER IF EXISTS 'DB_USER'@'%';

2. Datenbanken und Schemata

2.1 Datenbank erstellen

sql
CREATE DATABASE ecg_otrs_intern_test
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

2.2 Datenbank löschen

sql
DROP DATABASE db_name;

2.3 Schemagröße anzeigen

sql
SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM   information_schema.tables
GROUP  BY table_schema
ORDER  BY total_mb DESC;

3. Remote-Zugriff

3.1 Server für Remote-Zugriff konfigurieren

Standardmäßig lauscht MySQL/MariaDB nur auf 127.0.0.1.

Typische Konfigurationsdateien:

  • MySQL: /etc/mysql/mysql.conf.d/mysqld.cnf
  • MariaDB: /etc/mysql/mariadb.conf.d/50-server.cnf

Beispiel:

ini
[mysqld]
# bind-address = 127.0.0.1      # Standard

# Variante 1: nur interne Adresse
bind-address = 10.0.0.5

# Variante 2: alle Interfaces (nur mit Firewall/VPN!)
# bind-address = 0.0.0.0

Dienst neu starten:

bash
systemctl restart mysql

Warnung: bind-address = 0.0.0.0 ohne Firewall oder VPN ist ein deutliches Sicherheitsrisiko.

3.2 Benutzer für Remote-Zugriff freischalten

Bereits vorhandene Benutzer mit Remote-Hosts anzeigen:

sql
SELECT User, Host
FROM mysql.user
WHERE Host <> 'localhost';

Beispiele:

sql
-- Benutzer mit Vollzugriff von einem bestimmten Host
CREATE USER 'echomike'@'ecgwin64.vngag.de'
  IDENTIFIED BY 'neuesPasswort';

GRANT ALL PRIVILEGES ON *.* TO 'echomike'@'ecgwin64.vngag.de'
  WITH GRANT OPTION;

-- Benutzer mit Zugriff aus einem Subnetz
CREATE USER 'user_otrs'@'10.96.%.%'
  IDENTIFIED BY 'otrs4test';

GRANT ALL PRIVILEGES ON ecg_otrs_intern_test.* TO 'user_otrs'@'10.96.%.%'
  WITH GRANT OPTION;

Hinweis: GRANT ALL ON *.* WITH GRANT OPTION nur sehr sparsam und nur für Admin-Konten verwenden.

4. Backup & Restore mit mysqldump

4.1 Gesamten Server sichern

bash
mysqldump -u root -p --single-transaction --all-databases > dump.sql

4.2 Einzelne Datenbank(en) sichern

bash
# eine Datenbank
mysqldump -u root -p --single-transaction DBNAME > DBNAME.sql

# mehrere Datenbanken
mysqldump -u root -p --single-transaction --databases db1 db2 > multi.sql

Beispiel mit Datumsstempel:

bash
mysqldump -u otrs -p otrs \
  > /tmp/otrs-$(date +%Y%m%d-%H%M).sql

Hinweis: --single-transaction ist für InnoDB der Standardweg, um konsistente Dumps zu erzeugen, während Anwendungen weiter schreiben können.

4.3 Restore eines Dumps (klassisch, ohne Docker)

bash
mysql -u BENUTZER -p DATENBANK < datenbank.sql

Beispiel:

bash
mysql -u root -p dfsgwhh < dump.sql -v

Hinweis: -v (--verbose) zeigt den Fortschritt ausführlicher an.

Warnung: Bei Dumps mit --all-databases wird auch die Systemdatenbank mysql mit Benutzerkonten und Rechten überschrieben.

4.4 Dump mit Fortschrittsanzeige

bash
apt install pv

pv dump.sql.gz | zcat | mysql -u user -p database

4.5 Dump per Cron (Beispiel mit Docker-Container)

bash
crontab -e
cron
# Zabbix database backup
2 0 * * * /srv/zabbix/cron/mysqldump.sh >/dev/null 2>&1

/srv/zabbix/cron/mysqldump.sh:

bash
#!/bin/bash

USER=zabbix
PASSWORD=PASSWORD
OUTPUT="/srv/exx/tmp_dbdumps/"
CONTAINER=zabbix-db
DATABASE=zabbix
DATE=$(date +%Y-%m-%d)
[email protected]
HOSTNAME=$(cat /proc/sys/kernel/hostname)

# Backup-Verzeichnis anlegen
if [ ! -d "$OUTPUT" ]; then
   mkdir -p "$OUTPUT"
fi

rm -f "$OUTPUT"/zabbix*

# Dump aus Docker-Container
docker exec "$CONTAINER" \
  mysqldump -u "$USER" --password="$PASSWORD" \
  --single-transaction "$DATABASE" \
  > "$OUTPUT/${DATABASE}_${DATE}.sql"

if [ "$?" -eq 0 ]; then
   rm -f /srv/exx/z_backups/*.*
   cp "$OUTPUT"/zabbix* /srv/exx/z_backups/
   echo "DB Backup erfolgreich"
else
   echo "$HOSTNAME - DB Backup $DATABASE - $DATE fehlgeschlagen" | \
   mail -s "$HOSTNAME - DB Backup $DATABASE - $DATE fehlgeschlagen" -- "$MAILTO"
fi

4.6 Restore, wenn MySQL/MariaDB im Docker-Container läuft

Bei MySQL/MariaDB im Docker-Container wird der Restore über docker exec ausgeführt.
Das < dump.sql passiert auf dem Host, nicht im Container.

4.6.1 Einzelne Datenbank in einem Container zurückspielen

Beispiel:

bash
docker exec -i zabbix-db \
  mysql -u zabbix --password=PASSWORD zabbix \
  < /srv/exx/z_backups/zabbix_2025-01-01.sql

Hinweis:

  • -i streamt den Dump über stdin in den Container
  • zabbix-db ist der Containername
  • mysql -u zabbix ... zabbix verbindet sich auf die Datenbank zabbix

Der Dump wurde typischerweise mit:

bash
mysqldump -u zabbix -p --single-transaction zabbix > zabbix_2025-01-01.sql

erstellt und enthält keinen CREATE DATABASE.

4.6.2 Kompletten Server-Dump (--all-databases) zurückspielen

bash
docker exec -i mysql-db \
  mysql -u root --password=ROOTPASS \
  < /pfad/zu/dump-all.sql

Hinweis:

  • Dump enthält alle Datenbanken inkl. mysql (Benutzer und Rechte)
  • Für vollständige Neuinstallation geeignet, sonst kritisch

4.6.3 Datenbank vor dem Restore neu anlegen/aufräumen

bash
# Datenbank im Container neu anlegen
docker exec mysql-db \
  mysql -u root --password=ROOTPASS \
  -e "DROP DATABASE IF EXISTS confluencedb;
      CREATE DATABASE confluencedb
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_unicode_ci;"

# Dump in die frisch angelegte DB einspielen
docker exec -i mysql-db \
  mysql -u root --password=ROOTPASS confluencedb \
  < /pfad/zu/confluencedb.sql

4.6.4 Restore aus einem separaten Client-Container

Alternative über einen eigenen MySQL-Client-Container im selben Docker-Netzwerk:

bash
docker run --rm -i --network=my-net \
  -v /srv/backups:/backups \
  mysql:8 \
  mysql -h mysql-db -u root --password=ROOTPASS confluencedb \
  < /srv/backups/confluencedb.sql

Hinweis:

  • --network=my-net: gemeinsames Docker-Netzwerk
  • /srv/backups: Host-Verzeichnis mit Dumps

4.6.5 Typische Fehlerquellen

  • "Access denied for user …"
    → Benutzer/Passwort/Host (User@Host) prüfen
  • "Unknown database …"
    → Datenbank anlegen oder prüfen, ob der Dump CREATE DATABASE enthält
  • Container nicht erreichbar
    → docker ps, Docker-Netzwerk, Ports und Firewall prüfen

4.6.6 Alte MySQL-Daten im Docker-Setup aufräumen

Wenn eine MySQL-/MariaDB-Instanz im Docker-Setup komplett verworfen und "frisch" neu aufgebaut werden soll, kann das gemountete Datenverzeichnis auf dem Host gelöscht werden.

Achtung:

  • Danach sind alle Daten und Benutzer endgültig gelöscht
  • Nur machen, wenn ein verlässlicher Dump existiert
  • Pfad und Volume-Mapping genau kontrollieren

Beispiel (Volume auf ./data/mysql):

bash
# Container stoppen
docker compose stop joomla_db

# Datenverzeichnis entfernen (zerstörerisch!)
rm -rf ./data/mysql

# Container mit leerem Datenverzeichnis neu starten
docker compose up -d joomla_db

Beim nächsten Start initialisiert MySQL/MariaDB die Systemtabellen neu.
Anschließend kann ein Dump wie oben beschrieben eingespielt werden.

5. Binary Logging

5.1 Konfiguration (MySQL < 8.4 / MariaDB)

ini
[mysqld]
log_bin          = /var/log/mysql/mysql-bin.log
expire_logs_days = 2
max_binlog_size  = 50M

5.2 MySQL 8.4 und neuer

expire_logs_days ist entfernt, stattdessen:

ini
[mysqld]
log_bin                    = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 172800  # 2 Tage
max_binlog_size            = 50M

Zum Deaktivieren des Binary Logging log_bin auskommentieren/entfernen
(nur tun, wenn keine Replikation / Point-in-Time-Recovery benötigt wird).

6. Konfiguration (my.cnf / .my.cnf)

6.1 MySQL/MariaDB-Konfigurationsdateien

Typische Orte:

  • /etc/mysql/my.cnf (Hauptdatei mit Includes)
  • /etc/mysql/mysql.conf.d/mysqld.cnf (MySQL-Server)
  • /etc/mysql/mariadb.conf.d/50-server.cnf (MariaDB-Server)

6.2 Persönliche Client-Konfiguration (~/.my.cnf)

Erlaubt Login ohne Passwort auf der Kommandozeile:

ini
[client]
host     = localhost
user     = root
password = 123456

[mysqldump]
user     = root
password = 123456

Sicherheit:

  • Datei auf den jeweiligen Benutzer beschränken: chmod 600 ~/.my.cnf
  • Für produktive Umgebungen besser dedizierte Backup-User mit eingeschränkten Rechten nutzen

6.3 Root-Passwort ändern

Moderne Variante (MySQL 5.7+/8.x, MariaDB):

sql
ALTER USER 'root'@'localhost'
  IDENTIFIED BY 'NeuesSicheresPasswort';

Ältere Variante mit mysqladmin:

bash
mysqladmin -u root -p password

Bei Distributionen mit auth_socket-Plugin kann zuvor ein Wechsel des Plugins nötig sein.

6.4 mysql_upgrade (nur Altversionen)

Ältere MySQL-Versionen:

bash
mysql_upgrade -u root -p -h localhost

Hinweis:

  • Bis MySQL 8.0.15 relevant
  • Seit 8.0.16 wird das Schema beim Serverstart automatisch aktualisiert
  • In neueren Versionen ist mysql_upgrade entfernt

7. Monitoring mit mytop

mytop zeigt laufende Queries ähnlich wie top.

Installation (Debian/Ubuntu):

bash
apt install mytop

Verwendung:

bash
mytop -d mysql

8. Nützliche SQL-Befehle (Beispiel Mailserver)

8.1 Datenbank auswählen

sql
USE mailbase;

8.2 Tabellen anzeigen

sql
SHOW TABLES;
```

Beispielausgabe:
```
+--------------------+
| Tables_in_mailbase |
+--------------------+
| domains            |
| mailusers          |
| weiterleitungen    |
+--------------------+

8.3 Tabelleninhalte anzeigen

sql
SELECT * FROM domains;
```

Beispiel:
```
+----+-----------------------------+
| id | domain                      |
+----+-----------------------------+
|  1 | irgendwas.de                |
|  2 | nochwas.de                  |
+----+-----------------------------+

8.4 Datensätze einfügen

sql
INSERT INTO mailusers
SET account       = '[email protected]',
    maildir       = 'irgendwas.de/p.meier',
    home          = '/var/spool/maildirs',
    password      = 'DevCiphEqu',
    angelegt_von  = 'm.müller',
    angelegt_am   = CURDATE();

INSERT INTO weiterleitungen
SET forward_from  = '[email protected]',
    forward_to    = '[email protected]',
    angelegt_von  = 'm.müller',
    angelegt_am   = CURDATE();

Hinweis: In realen Mail-Setups werden Passwörter in der Regel gehasht gespeichert.

8.5 Suchen

sql
-- exakte Suche
SELECT * FROM weiterleitungen
WHERE forward_from = '[email protected]';

-- unscharfe Suche mit LIKE
SELECT * FROM weiterleitungen
WHERE forward_from LIKE '%pressestelle%';
```

Beispielausgabe:
```
+-----+-------------------------------+----------------------------+--------------+-------------+----------+
| id  | forward_from                  | forward_to                 | angelegt_von | angelegt_am | memo_txt |
+-----+-------------------------------+----------------------------+--------------+-------------+----------+
| 751 | [email protected]     | [email protected]       | m.müller     | 2015-09-08  |          |
+-----+-------------------------------+----------------------------+--------------+-------------+----------+

8.6 Löschen

sql
DELETE FROM weiterleitungen
WHERE id = 751;

8.7 Werte ändern

sql
UPDATE weiterleitungen
SET angelegt_von = 'k.lehmann'
WHERE forward_from = '[email protected]';

9. Hinweise zu Locks während mysqldump

Früher:

  • "Während des Dumps kann nicht in die Tabellen geschrieben werden."

Genauer:

  • Ohne --single-transaction und bei MyISAM-Tabellen werden Tabellen gesperrt (Schreibzugriffe blockiert)
  • Mit --single-transaction und InnoDB können Anwendungen weiter schreiben, während mysqldump einen konsistenten Snapshot liest

Für moderne Installationen mit InnoDB ist:

bash
mysqldump --single-transaction ...

die Standardempfehlung.

Back to top