MySQL Restore Large Dump

Question

I have a dump file of a single table which is 7GB. I try to restore it using

mysql -u root -p DB_NAME < .SQL FILE

But I don't see any activities in task manager. CPU is 00 and RAM is 5,700 K. I don't know if it is really restoring it or and error occurred. Is there any other way to restore this? I am running it on windows 7.

Answer 1

For this answer, let's use /root/MySQLData.sql as the SQL File.

Go into mysql via the client and run it like this:

mysql> source /root/MySQLData.sql

Give it a Try !!!

UPDATE 2011年12月16日 21:08 EDT

I just caught on to your problem: Your program is not running because you did not supply the password. It is stuck in the background waiting for a password. That's your actual problem. Kill the job and run it again with the password on the command line:

mysql -u root -ppassword DB_NAME < .SQL FILE

or just use my original submitted answer up above.

UPDATE 2011年12月19日 11:40 EDT

I just re-read the question. You said single table. Then it hit me !!!

You need to ramp up your bulk_insert_buffer_size. The default is 8M.

To see what your current setting is, run this:

mysql> show variables like 'bulk%';

Just add this to /etc/my.cnf

[mysqld]
bulk_insert_buffer_size=512M

So as to not restart mysql, run this SQL command:

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 512;

Give it a Try !!!

• AliBZ: Thanx man, but I entered the password. Actually it is done. But its too much for a server with 24GB Ram. I don't know. Thanx anyway.
• RBB: @Rolando, Is there any calculation for bulk_insert_buffer_size based on table size?

Answer 2

mysql -u root -p
set global net_buffer_length=1000000; --Set network buffer length to a large byte number
set global max_allowed_packet=1000000000; --Set maximum allowed packet size to a large byte number
SET foreign_key_checks = 0; --Disable foreign key checking to avoid delays,errors and unwanted behaviour
source /path/folder/database_forum_.sql 
SET foreign_key_checks = 1; --Remember to enable foreign key checks when procedure is complete!