large mysql table import trouble

struggling to import large table easily

[hughie]

This probably isn't in the right place but there isn't a mysql forum..

Basically i've a 800mb table and i want an easy way of moving it from server a to server b.

The usual suspects are giving me grief, timeouts etc so i've used mysqldump and got a big dump file of the desired table.

The problem arises when i have it on my server and i want to import that dump.

I run

mysqldump -q -u username -p dbname < /home/dumpfolder/bigDump.sql

but it just starts scanning through all the database rather than cutting to the chase and just importing the contents of the sql file.

I don't think this would be a problem except the rest of the datbase is 1.2gig so it's going to take forever to run.

Am i doing something wrong and/or is there a better solution?

hughie

[phranque]

try this:

mysql -q -u username -p dbname < /home/dumpfolder/bigDump.sql

[hughie]

trying at the moment on my testing server, it's doing something and has been for the past 30 minutes, will let you know how it goes.

thanks,
hughie

[joelgreen]

If loaded from the script, following could help.

LOAD DATA INFILE should work fast.
LOAD DATA INFILE 'data.txt' INTO TABLE my_table;

SET FOREIGN_KEY_CHECKS=0 - to ignore foreign key constraints during the load operation

ALTER TABLE ... DISABLE KEYS before loading the file into the table and using ALTER TABLE ... ENABLE KEYS to re-create the indexes after loading the file

Explained in more details: [dev.mysql.com...]

[hughie]

Just to report back, the basic little mysql... < bigdump.sql worked a treat and didn't hog all my resources in the process.

Thanks for the help

hughie