How to import 360mb db file with phpmyadmin

[Adenoid Hynkel]

I need to get a db up and running locally. The db file is 360mb big. Compressed its about 79mb big.

Is there a way I can import the complete db with phpmyadmin? Locally it gives me a limit of 16mb i think.

Is there a way or do I really need to split the db into pieces? If yes, is there a way to do it once the db has been downloaded already?

Thanks

 

[chiskop]

Are you restricted to phpmyadmin? Is there no way you can get shell access - even if it's just temporary?

 

[dyanamo]

If you can use mysql administrator. Phpmyadmin isnt the best at importing large files.

 

[Adenoid Hynkel]

Are you restricted to phpmyadmin? Is there no way you can get shell access - even if it's just temporary?

No. I have shell access on the live server. Im not a guru if it comes to shell.

I need to get the db up and running on my local pc, as one of my clients uploaded 1.8mill contacts into our email marekting app. The program itselfs just freezes when I try to export the contact list. I need to export it, as we need to split his contacts in 500k groups.
Downloaded the whole db and i guess I will be able to export the contacts locally.

Locally i use xampp. Can shell be used locally?

 

[R4tt3xx]

http://www.wanderings.net/notebook/Main/HowToImportLargeMySQLDataFiles

 

[Adenoid Hynkel]

Edit: tried this, http://www.ozerov.de/bigdump.php
It doesnt seem to work. After 55min it imported 1 table of 16kb. Maybe I'm doing something wrong, or does it take hours to import a 360mb db? Ill give MySQL administrator a go

 

[guest2013-1]

I'm confused. a 360mb db compressed should be around 12-14mb big...

Anyway, upload the file to your webhost and ask them to restore it for you

 

[Other Pineapple Smurf]

What you need to do is select his 1,3 million into a temp table:

SELECT * INTO temp_table_xxx FROM import_table WHERE client_id='xxxx' AND sent=FALSE;

Then you delete his data out of the main table.

DELETE FROM import_table WHERE client_id='xxxx' AND sent=FALSE;

You then import his data back into the main table with a limit;

INSERT INTO import_table SELECT * FROM temp_table_xxx LIMIT 500000 OFFSET 0;

Process, then next batch:

INSERT INTO import_table SELECT * FROM temp_table_xxx LIMIT 500000 OFFSET 500000;

Process, then last batch:

INSERT INTO import_table SELECT * FROM temp_table_xxx LIMIT 300000 OFFSET 1000000;


I've done this before with a callcenter application I have to maintain.

 

[Adenoid Hynkel]

I'm confused. a 360mb db compressed should be around 12-14mb big...

Anyway, upload the file to your webhost and ask them to restore it for you

Well, the file is 79MB big .gz. And btw - read at the top, i clearlly said i need to get it working on my local Pc.

@cbrunsdonza

Thx for the tip - but please speak english to me If its that easy, can you do it for me? Ill buy u a beer at old bridge in return

Edit: I have found this amazing app. http://www.mysqldumper.de/en/ works like a charm

 

[guest2013-1]

Well, the file is 79MB big .gz. And btw - read at the top, i clearlly said i need to get it working on my local Pc.

@cbrunsdonza

Thx for the tip - but please speak english to me If its that easy, can you do it for me? Ill buy u a beer at old bridge in return

Edit: I have found this amazing app. http://www.mysqldumper.de/en/ works like a charm

Yea I know but if it's on your local PC then it's piss easy? I just do it in mysql query analyzer

 

[koffiejunkie]

why on earth do you want to fool around with gui tools if you have shell access to the box? Copy the sql dump to the server. If you're importing into an existing empty database, make sure it doesn't have a USE and/or DROP DATABASE statement in (would be in the first 20-30 lines).

Then run:

mysql -u username -p database_name < database_file.sql

That's it. The only time this will fail is if you have very large rows, you may have to increase the max_allowed_packet to something big enough.

 

[SilverNodashi]

If it's on your local PC ( I presume you run Windows), then you can use mysql in the command line to import it.

Let's say mysql is installed in c:\mysql

Then, open a command line, and cd \mysql
Now run: mysql -uroot -p newdatabase < old_database.sql