Sehr schneller import mit MySqlDump und LOAD DATA INFILE
Hin und wieder möchte man große Datenmengen aus einer MySQL Datenbank sichern und diese wiederherstellen.
Der Umgang mit Umlauten und anderen länderspezifischen Sonderzeichen (latin1 oder UTF-8) erschwert die Handhabung solcher Datensicherungen und macht die Backup- und Restore-Prozedur oft zu einer mühenvollen Angelegenheit.
Häufig genügt die Verwendung von mysqldump unter der Angabe von Benutzernamen und Passwort in Verbindung mit dem Namen der zu exportierenden Datenbank.
mysqldump -u root -p mydatabase > my_backup_file.sql
Der obenstehende Befehl wird in den meisten Fällen zum gewünschten Erfolg führen. Schema-Definition (CREATE-Statements) und Daten (INSERT-Statements) stehen dann in einer Datei.
Möchte man jedoch sehr große Tabellen exportieren und importien, empfielt sich eine Kombination aus mysqldump sowie dem SQL-Befehl LOAD DATA INFILE. Die Einfügegeschwindigkeit dieser Variante ist deutlich höher.
LOAD DATA INFILE lädt Daten mit sehr hoher Geschwindigkeit aus einer Datei. Dies bedeutet ein Dump, wie oben erstellt, in dem alle Tabellen und Daten in einer Datei stehen, kann in diesem Kontext nicht ohne weitere Verarbeitung verwendet werden. Um eine Ausgabe-Datei zu erzeugen, die LOAD DATA INFILE lesen kann, kommt ebenfalls mysqldump, diesmal jedoch mit der zusätzlichen Option -T, zu Einsatz.
mysqldump -T /tmp/tst --default-character-set=utf8 mydb -u root -p
Bei der Verwendung von mysqldump gilt es allerdings ein paar Hürden zu nehmen. So kommt es bei der Verwendung des Befehls u.u. zu Fehlermeldungen wie:
Got error: 1: Can't create/write to file '/tmp/tst/my_table.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
Bei näherer Betrachtung fällt auf, dass die Datei /tmp/tst/my_table.sql, die das zugehörige Create-Statement enthält angelegt werden konnte.
Es scheint demnach so, als würden Datendatei (my_table.txt) und Schema-Datei (my_table.sql) von unterschiedlichen Prozessen geschrieben werden. Genauer gesagt, scheint das Schema-File von mysqldump und die Datendatei vom MySQL-Server-Dienst erstellt zu werden.
Nachdem dem Ausgabeverzeichnis ausgiebig Schreibrechte zugeteilt wurden (auf Multi-User-Umgebungen besser so restriktiv wie möglich), funktioniert das Backup einwandfrei:
chmod 777 /tmp/tst
mysqldump -T /tmp/tst --default-character-set=utf8 mydb -u root -p
Die Dateien landen dann im Verzeichnis /tmp/tst
Für jede Tabelle wird dort eine .sql Datei mit dem zugehörigen CREATE-Statement sowie eine .txt Datei mit den entsprechende Daten erzeugt.
Um die Daten in eine Datenbank zu importieren, geht man wie folgt vor.
mysql> LOAD DATA INFILE '/tmp/tst/my_table.txt'
Natürlich muss die entsprechende Tabelle zuvor angelegt worden sein.
Die vollständige Syntax des LOAD DATA INFILE-Befehls findet sich in der MySQL Dokumentation.
Ein Nachteil dieser Variante ist, dass jede Tabelle einzeln importiert werden muss.
Aus diesem Grund empfiehlt sich, alternativ zu LOAD DATA INFILE. die Verwendung des Hilfsmittels mysqlimport.
mysqlimport empfängt eine Liste von Dateinamen, die dann mittels LOAD DATA INFILE importiert werden.
Mehr Informationen zu mysqlimport sind in der MySQL-Dokumentation zu finden.