Announcement

Collapse
No announcement yet.

How long does it takes to restore a database ?

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

  • Dennis Olson
    replied
    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.

    Leave a comment:


  • Morgalis
    replied
    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!

    Leave a comment:


  • SaN-DeeP
    replied
    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,

    Leave a comment:


  • Morgalis
    replied
    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

    Leave a comment:


  • SaN-DeeP
    replied
    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,

    Leave a comment:


  • Morgalis
    replied
    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

    Leave a comment:


  • Zachery
    replied
    It allows your membesr to search the forums

    Leave a comment:


  • SaN-DeeP
    replied
    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,

    Leave a comment:


  • Jake Bunce
    replied
    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.

    Leave a comment:


  • SaN-DeeP
    started a topic How long does it takes to restore a database ?

    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,

Related Topics

Collapse

Working...
X