Restoring Large SQL Files
Posted by: cfaice (---.cable.ubr01.stav.blueyonder.co.uk)
Date: June 29, 2006 07:37PM

Folks,

I have installed the SQL Administrator addon, and all works perfectly, with the exception of restoring a large database (250mb +).

I haven’t yet allowed the restoration to complete as it takes too long.

Currently I have allowed up to 60 minutes to restore this database, but in this time it only completes approximately 5 percent (I end the restore it in fear of frying the CPU / it taking so long!).

My setup is 1GB RAM, Intel P4 2.6Ghz.

My dedicated server located in the USA runs Cent OS, has 1GB and only a Celeron 2.2ghz. Restoring the same database from Shell takes approximately 3 minutes.

So my dedicated server hardware spec is lower than my personal home server, yet it completes a restore in no time at all.

Does anyone have any suggestions how to make the restore take a more realistic time, or alternatives to restoring such a large database?

Thanks in advance,
Craig

Options: ReplyQuote
Re: Restoring Large SQL Files
Posted by: cfaice (---.cable.ubr01.stav.blueyonder.co.uk)
Date: June 30, 2006 01:04AM

Found myself the command to use via the standard command prompt in Windows!

CD to ..\mysql\bin (where .. is the path of your WAMP installation).

In this instance my install is:

C:\Program Files\Server\mysql\bin

Then type the following commands depending what you wish to do:

Restore:
mysql.exe -u username -p dbname < Restore.sql

Backup:
mysqldump.exe -u username -p dbname > Backup.sql

In the examples given above, the .sql files have to be placed / will be placed in C:\Program Files\Server\mysql\bin.

Thats it.

Hope this helps anyone else with the same sort of problem.

Options: ReplyQuote
Re: Restoring Large SQL Files
Posted by: hondaoftroy (---.singnet.com.sg)
Date: July 22, 2006 02:33PM

i've tried the following but got

ERROR 1102 <42000>: Incorrect database name 'test.sql'

ERROR 1049 <42000>: Unknown database 'forum'


my sql file is "test.sql" i've saved it to the bin dir already.

This is what i've typed:

mysql.exe -u root -p forum < test.sql

Options: ReplyQuote
Re: Restoring Large SQL Files
Posted by: Xajel (82.194.62.---)
Date: July 25, 2006 05:03PM

What I've found from my tests, some .sql files will really take too long time...

but be sure,,, if the .sql file is currepted for some reasons, will not be able to even know it's correpted, you may test it using the SQL Administrator addon, using the analyize feature...
but if you started to restore it ( by SQL Administrator addon or direct mysql command ) you will wait for ever without any thing...





bro hondaoftroy...
you have first to create a new database called forum, mysql can't make a database directly with this command..

mysql -u root -p forum < test.sql

it will ask for password just press enter whtout typing any thing ( WAMP does not have a password for MySQL, unless you manually added a one )

if this not working.. copy the test.sql to the root of the C Drive ( so the path will be C:\test.sql ) and run the command

mysql -u root -p forum < "C:\test.sql"

Options: ReplyQuote


Sorry, only registered users may post in this forum.