Good method to backup database from a high traffic forum

Discussion in 'Domains, Hosting and Servers' started by LuisFX, Mar 29, 2012.

  1. LuisFX

    LuisFX Regular Member

    Joined:
    Mar 28, 2012
    Messages:
    15
    Likes Received:
    4
    Besides replication what is the best method to backup database from a high traffic forum?

    It is better to take your forum offline while doing it or backup without closing down your forum?


    Thank you
     
  2. Clickfinity

    Clickfinity Regular Member

    Joined:
    Apr 18, 2011
    Messages:
    57
    Likes Received:
    30
    Location:
    UK
    I do a backup at 4am (U.K. time) to a second drive in the server. This is the quietest time for our forums.

    If I ever get to the point of having multiple servers I imagine I'd move to an "image" based live backup. :thumbsup:
     
  3. LuisFX

    LuisFX Regular Member

    Joined:
    Mar 28, 2012
    Messages:
    15
    Likes Received:
    4
    image based live bakcup? how is that done? O.o
     
  4. Brandon

    Brandon Regular Member

    Joined:
    Jun 1, 2009
    Messages:
    6,602
    Likes Received:
    1,706
    Location:
    Topeka, Kansas
    First Name:
    Brandon
    Clickfinity likes this.
  5. dojo

    dojo Regular Member

    Joined:
    Feb 9, 2012
    Messages:
    250
    Likes Received:
    181
    You can also select the database in the phpmyadmin area, click OPERATIONS (hope I'm not mistaken the link name) and then select the copy option. You'll copy the database to a new one, say db_bk and have it already there on the server. Just don't forget to delete the old backups. I usually do this when I have to operate on the database and I don't want to export-import stuff. If anything goes wrong, I just point my config file to the backup database and save time
     
  6. Dan Hutter

    Dan Hutter aka Big Dan

    Joined:
    Jul 20, 2006
    Messages:
    1,412
    Likes Received:
    515
    Location:
    New York
    I have a buddy of mine working to on a cron job to dump my DB nightly and FTP it onto the Amazon cloud. Right now I'm relying on the Host's daily server snapshots and not that I don't trust them but I like some redundancy.
     
    Clickfinity likes this.
  7. LuisFX

    LuisFX Regular Member

    Joined:
    Mar 28, 2012
    Messages:
    15
    Likes Received:
    4
    Thank you all for the answers. I think the chosen method I will use is just put offline my forums at nights to do the db backups, I know that for high traffic forums it could be a problem but I think this is the most secure method I guess... or just shutdown the db server and copy the database to another folder location.
     
  8. Creaky

    Creaky Regular Member

    Joined:
    Aug 15, 2011
    Messages:
    310
    Likes Received:
    96
    Location:
    London
    I use a daily cron job in the early hours and downloads it to my PC, also do manual backups when making any changes.
     
  9. LuisFX

    LuisFX Regular Member

    Joined:
    Mar 28, 2012
    Messages:
    15
    Likes Received:
    4
    Do you put your forum offline while doing backups?
     
  10. Brandon

    Brandon Regular Member

    Joined:
    Jun 1, 2009
    Messages:
    6,602
    Likes Received:
    1,706
    Location:
    Topeka, Kansas
    First Name:
    Brandon
    I run daily backups of all my sites once a day and email them to my account once a week.
    My host also has full live copies of everything.
    No I do not shut the site down during backups.
    I've backed up sites with over 3 million post while still live.
    There is nothing wrong with getting a backup while the site is down, but rarely a necessity.
     
  11. Creaky

    Creaky Regular Member

    Joined:
    Aug 15, 2011
    Messages:
    310
    Likes Received:
    96
    Location:
    London
    No, they are done during the night at around 2 or 3am
     
  12. dojo

    dojo Regular Member

    Joined:
    Feb 9, 2012
    Messages:
    250
    Likes Received:
    181
    DO NOT inactivate your forums. Come on, the server can run backups regardless. I would never consider losing 2 hours EACH night for backups, especially since my forums have visits from all over the world. 2AM for me is noon in the US. Would I mess up my traffic from there? Just set the cron jobs and let the forums run. Or talk to your host. if you have such a big site that needs 2 hours to backup a database you might have a bigger hosting plan and they should help you with some ideas.
     
  13. LuisFX

    LuisFX Regular Member

    Joined:
    Mar 28, 2012
    Messages:
    15
    Likes Received:
    4
    .. I might sound stuburn sorry :( but... backing up db while online wont corrupt the db since data are constantly written by users?

    Thank you
     
  14. dojo

    dojo Regular Member

    Joined:
    Feb 9, 2012
    Messages:
    250
    Likes Received:
    181
    Ask your host about this, it's their JOB to give you ideas and support. i would never consider losing almost 10% of my day to make a backup, that's for sure
     
  15. gallitin

    gallitin Regular Member

    Joined:
    Jun 7, 2011
    Messages:
    249
    Likes Received:
    55
    Location:
    Kansas City
    First Name:
    Nathan
    No it will not corrupt the database, tables are not locked during a backup so you are fine.
     
  16. LuisFX

    LuisFX Regular Member

    Joined:
    Mar 28, 2012
    Messages:
    15
    Likes Received:
    4
    I just spoke with my hosting support and this is what he said:
    Matthew F.:Hello Luis, The backup is real time so at the moment you back it up it will collect all information at that exact moment only, People adding information to the database during backup will not corrupt your database.
    (2:54:52pm)​
    Luis Stamatis:so tables are not locked then?
    (2:55:11pm)​
    Matthew F.:They are locked to the moment you click backup.
    (2:55:45pm)​
    Matthew F.:A backup will not corrupt your database even if there is information being added/edited at the moment.
    So database tables are locked then?
    Thank you
     
    Brandon likes this.
  17. LuisFX

    LuisFX Regular Member

    Joined:
    Mar 28, 2012
    Messages:
    15
    Likes Received:
    4
    So... i've been reading on the net about db backup and seems that backing up database while online will bring inconsistency and this backup is not good for data disaster recovery right? also that it is also not good as a standalone backup right?
     
  18. Clickfinity

    Clickfinity Regular Member

    Joined:
    Apr 18, 2011
    Messages:
    57
    Likes Received:
    30
    Location:
    UK
    Wrong. When you take a backup your system will take a snapshot of the database file and back up that copy of it - NOT your live database file that everyone is currently accessing - so there will be no corruption or inconsistency!! (Which is what people here [and your host] have been telling you!!). :thumbsup:

    But suit yourself - if you want to take your site down for hours per day, that's fine. Competing sites in your niche will simply gain more posts whilst you backup your data ... :P

    Cheers,
    Shaun :D
     
  19. gallitin

    gallitin Regular Member

    Joined:
    Jun 7, 2011
    Messages:
    249
    Likes Received:
    55
    Location:
    Kansas City
    First Name:
    Nathan
    Heh, this is my last post here as we are going round and round.

    Taking a backup on a live site will NOT corrupt the database, does NOT lock tables, and does NOT cause inconsistency. This is how I have been backing up for years without any issues.
     
  20. Brandon

    Brandon Regular Member

    Joined:
    Jun 1, 2009
    Messages:
    6,602
    Likes Received:
    1,706
    Location:
    Topeka, Kansas
    First Name:
    Brandon
    I don't think anyone is arguing here guys..
    It looks like @LuisFX is just looking for advise, please relax.
     

Share This Page