Mit MySQL-Dump lässt sich ein Database-Backup erstellen. Dabei werden die Daten aus der MySQL-internen Baum­struk­tur in ein Text­for­mat überführt. Das Anlegen von MySQL-Backups gehört zu den Stan­dard­auf­ga­ben von Web- und Sys­tem­ad­mi­nis­tra­to­ren. Wir erklären, was ein MySQL-Dump ist und wie dieser angelegt wird.

My­De­fen­der
Cyber Security aus Deutsch­land
  • Geplante Viren-Scans
  • Au­to­ma­ti­sche Backups, einfache Wie­der­her­stel­lung

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 Da­ten­ban­ken. Eine MySQL-Datenbank enthält Daten in einem für Spei­che­rung und Zugriff op­ti­mier­ten Format. Um Daten aus der Datenbank zu ex­tra­hie­ren, müssen diese in ein ge­eig­ne­tes Format gebracht werden.

Hinweis

Die generelle Frage „Was ist ein Backup?“ be­ant­wor­ten wir an anderer Stelle im Digital Guide.

Bei einem SQL-Dump handelt es sich um eine Plain-Text-Datei mit SQL-An­wei­sun­gen. Es gibt ver­schie­de­ne Wege, ein MySQL-Backup zu erzeugen. So lässt sich die Ad­mi­nis­tra­ti­ons-Ober­flä­che phpMy­Ad­min nutzen oder ein Backup mit MyS­QLDum­per erstellen. Wir befassen uns hier mit dem Erzeugen eines MySQL-Dumps unter Zu­hil­fe­nah­me des Kom­man­do­zei­len-Tools mysqldump.

Tipp

MySQL ist das meist­ge­nutz­te re­la­tio­na­le Datenbank-Ma­nage­ment-System. Der mächtige Kon­kur­rent Postgres erfreut sich zu­neh­men­der Be­liebt­heit. Wie man ein Post­greS­QL-Backup anlegt, erklären wir in einem anderen Artikel.

Was bedeutet MySQL-Dump?

Der Begriff „MySQL-Dump“ hat zwei Be­deu­tun­gen:

  1. Die Export-Datei einer MySQL-Datenbank
  2. Das Kom­man­do­zei­len-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 or­ga­ni­siert Daten in op­ti­mier­ten Da­ten­struk­tu­ren. Für generelle Daten kommt ein so­ge­nann­ter B-Tree zum Einsatz, geo­gra­fi­sche Daten werden in einem R-Tree abgelegt. Die Daten zu „dumpen“ bedeutet, sie aus diesen spe­zi­el­len Baum­struk­tu­ren in eine lineare Dar­stel­lung zu über­füh­ren – den „Dump“.

Das Dumpen der baum­för­mi­gen Daten liefert eine Textdatei. Diese lässt sich mit her­kömm­li­chen 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-An­wei­sun­gen, die den Inhalt der Datenbank vor dem Dumpen wi­der­spie­geln. Auf einer anderen Datenbank-In­stal­la­ti­on lässt sich der Dump im­por­tie­ren. Dabei werden die SQL-An­wei­sun­gen aus­ge­führt und die Datenbank samt aller Inhalte schritt­wei­se neu aufgebaut. Nach dem Im­por­tie­ren sollte die Datenbank dieselben Daten in der gleichen Struktur enthalten wie vor dem Dumpen.

Für den kom­plet­ten Prozess vom Erzeugen des MySQL-Backups bis zum Import des Dumps ergibt sich das folgende Schema:

mysql_import(mysql_dump(database)) == database

Viel­leicht 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äu­figs­ten in einem MySQL-Dump an­zu­tref­fen­den SQL-An­wei­sun­gen:

Anweisung im MySQL-Dump Erklärung
DROP TABLE IF EXISTS Exis­tie­ren­de 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-Kom­man­do­zei­len-Tool wird für ge­wöhn­lich als Teil des MySQL-Clients oder MySQL-Servers in­stal­liert. Um das Tool ein­zu­set­zen, sollten Sie zunächst fest­stel­len, ob mysqldump auf dem lokalen System in­stal­liert ist. Wir benutzen den folgenden Befehl, um zu über­prü­fen, wo die mysqldump-Bi­när­da­tei liegt:

which mysqldump

Liefert der Aufruf des Befehls eine Feh­ler­mel­dung zurück, wurde das Tool nicht in­stal­liert.

Wir nutzen den folgenden Befehl, um die Version der mysqldump-In­stal­la­ti­on an­zu­zei­gen:

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-In­stal­la­ti­on in einem Docker-Container läuft, können Sie MySQL-Dump innerhalb des Con­tai­ners verwenden, um ein MySQL-Backup anzulegen. Ferner bietet sich die Mög­lich­keit, 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 Kom­man­do­zei­le statt. Für ge­wöhn­lich 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 ex­por­tie­ren­den Datenbank
  2. Der Name des Datenbank-Benutzers
  3. Das Passwort des Datenbank-Benutzers

Wenn Sie alle drei In­for­ma­tio­nen vorliegen haben, können Sie mit dem Erstellen des MySQL-Backup loslegen. Dabei kommen je nach Ein­satz­sze­na­rio und An­for­de­run­gen ver­schie­de­ne Methoden zum Einsatz. Wir stellen drei häufig genutzte Ansätze vor.

Eingabe des mysqldump-Befehls auf der Kom­man­do­zei­le

Zunächst lässt sich der mysqldump-Befehl händisch auf der Kom­man­do­zei­le eingeben. Das ist sinnvoll, wenn man einmalig ein Backup einer Datenbank erzeugen möchte, bei­spiels­wei­se um eine WordPress-In­stal­la­ti­on 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 Kom­po­nen­ten:

  1. mysqldump-Aufruf
  2. Datenbank-Nutzer
  3. Datenbank-Passwort
  4. Datenbank-Name
  5. Wei­ter­lei­tung der Ausgabe
  6. Pfad zur Ausgabe-Datei

In der Literatur ist eine al­ter­na­ti­ve Schreib­wei­se des mysqldump-Aufrufs ver­brei­tet. Statt de­skrip­ti­ver Op­ti­ons­na­men wie --user kommen dabei Kurz­op­tio­nen zum Einsatz. Wir zeigen das Schema hier der Voll­stän­dig­keit halber. Der folgende mysqldump-Aufruf ist äqui­va­lent zum bereits vor­ge­stell­ten:

mysqldump -u<username> -p<password> <database> > <path/to/mysql_dump.sql>

Das Umwandeln der Daten aus der Baum­struk­tur führt in der Regel zu einer hohen Redundanz der im MySQL-Dump ent­hal­te­nen SQL-An­wei­sun­gen. Da es sich bei einem MySQL-Dump um eine Plain-Text-Datei handelt, lässt sich der Dump gut kom­pri­mie­ren. Es ist üblich, die Ausgabe des mysqldump-Befehls über eine so­ge­nann­te Pipe an das Kom­pres­si­ons-Tool Gzip wei­ter­zu­lei­ten. Der Kon­ven­ti­on folgend wird die dabei re­sul­tie­ren­de Datei mit der Endung .sql.gz versehen:

mysqldump --user=<username> --password=<password> <database> | gzip > <path/to/mysql_dump.sql.gz>

Ver­glei­chend eine Übersicht der drei vor­ge­stell­ten, häufig an­zu­tref­fen­den Varianten des mysqldump-Aufrufs zum Erzeugen eines MySQL-Backups:

  mysqldump-Aufruf Datenbank-Nutzer Datenbank-Passwort Datenbank-Name Wei­ter­lei­tung der Ausgabe Pfad zur Ausgabe-Datei
Aufruf mit de­skrip­ti­ven Optionen mysqldump --user=<username> --password=<password> <database> > <path/to/mysql_dump.sql>
Aufruf mit Kurz­op­tio­nen mysqldump -u<username> -p<password> <database> > <path/to/mysql_dump.sql>
Aufruf mit Kom­pri­mie­rung mysqldump --user=<username> --password=<password> <database> | gzip > <path/to/mysql_dump.sql.gz>

Verwenden einer MySQL-Dump-Database-Kon­fi­gu­ra­ti­ons­da­tei

Wie Sie gesehen haben, lässt sich der mysqldump-Befehl einfach auf der Kom­man­do­zei­le ausführen. Dabei besteht jedoch ein Si­cher­heits­ri­si­ko. Denn das Datenbank-Passwort wird als Teil des mysqldump-Befehls im Klartext übergeben. Stan­dard­mä­ßig werden jedoch alle auf der Kom­man­do­zei­le ein­ge­ge­be­nen Befehle in einer spe­zi­el­len Datei pro­to­kol­liert. Nutzt jemand später den history-Befehl, um ein­ge­ge­be­ne Befehle an­zu­zei­gen, tauchen auch sensible Daten im Klartext auf.

Um die Datenbank vor un­be­fug­tem Zugriff zu schützen, ist es besser, die Daten für den Da­ten­bank­zu­griff in einer separaten Datei als Variablen zu de­fi­nie­ren. Die Datei wird vor dem Aufrufen des mysqldump-Befehls ein­ge­le­sen. Im History-Protokoll tauchen dann nur die Variablen-Namen auf; die ei­gent­li­chen Werte bleiben verborgen.

Wir zeigen nun Schritt für Schritt, wie ein MySQL-Backup unter Nutzung einer Kon­fi­gu­ra­ti­ons­da­tei erzeugt wird. Kopieren Sie einfach die nach­fol­gen­den Befehle in Ihre Kom­man­do­zei­le und führen Sie diese dort aus.

  1. Zunächst erstellen wir einen Ordner für die MySQL-Dump-Database-Kon­fi­gu­ra­ti­ons­da­tei unterhalb unseres Be­nut­zer­ord­ners:
mkdir ~/.mysql-dump/
  1. Mithilfe des folgenden Codes schreiben wir die MySQL-Dump-Database-Kon­fi­gu­ra­ti­on 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 Kon­fi­gu­ra­ti­ons­da­tei im nano-Editor und passen die Zu­griffs­da­ten der Datenbank an:
nano ~/.mysql-dump/database.conf

Ersetzen Sie die 'your_db'-Platz­hal­ter durch zu Ihrer Datenbank passende Werte. Drücken Sie dann die Tas­ten­kom­bi­na­ti­on [Strg] + [X], gefolgt von [Y] und [Enter], um die Datei zu schließen und die Än­de­run­gen zu speichern.

An diesem Punkt ist die Vor­be­rei­tung ab­ge­schlos­sen, und wir können ein MySQL-Backup erzeugen.

  1. Um ein MySQL-Dump zu erzeugen, lesen wir die Kon­fi­gu­ra­ti­on 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 ge­wöhn­lich zunächst im Da­tei­sys­tem des Datenbank-Servers ge­spei­chert. Der 3-2-1-Backup-Regel folgend sollte jedoch min­des­tens 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 vor­ge­stell­ten Ansätze eignen sich für den schnellen Export einer einzelnen MySQL-Datenbank. Für re­gel­mä­ßi­ge Backups oder um MySQL-Backups mehrerer Da­ten­ban­ken zu erstellen, bietet sich an, ein MySQL-Backup-Script anzulegen. Dieses wird pe­ri­odisch aus­ge­führt und sichert die Inhalte aller vor­han­de­nen MySQL-Da­ten­ban­ken als MySQL-Dumps in einem vor­de­fi­nier­ten Ordner. Die Nutzung eines MySQL-Backup-Scripts stan­dar­di­siert den Backup-Prozess und senkt das Feh­ler­ri­si­ko.

Zum Erstellen des Backup-Scripts erweitern wir den zuletzt vor­ge­stell­ten Ansatz. Wir legen einen zu­sätz­li­chen Ordner für die MySQL-Backups an. Jedes Backup landet in einem eigenen Un­ter­ord­ner. Dieser enthält die MySQL-Dumps der einzelnen Da­ten­ban­ken. Darüber hinaus erzeugen wir eine Script-Datei, die den Prozess au­to­ma­ti­siert. Folgen Sie den einzelnen Schritten, um das MySQL-Backup-Script ein­zu­rich­ten:

  1. Zunächst legen wir einen zu­sätz­li­chen Ordner für die MySQL-Backups an:
mkdir -p ~/.mysql-dump/backups/
  1. Im Anschluss erzeugen wir das ei­gent­li­che 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 Kon­fi­gu­ra­ti­ons­da­tei im nano-Editor und passen die Zu­griffs­da­ten der Datenbank an:
nano ~/.mysql-dump/backup_databases

Ersetzen Sie die 'your_db'-Platz­hal­ter durch zu Ihrer Datenbank passende Werte. Drücken Sie dann die Tas­ten­kom­bi­na­ti­on [Strg] + [X], gefolgt von [Y] und [Enter], um die Datei zu schließen und die Än­de­run­gen zu speichern.

  1. Nachdem die Vor­be­rei­tun­gen ab­ge­schlos­sen 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 funk­tio­niert.

MySQL-Dump in Datenbank im­por­tie­ren

Der mysqldump-Befehl dient einzig zum Ex­por­tie­ren einer MySQL-Datenbank. Zum Im­por­tie­ren eines Dumps wird der mysql-Befehl benötigt. Wir zeigen hier ab­schlie­ßend das generelle Schema:

mysql --user=<username> --password=<password> database_name < <path/to/mysql_dump.sql>

Falls eine MySQL-Dump-Database-Kon­fi­gu­ra­ti­ons­da­tei 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>
Zum Hauptmenü