Best I give a back story so you get the full picture.
I am in the process of setting up a new business, which hopefully will start in April 2012, and as such I am preparing every aspect of it. One part of the business will be web hosting.
I am quite experienced in this area and have managed VPS before. In my case, I went for two servers to start with; the first is just a web server, the second has mail and databases.
While MS SQL databases won't be available initially to the customers it may or may not be added with an "upgrade" etc. either way I have 2008 express installed as I require it for Website Panel (which I may or may not end up using). Otherwise MySQL databases are free to use for customers.
I was pondering the backup strategy for both database systems.
For the MS SQL I have one daily full backup at 00:00 and the differentials on every hour from 01:00 to 23:00. I figured for now this would be the best way to backup the "control panel" - I may end up doing log backups every 10 mins also when I go live.
As for MYSQL, it seems I can't do differentials, like I did with MS SQL, where the preferred action is to do a full dump each time. I'm sure you can imagine disk space will fill up quite quick over time in this respect.
My main question is: how should I be backing up MySQL hourly?
One full backup daily is not good enough for customers, no matter if it is in writing or not. I do realize in reality customers are responsible for their data and backups and indeed this is how most companies work but I want to add the extra safety net that may or may not help - best effort if you will.
I read somewhere that replicating MySQL to another machine then doing hourly dumps would solve my purpose but then I would be worried about the lag. I have read that replication an cause bottlenecks. Is this still the case?
Ideally, I would like to have another server for staging/backups/replication but this is not in the budget right now. Maybe when I get a certain number of customers I will do this.
Anyway, that's pretty much the bulk of it.
Thanks for your time and help.
P.S
I thought to mention, just in case it is not obvious, I am using Windows Server for bot VPS - In this case 2008 R2 Data Center.
Also, in addition to my backups - I ship them off over the net to iDrive as online backup. My idea, which works perfectly, is that I create the backup files with the exact same names as previous. This way, on the online backup, I only use up the size of data that is the local backup files yet while keeping a 30 day "previous version" set online.
So I can say to my customers I have 30 (min) days worth of backups, which I do in reality all while officially only using up one backup set space.
You should not mess with hourly dumps. You could just archive the binlogs of the mysql daly. Together with a nightly dump, you could restore the database to any point in time
Have a look at this descriptions: