Restaurarea bazei de date MySQL este foarte lentă

Tocmai mi-am cumpărat un desktop de acasă nou. Am trei baze de date MySQL pe care vreau să le mut. Le-am aruncat pe toate folosind mysqldump înainte de a scoate unitatea de pe vechea mașină.

Restaurarea celei mai mari baze de date durează epocă (până acum 24 de ore). Refac prin:

mysql -uxxxx -p
Introduceți parola: aaaa
mysql> create database foo;
mysql> folosește foo;
mysql> începe tranzacția;
mysql> \. <dump-file>

Mă rog să îmi aduc aminte comite cand se termina asta!

Am abandonat prima încercare de restaurare, fără tranzacție, deoarece dura atât de mult.Intenționez să nu mă deranjez cu tranzacția atunci când refac celelalte două baze de date, deoarece pare să nu facă nicio diferență.

Pot face ceva pentru a accelera restaurarea? Ar fi trebuit să fac backup diferit - doar am folosit o vanilie mysqldump linie de comandă, cu --skip-coloană-statistici deoarece în caz contrar depozitul ar eșua, așa cum se propune în acest răspuns stackoverflow.

Pentru referință, noua mașină rulează Windows 10 și MySQL 8.0.25 Community; vechea mașină rula Windows 7 și MySQL 5.6.22.

Privind mai îndeaproape fișierele dump (pentru a găsi versiunea serverului), văd că tabelele sunt create cu indecșii lor, urmate de o mulțime de instrucțiuni INSERT INTO. Acest lucru nu pare deosebit de inteligent. Există vreo modalitate de a induce mysqldump pentru a produce un script care:

1. Creează tabelele fără indexuri
2. Inserează rândurile; și apoi
3. Creează indicii.

care ar părea o abordare mai bună pentru atunci când baza de date este restaurată. (Este puțin surprinzător că acesta nu este implicit!) Presupun că acest lucru este posibil cu MySQL - am folosit SQLLite în ultimii 3 ani și cu siguranță este posibil acolo.

Actualizați

Această întrebare nu este complet răspuns de Cum pot accelera o restaurare MySQL dintr-un fișier dump?, deoarece această întrebare se referă în special la tabelele MyISAM, iar ale mele sunt InnoDB. (Scuze. Probabil ar fi trebuit să menționez motorul de stocare în întrebarea mea inițială.)

Cu toate acestea, acest răspuns a motivat și a informat încercările mele de a rezolva problemele, pe care le documentez în propriul meu răspuns de mai jos.

În cele din urmă, am renunțat să aștept ca importul să se finalizeze, după >24 de ore.

Am modificat scripturile generate de mysqldump astfel încât să se creeze tabelele fără indecși secundari și să se creeze indecșii numai atunci când toți INTRODUCE declaraţiile fuseseră executate.Odată cu această modificare, importul se finalizase când am verificat la trei ore după pornire.

Apoi am modificat în continuare scriptul pentru a insera rândurile într-o tranzacție, prin adăugare SET AUTOCOMMIT=0; ÎNCEPE TRANZACȚIA; la început, și COMIT; la sfarsit. Cu această modificare, restaurarea s-a finalizat în 90 de minute.

În timp ce editam depozitele generate, am observat că acestea includ

/*!40000 ALTER TABLE `xxx` DISABLE KEYS */;

și

/*!40000 ALTER TABLE `xxx` ENABLE KEYS */;

înconjurând INTRODUCE declarații. Am privit în sus documentație pe aceste comenzi și am descoperit că se aplică numai tabelelor MyISAM. The documentație privind mysqldump utilitate afirmă că aceste linii (inclusiv delimitatorii de comentarii și numărul magic 40000) sunt generate dacă specificați --disable-keys pe linia de comandă. Documentația de utilitate mai precizează că va adăuga comportamentul „inserați totul într-o singură tranzacție” pe care l-am considerat de dorit dacă specificați --no-autocommit pe linia de comandă.

În rezumat, probabil că ar fi trebuit să iau backup-urile cu

 mysqldump --no-autocommit...

Cel putin in cazul meu (mysqldump versiunea 8.0.25 și un tabel InnoDb) the --disable-keys opțiunea nu face nicio diferență; ALTER TABLE ... ACTIVARE|DEZACTIVARE TASTE declarațiile (comentate și cu un număr magic) sunt întotdeauna generate. eu do trebuie să modificați dump-ul generat pentru a crea tabelele fără chei secundare și să le adăugați o dată pe toate INTRODU IN au fost executate instrucțiuni.