Announcement

Collapse
No announcement yet.

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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?

  • #2
    are you using SSH for this?. and have you moved all the files from the original database to the file system.
    Simple Straight Forward EU cPanel vBulletin Web Hosting Provider.

    Comment


    • #3
      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?

      Comment


      • #4
        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
        Simple Straight Forward EU cPanel vBulletin Web Hosting Provider.

        Comment


        • #5
          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?

          Comment


          • #6
            Originally posted by Kadence View Post
            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.

            Comment


            • #7
              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.
              VB 4.0 is the same as Windows Vista...

              Comment


              • #8
                Originally posted by Buck_gps View Post
                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

                Comment


                • #9
                  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.

                  Comment


                  • #10
                    what kind of server? dedicated? vps? shared?

                    linux or windows?
                    VB 4.0 is the same as Windows Vista...

                    Comment


                    • #11
                      Dedicated, Linux.

                      Comment


                      • #12
                        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
                        Last edited by Buck_gps; Sat 17 Oct '09, 9:59pm.
                        VB 4.0 is the same as Windows Vista...

                        Comment


                        • #13
                          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).

                          Comment

                          widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                          Working...
                          X