Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a forum which is growing way faster than I could imagine, and my site has been a target for DDoS attacks, NTP attacks, and a bunch of hacking attempts. I am starting to worry that there may be a compromise soon with the way these attacks are going about.

The forum database grows an average of 200MBs a day worth of forum topics, posts, and data. I am backing up my system once a week currently to make sure that I won't lose too much data if something is to happen.

Please Note: The backups are transferred to my file storage server after they have been created. I am keeping two backups of the data. One at the beginning of the month and one in the middle of the month, and the final one at the end of the month for the life-time of the site. The others are deleted after three months.

Am I making sure my data is protected enough in case I have to use these backups, or should I keep more copies throughout the lifespan of the site?

share|improve this question
3  
The backup strategy might also depend on the DBMS you are using and what kind of backup it offers (e.g. incremental backups, online backups, file system backups, database dumps, ...) –  a_horse_with_no_name Apr 28 at 6:43
    
Is the transfer to the storage server done through "push" or through "pull"? If someone hacks your site, can they overwrite your backups, corrupt your backups, or inject trojans into your backups? –  jjanes Apr 28 at 17:28

3 Answers 3

up vote 8 down vote accepted

Some important factors to consider for backup are:

  • how annoyed the forum users would be if X days of activity disappeared
  • the probability of that happening
  • how long will it take to recover the data if it's backed up
  • how much does backup interrupt the normal operation of the site


As for, "Am I making sure my data is protected enough ...?", I'll ask a question back. What Recovery Point Objective (RPO) and Recovery Time Objective (RTO) are you aiming for?

Your current backup schedule has a recovery point of

  • for the current month, to the nearest weekly backup
  • for the last three months, to the nearest 1/2 monthly backup
  • for the "life-time of the site", to the nearest monthly backup

I'll suggest that you add daily backups to your rotation. If space is a concern, look into differential backups that only backup the data created that day. If space is a concern and RTO is not of paramount importance, you can reduce the size of backup files by compressing them. Backups of a DB containing primarily text might compress well.

By the way, if your forum keeps growing at an average of 200 MB/day (and growth is not accelerating), that's 6 GB/month. If you keep doing full backups on the schedule you outlined and backups are more-or-less the same size as the forum DB and they aren't compressed, by the end of the year you will have:

  • January monthly backup, 2 GB
  • February monthly backup, 8 GB
  • March monthly backup, 14 GB
  • April monthly backup, 20 GB
    ...
  • September monthly backup, 50 GB
    • September 1/2 monthly backup, 53 GB
  • October monthly backup, 56 GB
    • October 1/2 monthly backup, 59 GB
  • November monthly backup, 62 GB
    • November 1/2 monthly backup, 65 GB
  • December monthly backup, 68 GB
    • Week 1 backup, 69.5 GB
    • Week 2 (December 1/2 monthly) backup, 71 GB
    • Week 3 backup, 72.5 GB
    • Week 4 backup, 74 GB

approximately 1 TB of stored backups. And backups will be taking 20-40 times longer than they are now. An interesting followup question would be, "Are you doing cold or hot backups?"

share|improve this answer
5  
One important thing to add: TEST your backup (for example in a virtual machine). The moment you need it is the worst time to find out that the export produced wrong syntax or missed a table or directory. –  Rüdiger Voigt Apr 28 at 8:42
    
I never put thought into it really, besides making sure my backups do work all of the time and I had some half-ready plans for if anything would happen. –  user37646 Apr 28 at 10:55
    
Storage is not an issue for how cheap HHD space is for hot and cold storage. I think I only should keep backups for at least up to a year in cold storage, and hot should be up to three months now. The answer helped a lot with re-evaluation. –  user37646 Apr 28 at 10:56
    
If the DB backup isn't internally consistent, the backup may be useless. Hot and cold backups refer to the two most common methods of quiescing the DB files on disk during backup in order to ensure internal consistency, not the method of storage. Without quiescing the DB, either by turning off DB changes (cold) or temporary only logging changes (hot), your chances of recovering anything are iffy. On the gripping hand, you might be using a DB that has another fully consistent backup method. Just copying the DB files without any attempt to quiesce the DB is a Bad Idea. –  Scott Leadley Apr 28 at 12:28

I would do daily backups, or every-other-day, just to play it safe.

But, that's obviously if you have enough space to do so. Maybe do every other day backups, and delete old ones when it becomes too much. From the month before, etc.

share|improve this answer

For a database of that size you want to discover slave servers ( for a running, real-time backup) , segments ( to reduce the amount of redundant backups you have to keep ) and rsync ( to copy only the parts that need copying ).

I would do the following:

  • set up a slave server on different hardware. If the primary hardware has a meltdown it can be promoted very quickly.

  • change the slave tables to add partitions ( Mysql part 18 ). Keep each partition to a reasonable size, maybe half the daily addition.

  • use rsync to copy the database files to a separate volume, use the --link-dest option to have rsync make hardlinks to parts that have not changed since the day before. This way you get both a convenient point-in-time backup and a much faster copy as it's only grabbing the changed parts.

Search for "Time Machine rsync script" for examples. It's not complex but all the options have to be just right.

Don't forget to include slave stop and slave start or the indexes won't copy properly.

share|improve this answer

Your Answer

 
discard

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