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.

Hinweis

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.

Tipp

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:

  1. Die Export-Datei einer MySQL-Datenbank
  2. Das Kommandozeilen-Tool zum Erstellen eines Datenbank-Exports

Schauen wir uns die beiden Begriffe im Detail an.

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:

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:

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.

Tipp

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:

  1. Der Name der zu exportierenden Datenbank
  2. Der Name des Datenbank-Benutzers
  3. Das Passwort des Datenbank-Benutzers

Wenn Sie alle drei Informationen vorliegen haben, können Sie mit dem Erstellen des MySQL-Backup loslegen. Dabei kommen je nach Einsatzszenario und Anforderungen verschiedene Methoden zum Einsatz. Wir stellen drei häufig genutzte Ansätze vor.

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:

  1. mysqldump-Aufruf
  2. Datenbank-Nutzer
  3. Datenbank-Passwort
  4. Datenbank-Name
  5. Weiterleitung der Ausgabe
  6. Pfad zur Ausgabe-Datei

In der Literatur ist eine alternative Schreibweise des mysqldump-Aufrufs verbreitet. Statt deskriptiver Optionsnamen wie --user kommen dabei Kurzoptionen zum Einsatz. Wir zeigen das Schema hier der Vollständigkeit halber. Der folgende mysqldump-Aufruf ist äquivalent zum bereits vorgestellten:

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.

  1. Zunächst erstellen wir einen Ordner für die MySQL-Dump-Database-Konfigurationsdatei unterhalb unseres Benutzerordners:
mkdir ~/.mysql-dump/
  1. 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
  1. 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.

  1. 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>
Tipp

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:

  1. Zunächst legen wir einen zusätzlichen Ordner für die MySQL-Backups an:
mkdir -p ~/.mysql-dump/backups/
  1. 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
  1. 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.

  1. Nachdem die Vorbereitungen abgeschlossen sind, führen wir das MySQL-Backup-Script aus:
cd ~/.mysql-dump/ && source backup_databases
  1. 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>
War dieser Artikel hilfreich?