Welcome to WebmasterWorld Guest from 23.22.182.29

Forum Moderators: open

Message Too Old, No Replies

How do you import large SQL dumps into phpmyadmin?

Over 1M records, need some help.

     
11:42 am on Feb 23, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Mar 30, 2004
posts:1148
votes: 0


Hey All,

I have a probem with a large SQL dump its just over 1M records large however whats worse is that each INSERT has 7000(ish) records so its 1M odd records done within 200 INSERT statements.

I can't even import them one at a time, I tried BigDump but that was also no help. I tried inserting one insert directly into the sql window of phpmyadmin however got a timeout error (I don't fancy doing 200 of these anyway)

If anyone could help that would be great.

RJ

6:48 pm on Feb 23, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


I don't know of a solution via the web for that size of data, but if you have command line access to your server, log in to mysql and type the following:

use [your database];
source [path/to/file.sql];

5:23 pm on Feb 25, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Mar 9, 2006
posts:75
votes: 0


for a database import of that size your going to have to go to the command line.

And a single insert with 7000 rows sounds a bit ott.

I would maybe think about dumping the data to a flat file/files and then doing a batch update. Just so i could be sure that all the records went across ok - ok I used to write biiling systems so i'me a bit anal about data integrity :-)

5:40 pm on Feb 25, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Mar 30, 2004
posts:1148
votes: 0


Cheers for your help, I managed to figure out how to work textpad really well so I managed to each record on its own line with its own insert.

Thanks All - Working with this data is going to be another task on its own. :(

11:49 pm on Feb 26, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 8, 2002
posts:2335
votes: 0


Something that may work for you depending on your situation is to stop using mysqldump and start using mysqlhotcopy [dev.mysql.com]. It's much easier and quicker to backup and restore...but you must backup within the same machine. It also has some advantages and disadvantages to mysqldump.

[edited by: Clark at 11:49 pm (utc) on Feb. 26, 2008]