mysqldump: Backup von MySQL-Datenbanken (MyISAM und InnoDB)

Letzte Änderung am 06.03.2019
Dienste
Datenbanken
MySQL
Backup

Mit mysqldump lassen sich Datenbanken schnell sichern, wie folgende Beispiele und Erläuterungen zeigen.

Allgemeiner Aufruf von mysqldump

Mit mysqldump lassen sich alle Datenbanken, einige Datenbanken oder bestimmte Tabellen einer Datenbank sichern. Alle Befehle werden standardmäßig mit der Option opt ausgeführt.

Sichern aller Datenbanken mit mysqldump

Dies ist z.B. vor einem MySQL-Upgrade äußerst sinnvoll. Allgemeiner Aufruf:

  1. mysqldump [Optionen] --all-databases [Optionen]

Konkretes (sinnvolles) Beispiel zum Sichern aller Datenbanken mit mysqldump:

  1. mysqldump -u root -p --all-databases --result-file=dbdata.sql

Zunächst wird man aufgefordert, das Kennwort des Datenbank-Benutzers(!) root anzugeben. Danach wird die Tabellenstruktur und der Inhalt aller Datenbanken d in die Datei dbdata.sql geschrieben.

Achtung: Das beinhaltet auch die Datenbank mysql mit allen Benutzerdaten. Beim zurück spielen des Backups wird diese Tabelle komplett überschrieben.

Sichern einer oder mehrerer Datenbanken mit mysqldump

Häufig ist es sinnvoll nur bestimmte oder nur eine Datenbank komplett zu sichern. Für den Befehl muss nur eine Option geändert werden. Allgemein:

  1. mysqldump [Optionen] --databases [Optionen] DB1 [DB2 DB3...]

Will man den kompletten Inhalt der Datenbank db1 sichern, so bedeutet dies:

  1. mysqldump -u root -p --databases db1 --result-file=dbdata.sql

Die Schreibweise

mysqldump -u root -p db1 --result-file=dbdata2.sql

schreibt auch Daten und Struktur in die Datei dbdata2.sql. Der Unterschied ist, dass mit der Option "--databases" tatsächlich die gesamte Datenbank gesichert wird, während im zweiten Fall nur alle Tabellen der Datenbank db1 in dbdata2.sql geschrieben werden.
In dbdata.sql stehen zwei zusätzliche Zeilen: eine existierende Datenbank db1 würde zunächst gelöscht, dann neu angelegt und zuletzt als aktive Datenbank ausgewählt. In dbdata2.sql stehen nur Befehle zum Anlegen der Tabellen-Struktur. Vorm Zurückspielen des Backups muss eine Datenbank als aktive mit use db1; ausgewählt werden.

Für den Inhalt mehrerer Datenbanken db1 db2 db3 gilt entsprechend:

  1. mysqldump -u root -p --databases db1 db2 db3 --result-file=dbdata.sql

Auch hier gilt, dass beim Ausführen des mysqldump Befehls nach dem Kennwort des Datenbankbenutzers root gefragt wird.

Sichern ausgewählter Tabellen einer Datenbank mit mysqldump

Für ein Backup eher nicht zu empfehlen. Sinnvoll mag dies vielleicht zu einem Übertrag von Benutzerdaten dienen. Allgemein lassen sich einzelne Tabellen Datenbanken mit dem folgende mysqldump Befehl sichern:

  1. mysqldump [Optionen] DB1 [Tabellen]

Als sinnvolles Beispiel dient die Benutzerdatenbank mysql von MySQL. Die Tabellen db und user sollen gesichert werden:

  1. mysqldump -u root -p mysql db user --result-file=dbdata.sql

Auch hier gilt wieder: Erst Passwortabfrage, dann die Ausführung des mysqldump Befehls.

mysqldump: Weiterführende Informationen

mysqldump: myisam und innodb Tabellen

Für die Datensicherung, bzw. das Erstellen eines Backups ist der Tabellentyp (z.B. MyIsam grundsätzlich nicht von Bedeutung.

Timestamps

Hinweis zu älteren Versionen: timestamps werden unter Umständen auf den aktuellen Stand gebracht. Der Einfachheit hatte ich timestamp benutzt, um daraus das Datum der letzten Änderung für Webseiten zu generieren. Nach einem dump hatten alle das Datum des Dumps.

Mit aktuellen MySQL- / mysqldump-Versionen sollte es dieses Problem bicht mehr geben.

Kommentare

stefan

9. April 2013 - 12:24

Eben benötigt auf einem 1&1 Managed Server: Aufruf von mysqldump mit anderem Port und Angabe des Sockets:

  1. mysqldump -u USER -p --databases DB1 -P 3316 --result-file=export.sql --socket=/tmp/mysql5.sock

raiserle (nicht überprüft)

10. Mai 2013 - 19:05

Das mit den TIMESTAMPs kann ich nicht nachvollziehen. 
  1. --
  2. -- Table structure for table `test`
  3. --
  4.  
  5. DROP TABLE IF EXISTS `test`;
  6. CREATE TABLE `test` (
  7.   `id` int(11) NOT NULL auto_increment,
  8.   `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  9.   PRIMARY KEY  (`id`)
  10. ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
  11. SET character_set_client = @saved_cs_client;
  12.  
  13. --
  14. -- Dumping data for table `test`
  15. --
  16.  
  17. LOCK TABLES `test` WRITE;
  18. /*!40000 ALTER TABLE `test` DISABLE KEYS */;
  19. INSERT INTO `test` VALUES (1,'2013-05-10 17:00:24'),(2,'2013-05-10 17:00:39'),(3,'2013-05-10 17:00:39'),(4,'2013-05-10 17:00:49'),(5,'2013-05-10 17:00:49');
  20. /*!40000 ALTER TABLE `test` ENABLE KEYS */;
  21. UNLOCK TABLES;
Bitte beschreibe doch mal genau, was du da gemacht hast! vG raiserle

stefan

7. Juli 2013 - 15:05

Diese Erfahrung liegt durchaus ein paar Jahre zurück. Leider kann ich keine Version mehr nennen. Kann aber gut sein, dass es sich um MySQL 4.x oder sogar 3.x handelte. Reproduzieren konnte ich dies unter MySQL 5.5 / mysqldump 10.13 nicht mehr.

Jedenfalls habe ich diese Funktion seitdem weder aktiv genutzt noch musste ich damit arbeiten, deswegen habe ich diese Zeilen nicht aktualisiert.

Danke für den Hinweis!

Gruß
Stefan

raiserle (nicht überprüft)

10. Juli 2013 - 18:24

OT: Bei dem Beitrag steht ja leider kein Datum - oder ich bin blind. Ich hatte mich am Kommentar orientiert ;)

vG raiserle

stefan

11. Juli 2013 - 22:50

Ein Datum gibt es (noch) nicht, steht aber auf der ToDo-Liste, wie die gesamte Überarbeitung des Templates.
Die letzte Berarbeitung des Artikels liegt aber auch erst ein paar Tage zurück, von daher wäre das auch irreführend ;-)

Bei den jüngeren Artikeln stehen normalerweide immer konkrete Versionsangaben dabei.