To backup your database regularly is one of the tasks that can be very easily accomplished but is quite often neglected until the minute you face the first disaster. Backup solutions can be of very complex types which you might engineer to simple ones like copy and pasting. Depending on your application, the necessity for complex solutions might vary. So what could be a minimum solution that is viable and stable?
The simplest solution in my opinion should contain these elements:
- Create a dump of the database
- Copy the dump to another location (different from the DB server)
Now depending on your RDBMS and operating system, you can take different approaches to implement these two tasks. On a Linux system with a MySQL or PostgreSQL, my approach would be to write a shell script which does these two functions. One such a script might look like this
#!/bin/bash DATE=`date +%Y%m%d-%s` FILE=/path_to_bckp_folder/$DATE-blog.sql mysqldump db_name > $FILE
Line no. 3 creates the DATE variable which holds the current timestamp. It is used to construct the name of the backup file. Line no. 5 creates the FILE variable which holds the complete path of the backup file. Line no. 7 is the MySQL command to dump the database named “db_name” to the path determined in line no. 5.
So far, this code completes the first task of backup up the database. The next task is to copy it somewhere else so we don’t have the db and backup on the same disk. Now this “somewhere” could be some other server, some other disk, or somewhere else e.g. cloud storage. My preferred choice would be to put it to a cloud storage like AWS S3. Having aws cli installed, copying the backup file to an AWS S3 bucket is a one line code
aws s3 cp $FILE s3://bucket_path
The complete file would look like:
#!/bin/bash DATE=`date +%Y%m%d-%s` FILE=/path_to_bckp_folder/$DATE-blog.sql mysqldump db_name > $FILE aws s3 cp $FILE s3://blog-db-bckp
I know a lot of people might not agree with me that this is a good solution, but in my opinion, this is the minimum code that does the work. What you need to do next is add this script to /etc/cron.daily folder and your script will execute daily and do the backup for you (please make sure that permissions are correctly set so it can be executed). It is not the most elegant solution but does the job.
As a bonus point, I would also add a “done” notification to a script which would send an email or post to a slack channel or whatever notification that suits you so you know that your script has executed.