Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Why are you thinking about hourly backups? Would a replicated failover Database with a nightly full dump not be enough? –  Thomas Berger Aug 19 '11 at 22:21
    
Where am I replicating this to? I don't have a free server to replicate to. In regards to your comment also if I simply only do one nightly and work with a replicated slave what happens when a client, or third party, deletes a table which has had data modified since the nightly. In respect I would not be able to recover it. At least if I had hourly backups there would be a greater chance that I "could" restore it for them. Let's face it, Joe Soap is just unaware of what could go wrong until it is to late, yet they end up pointing fingers at the wrong people, would be me in this case. –  Anthony Aug 20 '11 at 11:34
    
... I've seen it happen before. In fact, my last boss went hell for leather at the hosting company because they had a server fail, we lost all the data/clients sites. I had repeatedly warned against this and said we need a backup scheme/system in place and he completely disregarded on every occasion. "It would cost money" or "The hosting company is responsible for that". he simply would not listen. So the worst happens and we spend a week trying to recover, where I had said to leave it and wait for the hosters to get the RAID repaired. We wasted a week labour. ... –  Anthony Aug 20 '11 at 11:44
    
... My point is that everyone will always blame the next in line rather than admit mistakes on their part. All I am trying to do is say: "Hey look, we are not responsible for your data/content, which we did mention in both TOS and in FAQs, BUT we might have a recent backup available for you." Best effort, to show we would try to help as best as possible. It's customer service that pays at the end of the day regardless of best practice. Its best to be proactive than be reactive. Customers appreciate that. After all, it only takes one customer to bad mouth your company. –  Anthony Aug 20 '11 at 11:47
add comment

1 Answer

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:

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.