intro
In this blog, we dig into the so-called “golden backup rule” for databases. Implement the perfect data backup and recovery strategy. Join in!
Backing up data is of crucial importance to everyone as it’s an essential step to adhere to many regulations as well as a part of various solutions to privacy and security problems. If your aim is to understand how, why and when you should back up data, you came to the right place.
Why Back Up Data?
Data backup and recovery is an essential step of your software engineering journey. No matter your reason for storing data (your application probably stores data because of the way it’s used by your users, visitors, or customers, but there can be other reasons), data has to be backed up.
Your backups have to be tested and verified that they work because if they cannot be restored, once you find yourself in hot water, you may be in even deeper trouble than it seems.
When and How to Back Up Data?
Once you understand that you must back up data to not cause headaches either for yourself or those around you, understand that the way you back up data has a direct impact on many things. The approach you follow for data backup has an impact on:
Choosing a way to back up data in the first place is of key importance. Understand that backups can be either logical (we can back up statements that recreate the data) or physical (we can backup files that, when restored, will restore the data in the database), and also the phases of the backups can differ quite significantly: we can have incremental backups, full backups, or differential backups.
The ways we go about implementing and running the backups in those phases can also vary quite significantly depending on our data backup and recovery strategy.
So, back to the question — when and how best to backup data?
The Beginning of the Golden Backup Rule — Plan
Plans are the backbone of everything. The same goes for backups — the more well-executed your backup plan is, the better for everyone around you. You need a reliable data backup and recovery strategy — and the golden backup rule is a great place to get started.
The golden backup rule refers to the fact that you should always have at least three (yes, 3) copies of backups of your data: one copy should act as the primary copy, and the others should act as backups of the copy of the primary copy of the data. The reasons behind that are as follows:
Of course, it goes without saying that backups should be tested. In other words, make sure that the restoration process of all of the backups in question is completed successfully before calling them reliable.
Implementing the Golden Backup Rule
That’s all cool and roses, but how do you actually implement the golden backup rule? Worry not — just follow the steps outlined below:
How to Implement the Golden Rule for Data Backup and Recovery in MySQL
If you’re a user of MySQL, the golden backup rule may be as simple as:
No need to sweat — just set up a simple Bash script like so and you will be good to go:
1
backupfolder=/tmp/backups # backup directory
2
[backup_email=your@email.com](mailto:backup_email=your@email.com) # email to notify once backup is completed
3
user=mysql_username
4
password=complex_passwordhere # MySQL details
5
keep_days=7 # the number of days to keep the backup
6
sqlfile=$backupfolder/all-databases-$(date +%d-%m-%Y_%H-%M-%S).sql # setting the name of the backup
7
sudo mysqldump -u $user -p$password --all-databases > $sqlfile # executing the backup
8
mailx -s 'Backup was successfully created' $recipient_email # mailing
9
find $backupfolder -mtime +$keep_days -delete # deleting unnecessary backups
The contents of the bash script in question may differ depending on your specific use case, but you get the idea. For more information, refer to our guide on MySQL backup and recovery best practices.
Summary
Implementing a proper data backup and recovery plan for your use case isn’t at all difficult if you know your way around databases and have at least basic knowledge of Bash scripting. Remember key concepts — have at least three copies of your data (one local, a backup of the local copy, and at least one copy off-site), automate backups when necessary, and keep a cool head on your shoulders.
FAQ
What is a good data backup and recovery strategy?
A concrete answer to that question would be “it depends.” It really depends because backups are influenced by many factors such as the strain on your database when you’re taking the backup, the size of your data, how often you take backups in the first place, etc.
Automate the way you take backups, use built-in tools that help you back up data instead of relying on ghetto solutions, and you will be good to go.
Where can I learn more about backing up data?
To learn more about backups, indexing, performance, security, and databases in general make sure to visit our blog to keep updated on the latest trends. Specifically, refer to:
Alternatively, consider reading books on the subject to help you stay on track. The book Hacking MySQL: Breaking, Optimizing, and Securing MySQL for Your Use Case is a good place to start.
