Certified MySQL Associate, Kapitel 32.4

32.4.1 Making Text Backups via SQL

Wie bereits in 15.2.2 "Exporting Data with SELECT ... INTO OUTFILE" kann man die Anweisung SELECT ... INTO OUTFILE dazu nutzen, um Inhalten von Datenbanken und Tabellen in eine Textdatei zu exportieren. Der Export an sich hat die Form einer Tabelle.

Hier noch mal als Übersicht die wichtigsten Eigenschaften von SELECT ... INTO OUTFILE

- Der Export kann auf lokalen oder entfernten Servern über den MySQL-Client erfolgen.
- Die resultierende Export-Datei wird immer auf dem Serverhost erstellt
- Das Statement SELECT ... INTO OUTFILE funktioniert bei jeder Storage Engine (MyISAM, etc)
- Das Statement benötigt FILE-Rechte
- Das Ausgabe-Format kann beeinflusst werden (Trennzeichen, Escape-Sequenzen, etc)

32.4.2 Making Text Backups with mysqldump

Das wohl häufiger verwendete mysqldump exportiert ebenfalls Datenbank- und Tabelleninhalte. Hierbei werden aber Textdateien generiert, welche vollständige MySQL-Statements enthalten, so dass beispielsweise alle Datenbanken, spezielle Datenbanken oder spezielle Tabellen exportiert werden können. Außerdem wird mysqldump über die Shell bzw. Windows Eingabeaufforderung gestartet, so dass sich mysqldump auch für automatisierte Skripte eignet.

- mysqldump kann alle Datenbanken, spezielle Datenbanken oder spezielle Tabellen exportieren
- mysqldump kann lokale und entfernte Server sichern, obwohl der Befehl lokal gestartet wurde
- es können alle Tabellen exportiert werden, die von unterschiedlichen Storage Engines angelegt wurden
- Die exportierten Dateien können auf andere Server portiert werden

Folgendermaßen kann man einen Standard-MySQL-Dump erstellen:

# Die Datenbank db01 exportieren. Der Result wird in backup.sql gespeichert
shell> mysqldump db01 > backup.sql

# Nur die Tabellen tbl01 + tbl02 aus db01 sollen gesichert werden
shell> mysqldump db01 tbl01 tbl02 > backup_nur_2_tables.sql

# Mehrere Datenbanken sichern
shell> mysqldump --databases db01 db02 > backup_2_datenbanken.sql

# Alle Datenbanken sichern
shell> mysqldump --all-databases > alles.sql

Hier die wichtigsten Optionen in Verbindung mit MySQL-Dump

--add-drop-table
mysqldump legt für jede Tabelle ein DROP TABLE Statement an, so dass beim Wiedereinspielen des Dumps die entsprechende Tabelle gelöscht wird, bevor sie neu angelegt wird.

--add-locks
Fügt "locks" um INSERT-Statements herum an. Jede INSERT-Anweisung wird mit einer LOCK TABLES- und eine UNLOCK TABLES-Anweisung umklammert. Beschleunigt INSERT-Statements.

--create-options
Fügt alle MySQL-spezifischen Tabellenoptionen in die CREATE TABLE-Anweisungen ein. Dieses erhöht die Wahrscheinlichkeit, dass die Dumps auch in anderen Datenbanksystemen eingespielt werden können.

--disable-keys, -K
Diese Option ist nur bei MyISAM wirksam und bewirkt, dass zusätzliche ALTER TABLE-Statements hinzugefügt werden, welche das Index Updating ein oder ausschalten. Der Dump wird schneller eingelesen, weil die Indizes erst nach Einfügen aller Datensätze erstellt werden.

--extended-insert, -e
Standardmäßig erzeugt mysqldump für jeden Zeile einer Tabelle ein separates INSERT-Statement. Mit der Option --extended-insert werden für eine INSERT Anweisung mehrer Zeilen eingefügt, welches mithilfe mehrerer VALUES-Listen möglich ist. --extended-insert beschleunigt zwar den Import, ist aber weniger kompatibel zu anderen Datenbank-Systemen.

--flush-logs, -F
Synchronisiert bzw. leert die Logdateien, bevor der Dump gestartet wird. Somit kann festgelegt werden, dass Recovery-Aktionen zu genau diesem Zeitpunkt erfolgen können.

--lock-tables, -l
Sperrt alle Tabellen vor Beginn des Dumps mithilfe von READ LOCAL. Erhöht die Wahrscheinlichkeit, dass die Datenbanken konsistent sind.

--no-create-db, -n
Es werden keine CREATE DATABASE Statements in dem Dump angelegt

--no-create-info, -t
Es werden keine CREATE TABLE Statements in dem Dump angelegt

--no-data, -d
Es werden keine Datensätze exportiert. Nützlich, um nur Tabellenstrukturen zu exportieren.

--opt
Kurzform für --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. Kann mit --skip-opt deaktiviert werden. Um nur einige der mit --opt aktivierten Optionen zu deaktivieren, verwendet man --skip-Optionen (--skip-add-drop-table, --skip-quick).

--quick
Diese Option ist nützlich, wenn man große Datenbanken exportieren möchte. Anstatt die komplette Datenbank einzulesen, um sie dann zu exportieren, schreibt mysqldump --quick Zeile für Zeile in die Ausgabedatei.

--single-transaction
Diese Option ist empfehlenswert, wenn man InnoDB einsetzt. Diese Option setzt eine SQL-Anweisung BEGIN, bevor der Speicherauszug vom Server durchgeführt wird. Somit wird der konsistente Stand exportiert, der bei Begin des Dumps vorhanden war.