MySQL-Datenbank sichern und wiederherstellen mit mysqldump

Das Tool mysqldump ist ein integriertes Dienstprogramm von MySQL zur Sicherung und Wiederherstellung von Datenbanken. Es extrahiert sowohl die Struktur als auch die Daten einer Datenbank in eine textbasierte Datei, die später zur Wiederherstellung verwendet werden kann. Normalerweise enthält diese Datei SQL-Befehle wie CREATE, INSERT und DROP. Alternativ lassen sich auch Ausgaben in anderen Formaten generieren.

Diese Anleitung zeigt dir, wie du mit mysqldump eine oder mehrere MySQL-Datenbanken in Formate wie .sql, .csv oder .txt exportierst und sie anschließend über die MySQL-Befehlszeile wiederherstellst.

Voraussetzungen

Stelle vor dem Start sicher, dass folgende Bedingungen erfüllt sind:

• Zugriff auf einen laufenden MySQL-Datenbankserver
• Ein installierter MySQL-Client auf deinem System

Überblick zur Syntax von mysqldump

Das Dienstprogramm mysqldump ist Teil der MySQL-Client-Tools und folgt folgender Befehlssyntax:

mysqldump -h [host] -u [benutzer] -P [port] -p [datenbank] > [export-datei]

Erklärung der Parameter:

• -h [host]: Optional. Hostname des MySQL-Servers, Standard ist localhost.
• -u [benutzer]: Benutzername mit den Rechten SELECT, LOCK, VIEW und TRIGGER.
• -P [port]: Optional. Port des MySQL-Servers.
• -p: Fordert zur Passworteingabe auf.
• [datenbank]: Name der zu exportierenden Datenbank.
• > [export-datei]: Zielpfad der Ausgabedatei.

Unterstützte Exportformate

Mit mysqldump kannst du Daten in verschiedenen Formaten sichern, darunter:

• .sql: Standard-SQL-Datei mit Struktur und Daten
• .sql.gz: Gzip-komprimierte SQL-Datei
• .sql.zip: ZIP-Archiv einer SQL-Datei
• .sql.tar: Tar-Archiv einer SQL-Datei
• .bak: Allgemeine Backup-Datei
• .csv: Kommagetrennte Textdatei (CSV)
• .txt: Reine Textdatei
• .xml: XML-strukturierte Datei
• .dump: Vollständiger Dump, vergleichbar mit einer SQL-Datei

Vorbereitung der MySQL-Datenbank für den Export

Bevor du die Datenbank exportierst, verbinde dich mit dem Server und stelle sicher, dass die gewünschte Datenbank vorhanden ist. Wenn sie aktiv verwendet wird, empfiehlt sich ein Sperren oder kurzzeitiges Herunterfahren. Gehe wie folgt vor:

1. Verbinde dich mit dem MySQL-Server über ein Konto mit ausreichenden Rechten:

$ mysql -u root -p

Gib das Passwort ein, sobald du dazu aufgefordert wirst.

2. Zeige alle verfügbaren Datenbanken an:

mysql> SHOW DATABASES;

Beispielausgabe:

+--------------------+
| Database           |
+--------------------+
| exampledb          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

3. Wechsle in die gewünschte Datenbank:

mysql> USE exampledb;

4. Liste alle Tabellen in der Datenbank auf:

mysql> SHOW TABLES;

5. Prüfe, ob du Lesezugriff auf eine Tabelle hast:

mysql> SELECT * FROM table_name;

MySQL-Datenbank mit mysqldump exportieren

Um eine oder mehrere Datenbanken zu sichern, befolge die folgenden Schritte. Stelle sicher, dass dein MySQL-Benutzer die nötigen Berechtigungen hat.

1. Erstelle einen Ordner für deine Backups:

$ mkdir database-backups

2. Wechsle in den soeben erstellten Ordner:

$ cd database-backups

3. Exportiere eine einzelne Datenbank, z. B. exampledb:

$ mysqldump -u root -p exampledb > exampledb.sql

Um während des Exports alle Tabellen zu sperren:

$ mysqldump -u root -p --lock-all-tables exampledb > exampledb.sql

4. Wiederhole den Befehl für jede weitere Datenbank mit eigenem Dateinamen.
5. Exportiere alle vorhandenen Datenbanken in eine einzige Datei:

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

Mit diesem Befehl werden alle Datenbanken exportiert, auf die der Benutzer Zugriff hat.

6. Zeige die gesicherten Dateien im aktuellen Verzeichnis an:

$ ls

Erwartete Ausgabe:

exampledb.sql  all-databases.sql

Export einer MySQL-Datenbank als CSV- oder Textdatei

Obwohl mysqldump grundsätzlich nur SQL-Dateien erzeugt – selbst wenn die Dateiendung .csv lautet –, ist für echten CSV-Export eine SQL-Abfrage mit INTO OUTFILE direkt in der MySQL-Shell notwendig.

Eine einzelne Tabelle als CSV-Datei exportieren

Verbinde dich zunächst mit dem MySQL-Server:

$ mysql -u root -p

Führe eine SQL-Abfrage aus, um die Tabelle als CSV-Datei zu speichern:

mysql> SELECT * FROM example_table
       INTO OUTFILE '/tmp/example_table.csv'
       FIELDS TERMINATED BY ',' ENCLOSED BY '"'
       LINES TERMINATED BY '\n';

Die Tabelle wird als Datei unter /tmp/example_table.csv auf dem Server gespeichert. Passe gegebenenfalls die Zugriffsrechte an oder nutze sudo für den Zugriff.

Beende die MySQL-Sitzung:

mysql> EXIT;

Kopiere die CSV-Datei in das aktuelle Arbeitsverzeichnis:

$ sudo cp /tmp/example_table.csv .

Mehrere Tabellen per Bash-Skript als CSV exportieren

Erstelle zunächst ein neues Skript:

$ nano export_all_csv.sh

Füge den folgenden Inhalt ein:

#!/bin/bash

DB_NAME="exampledb"
MYSQL_USER="root"
MYSQL_PASS="yourpassword"
OUTPUT_DIR="/var/lib/mysql-files"

tables=$(mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW TABLES IN $DB_NAME;" | tail -n +2)

for table in $tables; do
    mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "
    SELECT * FROM $DB_NAME.$table
    INTO OUTFILE '$OUTPUT_DIR/${table}.csv'
    FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
    LINES TERMINATED BY '\n';
    "
    echo "Exported: $table -> $OUTPUT_DIR/${table}.csv"
done

Du kannst das Skript anpassen – z. B. andere Trennzeichen, Dateipfade oder Escape-Zeichen festlegen. Stelle sicher, dass das Verzeichnis mysql-files vom MySQL-Prozess beschrieben und von deinem Nutzer gelesen werden kann.

Mache das Skript ausführbar:

$ chmod +x export_all_csv.sh

Führe das Skript aus:

$ sudo ./export_all_csv.sh

Backup testen und Datenbank wiederherstellen

Nach dem Export über mysqldump solltest du prüfen, ob das Backup korrekt erstellt wurde, bevor du es im Produktivsystem nutzt. Folge diesen Schritten:

Stelle sicher, dass die Exportdatei existiert und nicht leer ist:

$ ls -lh

Stelle die Verbindung zum MySQL-Server her:

$ mysql -u root -p

Lege eine Testdatenbank zum Einspielen des Backups an:

mysql> CREATE DATABASE restore_db;

Beende die MySQL-Sitzung:

mysql> EXIT;

Spiele das SQL-Backup in die Testdatenbank ein:

$ mysql -u root -p restore_db < exampledb.sql

Melde dich erneut an, um die Daten zu überprüfen:

$ mysql -u root -p restore_db

Liste alle Tabellen auf:

mysql> SHOW TABLES;

Prüfe die Integrität einzelner Tabellen:

mysql> CHECK TABLE table_name;

Lösche die Testdatenbank, wenn alles erfolgreich war:

mysql> DROP DATABASE restore_db;

Beende die Sitzung:

mysql> EXIT;

Backup in Produktivdatenbank wiederherstellen

Bevor du ein Backup in die Live-Datenbank einspielst, erstelle eine zusätzliche Sicherung:

$ mysqldump -u root -p exampledb > backup-before-restore.sql

Importiere anschließend die neue SQL-Datei in die Produktivdatenbank:

$ mysql -u root -p exampledb < exampledb.sql

Falls die Datenbank noch nicht existiert, lege sie vorher an:

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

Öffne die Datenbank zur Prüfung:

$ mysql -u root -p exampledb

Führe erneut eine Tabellenprüfung durch:

mysql> CHECK TABLE table_name;

Verlasse die Sitzung abschließend:

mysql> EXIT;

Fazit

In dieser Anleitung hast du gelernt, wie du mit mysqldump ein MySQL-Backup erstellst, die Integrität überprüfst und die Wiederherstellung sicher testest. Diese Vorgehensweise ist essenziell für eine zuverlässige Backup-Strategie. Weitere Funktionen wie partielle Exporte, Trigger oder Zeichensatzoptionen findest du in der offiziellen mysqldump-Dokumentation.