mysqldump‑Leitfaden: So sichern und stellen Sie MySQL‑Datenbanken wieder her (mit Beispielen)

1. Einführung

Datenbank‑Backup und -Wiederherstellung sind grundlegend für das Datenmanagement und unverzichtbar für zuverlässige Abläufe. MySQLs mysqldump wird häufig als effizientes und flexibles Werkzeug zum Erstellen von Datenbank‑Backups eingesetzt. In diesem Leitfaden erklären wir alles von der grundlegenden Nutzung von mysqldump bis hin zu erweiterten Optionen, Wiederherstellungsmethoden und Fehlersuche im Detail. Am Ende des Artikels stellen wir zudem bewährte Vorgehensweisen und weiterführende Ressourcen vor – nutzen Sie dies also als hilfreiche Referenz, um mysqldump zu meistern.

2. Was ist mysqldump?

2.1 Überblick über mysqldump

mysqldump ist ein Befehlszeilen‑Tool zum Erstellen von Backups von MySQL‑Datenbanken. Sie können eine komplette Datenbank, bestimmte Tabellen oder nur die Daten, die bestimmten Bedingungen entsprechen, als SQL‑Skript ausgeben. Diese Dump‑Datei kann zur Wiederherstellung von Daten oder zur Migration auf einen neuen Server verwendet werden.

2.2 Häufige Anwendungsfälle

• Backup : Regelmäßige Backups erstellen, um sich auf Systemausfälle oder Datenverlust vorzubereiten.
• Datenmigration : Datenbanken zwischen Servern verschieben oder Daten in eine Entwicklungsumgebung kopieren.
• Datenanalyse : Bestimmte Datensätze für Analyse‑ und Validierungszwecke extrahieren.

3. Grundlegende Verwendung

3.1 Grundsyntax des Befehls

Die Grundsyntax für mysqldump lautet wie folgt:

mysqldump -u username -p database_name > output_file.sql

• -u username : Der Benutzername, der zum Zugriff auf die Datenbank verwendet wird.
• -p : Fordert Sie auf, das Passwort einzugeben.
• database_name : Der Name der Datenbank, die Sie sichern möchten.
• > output_file.sql : Der Zielpfad/Dateiname für die Dump‑Datei.

3.2 Optionen zur Benutzer‑Authentifizierung

• -h hostname : Der Hostname des Datenbank‑Servers (Standard ist localhost).
• -P port_number : Die Port‑Nummer, zu der verbunden werden soll (Standard ist 3306).

3.3 Beispiel: Gesamte Datenbank sichern

mysqldump -u root -p mydatabase > backup.sql

Dieser Befehl sichert alle Daten in mydatabase in die Datei backup.sql. Wenn Sie das Datum in den Backup‑Dateinamen für die Versionskontrolle aufnehmen, wird es einfacher, Ihre Backup‑Historie nachzuverfolgen.

4. Erklärung wichtiger Optionen

4.1 --all-databases (-A)

Diese Option sichert alle Datenbanken auf einmal. Sie ist nützlich, wenn Sie ein komplettes Backup des gesamten Servers erstellen möchten.

mysqldump -u root -p --all-databases > all_databases_backup.sql

4.2 --no-data (-d)

Verwenden Sie diese Option, wenn Sie nur das Tabellenschema sichern wollen, ohne Daten einzuschließen. Zum Beispiel ist sie praktisch, wenn Sie ausschließlich die Tabellenstruktur exportieren möchten, um eine Entwicklungsumgebung aufzusetzen.

mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql

4.3 --where (-w)

Verwenden Sie diese Option, wenn Sie nur die Daten sichern wollen, die bestimmten Bedingungen entsprechen. Zum Beispiel, um nur Datensätze zu sichern, bei denen die Spalte is_active den Wert 1 hat:

mysqldump -u root -p mydatabase --where="is_active=1" > filtered_data_backup.sql

4.4 --ignore-table

Verwenden Sie diese Option, um bestimmte Tabellen vom Backup auszuschließen. Das ist hilfreich, wenn es Tabellen gibt, die Sie nicht einbeziehen möchten.

mysqldump -u root -p mydatabase --ignore-table=mydatabase.table1 > partial_backup.sql

5. Praktische Beispiele

5.1 Nur bestimmte Tabellen dumpen

Wenn Sie nur bestimmte Tabellen sichern möchten, geben Sie den Tabellennamen nach dem Datenbanknamen an.

mysqldump -u root -p mydatabase table1 > table1_backup.sql

Dieser Befehl speichert nur die Daten von table1 in table1_backup.sql.

5.2 Nur Daten / Nur Schema dumpen

• Nur Daten : mysqldump -u root -p mydatabase --no-create-info > data_only_backup.sql Sichert ausschließlich die Daten und schließt die Tabellenstruktur aus.
• Nur Schema : bash mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql Sichert ausschließlich das Tabellenschema.

5.3 Bedingter Dump

Um nur die Daten zu sichern, die bestimmten Bedingungen entsprechen, verwenden Sie die Option --where.

mysqldump -u root -p mydatabase --where="created_at >= '2023-01-01'" > recent_data_backup.sql

Dieser Befehl sichert nur die Daten, bei denen created_at am oder nach dem 1. Januar 2023 liegt.

6. Wie man wiederherstellt

Um eine mit mysqldump gesicherte Datenbank wiederherzustellen, verwenden Sie den mysql-Befehl. Die Wiederherstellung ist der Prozess, eine Backupdatei zu verwenden, um eine Datenbank in einen vorherigen Zustand zurückzubringen.

6.1 Grundlegende Syntax für die Wiederherstellung

mysql -u username -p database_name < dump_file.sql

• -u username : Der Benutzername, der zum Verbinden mit der Datenbank verwendet wird.
• -p : Fordert Sie auf, das Passwort einzugeben.
• database_name : Der Name der Zieldatenbank.
• < dump_file.sql : Die Dump-Datei, die für die Wiederherstellung verwendet wird.

6.2 Beispiel: Eine Wiederherstellung ausführen

mysql -u root -p mydatabase < backup.sql

Dieser Befehl stellt Daten in mydatabase aus der Datei backup.sql wieder her.

6.3 Wichtige Hinweise zur Wiederherstellung

• Wenn die Datenbank, die Sie wiederherstellen möchten, nicht existiert, müssen Sie sie zuerst erstellen.
• Die Wiederherstellung einer großen Menge an Daten kann Zeit in Anspruch nehmen, daher ist es wichtig, im Voraus zu planen.

7. Best Practices für mysqldump

7.1 Planung von Backups

Automatisieren Sie regelmäßige Backups, indem Sie mysqldump in einem Skript programmieren und einen Planer wie cron verwenden. Im folgenden Beispiel für ein Shell-Skript wird täglich um Mitternacht ein vollständiges Backup aller Datenbanken erstellt.

#!/bin/bash
mysqldump -u root -p'password' --all-databases > /path/to/backup/all_databases_$(date +\%F).sql

7.2 Verschlüsselung von Backup-Dateien

Da Backup-Dateien sensible Informationen enthalten können, wird empfohlen, sie mit Tools wie gpg zu verschlüsseln.

gpg -c /path/to/backup/all_databases_$(date +\%F).sql

7.3 Versionskompatibilität

Beim Migrieren von Daten zwischen verschiedenen MySQL-Versionen müssen Sie auf Kompatibilitätsprobleme achten. Simulieren Sie vor dem Upgrade das Backup- und Wiederherstellungsverfahren in einer Testumgebung und überprüfen Sie die Kompatibilität.

1. Wiederherstellung von Tabellendefinitionen : mysqldump --all-databases --no-data --routines --events > dump-defs.sql Dieser Befehl dumpst nur die Tabellenstruktur, dann stellen Sie sie in der Umgebung der neueren Version wieder her, um die Kompatibilität zu überprüfen.
2. Wiederherstellung von Daten : mysqldump --all-databases --no-create-info > dump-data.sql Nach Bestätigung, dass die Tabellendefinitionen kompatibel sind, stellen Sie nur die Daten wieder her.
3. Validierung in einer Testumgebung : Um die Kompatibilität zwischen Versionen zu überprüfen, führen Sie Backup und Wiederherstellung in einer Testumgebung durch. Nach Bestätigung, dass alles korrekt funktioniert, fahren Sie mit der Migration in der Produktionsumgebung fort.

7.4 Speichern und Überprüfen von Backups

• Backups sicher speichern : Speichern Sie Backup-Dateien auf externem Speicher oder in der Cloud und aktualisieren Sie sie regelmäßig. Off-site-Speicherung schützt Daten vor physischen Ausfällen.
• Regelmäßig Wiederherstellungen überprüfen : Führen Sie regelmäßig Wiederherstellungstests durch, um zu bestätigen, dass Backups korrekt wiederhergestellt werden können. Es ist wichtig, die Überprüfung der Wiederherstellung nicht zu überspringen, falls Backups ungültig werden.

8. Fehlerbehebung

8.1 Häufige Fehler und Lösungen

• Fehler: @@GLOBAL.GTID_PURGED cannot be changed : Dieser Fehler tritt auf, wenn GTID-bezogene Probleme in MySQL 8.0 auftreten. Sie können ihn vermeiden, indem Sie die GTID-Einstellungen mit der Option --set-gtid-purged=COMMENTED auskommentieren. mysqldump -u root -p mydatabase --set-gtid-purged=COMMENTED > backup.sql
• Fehler: Not enough disk space : Wenn Sie während des Backups einer großen Datenbank den Festplattenplatz ausgehen, komprimieren Sie das Backup oder ändern Sie das Ziel. Zum Beispiel können Sie das Backup mit gzip komprimieren wie folgt: mysqldump -u root -p mydatabase | gzip > backup.sql.gz
• Fehler: Insufficient privileges : Wenn der Datenbankbenutzer nicht über ausreichende Berechtigungen verfügt, schlägt das Backup oder die Wiederherstellung fehl. Erteilen Sie die erforderlichen Berechtigungen (wie SELECT , LOCK TABLES , SHOW VIEW , etc.) und versuchen Sie es erneut.

8.2 Versionskompatibilitätsprobleme

Kompatibilitätsprobleme zwischen verschiedenen MySQL-Versionen können durch Tests vor dem Upgrade behoben werden. Insbesondere wird beim Wechsel von MySQL 5.7 zu 8.0 empfohlen, nur die Tabellendefinitionen mit der Option --no-data wiederherzustellen und die Kompatibilität zu überprüfen.

• Auf Kompatibilitätsprobleme testen : Vor dem Upgrade die Migration in einer Testumgebung simulieren, um mögliche Probleme zu identifizieren. Auf inkompatible Features oder Syntax achten und SQL‑Skripte bei Bedarf anpassen.

9. Zusammenfassung

mysqldump ist ein zuverlässiges und leistungsstarkes Werkzeug zum Sichern und Wiederherstellen von MySQL‑Datenbanken. In diesem Artikel haben wir alles von der Grundbenutzung bis zu erweiterten Optionen, bewährten Verfahren und Fehlersuche behandelt. Durch die Anwendung dieses Wissens können Sie Ihre Datenbanken effizienter schützen und verwalten, indem Sie mysqldump einsetzen.

Durch die Integration bewährter Verfahren wie das Planen von Backups und das Verschlüsseln von Dateien können Sie die Datensicherheit erhöhen und die Zuverlässigkeit Ihrer Datenbankoperationen steigern. Verwenden Sie mysqldump korrekt, um sich auf mögliche Datenbankprobleme vorzubereiten.

10. Referenzen und weiterführende Ressourcen

• Offizielle MySQL-Dokumentation

Nutzen Sie diese Ressource, um mehr über mysqldump zu erfahren und es in realen Szenarien anzuwenden. Durch regelmäßige Backups und die Überprüfung von Wiederherstellungen können Sie die Sicherheit der Datenbank gewährleisten und für unerwarteten Datenverlust gerüstet sein.