Wie man einen MySQL-Dump macht

Wie Sie Ihre MySQL-Datenbank speichern

MySQL ist ein Datenbankdienst zum Speichern von Daten jeder Art. Er bietet verschiedene Arten von Backup-Optionen, die je nach Datengröße, Hardwarespezifikationen oder Speicherkapazität ausgewählt werden können.

MySQL bietet:

• Logische Backups, die über eine .sql-Datei und create/insert-Anweisungen ausgeführt werden. Später können Sie eine Datenbank aus dieser Datei wiederherstellen. Diese Art der Sicherung gilt als langsamer als andere und eignet sich eher für mittelgroße Datenbanken.
• Physische Backups werden durch Kopieren von Dateien im Originalformat durchgeführt. Der Nachteil dieser Art der Sicherung ist, dass die Datenbank nur auf einem MySQL-Server wiederhergestellt werden kann.
• Konsistente Backups, die durch automatisierte regelmäßige Sicherung von Dateien durchgeführt werden.

Man kann auch eine vollständige Sicherung (eine Kopie der gesamten Datenbank), eine inkrementelle Sicherung (eine wiederholte Kopie der an der Datenbank vorgenommenen Änderungen) und eine differenzielle Sicherung (eine Kopie der Änderungen der letzten Sicherung) erstellen.

Teil des relationalen Datenbankpakets ist mysqldump, das ein Kommandozeilenprogramm für MySQL-Datenbank-Backups ist. Es kann verwendet werden, um Daten in einer einzigen Datei mit SQL-Anweisungen wiederherzustellen, und kann dann verwendet werden, um Daten in ihrem ursprünglichen Format wiederherzustellen. Mit diesem Befehl kann man Tabellen, mehrere Datenbanken oder alle Daten des Servers für die weitere Migration auf einen anderen Server dumpen.

Insgesamt kann man einen Befehl leicht verwenden. Viele Benutzer stoßen jedoch auf Komplikationen, wenn sie die Datenbank aus einer Sicherungsdatei mit SQL-Befehlen wiederherstellen. Die Wiederherstellung der Datenbank kann bei einer großen Datenbank eine Weile dauern, da mysqldump alle SQL-Anweisungen zum Einfügen der Daten ausführt.

Beachten Sie, dass mysqldump bestimmte Tabellen nicht dumpen kann, darunter temporäre Tabellen, Systemtabellen, Performance-Schema-Tabellen (aus der Performance-Schema-Datenbank) und Informationsschema-Tabellen (aus der Informationsschema-Datenbank). Der Befehl bietet jedoch Anpassungsmöglichkeiten: Sie können bestimmte Arten von Tabellen oder Daten mit dem Befehl --ignore-table aus dem Dump ausschließen oder die Option --no-data verwenden, um die Tabellenstruktur beizubehalten, während die Daten entfernt werden.

Welche Aufgaben erleichtert Mysqldump?

Das Dienstprogramm Mysqldump erleichtert folgende Aufgaben:

• Datenbank-Backups;
• Datenbank-Migration;
• Datenbankversionierung (Sie können verschiedene Versionen Ihrer Datenbank erstellen, zu denen Sie später zurückkehren können);
• Umgebungsreplikation (mit diesem Befehl können Sie die Produktionsdaten replizieren, um eine stabile Umgebung für Entwicklung oder Tests zu schaffen);

Struktur der exportierten Daten

Die exportierten Daten bestehen aus:

• Komprimierte Dateien in den Formaten gz (gzip) oder .bz2 (bzip2);
• SQL-Anweisungen in Form eines SQL-Skripts, das zur Wiederherstellung der Struktur und der Daten der Datenbank verwendet wird;
• XML-formatierte Daten zur leichteren Integration von Daten in andere Systeme;
• Tabulatorgetrennte Textdateien für den Datenimport und -export;
• Kommagetrennte Werte oder CSV-Dateien für den Datenaustausch;
• Benutzerdefinierte Formate werden für die Anpassung des Ausgabeformats verwendet;

Privilegien für die Verwendung von Mysqldump

Um den Befehl mysqldump zu verwenden, sind mindestens volle Leseberechtigungen erforderlich. Um die erweiterte Funktionalität zu nutzen, sind die folgenden Rechte erforderlich:

• SHOW VIEW (für den Zugriff auf View-Definitionen);
• SELECT (für das Lesen von Daten);
• LOCK TABLES (zum Sperren von Tabellen);
• RELOAD (für die Verwendung der Option --flush-privileges);

Beispiele für MySQL Dump

Wie bereits erwähnt, können Sie mit dem Befehl mysqldump bestimmte Tabellen, eine einzelne Datenbank, mehrere Datenbanken und alle Datenbanken des MySQL-Servers sichern. Schauen wir uns nun die einzelnen Beispiele an.

Sichern bestimmter MySQL-Tabellen

Um bestimmte Tabellen aus einer MySQL-Datenbank zu sichern, führen Sie den folgenden Befehl aus:

mysqldump -u my_user -p database_name table1 table2 > tables.sql

hier:

• my_user - steht für Ihren MySQL-Benutzernamen;
• -p - steht für das Passwort des MySQL-Benutzers;
• database_name - ist der Name der Datenbank, die die benötigten Tabellen enthält;
• table1 table2 - steht für die Namen der Tabellen, die gesichert werden sollen;

Wenn Sie mehr Tabellen sichern wollen, können Sie das gleiche Prinzip anwenden und die Namen der Datenbanken hinzufügen, die Sie zusätzlich sichern wollen.

Nach Abschluss der Sicherung enthält die von mysqldump erstellte Datei "tables.sql" die SQL-Befehle, die zur Wiederherstellung der Tabellen und der darin enthaltenen Daten erforderlich sind.

Sichern einer einzelnen Datenbank

Wenn Sie eine einzelne Datenbank sichern wollen, müssen Sie den folgenden Befehl verwenden:

mysqldump -u my_user -p database_name > backup.sql

Hier:

• my_user - steht für Ihren MySQL-Benutzernamen;
• -p - steht für das Passwort des MySQL-Benutzers;
• database_name - steht für den Namen der Datenbank, die Sie sichern wollen;

Nachdem Sie den Befehl ausgeführt haben, enthält die Datei "backup.sql" die Daten, die erforderlich sind, um die Datenbank in ihrer ursprünglichen Form wiederherzustellen.

Sichern von mehreren Datenbanken

Um mehrere Datenbanken zu sichern, müssen Sie das --databases-Flag zum mysqldump-Befehl hinzufügen, wie folgt:

mysqldump -u my_user -p --databases db1 db2 db3 > backup.sql

Hier:

• my_user - steht für Ihren MySQL-Benutzernamen;
• -p - steht für das Passwort des MySQL-Benutzers;
• db1 db2 db3 - steht für die Namen der Datenbanken, die Sie sichern wollen;

Sichern aller Datenbanken des MySQL-Servers

Wenn Sie alle Datenbanken eines MySQL-Servers sichern wollen, müssen Sie das Flag --all-databases hinzufügen, wenn Sie den Befehl mysqldump verwenden:

mysqldump -u my_user -p --all-databases > all_databases.sql

Die endgültige Datei all_databases.sql enthält die notwendigen SQL-Befehle, um die gesicherten Datenbanken wiederherzustellen.

So stellen Sie eine Datenbank auf einem entfernten Server wieder her

Um gesicherte Datenbanken auf einem neuen Server zu replizieren, müssen wir zunächst sicherstellen, dass der betreffende Server über eine leere Datenbank oder deren Umriss verfügt.

Zu diesem Zweck können Sie überprüfen, ob der Server bereits eine Datenbank hat, oder eine erstellen, wenn dies nicht der Fall ist:

mysql -u root -pYOUR_PASSWORD -e "CREATE DATABASE destination_db

Mit diesem Befehl wird eine Datenbank auf einem neuen Server erstellt.

Anschließend können Sie Tabellen oder Datenbanken aus den erstellten Sicherungen einfach wiederherstellen. Am Beispiel einer einzelnen Datenbank können Sie mit dem folgenden Befehl eine Datenbank aus den Sicherungsdateien wiederherstellen:

mysql -u root -pYOUR_PASSWORD destination_db < single_table_dump.sql

Zusammenfassung

Mysqldump ist ein vielseitiges Dienstprogramm, das die Sicherung von Datenbanken auf eine Art und Weise ermöglicht, die auch für Anfänger zugänglich ist. Mit diesem einen Befehl kann die gesamte Datenbank in eine einzige Datei gepackt werden, aus der Sie dann die Datenbank neu erstellen können. Mysqldump ist so flexibel, dass der Benutzer auswählen kann, welcher Teil der Datenbank gesichert werden soll: bestimmte Tabellen, die gesamte Datenbank, mehrere Datenbanken oder alle Datenbanken des Servers. Insgesamt kann dieses Tool für diejenigen, die regelmäßig mit Datenbanken zu tun haben, sehr nützlich sein.

FAQ

Was bewirkt das Flag --single-transaction in mysqldump?

Kurz gesagt, --single-transaction rationalisiert ein Datenbank-Backup durch nur eine Transaktion. Das bedeutet, dass mysqldump mit einer einzigen Transaktion durchgeführt werden kann, vorausgesetzt, die Datenbank bleibt während des gesamten Sicherungsprozesses konsistent.

Welche Methoden können eingesetzt werden, um große Tabellen effektiv zu dumpen?

Sie können --single-transaction und -quick kombinieren, um große Datenbanken schneller zu dumpen.

Dieser Ansatz eignet sich am besten für InnoDB-Tabellen, da er weniger RAM verbraucht und die Konsistenz des Dump-Prozesses gewährleistet.

Wie können Tabellen während des mysqldump-Prozesses ignoriert werden?

Sie können bestimmte Tabellen mit der Option --ignore-table ignorieren. Das kann etwa so aussehen:

mysqldump -u root -pmypassword my_db --ignore-table=my_db.table_to_ignore > my_db.sql

Die Tabellen, die Sie ignorieren wollen, werden also mit --ignore-table=DATABASE_NAME.TABLE_TO_IGNORE gemeldet.

Sie können auch alle Tabellen in einer Datenbank oder die gesamte Datenbank ignorieren, dann müssen Sie den Teil mit der Benachrichtigung wiederholen, damit alle Tabellen einbezogen werden:

mysqldump -u root -pmypassword --ignore-table="meine_db.table1" --ignore-table="meine_db.table2" --ignore-table="meine_db.table3" > all_databases.sql

Wie sollte man vorgehen, um binäre BLOB-Daten mit mysqldump zu dumpen?

Wenn eine Datenbank binäre Daten enthält, können Sie auf einige Probleme stoßen. Wenn Sie eine MySQL-Datenbank mit binären Daten dumpen müssen, verwenden Sie das --hex-blob Flag. Diese Option sortiert binäre Strings und bringt sie in ein unterscheidbares Format. So wollen Sie binäre Daten ausgeben:

mysqldump -u root -pmypassword my_bin_db --hex-blob > my_bin_db.sql

Ist es möglich, die "where"-Klausel mit mysqldump zu verwenden?

Sie können die "where"-Klausel mit mysqldump verwenden, um die Daten, die Sie aus der Datenbank sichern wollen, leichter herauszufiltern. Die "where"-Klausel leitet die Kette von Bedingungen ein und nimmt die Daten, die den Kriterien entsprechen:

mysqldump -u root -pmypassword wpdb --tables thetable --where="date_created > '2017-04-27'" > wpdb_myrecord.sql