Frage Wie kann ich eine MySQL-Wiederherstellung aus einer Dump-Datei beschleunigen?


Ich stelle eine Datenbank mit 30 GB aus einer mysqldump-Datei in eine leere Datenbank auf einem neuen Server wieder her. Wenn die SQL aus der Dump-Datei ausgeführt wird, startet die Wiederherstellung sehr schnell und wird dann langsamer und langsamer. Einzelne Einsätze benötigen jetzt 15+ Sekunden. Die Tabellen sind meist MyISAM mit einer kleinen InnoDB. Der Server hat keine anderen aktiven Verbindungen. SHOW PROCESSLIST; zeigt nur die Einfügung aus der Wiederherstellung (und die Show-Prozessliste selbst).

Hat jemand irgendwelche Ideen, was die dramatische Verlangsamung verursachen könnte?

Gibt es irgendwelche MySQL-Variablen, die ich ändern kann, um die Wiederherstellung während des Fortschritts zu beschleunigen?


25
2018-05-29 15:37


Ursprung


Bearbeitet, um die Tabellentypen zu korrigieren - Dave Forgac


Antworten:


Eine Sache, die den Prozess verlangsamen kann, ist die Schlüssel_Puffer_GrößeDies ist die Größe des für Indexblöcke verwendeten Puffers. Stellen Sie dies auf mindestens 30% Ihres Arbeitsspeichers ein, oder die Neu-Indizierung wird wahrscheinlich zu langsam sein.

Wenn Sie InnoDB und Fremdschlüssel verwenden, können Sie die Überprüfung von Fremdschlüsseln auch deaktivieren und am Ende erneut aktivieren (mit SET FOREIGN_KEY_CHECKS=0 und SET FOREIGN_KEY_CHECKS=1).


24
2018-05-29 16:00



Ich habe zwei Dinge gefunden: Die key_buffer_size wurde auf 8MB gesetzt und es gab eine InnoDB-Tabelle im Mix mit Fremdschlüsseln. Die key_buffer_size wurde auf 1 GB erhöht und die Überprüfung des Fremdschlüssels vorübergehend deaktiviert. Die Wiederherstellung endete in 5 Minuten. Vielen Dank! - Dave Forgac
Beeindruckend! Froh, dass es geholfen hat :) - Marco Ramos
Ich habe gerade gemerkt, dass ich '5' Minuten eingegeben habe. Ich bin mir ziemlich sicher, es war mehr wie 50 Minuten, aber immer noch viel vernünftiger ;-) - Dave Forgac
key_buffer_size ist für MYISAM. - Fernando Fabreti
@FernandoFabreti - das ist ein wichtiger Punkt für viele Leser, aber das OP gab an, dass sie hauptsächlich MyISAM hatten - mc0e


Dieser Link zeigt, was man tun kann, um den Wiederherstellungsprozess zu beschleunigen.

http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

Man kann die Befehle an den Anfang der Dump-Datei setzen

SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;

Und setzen Sie diese Anweisungen am Ende der Dump-Datei

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET AUTOCOMMIT = @OLD_AUTOCOMMIT;
COMMIT;

Das hat für mich funktioniert. Glückliche Wiederherstellung :-)


20
2018-01-20 06:14



Das hat ziemlich geholfen. Anstatt die Datei zu bearbeiten, habe ich pre.sql und post.sql aus den obigen Snippets erstellt und diese verwendet, um die db wiederherzustellen: cat pre.sql dump.sql post.sql | mysql ... - Jason R. Coombs


Der einzige Grund, warum ich mir vorstellen kann, warum die Wiederherstellung langsam verlangsamt wird, ist die Indizierung. Erforsche das Ausschalten der Indizierung bis zum Ende und lass es dann die ganze Menge auf einmal machen.


1
2018-05-29 21:23





Wenn Sie die physische Kopie der Dump-Datei (des DB-Verzeichnisses) haben, können Sie sie einfach auf den neuen Server kopieren, wenn der neue Server die gleiche MySQL-Version hat und es funktioniert. Dies funktioniert gut mit MyISAM und für mich ist es besser, als die Daten basierend auf der logischen SQL-Dump-Datei wiederherzustellen.


1
2018-06-16 11:08





Wenn Sie mehrere Tische haben, können Sie davon profitieren mk-parallel-wiederherstellen.


0
2018-05-29 18:24



Dies ist jetzt veraltet und sollte nur zum Wiederherstellen von Testdaten und nicht zum Wiederherstellen von tatsächlichen Sicherungen verwendet werden. - svandragt


Ich habe dich vorgeschlagen,

  1. Überprüfen Sie Ihre Tabellen: Hat es Trigger? Alle Auslöser löschen
  2. EINSTELLEN: AUTOCOMMIT=0, UNIQUE_CHECKS=0, FOREIGN_KEY_CHECKS=0 (UND VERGESSEN SIE NICHT, DIESE ÄNDERUNGEN ZU ROLLBACKEN)
  3. Verwenden Sie Befehlszeile wie mysql -u root -pPasss requests < mydb.sql
  4. Überprüfen Sie die Größe Ihrer Datenbankdatei

Viel Glück


-1
2017-07-26 21:56