Trying to restore mysqldump of forum DB causes server load to go crazy - what to do?

[Kadence]

I'm in the process of transferring a forum from one server to another. I originally tried restoring a cpmove, but that failed because after a while the server load spiked so high that the server became unresponsive and needed a reboot. So I did backed up the home directory and DB separately, dumping the DB to a file and then running the file from command line ('nice mysql ... < dump.sql' - tried with and without nice) - but restoring the DB once again caused the load to go haywire.

So now I'm trying to restore the DB in 3 parts - every table except for postindex and post, then postindex, then post. Those two tables are gigs in size. However the same thing happens when trying to restore the two big tables - load eventually goes nuts, crashing all sites on the server.

What can I do to successfully restore the database without these load issues that wreck the server?

[wda2015]

are you using SSH for this?. and have you moved all the files from the original database to the file system.

[Kadence]

Yes I'm using SSH.

No I haven't tried moving the files over, I'm using mysqldump and then executing the dump. Are you suggesting I should copy the DB files over directly?

[wda2015]

yes.. from vb admin change the attachments storage type to the file system this will reduce the size of the database, then upload all the attachments manually.

wD

[Kadence]

Thanks for the suggestion.

I don't quite follow about the attachments though. Currently attachments are stored in public_html/attachments, and under Attachment Storage Type the default circle is "Move Items from File System into Database" - does this mean what WebDosser suggested is already done?

Also, the server the DB is from is MySQL 4.1.22, and the new server is 5.0.81. Would this cause any problems with just copying over the database files?

[Lord Jordan]

Thanks for the suggestion.

I don't quite follow about the attachments though. Currently attachments are stored in public_html/attachments, and under Attachment Storage Type the default circle is "Move Items from File System into Database" - does this mean what WebDosser suggested is already done?

Also, the server the DB is from is MySQL 4.1.22, and the new server is 5.0.81. Would this cause any problems with just copying over the database files?

May i suggest using a php script called bigDump in a secret directory. it executes mysql commands at a staggered interval and can be resumed.

[Buck_gps]

definately!! i use big dump and have no issues.

one of my customers has a mysql dump that is 871 meg and used bigdump to restore the backup successfully.

[Lord Jordan]

definately!! i use big dump and have no issues.

one of my customers has a mysql dump that is 871 meg and used bigdump to restore the backup successfully.

yeah only reason i know about it is one of my customers had to restore a 3gb database and it knocked the sql server offline. Used it ever since

[Kadence]

I just tried bigdump, thanks for the suggestion. Still had big load issues while it was running, and the fact that it needs to run from browser rather than command line is a problem - the restore will take a long time, and a script doesn't run from the browser forever. The bigdump from the browser eventually stopped because the connection reset. I could change the timeout setting, but I don't know if that's wise.

[Buck_gps]

what kind of server? dedicated? vps? shared?

linux or windows?

[Kadence]

Dedicated, Linux.

[Buck_gps]

login to whm and go to php configuration edit, adjust the mysql sizes and times for the file upload and exicution limits, i changed mine to 2 gig.


set mysql.connect_timeout to 9999999999999999

mysql.max_links to -1

mysql.max_persistent to -1

[Kadence]

Thanks. The file is already on the server, I don't need to upload it. And I'd have to change the execution time to something ridiculous because the script will take a long time to finish.

Also while bigdump was running, my load was still getting very high and MySQL was starting to act wonky (other sites on the server which use MySQL couldn't access their databases).