[HOWTO] mySQL-Import mit großem Dump und Fortschrittsbalken

Der Import von großen mySQL-Dumps kann sehr zeit- und nervenraubend sein. Dieser Blog-Beitrag widmet sich dem SQL-Import von großen Datenmengen, der Optimierung beim Import und der Anzeige eines Fortschrittsbalkens während des Imports.

Aber alles der Reihe nach…

1) Optimieren des SQL-Dumps

Beim Import versucht mySQL nach jedem INSERT die Zeile zu commiten und auf die Festplatte zu schreiben. Um das zu verhindern wird die Logik umgestellt und erst die Datensätze übermittelt und dann commited:

Wir fügen zu Beginn und am Ende des SQL-Dumps folgende Zeilen ein:

SET FOREIGN_KEY_CHECKS=0; SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”; SET AUTOCOMMIT=0; SET unique_checks=0; START TRANSACTION;

/ SQL Statements /

SET unique_checks=1; SET FOREIGN_KEY_CHECKS=1; COMMIT;

Wenn die SQL-Datei mehrere Gigabyte groß ist, dann gestaltet sich das Öffnen des Dumps mit vim sehr zeitaufwendig. Daher können die Statements per sed eingefügt werden:

sed ’1iSET FOREIGN_KEY_CHECKS=0; SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”; SET AUTOCOMMIT=0; SET unique_checks=0; START TRANSACTION;’ < DUMP.sql > DUMP2.sql

rm DUMP.sql

sed ‘$aSET unique_checks=1; SET FOREIGN_KEY_CHECKS=1; COMMIT; SET AUTOCOMMIT = 1;’ < DUMP2.sql > DUMP3.sql

rm DUMP2.sql

2) Optimierung

In der my.cnf (evtl. unter /etc/my.cnf oder /etc/mysql/my.cnf) nehmen wir noch optional zwei Optimierungen vor, die NACH dem Import wieder entfernt werden müssen:

key_buffer_size = (erhöhen auf ca. 20-25% vom verfügbaren RAM)

innodb_flush_log_at_trx_commit

Danach den mysql neustarten

/etc/init.d/./mysql restart

3) pv installieren

Bevor wir den Import starten installieren wir pv (monitor the progress of data through a pipe):

apt-get install pv

bzw.

zypper install pv

Statt pv kann auch bar von http://clpbar.sourceforge.net/ verwendet werden

4) Import starten

Nun starten wir den Import unter Zuhilfenahme von pv für die Fortschrittsanzeige:

pv DUMP3.sql | mysql -uUSERNAME –p’PASSWORD‘ DATABASE_NAME

Das Password kann in Hochkommata gesetzt werden wenn Sonderzeichen verwendet werden (/*…). Nun startet der Import und kann über die Progressbar verfolgt werden:

761MB 0:05:07 [ 310kB/s] [=======> ] 27% ETA 0:13:27

Fehler beim Import: Wenn man trotz Fehler beim Import den Vorgang fortsetzen möchte, dann startet man mysql mit dem zusätzlichen Parameter –f (–force).

5) Abschluss

Nach dem Import ändern wir von Punkt 2) in der my.cnf die Parameter wieder auf die Ursprungseinstellungen zurück und starten den mysql neu:

/etc/init.d/./mysql restart