Announcement

Collapse
No announcement yet.

How long does it takes to restore a database ?

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

  • How long does it takes to restore a database ?

    any idea ? on this
    its been 3 hours already and my restore is still continuing via ssh
    I am checking the imported database from phpmyadmin

    Is this normal or something messup ? can i make the process faster someway ?

    My postindex has 5,109,804 records and around 115 mb in size, which is causing this huge delay i assume.

    DB is 650 MB in size, with 330 MB attachments

    Regards,
    TechArena - TechArena Community - News - Download - Video - Guide - Review

  • #2
    Originally posted by SaN-DeeP
    My postindex has 5,109,804 records and around 115 mb in size, which is causing this huge delay i assume.
    Yep, no doubt that is causing the delay. The total time required varies with the speed of your server and the total size and number of records in your database. But 5 million postindex records is a lot.

    As long as you can see progress being made in phpmyadmin, everything is working... you just have to wait.

    Comment


    • #3
      Hi Jake,
      thanks for reply.
      Can you tell me what does postindex table stores ?
      Is it something we can empty before we move the database ?

      Regards,
      TechArena - TechArena Community - News - Download - Video - Guide - Review

      Comment


      • #4
        It allows your membesr to search the forums

        Comment


        • #5
          If I may recommend, here is what I did in the past with a 2.6 gig dbase [post index was over 300mg]

          First, move all your attachments to files. Attachments gave the biggest grief when dumping and restoring the dbase and there were more often errors in the attachment table than anything. That is half your dbase, it will be half your time!

          May sure when you dump your dbase that you use some commands with it for the least amount of errors in restoring. For dumping and restoring I use

          mysqldump --quick --add-drop-table --add-locks --extended-insert --lock-tables --opt -uroot -p****** nameof_db > db-sqldump.sql

          mysql -uroot -p***** nameof_db < db-sqldump.sql

          You would obviously need to change the user and pw.

          Restoring the 2.6gig dbase didn't take 3 hours, so my best guess is that something is truly amiss

          Hope that helps
          www.karmicpower.com
          online community of like minded people

          be the change you wish to see in the world

          Comment


          • #6
            Thanks morgalist for the information.

            Few points:
            1. All my tables are getting parsed properly within few mins except the postindex which took more then 5 hours everytime.
            PostIndex table has around 51,98,233 records and 116 MB in size

            2. I have not faced any error in my last 2 tests of restoring mysql.

            3. mysql -uroot -p***** nameof_db < db-sqldump.sql
            I am using this command to restore my Database.

            4. mysqldump --quick --add-drop-table --add-locks --extended-insert --lock-tables --opt -uroot -p****** nameof_db > db-sqldump.sql
            Can you kindly ellaborate a few more details about this command and its parameters ?

            Regards,
            TechArena - TechArena Community - News - Download - Video - Guide - Review

            Comment


            • #7
              Originally posted by SaN-DeeP
              4. mysqldump --quick --add-drop-table --add-locks --extended-insert --lock-tables --opt -uroot -p****** nameof_db > db-sqldump.sql
              Can you kindly ellaborate a few more details about this command and its parameters ?

              Regards,
              This is the best way IMHO to correct dump a dbase, and I have used it well over 100 times over the years

              -q, --quick
              Don't buffer query, dump directly to stdout.

              --add-drop-table
              Add a 'drop table' before each create.

              --add-locks
              Add locks around insert statements.

              --e, --extended-insert
              Allows utilization of the new, much faster INSERT syntax.

              --l, --lock-tables
              Lock all tables for read.

              --opt [looks like this is me being redundance]
              Same as --add-drop-table --add-locks --all --quick --extended-insert --lock-tables --disable-keys
              www.karmicpower.com
              online community of like minded people

              be the change you wish to see in the world

              Comment


              • #8
                Awesome
                so i only need to run.

                mysqldump --quick --add-drop-table --add-locks --extended-insert --lock-tables --opt -uroot -p****** nameof_db > db-sqldump.sql

                instead of

                mysql -uroot -p***** nameof_db < db-sqldump.sql ?

                Big thanks, i will try in few hours and report back if some probs

                Regards,
                TechArena - TechArena Community - News - Download - Video - Guide - Review

                Comment


                • #9
                  To best dump your dbase use

                  mysqldump --quick --add-drop-table --add-locks --extended-insert --lock-tables --opt -uroot -p****** nameof_db > db-sqldump.sql


                  To restore it after use

                  mysql -uroot -p***** nameof_db < db-sqldump.sql


                  IMHO how well you dump your dbase is how well it will restore. Dump it again using the first command and then see how quickly it will restore using the second [and common] command

                  I'd still suggest moving your attachments to a file system, I took the plunge and am so glad that I did it.

                  If your host will allow the long server intensive process, I would recommend rebuilding your search index as well.

                  Good luck!
                  www.karmicpower.com
                  online community of like minded people

                  be the change you wish to see in the world

                  Comment


                  • #10
                    NO! The above example is how to MAKE the dumpfile. Look at the direction of the little arrow ">". That tells you what direction data is moving. Away from the "mysql" command is OUT of the database, and toward the "mysql" command is IN to the database.

                    Comment

                    Related Topics

                    Collapse

                    Working...
                    X