Der ultimative Leitfaden zu mysqldump: MySQL-Datenbank-Backup, Wiederherstellung und Automatisierung

mysqldump ist ein Befehlszeilen-Dienstprogramm, das mit MySQL und MariaDB gebündelt wird und logische Backups erstellt, indem es Datenbankobjekte und Daten als eine Folge von SQL-Anweisungen serialisiert. Die resultierende Dump-Datei kann eine identische Datenbank auf jedem kompatiblen Server wiederherstellen, was es zum Industriestandard-Tool für Backups, serverübergreifende Migrationen, Versions-Upgrades und Disaster-Recovery-Workflows macht.

Im Gegensatz zu physischen Backup-Tools wie Percona XtraBackup oder MySQL Enterprise Backup arbeitet mysqldump auf der SQL-Ebene — es liest Live-Daten über das MySQL-Protokoll und schreibt portables, menschenlesbares SQL. Diese Portabilität ist seine größte Stärke und im großen Maßstab seine primäre Einschränkung.

Was mysqldump tatsächlich im Hintergrund tut

Wenn Sie mysqldump aufrufen, verbindet sich der Client mit dem MySQL-Server, fragt das Information Schema und das Data Dictionary ab und gibt einen Stream von `CREATE DATABASE`-, `CREATE TABLE`-, `INSERT`- und DDL-Anweisungen an die Standardausgabe aus. Sie leiten diesen Stream in eine Datei, eine Pipe oder ein Komprimierungsdienstprogramm um.

Für InnoDB-Tabellen mit `–single-transaction` öffnet mysqldump eine Transaktion mit wiederholbarem Lesen, bevor Daten gelesen werden. Dies liefert Ihnen einen konsistenten Point-in-Time-Snapshot ohne globale Lesesperren zu erwerben — die Datenbank bleibt während des Dumps vollständig beschreibbar. Für MyISAM-Tabellen existiert kein solcher Mechanismus; mysqldump fällt auf `FLUSH TABLES WITH READ LOCK` zurück, was Schreibvorgänge kurzzeitig blockiert.

Das Verständnis dieser Unterscheidung ist entscheidend, bevor Sie mysqldump für Produktions-Workloads wählen. Wenn Ihr Schema InnoDB- und MyISAM-Tabellen mischt, ist `–single-transaction` allein unzureichend — Sie benötigen `–lock-all-tables` oder ein Wartungsfenster.

Voraussetzungen und erforderliche Berechtigungen

Bevor Sie einen Dump-Befehl ausführen, überprüfen Sie Folgendes:

• MySQL oder MariaDB ist installiert und zugänglich (lokaler Socket oder TCP/IP).
• Der Backup-Benutzer verfügt über die minimal erforderlichen Berechtigungen:
• `SELECT` für alle Zieltabellen
• `LOCK TABLES` (erforderlich, es sei denn, `–single-transaction` wird ausschließlich mit InnoDB verwendet)
• `SHOW VIEW` zum Einschließen von Views
• `TRIGGER` zum Einschließen von Triggern
• `PROCESS` bei Verwendung von `–single-transaction` auf MySQL 8+
• `RELOAD` für `FLUSH TABLES WITH READ LOCK`
• `REPLICATION CLIENT` wenn Sie Binary-Log-Koordinaten für die Replikationseinrichtung benötigen

Erstellen Sie einen dedizierten Backup-Benutzer, anstatt Dumps als Root auszuführen:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS, RELOAD, REPLICATION CLIENT

ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Das Ausführen von mysqldump als Root mit einem in den Shell-Befehl eingebetteten Passwort legt Anmeldeinformationen in Prozesslisten und der Shell-History offen — ein erhebliches Sicherheitsrisiko auf jedem gemeinsam genutzten oder Mehrbenutzersystem.

Grundlegende Syntax

“`

mysqldump [OPTIONS] database_name [table1 table2 …] > backup_file.sql

“`

Vollständige Optionsreferenz

Verbindungsoptionen

Bereichsoptionen

Konsistenz- und Sperroptionen

Ausgabe- und Inhaltsoptionen

mysqldump vs. alternative Backup-Methoden

Die Wahl der richtigen Backup-Strategie hängt von der Datenbankgröße, den RTO/RPO-Anforderungen und der Infrastruktur ab. Hier ist ein Vergleich von mysqldump mit den gängigsten Alternativen:

Für Datenbanken unter 10–20 GB in einer VPS Hosting-Umgebung bleibt mysqldump die praktischste und portabelste Lösung. Jenseits dieser Schwelle bieten physische Backup-Tools deutlich schnellere Backup- und Wiederherstellungsfenster.

Praktische Verwendungsbeispiele

Beispiel 1: Eine einzelne Datenbank sichern

“`bash

mysqldump -u backup_user -p database_name > /backups/database_name_$(date +%F).sql

“`

Die `$(date +%F)`-Substitution fügt automatisch das ISO-Datum (z. B. `2025-07-15`) an den Dateinamen an und verhindert so Überschreibungen.

Beispiel 2: Mehrere bestimmte Datenbanken sichern

“`bash

mysqldump -u backup_user -p –databases app_db analytics_db > /backups/multi_db_backup.sql

“`

Das `–databases`-Flag veranlasst mysqldump, `CREATE DATABASE`- und `USE`-Anweisungen auszugeben, wodurch der Dump für die Wiederherstellung eigenständig wird.

Beispiel 3: Alle Datenbanken sichern

“`bash

mysqldump -u backup_user -p –all-databases –events –routines –triggers

> /backups/full_server_$(date +%F).sql

“`

Schließen Sie immer `–events`, `–routines` und `–triggers` in vollständige Server-Dumps ein. Diese Objekte werden ohne explizite Flags stillschweigend ausgelassen.

Beispiel 4: Konsistentes InnoDB-Backup (produktionssicher)

“`bash

mysqldump -u backup_user -p

–single-transaction

–flush-logs

–source-data=2

–routines –triggers –events

database_name > /backups/database_name_$(date +%F).sql

“`

`–flush-logs` rotiert das Binary Log zu Beginn des Dumps. `–source-data=2` schreibt den aktuellen Binary-Log-Dateinamen und die Position als SQL-Kommentar, was eine Point-in-Time-Wiederherstellung durch Wiedergabe nachfolgender Binlogs ab dieser Position ermöglicht.

Beispiel 5: Komprimiertes Backup mit gzip

“`bash

mysqldump -u backup_user -p database_name | gzip -9 > /backups/database_name_$(date +%F).sql.gz

“`

Für CPU-begrenzte Server ersetzen Sie `pigz` (paralleles gzip), um mehrere Kerne zu nutzen:

“`bash

mysqldump -u backup_user -p database_name | pigz -9 > /backups/database_name_$(date +%F).sql.gz

“`

Beispiel 6: Schema-Only-Backup (Struktur ohne Daten)

“`bash

mysqldump -u backup_user -p –no-data database_name > /backups/schema_only.sql

“`

Nützlich für die Versionskontrolle Ihres Schemas in Git oder die Bereitstellung in einer Staging-Umgebung ohne Kopieren von Produktionsdaten.

Beispiel 7: Data-Only-Backup (kein Schema)

“`bash

mysqldump -u backup_user -p –no-create-info database_name > /backups/data_only.sql

“`

Verwenden Sie dies, wenn das Zielschema bereits vorhanden ist und Sie nur Daten befüllen oder aktualisieren müssen.

Beispiel 8: Eine einzelne Tabelle sichern

“`bash

mysqldump -u backup_user -p database_name orders > /backups/orders_table_$(date +%F).sql

“`

Beispiel 9: Eine gefilterte Teilmenge von Zeilen exportieren

“`bash

mysqldump -u backup_user -p database_name orders

–where="created_at >= '2025-01-01' AND status='completed'"

> /backups/orders_2025_completed.sql

“`

Die Option `–where` wird zu wenig genutzt, ist aber äußerst leistungsfähig für partielle Exporte, Datenarchivierung und das Debuggen bestimmter Datensätze.

Beispiel 10: Bestimmte Tabellen ausschließen

“`bash

mysqldump -u backup_user -p database_name

–ignore-table=database_name.cache

–ignore-table=database_name.sessions

> /backups/database_name_no_cache.sql

“`

Das Ausschließen großer, kurzlebiger Tabellen (Caches, Session-Stores, Log-Tabellen) kann die Dump-Größe und -Dauer um eine Größenordnung reduzieren.

Beispiel 11: Gespeicherte Prozeduren, Funktionen und Trigger einschließen

“`bash

mysqldump -u backup_user -p –routines –triggers –events database_name > /backups/full_backup.sql

“`

Beispiel 12: Remote-Datenbank-Backup

“`bash

mysqldump -u backup_user -p -h 192.168.1.100 -P 3306 database_name

“`

Beim Sichern eines Remote-Servers wird der Datenverkehr standardmäßig unverschlüsselt übertragen. Fügen Sie `–ssl-ca`-, `–ssl-cert`- und `–ssl-key`-Flags hinzu oder tunneln Sie über SSH:

“`bash

ssh user@remote-server "mysqldump -u backup_user -p database_name | gzip"

> /backups/remote_db_$(date +%F).sql.gz

“`

Wiederherstellen eines mysqldump-Backups

Eine einzelne Datenbank wiederherstellen

“`bash

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Wenn die Zieldatenbank noch nicht existiert, erstellen Sie sie zuerst:

“`bash

mysql -u root -p -e "CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Alle Datenbanken aus einem vollständigen Server-Dump wiederherstellen

“`bash

mysql -u root -p < /backups/full_server_2025-07-15.sql

“`

Da `–all-databases` `CREATE DATABASE`- und `USE`-Anweisungen einbettet, wird kein Zieldatenbank-Argument benötigt.

Aus einem komprimierten Backup wiederherstellen

“`bash

gunzip < /backups/database_name_2025-07-15.sql.gz | mysql -u root -p database_name

“`

Oder mit Prozesssubstitution:

“`bash

mysql -u root -p database_name < <(gunzip -c /backups/database_name_2025-07-15.sql.gz)

“`

Eine einzelne Tabelle aus einem vollständigen Datenbank-Dump wiederherstellen

Dies ist ein häufiges Betriebsszenario, das die ursprüngliche Dump-Datei nicht trivial macht. Verwenden Sie `sed` oder `grep`, um den relevanten Abschnitt zu extrahieren:

“`bash

sed -n '/^– Table structure for table `orders`/,/^– Table structure for table `/p'

backup_file.sql | head -n -1 | mysql -u root -p database_name

“`

Alternativ verwenden Sie `mysql_extract_table.sh` oder importieren Sie in eine temporäre Datenbank und kopieren Sie die Tabelle:

“`bash

mysql -u root -p temp_restore < backup_file.sql

mysql -u root -p -e "INSERT INTO database_name.orders SELECT * FROM temp_restore.orders;"

“`

Point-in-Time-Wiederherstellung mit Binary Logs

Wenn Ihr Dump mit `–source-data=2` erstellt wurde und Binary Logging aktiviert ist, können Sie zu jedem Zeitpunkt nach dem Dump wiederherstellen:

1. Identifizieren Sie die Binary-Log-Position aus dem Dump-Datei-Header-Kommentar.
2. Stellen Sie den Basis-Dump wieder her.
3. Wenden Sie nachfolgende Binary-Log-Ereignisse bis zum gewünschten Zeitstempel an:

“`bash

mysqlbinlog –start-position=154 –stop-datetime="2025-07-15 14:30:00"

/var/lib/mysql/binlog.000042 | mysql -u root -p database_name

“`

Backups mit Cron automatisieren

Einfacher täglicher Backup-Job

Speichern Sie Anmeldeinformationen in `~/.my.cnf`, anstatt sie in Cron-Befehlen einzubetten:

“`ini

[mysqldump]

user=backup_user

password=StrongPassword!

“`

Strikte Berechtigungen setzen:

“`bash

chmod 600 ~/.my.cnf

“`

Dann den Cron-Job erstellen:

“`bash

crontab -e

“`

“`

Daily compressed backup at 02:00, retained for 30 days

0 2 * * * mysqldump –single-transaction –routines –triggers –events database_name

Delete backups older than 30 days

10 2 * * * find /backups/ -name "*.sql.gz" -mtime +30 -delete

“`

Produktionsreifes Backup-Skript

Für Dedicated Servers, die mehrere Datenbanken hosten, verarbeitet ein robusteres Skript Fehlerprotokollierung, Festplattenplatzprüfungen und Remote-Auslagerung:

“`bash

#!/bin/bash

BACKUP_DIR="/backups/mysql"

RETENTION_DAYS=30

LOG_FILE="/var/log/mysql_backup.log"

DATE=$(date +%F_%H-%M)

DATABASES=$(mysql –defaults-file=/etc/mysql/backup.cnf -e "SHOW DATABASES;"

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do

OUTPUT="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

mysqldump –defaults-file=/etc/mysql/backup.cnf

–single-transaction –routines –triggers –events

"$DB" | gzip -9 > "$OUTPUT"

if [ $? -eq 0 ]; then

echo "$(date): SUCCESS – $DB -> $OUTPUT" >> "$LOG_FILE"

else

echo "$(date): FAILURE – $DB" >> "$LOG_FILE"

fi

done

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -delete

“`

Sicherheitshärtung für mysqldump-Operationen

Credential-Management ist der am häufigsten vernachlässigte Aspekt der Backup-Sicherheit. Übergeben Sie `-pYourPassword` niemals direkt in der Befehlszeile — es ist in der `ps aux`-Ausgabe und der Shell-History sichtbar. Verwenden Sie stattdessen einen dieser Ansätze:

• `~/.my.cnf` mit `chmod 600` (benutzerspezifisch)
• `/etc/mysql/backup.cnf` mit `chmod 640`, im Besitz von Root, lesbar durch die Backup-Gruppe
• Umgebungsvariable `MYSQL_PWD` (sichtbar in `/proc`, nur in isolierten Containern verwenden)
• MySQL Vault oder HashiCorp Vault für Enterprise-Umgebungen

Backup-Dateiberechtigungen müssen restriktiv sein:

“`bash

chmod 640 /backups/database_name_2025-07-15.sql.gz

chown root:backup_group /backups/database_name_2025-07-15.sql.gz

“`

Verschlüsselung im Ruhezustand: Für sensible Daten verschlüsseln Sie Backup-Dateien vor dem Speichern oder Übertragen:

“`bash

mysqldump –single-transaction database_name

> /backups/database_name_$(date +%F).sql.gz.enc

“`

Transportverschlüsselung: Beim Dumpen von einem Remote-Server verwenden Sie immer SSL/TLS oder einen SSH-Tunnel. In einer VPS mit cPanel-Umgebung übernimmt die Backup-Oberfläche von cPanel dies automatisch, aber manuelle mysqldump-Operationen erfordern explizite SSL-Flags.

Häufige Fallstricke und wie man sie vermeidet

Zeichensatz-Diskrepanzen sind die häufigste Ursache für beschädigte Wiederherstellungen. Geben Sie den Zeichensatz immer explizit an:

“`bash

mysqldump –default-character-set=utf8mb4 database_name > backup.sql

mysql –default-character-set=utf8mb4 database_name < backup.sql

“`

Fehlendes `–column-statistics=0` verursacht Fehler, wenn ein MySQL 8.0 Client von einem MySQL 5.7 oder MariaDB Server dumpt. Der MySQL 8 Client versucht, Spaltenstatistiken zu dumpen, die ältere Server nicht unterstützen:

“`bash

mysqldump –column-statistics=0 -u backup_user -p database_name > backup.sql

“`

Vergessen von `–routines`, `–triggers` und `–events` lässt kritische Datenbankobjekte stillschweigend aus. Diese Flags sind standardmäßig nicht aktiviert (außer `–triggers`) und werden bei Ad-hoc-Dumps häufig vergessen.

Große Tabellen-Dumps verursachen OOM: mysqldump puffert standardmäßig ganze Ergebnismengen im Speicher. Für sehr große Tabellen fügen Sie `–quick` hinzu (in den meisten Versionen standardmäßig aktiviert, aber es lohnt sich zu überprüfen), um Zeilen einzeln zu streamen anstatt zu puffern:

“`bash

mysqldump –quick –single-transaction database_name > backup.sql

“`

Wiederherstellung in einer anderen MySQL-Version: Dumps von MySQL 8.0 können Syntax enthalten, die in MySQL 5.7 nicht unterstützt wird (z. B. funktionale Indizes, unsichtbare Spalten). Testen Sie Wiederherstellungen immer in einer versionsgleichen Umgebung, bevor Sie sich auf versionsübergreifende Migrationen verlassen.

Auto-Increment-Wert-Drift: Wenn Sie eine Tabelle in ein vorhandenes Schema wiederherstellen, das bereits Zeilen enthält, schlagen `INSERT`-Anweisungen bei Primärschlüsselkonflikten fehl, es sei denn, Sie fügen `–add-drop-table` ein oder leeren die Zieltabelle manuell zuerst.

Verwendung von mysqldump für Datenbankmigrationen

mysqldump ist der Standardansatz für die Migration von Datenbanken zwischen Servern — zum Beispiel beim Umzug einer WordPress-Site von Shared Web Hosting auf einen VPS oder beim Wechsel zu einer VPS Control Panels-Umgebung mit mehr Ressourcen.

Der empfohlene Migrations-Workflow:

1. Die Quelldatenbank dumpen mit vollständigen Optionen:

“`bash

mysqldump –single-transaction –routines –triggers –events

–default-character-set=utf8mb4 source_db | gzip > migration.sql.gz

“`

1. Sicher übertragen mit rsync über SSH:

“`bash

rsync -avz -e ssh migration.sql.gz user@target-server:/tmp/

“`

1. Die Zieldatenbank erstellen mit passendem Zeichensatz:

“`bash

mysql -u root -p -e "CREATE DATABASE target_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

“`

1. Wiederherstellen und verifizieren:

“`bash

gunzip < /tmp/migration.sql.gz | mysql -u root -p target_db

mysql -u root -p target_db -e "SHOW TABLES; SELECT COUNT(*) FROM critical_table;"

“`

1. Anwendungskonfiguration aktualisieren, um auf den neuen Datenbankhost zu verweisen.

Für Anwendungen, die auch auf E-Mail-Infrastruktur angewiesen sind, stellen Sie sicher, dass DNS-Einträge und E-Mail-Hosting-Konfigurationen parallel zur Datenbankmigration aktualisiert werden, um Dienstunterbrechungen zu vermeiden.

Backup-Integrität verifizieren

Ein Backup, das nie getestet wurde, ist kein Backup — es ist eine ungetestete Annahme. Implementieren Sie eine Verifizierungsroutine:

“`bash

#!/bin/bash

Restore backup to a test database and verify row counts

TEST_DB="backup_verify_$(date +%s)"

BACKUP_FILE="/backups/database_name_$(date +%F).sql.gz"

mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

gunzip < "$BACKUP_FILE" | mysql -u root -p "$TEST_DB"

PROD_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM database_name.orders;")

TEST_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM $TEST_DB.orders;")

if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then

echo "Backup verified: row counts match ($PROD_COUNT rows)"

else

echo "BACKUP VERIFICATION FAILED: prod=$PROD_COUNT, test=$TEST_COUNT"

fi

mysql -u root -p -e "DROP DATABASE $TEST_DB;"

“`

Führen Sie dieses Verifizierungsskript wöchentlich über Cron aus und benachrichtigen Sie bei Fehlern.

Entscheidungsmatrix: Wann mysqldump verwenden

Technische Schlüssel-Checkliste

• Verwenden Sie immer `–single-transaction` für reine InnoDB-Datenbanken, um Schreibsperren während des Backups zu vermeiden.
• Schließen Sie immer `–routines –triggers –events` in jeden Dump ein, der als vollständiges Backup gedacht ist.
• Speichern Sie Anmeldeinformationen in `~/.my.cnf` oder `/etc/mysql/backup.cnf` mit `chmod 600/640` — niemals inline in Skripten oder Cron-Befehlen.
• Fügen Sie `–column-statistics=0` hinzu, wenn Sie einen MySQL 8.0 Client gegen einen MySQL 5.7 oder MariaDB Server verwenden.
• Geben Sie immer `–default-character-set=utf8mb4` sowohl beim Dump als auch bei der Wiederherstellung an, um Zeichenkodierungskorruption zu verhindern.
• Komprimieren Sie alle Backups mit gzip oder pigz; verschlüsseln Sie sensible Dumps mit AES-256 vor der Offsite-Übertragung.
• Schließen Sie `–flush-logs –source-data=2` in Produktions-Dumps ein, um die Point-in-Time-Wiederherstellung über Binary Logs zu ermöglichen.
• Automatisieren Sie die Aufbewahrungsbereinigung mit `find … -mtime +N -delete`, um Festplattenerschöpfung zu verhindern.
• Testen Sie Wiederherstellungen planmäßig — überprüfen Sie Zeilenzahlen und stichprobenartig die Datenintegrität gegenüber der Produktion.
• Verwenden Sie für Schemas mit gemischten Engines `–lock-all-tables` anstelle von `–single-transaction`, um Konsistenz zu gewährleisten.

Häufig gestellte Fragen

Sperrt mysqldump Tabellen während des Backups?

Mit `–single-transaction` auf einer reinen InnoDB-Datenbank werden keine Tabellensperren über ein kurzes anfängliches Flush hinaus erworben. MyISAM-Tabellen erfordern immer eine Lesesperre (`LOCK TABLES`), da ihnen Transaktionsunterstützung fehlt. Schemas mit gemischten Engines erfordern `–lock-all-tables` für einen konsistenten Snapshot, was Schreibvorgänge für die Dauer des Dumps blockiert.

Wie sichere ich nur das Datenbankschema ohne Daten?

Verwenden Sie das `–no-data`-Flag: `mysqldump -u backup_user -p –no-data database_name > schema.sql`. Dies exportiert alle `CREATE TABLE`-, `CREATE VIEW`-Anweisungen, gespeicherte Prozeduren und Trigger ohne `INSERT`-Anweisungen.

Warum schlägt mein mysqldump mit „column statistics”-Fehlern fehl?

Dies tritt auf, wenn ein MySQL 8.0 Client sich mit einem MySQL 5.7 oder MariaDB Server verbindet. Fügen Sie `–column-statistics=0` zu Ihrem Befehl hinzu. Alternativ aktualisieren Sie den Server auf MySQL 8.0 oder verwenden Sie eine Client-Binärdatei, die zur Serverversion passt.

Kann mysqldump inkrementelle Backups durchführen?

Nein. mysqldump erstellt immer einen vollständigen logischen Dump des angegebenen Bereichs. Inkrementelle Backup-Fähigkeit erfordert Binary-Log-Archivierung (`mysqlbinlog`) kombiniert mit einem Basis-mysqldump, der mit `–flush-logs –source-data=2` erstellt wurde. Echte inkrementelle physische Backups erfordern Percona XtraBackup oder MySQL Enterprise Backup.

Was ist der sicherste Weg, mysqldump zu automatisieren, ohne Passwörter preiszugeben?

Erstellen Sie einen dedizierten MySQL-Backup-Benutzer mit minimal erforderlichen Berechtigungen, speichern Sie seine Anmeldeinformationen in einem `[mysqldump]`-Abschnitt von `~/.my.cnf` oder einer separaten Optionsdatei mit `chmod 600`, und referenzieren Sie sie mit `–defaults-file=/path/to/backup.cnf`. Dieser Ansatz hält Anmeldeinformationen vollständig aus Prozesslisten, Shell-History und Cron-Job-Definitionen heraus.