In der täglichen Wartungsarbeit sind häufig Datenexportoperationen erforderlich, und mysqldump ist ein oft verwendeter Tool zum Exportieren von Daten. Er verfügt über viele integrierte Funktionsparameter, und in diesem Artikel werden einige häufig verwendete Operationen aufgelistet.
Was ist mysqldump?
mysqldump ist ein MySQL-Client-Programm zum Sichern von Datenbanken. Es erzeugt hauptsächlich eine Reihe von SQL-Anweisungen, die in einer Datei zusammengefasst werden können. Diese Datei enthält alle SQL-Befehle, die zur Rekonstruktion Ihrer Datenbank benötigt werden, wie beispielsweise CREATE DATABASE, CREATE TABLE, INSERT, etc. Es kann für leichte und schnelle Datenbankmigrationen oder Wiederherstellungen verwendet werden. Es ist eine Methode zur Durchführung logischer Sicherungen in MySQL-Datenbanken.
Wie exportiert man eine MySQL-Datenbank mit mysqldump?
1. Alle Datenbanken exportieren
<pDieser Befehl exportiert alle Datenbanken, einschließlich der Systemdatenbanken.
mysqldump -uroot -proot --all-databases > /tmp/all.sql
2. Exportieren Sie alle Daten aus den Datenbanken db1 und db2
mysqldump -uroot -proot --databases db1 db2 > /tmp/user.sql
Exportiert mehrere Datenbanken. Alle nachfolgenden Parameter nach der Option gelten als Datenbanknamen.
Fügen Sie eine DROP DATABASE-Anweisung hinzu, bevor Sie jede Datenbank erstellen.
mysqldump -uroot -p --databases test --add-drop-database
3. Tabellen a1 und a2 aus db1 exportieren
Hinweis: Das Exportieren bestimmter Tabellen kann nur für eine einzelne Datenbank durchgeführt werden, und der exportierte Inhalt unterscheidet sich vom Export einer Datenbank. Der exportierte Text für bestimmte Tabellen enthält keine Anweisungen zur Datenbankerstellung; er enthält nur DROP TABLE, CREATE TABLE und INSERT-Anweisungen.
mysqldump -uroot -proot --databases db1 --tables a1 a2 > /tmp/db1.sql
Überschreiben Sie den Parameter --databases und geben Sie die zu exportierenden Tabellen an.
mysqldump -uroot -p --host=localhost --databases test --tables test
Alle Tablespaces exportieren.
mysqldump -uroot -p --databases test --all-tablespaces
Schließen Sie alle Tablespace-Informationen aus dem Export aus.
mysqldump -uroot -p --databases test --no-tablespaces
Fügen Sie vor dem Erstellen jeder Tabelle eine DROP TABLE-Anweisung hinzu (standardmäßig aktiviert, verwenden Sie --skip-add-drop-table, um sie zu deaktivieren).
mysqldump -uroot -p --databases test
mysqldump -uroot -p --databases test --skip-add-drop-table
4. Bedingter Export: Datensätze mit id=1 aus Tabelle a1 in db1 exportieren
Die bedingte Exportfunktion kann nur für eine einzelne Tabelle ausgeführt werden.
mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' > /tmp/a1.sql
Es werden nur Datensätze exportiert, die durch die angegebene WHERE-Bedingung ausgewählt werden. Falls die Bedingung Befehlsinterpreter, Leerzeichen oder Sonderzeichen enthält, muss sie in Anführungszeichen gesetzt werden.
mysqldump -uroot -p --host=localhost --databases db1 --where="user='root'"
5. Nur Tabellenstrukturen exportieren, ohne Daten
mysqldump -uroot -proot --no-data --databases db1 > /tmp/db1.sql
Exportieren Sie nur die Daten, ohne CREATE DATABASE-Anweisungen hinzuzufügen.
mysqldump -uroot -p --host=localhost --databases db1 --no-create-db
Exportieren Sie nur die Daten, ohne CREATE TABLE-Anweisungen hinzuzufügen.
mysqldump -uroot -p --host=localhost --databases db1 --no-create-info
Bestimmte Tabellen ausschließen. Um mehrere Tabellen zu ignorieren, die Option für jede Tabelle wiederholen. Jede Tabelle muss den Datenbank- und Tabellennamen enthalten, z. B. --ignore-table=database.table1 --ignore-table=database.table2 ...
mysqldump -uroot -p --host=localhost --databases db1 --ignore-table=mysql.user
Verwenden Sie den verzögerten Einfügemodus (INSERT DELAYED) beim Exportieren von Daten.
mysqldump -uroot -p --databases db1 --delayed-insert
Verwenden Sie INSERT-Anweisungen mit mehreren VALUES-Klauseln. Dadurch wird die Größe der Exportdatei reduziert und der Import beschleunigt. Standardmäßig aktiviert; verwenden Sie --skip-extended-insert, um es zu deaktivieren.
mysqldump -uroot -p --databases db1
mysqldump -uroot -p --databases db1 --skip-extended-insert
6. Daten zwischen Servern exportieren und importieren
mysqldump --host=h1 -uroot -proot --databases db1 | mysql --host=h2 -uroot -proot db2
Dies bedeutet, alle Daten aus db1 auf Server h1 zu exportieren und in db2 auf h2 zu importieren. Die Datenbank db2 muss existieren; andernfalls tritt ein Fehler auf.
Verwenden Sie eine benannte Pipe, um eine Verbindung zu MySQL herzustellen.
mysqldump -uroot -p --host=localhost --all-databases --pipe
mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
Komprimierung für die Kommunikation zwischen Client und Server aktivieren.
mysqldump -uroot -p --all-databases --compress
7. Binlog-Position und Dateinamen der Masterdatenbank an die Exportdatei anhängen
Diese Option ist, wenn sie auf einem Slave-Server ausgeführt wird, äquivalent zur Ausführung von SHOW SLAVE STATUS.
Festlegen auf 1 gibt einen CHANGE MASTER-Befehl in der Datendatei aus.
Die Einstellung auf 2 fügt Kommentare vor CHANGE MASTER hinzu.
Diese Option aktiviert --lock-all-tables, es sei denn, --single-transaction wird angegeben.
Nach der Ausführung wird --lock-tables automatisch deaktiviert. Der Standardwert für --dump-slave ist 1.
mysqldump -uroot -proot --dump-slave=1 --database db1 > /tmp/db1.sql
mysqldump -uroot -proot --dump-slave=2 --database db1 > /tmp/db1.sql
Exportieren Sie Ereignisse.
mysqldump -uroot -p --all-databases --events
8. Protokolle vor dem Export löschen
Hinweis: Beim Exportieren mehrerer Datenbanken (--databases oder --all-databases) werden die Logs pro Datenbank geleert. Bei Verwendung von --lock-all-tables oder --master-data werden die Logs einmalig geleert, wobei alle Tabellen gesperrt werden. Um gleichzeitig zu exportieren und die Logs zu leeren, verwenden Sie --lock-all-tables oder --master-data zusammen mit --flush-logs.
mysqldump -uroot -p --all-databases --flush-logs
9. Stellen Sie eine konsistente Zustandsübertragung beim Export sicher
Bevor Daten exportiert werden, gibt diese Option eine BEGIN-SQL-Anweisung aus. BEGIN blockiert keine Anwendungen und stellt die Konsistenz der Datenbank während des Exports sicher. Sie ist nur für Mehrversionsspeicher-Engines (z. B. InnoDB) geeignet. Diese Option schließt die Option --lock-tables aus, da LOCK TABLES implizit alle ausstehenden Transaktionen bestätigt. Für den Export großer Tabellen sollte sie zusammen mit der Option --quick verwendet werden.
mysqldump -uroot -p --host=localhost --all-databases --single-transaction
10. Gespeicherte Prozeduren und Funktionen exportieren
mysqldump -uroot -p --host=localhost --all-databases --routines
Eine andere Methode: Verwendung von SELECT...INTO OUTFILE, um MySQL-Daten zu exportieren
SELECT...INTO OUTFILE ist eine MySQL-Syntax, die verwendet wird, um Abfrageergebnisse in eine Datei zu exportieren. In MySQL können Sie mit dieser Anweisung Daten einfach in eine Textdatei exportieren.
Grundlegende Verwendung:
SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;
Beispiel:
SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;
In der obigen SQL-Anweisung haben wir die Spalten id, name und email aus der Tabelle users ausgewählt und die Ergebnisse in die Datei /tmp/user_data.csv geschrieben. FIELDS TERMINATED BY ',' legt den Spaltentrenner (Komma) fest und LINES TERMINATED BY '\n' den Zeilentrenner (Zeilenumbruch).
Wichtige Hinweise:
Die Ausführung von SELECT...INTO OUTFILE erfordert die entsprechenden Berechtigungen, und das Verzeichnis der Ausgabedatei muss ein Ort sein, in den der MySQL-Server schreiben kann.
Im folgenden Beispiel exportieren wir Daten aus der Tabelle runoob_tbl in die Datei /tmp/runoob.txt:
mysql> SELECT * FROM runoob_tbl
-> INTO OUTFILE '/tmp/runoob.txt';
Sie können das Ausgabeformat mithilfe von Befehlsoptionen festlegen. Das folgende Beispiel exportiert Daten im CSV-Format:
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
Eigenschaften von SELECT ... INTO OUTFILE:
LOAD DATA INFILE ist die umgekehrte Operation von SELECT ... INTO OUTFILE. Um Datenbankdaten in eine Datei zu schreiben, verwenden Sie SELECT ... INTO OUTFILE, und um die Datei wieder in die Datenbank zu laden, verwenden Sie LOAD DATA INFILE.
Eine SELECT-Anweisung in der Form SELECT...INTO OUTFILE 'file_name' schreibt die ausgewählten Zeilen in eine Datei. Da die Datei auf dem Server-Host erstellt wird, benötigen Sie das FILE-Privileg, um diese Syntax verwenden zu können.
Die Ausgabedatei darf noch nicht existieren, um Datenmanipulationen zu verhindern.
Sie benötigen ein Konto mit Zugriff auf die Serveranmeldung, um die Datei abzurufen. Andernfalls funktioniert SELECT ... INTO OUTFILE nicht.
Unter UNIX-Systemen ist die Datei nach ihrer Erstellung lesbar, und ihre Berechtigungen gehören dem MySQL-Server. Das bedeutet, dass Sie die Datei zwar lesen, sie aber möglicherweise nicht löschen können.
Ultimativer Schutz für Ihr Datenbank-Ökosystem
In einer Ära, in der Datenausfallsicherheit entscheidend ist, ist die Wahl einer Backup-Lösung mit globaler Skalierbarkeit und Multi-Cloud-Flexibilität entscheidend. Vinchin Backup & Recovery zeichnet sich durch einen leistungsstarken MySQL-Schutz aus, der auf internationale Unternehmen zugeschnitten ist:
✅ Umfassende Sicherungsstrategien
Unterstützt Hot-Backup, Voll-Backup, inkrementelles Backup und Transaktionsprotokoll-Backup-Strategien;
✅ Unterstützung für universelle Datenbanken
Einheitlichen Schutz für MySQL, Oracle, SQL Server und mehr in hybriden Umgebungen (physisch/Cloud/VM) gewährleisten.
Vereinfachen Sie das Management mit einer einzigen Konsole für Backup-Richtlinien mehrerer Datenbanken.
✅ Anti-Ransomware-Verstärkung
Sicherung der Daten in verschlüsselter Speicherung + Funktion gegen Ransomware, um bösartigen Angriffen zu widerstehen.
✅ Globale Unterstützung, lokale Effizienz
Technische Unterstützung rund um die Uhr und kostenoptimierte Lizenzierung, ideal für KMU und verteilte Teams.
Um MySQL-Datenbanksicherungsaufgaben mit Vinchin zu erstellen, gehen Sie bitte zur Seite Physical Backup > Database Backup > Backup:
1. Wählen Sie die Datenbanken aus, die gesichert werden müssen.
2. Wählen Sie einen Sicherungsknoten aus, auf dem die Sicherungsdaten verarbeitet und gespeichert werden sollen.
3. Konfigurieren Sie Sicherungsstrategien entsprechend Ihren Anforderungen.
4. Überprüfen und bestätigen Sie die Einstellungen.
Vinchin wird von Finanzinstitutionen und multinationalen IT-Infrastrukturen in über 60 Ländern vertraut. Klicken Sie auf die Schaltfläche unten, um die kostenlose 60-Tage-Testversion von Vinchin auszuprobieren und eine effiziente und zuverlässige Datenbackup-Lösung zu erleben!
Fazit
In diesem Artikel haben wir verschiedene mysqldump-Optionen zur effizienten Exportierung von MySQL-Datenbankdaten untersucht, von vollständigen Datenbankexporten bis hin zu granulareren Exporten wie einzelnen Tabellen oder bestimmten Datensätzen. Das Verständnis dieser Befehle und ihrer Funktionalität hilft dabei, Prozesse wie Datensicherung, Migration und Wiederherstellung der Datenbank zu optimieren.
