patternMinor
Database Backups of 2GB+
Viewed 0 times
database2gbbackups
Problem
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?
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?
Solution
Some important factors to consider for backup are:
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
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:
...
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?"
- 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?"
Context
StackExchange Database Administrators Q#64029, answer score: 8
Revisions (0)
No revisions yet.