Good method to backup database from a high traffic forum

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

 

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.

 

image based live bakcup? how is that done?

 

This should answer that question.

http://searchdatabackup.techtarget.com/definition/image-based-backup

 

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

 

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.

 

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.

 

I use a daily cron job in the early hours and downloads it to my PC, also do manual backups when making any changes.

 

Do you put your forum offline while doing backups?

 

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.

 

No, they are done during the night at around 2 or 3am

 

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.

 

.. I might sound stuburn sorry but... backing up db while online wont corrupt the db since data are constantly written by users?

Thank you

 

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

 

No it will not corrupt the database, tables are not locked during a backup so you are fine.

 

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​

 

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?

 

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!!).

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 ...

Cheers,
Shaun

 

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.

 

I don't think anyone is arguing here guys..
It looks like @LuisFX is just looking for advise, please relax.