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 na
- [HOWTO] mySQL-Import mit großem Dump und Fortschrittsbalken
\[HOWTO\] mySQL-Import mit großem Dump und Fortschrittsbalken
\[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/(oeffnet in neuem Tab) 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
