MySQL-Backup mit MySQL-Dump
Mit MySQL-Dump lässt sich ein Database-Backup erstellen. Dabei werden die Daten aus der MySQL-internen Baumstruktur in ein Textformat überführt. Das Anlegen von MySQL-Backups gehört zu den Standardaufgaben von Web- und Systemadministratoren. Wir erklären, was ein MySQL-Dump ist und wie dieser angelegt wird.
MyDefender
Cyber Security aus Deutschland - Schutz vor Ransomware-Attacken
- Geplante Viren-Scans
- Automatische Backups, einfache Wiederherstellung
Was ist ein MySQL-Backup und wie wird es angelegt?
Das Erstellen eines MySQL-Dumps dient zum Anlegen von MySQL-Backups sowie zum Migrieren von Daten zwischen zwei Datenbanken. Eine MySQL-Datenbank enthält Daten in einem für Speicherung und Zugriff optimierten Format. Um Daten aus der Datenbank zu extrahieren, müssen diese in ein geeignetes Format gebracht werden.
Die generelle Frage „Was ist ein Backup?“ beantworten wir an anderer Stelle im Digital Guide.
Bei einem SQL-Dump handelt es sich um eine Plain-Text-Datei mit SQL-Anweisungen. Es gibt verschiedene Wege, ein MySQL-Backup zu erzeugen. So lässt sich die Administrations-Oberfläche phpMyAdmin nutzen oder ein Backup mit MySQLDumper erstellen. Wir befassen uns hier mit dem Erzeugen eines MySQL-Dumps unter Zuhilfenahme des Kommandozeilen-Tools mysqldump.
MySQL ist das meistgenutzte relationale Datenbank-Management-System. Der mächtige Konkurrent Postgres erfreut sich zunehmender Beliebtheit. Wie man ein PostgreSQL-Backup anlegt, erklären wir in einem anderen Artikel.
Was bedeutet MySQL-Dump?
Der Begriff „MySQL-Dump“ hat zwei Bedeutungen:
- Die Export-Datei einer MySQL-Datenbank
- Das Kommandozeilen-Tool zum Erstellen eines Datenbank-Exports
MySQL-Dump als Export-Datei einer MySQL-Datenbank
Das englische Verb „to dump“ bedeutet „auf einen Haufen schütten“. Eine MySQL-Datenbank organisiert Daten in optimierten Datenstrukturen. Für generelle Daten kommt ein sogenannter B-Tree zum Einsatz, geografische Daten werden in einem R-Tree abgelegt. Die Daten zu „dumpen“ bedeutet, sie aus diesen speziellen Baumstrukturen in eine lineare Darstellung zu überführen – den „Dump“.
Das Dumpen der baumförmigen Daten liefert eine Textdatei. Diese lässt sich mit herkömmlichen Methoden speichern und zwischen Systemen bewegen. Ebendies benötigen wir, um aus einer MySQL-Datenbank ein Backup zu erzeugen. Was ist nun genau in einem MySQL-Dump enthalten?
Ein MySQL-Dump enthält SQL-Anweisungen, die den Inhalt der Datenbank vor dem Dumpen widerspiegeln. Auf einer anderen Datenbank-Installation lässt sich der Dump importieren. Dabei werden die SQL-Anweisungen ausgeführt und die Datenbank samt aller Inhalte schrittweise neu aufgebaut. Nach dem Importieren sollte die Datenbank dieselben Daten in der gleichen Struktur enthalten wie vor dem Dumpen.
Für den kompletten Prozess vom Erzeugen des MySQL-Backups bis zum Import des Dumps ergibt sich das folgende Schema:
Das Dumpen der baumförmigen Daten liefert eine Textdatei. Diese lässt sich mit herkömmlichen Methoden speichern und zwischen Systemen bewegen. Ebendies benötigen wir, um aus einer MySQL-Datenbank ein Backup zu erzeugen. Was ist nun genau in einem MySQL-Dump enthalten?
Ein MySQL-Dump enthält SQL-Anweisungen, die den Inhalt der Datenbank vor dem Dumpen widerspiegeln. Auf einer anderen Datenbank-Installation lässt sich der Dump importieren. Dabei werden die SQL-Anweisungen ausgeführt und die Datenbank samt aller Inhalte schrittweise neu aufgebaut. Nach dem Importieren sollte die Datenbank dieselben Daten in der gleichen Struktur enthalten wie vor dem Dumpen.
Für den kompletten Prozess vom Erzeugen des MySQL-Backups bis zum Import des Dumps ergibt sich das folgende Schema:
mysql_import(mysql_dump(database)) == database
Vielleicht haben Sie bereits einen MySQL-Dump in einem Code- oder Plain-Text-Editor geöffnet. Der Inhalt eines MySQL-Dumps wirkt anfangs recht kryptisch. Hier eine Übersicht der am häufigsten in einem MySQL-Dump anzutreffenden SQL-Anweisungen:
Anweisung im MySQL-Dump | Erklärung |
---|---|
DROP TABLE IF EXISTS | Existierende Tabelle beim Import entfernen |
CREATE TABLE | Tabelle beim Import neu anlegen |
INSERT INTO … VALUES | Spalten beim Import mit Werten füllen |
MySQL-Dump als Tool zum Erstellen eines Datenbank-Exports
Das mysqldump-Kommandozeilen-Tool wird für gewöhnlich als Teil des MySQL-Clients oder MySQL-Servers installiert. Um das Tool einzusetzen, sollten Sie zunächst feststellen, ob mysqldump auf dem lokalen System installiert ist. Wir benutzen den folgenden Befehl, um zu überprüfen, wo die mysqldump-Binärdatei liegt:
which mysqldump
Liefert der Aufruf des Befehls eine Fehlermeldung zurück, wurde das Tool nicht installiert.
Wir nutzen den folgenden Befehl, um die Version der mysqldump-Installation anzuzeigen:
Wir nutzen den folgenden Befehl, um die Version der mysqldump-Installation anzuzeigen:
mysqldump --version
So lässt sich die Hilfe des mysqldump-Befehls ausgeben:
mysqldump --help
Den Einsatz des mysqldump-Befehls zum Erzeugen eines MySQL-Backups erklären wir im Folgenden.
Wenn Ihre MySQL-Installation in einem Docker-Container läuft, können Sie MySQL-Dump innerhalb des Containers verwenden, um ein MySQL-Backup anzulegen. Ferner bietet sich die Möglichkeit, den gesamten Container als Docker-Backup zu speichern.
Schritt-für-Schritt Anleitung zum Erstellen eines MySQL-Dumps
Die Erzeugung eines MySQL-Backups mit dem mysqldump-Tool findet auf der Kommandozeile statt. Für gewöhnlich loggt man sich zunächst über SSH auf dem Datenbank-Server oder Docker-Container ein. Zum Erstellen des MySQL-Backup werden zumindest drei Daten benötigt:
- Der Name der zu exportierenden Datenbank
- Der Name des Datenbank-Benutzers
- Das Passwort des Datenbank-Benutzers
Eingabe des mysqldump-Befehls auf der Kommandozeile
Zunächst lässt sich der mysqldump-Befehl händisch auf der Kommandozeile eingeben. Das ist sinnvoll, wenn man einmalig ein Backup einer Datenbank erzeugen möchte, beispielsweise um eine WordPress-Installation von einem Server zu einem anderen zu migrieren. Das generelle Schema zum Aufrufen des mysqldump-Befehls lautet wie folgt:
mysqldump --user=<username> --password=<password> <database> > <path/to/mysql_dump.sql>
Wie Sie sehen, besteht ein typischer mysqldump-Aufruf aus sechs Komponenten:
- mysqldump-Aufruf
- Datenbank-Nutzer
- Datenbank-Passwort
- Datenbank-Name
- Weiterleitung der Ausgabe
- Pfad zur Ausgabe-Datei
mysqldump -u<username> -p<password> <database> > <path/to/mysql_dump.sql>
Das Umwandeln der Daten aus der Baumstruktur führt in der Regel zu einer hohen Redundanz der im MySQL-Dump enthaltenen SQL-Anweisungen. Da es sich bei einem MySQL-Dump um eine Plain-Text-Datei handelt, lässt sich der Dump gut komprimieren. Es ist üblich, die Ausgabe des mysqldump-Befehls über eine sogenannte Pipe an das Kompressions-Tool Gzip weiterzuleiten. Der Konvention folgend wird die dabei resultierende Datei mit der Endung .sql.gz versehen:
mysqldump --user=<username> --password=<password> <database> | gzip > <path/to/mysql_dump.sql.gz>
Vergleichend eine Übersicht der drei vorgestellten, häufig anzutreffenden Varianten des mysqldump-Aufrufs zum Erzeugen eines MySQL-Backups:
mysqldump-Aufruf | Datenbank-Nutzer | Datenbank-Passwort | Datenbank-Name | Weiterleitung der Ausgabe | Pfad zur Ausgabe-Datei | |
Aufruf mit deskriptiven Optionen | mysqldump | --user=<username> | --password=<password> | <database> | > | <path/to/mysql_dump.sql> |
Aufruf mit Kurzoptionen | mysqldump | -u<username> | -p<password> | <database> | > | <path/to/mysql_dump.sql> |
Aufruf mit Komprimierung | mysqldump | --user=<username> | --password=<password> | <database> | | gzip > | <path/to/mysql_dump.sql.gz> |
Verwenden einer MySQL-Dump-Database-Konfigurationsdatei
Wie Sie gesehen haben, lässt sich der mysqldump-Befehl einfach auf der Kommandozeile ausführen. Dabei besteht jedoch ein Sicherheitsrisiko. Denn das Datenbank-Passwort wird als Teil des mysqldump-Befehls im Klartext übergeben. Standardmäßig werden jedoch alle auf der Kommandozeile eingegebenen Befehle in einer speziellen Datei protokolliert. Nutzt jemand später den history-Befehl, um eingegebene Befehle anzuzeigen, tauchen auch sensible Daten im Klartext auf.
Um die Datenbank vor unbefugtem Zugriff zu schützen, ist es besser, die Daten für den Datenbankzugriff in einer separaten Datei als Variablen zu definieren. Die Datei wird vor dem Aufrufen des mysqldump-Befehls eingelesen. Im History-Protokoll tauchen dann nur die Variablen-Namen auf; die eigentlichen Werte bleiben verborgen.
Wir zeigen nun Schritt für Schritt, wie ein MySQL-Backup unter Nutzung einer Konfigurationsdatei erzeugt wird. Kopieren Sie einfach die nachfolgenden Befehle in Ihre Kommandozeile und führen Sie diese dort aus.
Um die Datenbank vor unbefugtem Zugriff zu schützen, ist es besser, die Daten für den Datenbankzugriff in einer separaten Datei als Variablen zu definieren. Die Datei wird vor dem Aufrufen des mysqldump-Befehls eingelesen. Im History-Protokoll tauchen dann nur die Variablen-Namen auf; die eigentlichen Werte bleiben verborgen.
Wir zeigen nun Schritt für Schritt, wie ein MySQL-Backup unter Nutzung einer Konfigurationsdatei erzeugt wird. Kopieren Sie einfach die nachfolgenden Befehle in Ihre Kommandozeile und führen Sie diese dort aus.
- Zunächst erstellen wir einen Ordner für die MySQL-Dump-Database-Konfigurationsdatei unterhalb unseres Benutzerordners:
mkdir ~/.mysql-dump/
- Mithilfe des folgenden Codes schreiben wir die MySQL-Dump-Database-Konfiguration in eine neue Datei:
cat << EOF > ~/.mysql-dump/database.conf
DB_NAME='your_db_name'
DB_USER='your_db_user_name'
DB_PASS='your_db_password'
EOF
- Wir öffnen die Konfigurationsdatei im nano-Editor und passen die Zugriffsdaten der Datenbank an:
nano ~/.mysql-dump/database.conf
Ersetzen Sie die 'your_db'-Platzhalter durch zu Ihrer Datenbank passende Werte. Drücken Sie dann die Tastenkombination [Strg] + [X], gefolgt von [Y] und [Enter], um die Datei zu schließen und die Änderungen zu speichern.
An diesem Punkt ist die Vorbereitung abgeschlossen, und wir können ein MySQL-Backup erzeugen.
An diesem Punkt ist die Vorbereitung abgeschlossen, und wir können ein MySQL-Backup erzeugen.
- Um ein MySQL-Dump zu erzeugen, lesen wir die Konfiguration mit dem source-Befehl ein und führen im Anschluss MySQL-Dump aus:
source ~/.mysql-dump/database.conf
mysqldump --user="$DB_USER" --password="$DB_PASS" "$DB_NAME" > <path/to/mysql_dump.sql>
Mit MySQL-Dump erzeugte MySQL-Backups werden für gewöhnlich zunächst im Dateisystem des Datenbank-Servers gespeichert. Der 3-2-1-Backup-Regel folgend sollte jedoch mindestens ein Backup in der Cloud gelagert werden. Wir erklären, wie Sie ein Server-Backup mit Rsync erstellen.
Verwenden eines MySQL-Backup-Scripts
Die bisher vorgestellten Ansätze eignen sich für den schnellen Export einer einzelnen MySQL-Datenbank. Für regelmäßige Backups oder um MySQL-Backups mehrerer Datenbanken zu erstellen, bietet sich an, ein MySQL-Backup-Script anzulegen. Dieses wird periodisch ausgeführt und sichert die Inhalte aller vorhandenen MySQL-Datenbanken als MySQL-Dumps in einem vordefinierten Ordner. Die Nutzung eines MySQL-Backup-Scripts standardisiert den Backup-Prozess und senkt das Fehlerrisiko.
Zum Erstellen des Backup-Scripts erweitern wir den zuletzt vorgestellten Ansatz. Wir legen einen zusätzlichen Ordner für die MySQL-Backups an. Jedes Backup landet in einem eigenen Unterordner. Dieser enthält die MySQL-Dumps der einzelnen Datenbanken. Darüber hinaus erzeugen wir eine Script-Datei, die den Prozess automatisiert. Folgen Sie den einzelnen Schritten, um das MySQL-Backup-Script einzurichten:
Zum Erstellen des Backup-Scripts erweitern wir den zuletzt vorgestellten Ansatz. Wir legen einen zusätzlichen Ordner für die MySQL-Backups an. Jedes Backup landet in einem eigenen Unterordner. Dieser enthält die MySQL-Dumps der einzelnen Datenbanken. Darüber hinaus erzeugen wir eine Script-Datei, die den Prozess automatisiert. Folgen Sie den einzelnen Schritten, um das MySQL-Backup-Script einzurichten:
- Zunächst legen wir einen zusätzlichen Ordner für die MySQL-Backups an:
mkdir -p ~/.mysql-dump/backups/
- Im Anschluss erzeugen wir das eigentliche MySQL-Backup-Script:
cat << EOF > ~/.mysql-dump/backup_databases
DB_USER='your_db_user_name'
DB_PASS='your_db_password'
# neuen Ordner für aktuelles Backup anlegen
backup_dir='./backups/$(date +%F_%H%M)'
# Datenbanken ermitteln
databases=$(mysql --user="$DB_USER" --password="$DB_PASS" -Bs --execute 'SHOW DATABASES')
# alle Datenbanken exportieren
for database in $databases; do
mysqldump --user="$DB_USER" --password="$DB_PASS" "$database" > "${backup_dir}/${database}.sql"
done
# Variablen aus dem Speicher löschen
unset DB_USER DB_PASS
EOF
- Wir öffnen die Konfigurationsdatei im nano-Editor und passen die Zugriffsdaten der Datenbank an:
nano ~/.mysql-dump/backup_databases
Ersetzen Sie die 'your_db'-Platzhalter durch zu Ihrer Datenbank passende Werte. Drücken Sie dann die Tastenkombination [Strg] + [X], gefolgt von [Y] und [Enter], um die Datei zu schließen und die Änderungen zu speichern.
- Nachdem die Vorbereitungen abgeschlossen sind, führen wir das MySQL-Backup-Script aus:
cd ~/.mysql-dump/ && source backup_databases
- Im letzten Schritt testen wir, ob das Backup erzeugt wurde:
ls -la ~/.mysql-dump/backups/*
Wenn Ihnen Dateien mit der Endung .sql angezeigt werden, hat das Backup funktioniert.
MySQL-Dump in Datenbank importieren
Der mysqldump-Befehl dient einzig zum Exportieren einer MySQL-Datenbank. Zum Importieren eines Dumps wird der mysql-Befehl benötigt. Wir zeigen hier abschließend das generelle Schema:
mysql --user=<username> --password=<password> database_name < <path/to/mysql_dump.sql>
Falls eine MySQL-Dump-Database-Konfigurationsdatei existiert, lässt sich der folgende Befehl nutzen:
source ~/.mysql-dump/database.conf
mysql --user="$DB_USER" --password="$DB_PASS" "$DB_NAME" < <path to mysql_dump.sql></path>