22 Datenbank administrieren

22.1 Backup und Restore

22.1.1 Backup mit mysqldump

mysqldump ist ein Kommandozeilenwerkzeug zum Erstellen von Backups:

# Komplette Datenbank sichern
mysqldump -u username -p datenbankname > backup.sql

# Bestimmte Tabellen sichern
mysqldump -u username -p datenbankname tabelle1 tabelle2 > backup.sql

# Mit zusätzlichen Optionen
mysqldump -u username -p \
    --add-drop-database \
    --add-drop-table \
    --extended-insert \
    --complete-insert \
    datenbankname > backup.sql

Wichtige Optionen:

--add-drop-database: Fügt DROP DATABASE hinzu
--add-drop-table: Fügt DROP TABLE hinzu
--extended-insert: Mehrere INSERT-Anweisungen zusammenfassen
--complete-insert: Vollständige INSERT-Anweisungen mit Spaltennamen
--no-data: Nur Struktur, keine Daten
--routines: Stored Procedures und Funktionen einschließen
--triggers: Trigger einschließen
--events: Events einschließen

22.1.2 Restore mit mysqldump

Das Wiedereinspielen eines Backups:

# Backup einspielen
mysql -u username -p datenbankname < backup.sql

# Über die MySQL-Konsole
mysql> source backup.sql

# Mit Fortschrittsanzeige
pv backup.sql | mysql -u username -p datenbankname

22.2 Benutzerrechte

22.2.1 Benutzerrechte und Privilegien

MySQL kennt verschiedene Arten von Rechten:

Datenbankrechte:

-- Alle Rechte auf Datenbankebene
GRANT ALL PRIVILEGES ON datenbank.* TO 'benutzer'@'localhost';

-- Spezifische Rechte
GRANT SELECT, INSERT, UPDATE ON datenbank.* TO 'benutzer'@'localhost';

Tabellenrechte:

-- Alle Rechte auf Tabellenebene
GRANT ALL PRIVILEGES ON datenbank.tabelle TO 'benutzer'@'localhost';

-- Spezifische Rechte
GRANT SELECT, UPDATE ON datenbank.tabelle TO 'benutzer'@'localhost';

Spaltenrechte:

-- Rechte für bestimmte Spalten
GRANT SELECT (id, name), UPDATE (email) 
ON datenbank.tabelle TO 'benutzer'@'localhost';

Wichtige Privilegien:

SELECT: Daten lesen
INSERT: Daten einfügen
UPDATE: Daten ändern
DELETE: Daten löschen
CREATE: Tabellen erstellen
DROP: Tabellen löschen
ALTER: Tabellen ändern
INDEX: Indizes verwalten
EXECUTE: Stored Procedures ausführen

22.2.2 Benutzer anlegen/Recht zuweisen

22.2.2.1 CREATE USER

Neue Benutzer anlegen:

-- Einfacher Benutzer
CREATE USER 'username'@'localhost' 
IDENTIFIED BY 'passwort';

-- Mit zusätzlichen Optionen
CREATE USER 'username'@'%' 
IDENTIFIED BY 'passwort'
PASSWORD EXPIRE INTERVAL 90 DAY
ACCOUNT LOCK;

-- Mit Ressourcenbeschränkungen
CREATE USER 'username'@'localhost'
IDENTIFIED BY 'passwort'
WITH MAX_QUERIES_PER_HOUR 1000
     MAX_UPDATES_PER_HOUR 500
     MAX_CONNECTIONS_PER_HOUR 100
     MAX_USER_CONNECTIONS 10;

22.2.2.2 GRANT

Rechte zuweisen:

-- Alle Rechte auf Datenbankebene
GRANT ALL PRIVILEGES 
ON datenbank.* 
TO 'username'@'localhost';

-- Spezifische Rechte
GRANT SELECT, INSERT, UPDATE, DELETE
ON datenbank.tabelle
TO 'username'@'localhost';

-- Mit Einschränkungen
GRANT SELECT, UPDATE (gehalt)
ON datenbank.mitarbeiter
TO 'username'@'localhost'
WITH GRANT OPTION;

-- Rechte übernehmen
FLUSH PRIVILEGES;

22.2.2.3 REVOKE

Rechte entziehen:

-- Alle Rechte entziehen
REVOKE ALL PRIVILEGES 
ON datenbank.* 
FROM 'username'@'localhost';

-- Spezifische Rechte entziehen
REVOKE UPDATE, DELETE
ON datenbank.tabelle
FROM 'username'@'localhost';

-- GRANT OPTION entziehen
REVOKE GRANT OPTION
ON datenbank.*
FROM 'username'@'localhost';

Praktische Beispiele:

Benutzer für Anwendung:

-- Anwendungsbenutzer erstellen
CREATE USER 'app_user'@'%' 
IDENTIFIED BY 'sicheres_passwort';

-- Minimale Rechte zuweisen
GRANT SELECT, INSERT, UPDATE
ON anwendung.*
TO 'app_user'@'%';

-- Stored Procedures ausführen
GRANT EXECUTE
ON anwendung.*
TO 'app_user'@'%';

Readonly-Benutzer:

-- Benutzer für Reporting
CREATE USER 'report_user'@'localhost'
IDENTIFIED BY 'passwort';

-- Nur Leserechte
GRANT SELECT
ON reporting_db.*
TO 'report_user'@'localhost';

Administrator-Benutzer:

-- Admin mit allen Rechten
CREATE USER 'admin'@'localhost'
IDENTIFIED BY 'sehr_sicheres_passwort';

-- Volle Rechte
GRANT ALL PRIVILEGES
ON *.* 
TO 'admin'@'localhost'
WITH GRANT OPTION;

Tipps für die Administration:

Regelmäßige Backups:

# Tägliches Backup-Skript
#!/bin/bash
DATUM=$(date +%Y%m%d)
mysqldump -u backup_user -p \
    --add-drop-database \
    --events --routines --triggers \
    meine_db > backup_$DATUM.sql

Benutzer überprüfen:

-- Aktive Benutzer anzeigen
SELECT user, host FROM mysql.user;

-- Benutzerrechte anzeigen
SHOW GRANTS FOR 'username'@'localhost';

Ressourcennutzung überwachen:

-- Aktive Prozesse
SHOW PROCESSLIST;

-- Tabellengröße
SELECT 
    table_name,
    table_rows,
    data_length/1024/1024 AS data_size_mb,
    index_length/1024/1024 AS index_size_mb
FROM information_schema.TABLES
WHERE table_schema = 'meine_db';