large mysql table import trouble

Forum Moderators: open

Message Too Old, No Replies

large mysql table import trouble

struggling to import large table easily

hughie

11:56 am on Sep 20, 2007 (gmt 0)

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

1:26 pm on Sep 20, 2007 (gmt 0)

try this:

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

hughie

4:24 pm on Sep 20, 2007 (gmt 0)

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

4:29 pm on Sep 20, 2007 (gmt 0)

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

1:08 pm on Sep 21, 2007 (gmt 0)

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