Backup your database

Creating regular backup of your database 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

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

The complete file would look like:

I know a lot of people might not agree with me that it 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).

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.

If you enjoyed the article, please share...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestShare on RedditEmail this to someone

2 Comments Backup your database

  1. Ivan Micic

    With this piece of code, you can regularly backup your database for sure! The cron and email notifications are just bonuses. Great post Arian!


Leave A Comment

Your email address will not be published. Required fields are marked *