I ask this because I lost my forums once, but luckily I had a backup of the files (granted they were a week or two old, but they still had the designs, styles, etc. intact).

So I am curious:
1. How many times should I be creating backups of the databases? (I use cpanel, but it probably doesn't make too much difference.) Once a week? Once a month? Etc.

2. Which gz files are the correct ones to backup? Should I be doing a full backup? Or just downloading the home directory, email, or database backups to my desktop?

(I suppose these may be different for anyone, but I'm not quite sure for me, as my sites are just two blogs and a forum.)

the two blogs: the files whenever you update the design and the appropriate tables whenever you add a post, a user, change the configuration.

the forum, depending on the activity. The greater the activity the more time you can leave between backups. This sounds odd but if a forum gets 5 posts a day then those are 5 important posts. If a forum gets 500 posts a day then each post is unlikely to be valued as much and so once a week should be fine.

Jamie

Jamie is right. For my personal sites I don't back them up unless I change something, most are random static pages, now when I am playing around with the blog theme - I am backing that up all the time.

All of my work sites are backed up nightly.

I had a similar quandry.

For the past 8 months I've been working on a business directory website.

I was on a pretty low budget, and I decided that 3 separate cPanel hosts, and one reliable vServer host (as a load-balancer) was the best way to go.

So the website has been backed up nightly across the three webservers using a nifty little program I found on the net.

The problem I had was that the database behind the whole thing, which is growing quite rapidly, was located on just one server - and if this server went down - all the backup webservers couldn't access the data so the site was useless anyway!

So after all these months I sat down and wrote a little PHP script (which I'm currently still messing with) which acts as middleware between the website and the database.

It replaces the main mysql functions in PHP (such as mysql_query and mysql_fetch_array etc...) with it's own variants.

The idea is that it holds details on database servers on all three web servers (ie each webserver has its own MySQL server).

When I do a select query (or similar) the script sends it to it's own MySQL database. If that is unavailable, it will try all of the other databases until it finds one which is available.

The other issue (relating to your backup question) was how often do I sync all of these databases, and how?

So I have built into this script a little decision maker which analyses my MySQL query, and if it finds an update, insert or delete query, it sends it to all of the database servers in turn, and sends me back a status flag for each server as an array.

Currently the three functions it can handle are:

mysql_query
mysql_num_rows
mysql_fetch_array

I was thinking of making it open-source if anyone's interested in it, but I'd love to hear your ideas (or tell me I'm reinventing the wheel!).

I know that MySQL has built-in replication but I don't have the budget to go for a dedicated MySQL server so I need to find the best way around it I can!

So every time I write a new post for my blogs, I should create a new backup for it? That sounds like an awful lot of backups to create, as I write every 2-3 days, but then again I don't want to lose any information, so I suppose I should.

My forum doesn't have much activity ATM, so at first I figured I wouldn't have to backup as often, due to the lack of info, but guess not, thanks for the tip.

Would it be wise to do a "full backup" for each site, or just specific database backups? (full backups take a while longer if I'm not mistaken)

@HandCoder - Your script does sound interesting, but for what I have set up specifically, don't think I would have much use for it, but others may.

Specific database backups should be fine.

Jamie

Thanks again Jamie,
now some more dumb questions:

I have everything sitting on one domain, and all my public sites (in sig, for examples) so it's probably easier for me to do just a complete "full backup", and not risk missing anything.

So I'm in my server's cpanel right now and it's asking me where the backup generation should be destined for. Do I want to select "Home directory" (which I'm assuming still keeps the backup on the server), or "Remote FTP Server (or Passive, there's two similar options)" to put it on my desktop?