MySQL: nagy adatbázis visszaállítása dump-ból

Adatbázis: SQL, XML DB

Sziasztok!

Lenne egy olyan problémám, hogy anno mysqldump-al kimentettem magamnak egy 2GB-os adatbázist (ebből 1 tábla kb 2 GB, a több pár MB helyet foglal).
Próbáltam mostanában visszaállítani, nem sok sikerrel, a következő hibát kaptam:

Error 2013: Lost connection to MySQL server during query

Rákerestem, sok helyen írták, hogy ilyen problémát tapasztaltak, de a megoldások közül egyik sem működött nekem.

Valakinek van esetleg ötlete, tapasztalata, hogyan tudnám visszaállítani az adatbázist?
Előre is köszönök minden segítséget!

--update--

Pár plusz infó:

my.conf:
port = 3306
socket = /tmp/mysql.sock
key_buffer = 256M
max_allowed_packet = 4096M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

SHOW GLOBAL VARIABLES LIKE '%timeout%':
connect_timeout
10
delayed_insert_timeout
300
innodb_lock_wait_timeout
50
innodb_rollback_on_timeout
OFF
interactive_timeout
28800
lock_wait_timeout
31536000
net_read_timeout
30
net_write_timeout
60
slave_net_timeout
3600
wait_timeout
28800

SELECT @ @max_allowed_packet:
@@max_allowed_packet
1073741824

  • 6858 megtekintés

( Z0l v | 2013. 01. 10., cs – 13:29 )

Az innodb_lock_wait_timeout novelese nem oldja meg? Egyebkent a dump human readable, szoval legvegso esetben szet is lehet szabdalni kisebb darabokra.

Sajnos nem a legegyszerűbb szétvagdosni, Sublime 2 Text, ami nekem elég gyorsan tölt be mindent, 2 óra alatt a negyedéig jutott kb. Próbáltam szétszedni nem kézi módszerrel is, a gond csak ott van, hogy voltak karakterek amiknek az első bájtja az egyik fájl végére került, a második pedig a következő fájl elejére...

Attól függetlenül, hogy hogyan végződött vagy végződik az eseted, ezzel tudsz a fenti mintának megfelelő formájú kompozit insertet egyedi insertekre bontani: 


awk  '
   BEGIN { RS = "\\)[,;]" }

   NR==1 {
      insert___values = $0;
      sub( /\([^\(]+$/, "", insert___values)
      print $0 ");"
      next
   }

   length() > 3 {
      print insert___values $0 ");"
   }
'

mysql -u -p < db_backup.dump

A táblát "kikommentezni" nem túl egyszerű, egy 2 Gb-os text fájlt elég sok ideig tölt még egy normálisabb szövegszerkesztő is. Egyébként anélkül lefut, a probléma ott van, hogy ez az egy tábla a dumpban úgy néz ki, hogy:
INSERT INTO table (...) VALUES (value1, value2, value3, ..., value800000), tehát egy INSERT-ben benne van a komplett tábla 800 ezer sorral. Ennek a futása közben történik a már említett hiba.

De! Gondolj bele, hogy anno a Vissza a jövőbe c. filmben mára már repülő autókat vizionáltak, Marty McFly anyja pedig pizzát hidratált, magától befűződő csukát hordtak, légdeszka etc. Az egész filmből két dolog valósult meg: az almanac és a cipő. De az utóbbit is csak a film kedvéért csinálták meg. Ehhez képest nekem még villanyablak sincs a kocsimban, repülni pedig addig tud, amíg el nem éri a szakadék alját (bár még nem próbáltam) :)

-----------
"640GB sokmindenre elég"

"tehát egy INSERT-ben benne van a komplett tábla 800 ezer sorral"

Félreértések elkerülése végett

INSERT INTO table(a,b,c) VALUES (valuea,valueb,valuec), (valuea2, valueb2, valuec2), ... , (valuea800000, valueb800000, valuec800000);

formában van az inserted :)

Huncutkrvstn, hogy van hozzá kapcsoló.

Szerk.: a mysqldump manualját elnézve sok kapcsolóval sok időt meg lehet spórolni. Példának okádék, ha használod a --disable-keys, -K optiont, azzal is gyorsítani tudod az insertet (tekintve, hogy a kulcsok lassítják az insert műveleteket).

Szerk2.: --extended-insert, -e

Use multiple-row INSERT syntax that include several VALUES lists.
This results in a smaller dump file and speeds up inserts when the
file is reloaded.

Szerk3.: http://linuxcommand.org/man_pages/mysqldump1.html így nem kell keresni :)

-----------
"640GB sokmindenre elég"

A félreértések elkerülése végett: a mysql dump a dump készítésére való, visszaállítani az adatbázist nem azzal kell. Az extended insert kapcsolót is akkor kellett volna használnom...
Tisztában vagyok hogy néz ki az insert-em, nem volt célom tökéletes példát írni, gondoltam ebből is lejön, hogy mire gondolok. :)
A mysql, mysqldump manual-t már végigolvastam több stackoverflow-s, egyéb fórumos témával egyetemben, sőt a hibát a hivatalos mysql oldalán is jelentették többszor bug tracker-ben,
érdemi/használható válasz nem volt rá, a ticketek régóta nyitva vannak.

Jaigen, sorry, régen használtam mysql-t, de igyekeztem valami használható segítséget kihányni magamból :)

Szerk.: Kicsit móricka megoldás, de nincs valahol egy másik instance, ami megenné a dumpot (legalább a nagy táblát), majd abból okosan áttolni az eredeti helyére? Az már mehetne load data infile-val csv-ből, esetleg Import and Export Data cuccal ODBC-n keresztül.

-----------
"640GB sokmindenre elég"

( petya | 2013. 01. 10., cs – 14:18 )

A mysql error logjaban benne lesz, hogy mi a hiba, ha nem, akkor log_warnings=2.

Először is bocsi a félrevezetésért, a hibaüzenetet egy weboldalról másoltam ki, mert nincs előttem, de eszembe jutott, hogy megkéne kérdezni itt HUP-on, és emlékeztem a hibaüzenetre és a hibakódra (mert megegyezik az aktuális évvel:). A lényeg, hogy a 'mysqldump: Error 2013: Lost connection to MySQL server during query'-ból a mysqldump-ot ne vedd figyelembe.
--magyarázkodás vége--

A logban ugyanez van, ugyanis a MySQL mondja ezt. Mysqldump-al nem is lehet visszatölteni az adatbázist, az csak a dump-olásra való. A max_allowed_packetet már próbáltam, felhúztam egész 4 gigáig, akkor sem volt jó. Itten írták ni: http://stackoverflow.com/questions/7921618/importing-a-large-sql-file

Az összes témára vonatkozó Google-ös találatot átnéztem (https://www.google.hu/search?q=mysql+import+big+dump+rror+2013%3A+Lost+…), plusz a http://bugs.mysql.com/ oldalon is végignyálaztam az ide vonatkozó panaszokat.

Elnézést ha tévedek, de a logban nem lehet ugyanez. Mármint a mysql szerver logjában, mert a hiba a klienstől jött, és csak annyit jelent, hogy a szerver valamiért eldobta a kanalat. Ha a szerver logban bővebb infó van, akkor az adhat támpontot a kanáldobálás okára.

A max_allowed_packet mellé esetleg érdemes megnézni az ulimitet is (ulimit -d).

Illetve ha jól értem, akkor van egy dump, amit nem lehet még egyszer dumpolni, ugye?

( szabo.b.gabor | 2013. 01. 10., cs – 14:59 )

szerintem annyit kellene tenned, hogy az insert-et feldarabolod kisebb darabokra. a fájl maradhat nagy.

vmi script fájllal megnyitod a dump fájlt meg megnyitsz egy másikat írásra, amit beolvasol az egyikből beírod a másikba, a kérdéses résznél pedig ezeket a részeket '),(' x byteonként átalakítod beszúrva az insert fejléceket..

( _Gabor_ | 2013. 01. 10., cs – 15:12 )

Mysql verzió?

timeout értékek mekkorák?
show global variables like '%timeout%';

max allowed packet mekkora?
select @@max_allowed_packet;

( pch v | 2013. 01. 10., cs – 15:19 )

MC

MC editorba megnyitod (nem kell félni nem olvassa be az egészet)
majd az insert közepén nyomsz entert, bele szerkeszted az ij insert sort és save.

pch
--
http://www.buster.hu "A" számlázó
--

( cstt v | 2013. 01. 10., cs – 16:34 )

"Lost connection to MySQL server during query" tipikus megoldása :
mysql szerever my.cnf

[mysqld]
connect_timeout=30

és utánna
mysql restart

(off to tibyke: legalább valami szakmait is írnál hozzá, de nem, mindenhol csak a helyesírásba kötsz bele... :-/)

A nyitónak:
Határozottan emlékszem, hogy az elmúlt félévben volt valami hasonló topic/blog itt a HuP-on. Keresgéltem, nem találtam meg.

( pr23 | 2013. 01. 10., cs – 20:31 )

A témaindító hozzászólásba beírtam pár plusz infót, amiket kértetek:

my.conf:
port = 3306
socket = /tmp/mysql.sock
key_buffer = 256M
max_allowed_packet = 4096M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

SHOW GLOBAL VARIABLES LIKE '%timeout%':
connect_timeout
10
delayed_insert_timeout
300
innodb_lock_wait_timeout
50
innodb_rollback_on_timeout
OFF
interactive_timeout
28800
lock_wait_timeout
31536000
net_read_timeout
30
net_write_timeout
60
slave_net_timeout
3600
wait_timeout
28800

SELECT @ @max_allowed_packet:
@@max_allowed_packet
1073741824