Die ultimative Anleitung zu mysqldump – Ein Datenbank-Backup-Programm

mysqldump ist ein von MySQL bereitgestelltes Befehlszeilenprogramm, mit dem Sie logische Backups von MySQL-Datenbanken erstellen können. Dieses Tool wird häufig zum Erstellen von Datenbank-Backups, zum Exportieren von Datenbanken auf andere Server und zum Migrieren von Datenbanken zwischen MySQL-Versionen oder verschiedenen Systemen verwendet. Es ist eine einfache, leistungsfähige und flexible Möglichkeit, Datenbanken zu sichern und wiederherzustellen, was es zu einem wichtigen Werkzeug für jeden Datenbankadministrator macht.

Dieses ultimative Handbuch enthält alles, was Sie über mysqldump wissen müssen, einschließlich Installation, Syntax, Anwendungsbeispiele und bewährte Verfahren.

Was ist mysqldump?

• mysqldump erstellt ein logisches Backup einer MySQL-Datenbank, das heißt, es gibt die Daten und die Struktur in Form von SQL-Befehlen aus.
• Diese SQL-Befehle können dann verwendet werden, um die Datenbank neu zu erstellen, wodurch mysqldump ideal für Backups, Migrationen und Datenübertragungen ist.
• Es unterstützt den Export ganzer Datenbanken, einzelner Tabellen oder bestimmter Daten mit verschiedenen Optionen zur Anpassung.

Grundlegende Syntax von mysqldump

Die grundlegende Syntax des mysqldump-Befehls lautet:

• [OPTIONEN]: Optionale Flags, die das Verhalten von mysqldump ändern (z.B. Angabe von Benutzer, Passwort, Host usw.).
• database_name: Der Name der Datenbank, die Sie sichern wollen.
• [Tabellen]: (Optional) Bestimmte Tabellen, die gesichert werden sollen. Wird dies nicht angegeben, wird die gesamte Datenbank gesichert.
• > backup_file.sql: Leitet die Ausgabe in eine Datei (backup_file.sql) um, die die SQL-Befehle zur Wiederherstellung der Datenbank enthält.

Voraussetzungen

• MySQL oder MariaDB auf dem Server oder Client installiert.
• Ein Benutzerkonto mit ausreichenden Rechten (SELECT- und LOCK-Berechtigungen) für die zu sichernde Datenbank.
• Zugriff auf die Kommandozeile (Terminal) unter Linux/macOS oder die Eingabeaufforderung unter Windows.

Häufig verwendete Optionen mit mysqldump

• -u oder –user: Gibt den MySQL-Benutzernamen an.
• -p oder –password: Fragt nach dem Passwort des Benutzers.
• -h oder –host: Gibt den Host an (z.B. localhost oder IP-Adresse).
• -P oder –Port: Gibt die Portnummer für den MySQL-Server an.
• –databases: Ermöglicht Ihnen die Sicherung mehrerer Datenbanken.
• –alle-Datenbanken: Sichert alle Datenbanken des Servers.
• –no-data: Exportiert nur die Datenbankstruktur (keine Daten).
• –einzelne-Transaktion: Erstellt einen konsistenten Snapshot der Datenbank, nützlich für InnoDB-Tabellen.
• –add-drop-table: Fügt DROP TABLE-Anweisungen vor CREATE TABLE-Anweisungen hinzu, um sicherzustellen, dass Tabellen gelöscht und bei der Wiederherstellung neu erstellt werden.
• –Routinen: Nimmt gespeicherte Prozeduren und Funktionen in das Backup auf.
• –auslöser: Schließt Trigger in die Sicherung ein.
• –Komprimieren: Komprimiert die Daten, die zwischen dem Client und dem MySQL-Server gesendet werden, sofern dies unterstützt wird.

Grundlegende Beispiele

Beispiel 1: Sichern einer einzelnen Datenbank

• Fragt nach dem Passwort des Root-Benutzers.
• Dumpt den Inhalt von datenbank_name in backup_file.sql.

Beispiel 2: Sichern mehrerer Datenbanken

• Verwenden Sie die Option –databases gefolgt von den Namen der Datenbanken.
• Dumpt Datenbank1 und Datenbank2 in multiple_databases_backup.sql.

Beispiel 3: Sichern aller Datenbanken

• Erstellt eine Sicherungskopie aller Datenbanken auf dem MySQL-Server.
• Enthält CREATE DATABASE- und USE-Anweisungen, so dass Sie alle Datenbanken leicht wiederherstellen können.

Beispiel 4: Sichern einer bestimmten Tabelle

• Dumpt die Struktur und die Daten einer bestimmten Tabelle (table_name) von database_name in table_backup.sql.

Beispiel 5: Sichern nur der Datenbankstruktur

• Die Option –no-data schließt die Daten aus und exportiert nur die Tabellenstrukturen.

Erweiterte Beispiele

Beispiel 6: Verwendung von –single-transaction für große Datenbanken

• Die Option –single-transaction ist ideal für InnoDB-Tabellen und gewährleistet ein konsistentes Backup ohne Sperrung von Tabellen.
• Sie wird für große Datenbanken empfohlen, da sie die Zeit, in der Tabellen gesperrt sind, minimiert.

Beispiel 7: Komprimierte Sicherung

• Verwendet gzip, um die Ausgabe von mysqldump im laufenden Betrieb zu komprimieren.
• Das Ergebnis ist eine kleinere Backup-Datei (backup_file.sql.gz), die Speicherplatz spart.

Beispiel 8: Einschließen von Stored Procedures, Funktionen und Triggern

• Die Option –routines schließt gespeicherte Prozeduren und Funktionen ein.
• Die Option –triggers schließt Trigger in das Backup ein.

Beispiel 9: Ausschluss bestimmter Tabellen

• Die Option –ignore-table schließt eine bestimmte Tabelle aus dem Backup aus.
• Sie können diese Option für jede Tabelle, die Sie ausschließen möchten, wiederholen.

Wiederherstellen eines mysqldump-Backups

Um ein mit mysqldump erstelltes Backup wiederherzustellen, benutzen Sie den Befehl mysql:

Beispiel 10: Wiederherstellen einer einzelnen Datenbank

• Fragt nach dem Root-Passwort.
• Stellt die Datenbank aus backup_file.sql wieder her.

Beispiel 11: Mehrere Datenbanken oder alle Datenbanken wiederherstellen

• Dieser Befehl kann verwendet werden, um mehrere Datenbanken oder alle Datenbanken wiederherzustellen, wenn all_databases_backup.sql mit den Optionen –databases oder –all-databases erstellt wurde.

Beispiel 12: Wiederherstellen aus einer komprimierten Sicherung

• Verwendet gunzip zum Dekomprimieren der Sicherungsdatei und leitet die Ausgabe an den Befehl mysql weiter.

Bewährte Praktiken für die Verwendung von mysqldump

1. Verwenden Sie –single-transaction für InnoDB:
   • Diese Option gewährleistet ein konsistentes Backup, ohne die Datenbanktabellen zu sperren, was besonders für große Datenbanken wichtig ist.
2. Planen Sie regelmäßige Backups:
   • Verwenden Sie Cron-Jobs unter Linux, um den Sicherungsprozess zu automatisieren. Erstellen Sie zum Beispiel einen täglichen Backup-Auftrag:
     0 2 * * * mysqldump -u root -pIhrPasswort datenbank_name > /pfad/zu/backup/datenbank_name_$(datum \%F).sql
   • Dieser Auftrag erstellt jeden Tag um 2 Uhr morgens ein Backup.
3. Backups offsite aufbewahren:
   • Speichern Sie Backups auf einem anderen Server oder in einem Cloud-Speicher wie AWS S3 oder Google Drive, um die Wiederherstellung von Daten im Falle eines Hardwareausfalls sicherzustellen.
4. Überprüfen Sie Backups:
   • Überprüfen Sie regelmäßig, ob Ihre Backups ordnungsgemäß wiederhergestellt werden können, indem Sie sie in einer separaten Umgebung testen.
5. Verwenden Sie Komprimierung für große Datenbanken:
   • Verwenden Sie gzip oder bzip2, um die Sicherungsdateien zu komprimieren und Speicherplatz zu sparen.
6. Überlegungen zur Sicherheit:
   • Vermeiden Sie es, Passwörter direkt in der Befehlszeile anzugeben, da sie für andere Benutzer sichtbar sein könnten. Verwenden Sie stattdessen eine Konfigurationsdatei oder fragen Sie nach dem Passwort.
   • Schützen Sie Sicherungsdateien mit entsprechenden Dateiberechtigungen, um unbefugten Zugriff zu verhindern.

Fazit

mysqldump ist ein vielseitiges und leistungsstarkes Werkzeug für die Verwaltung von Backups in MySQL-Datenbanken. Wenn Sie seine Syntax und Optionen verstehen, können Sie maßgeschneiderte Backups erstellen, die Ihren speziellen Anforderungen entsprechen, egal ob Sie mit großen Datenbanken arbeiten, Daten migrieren oder automatische Backups einrichten. Befolgen Sie bewährte Verfahren, um sicherzustellen, daß Ihre Daten stets sicher sind und bei Bedarf wiederhergestellt werden können.