Setting-Up Automated MySQL Database Backups

As a Big Data Analytics Developer I am constantly utilizing the Hadoop ecosystem. But as we all know, not all data should be stored there. I found several use cases where it was better to utilize a MySQL database on a local server that can be accessed by tools that we use around the shop. With that came learning how to manage a database so that we could incorporate some DLP(data loss prevention) strategies. One of those is backing up the database so that it would not be a manual process, and time can be spent on more important items. In this post I will walk you through setting up a cron job to backup the MySQL database daily at midnight. I will also walk you through limiting the backup files in the directory to only 10 so that you have more than one restore date, and at the same time keeping a limit on the number of files for storage size restraints.

Step 1: Create a database and table for use in this tutorial

I created a database called ‘MEDIUM’ and a small table in it called ‘example’ for this tutorial.

Step 2: Create a user that has read only access to this database

Note that the password for this user is ‘PASSWORD’. This is bad practice in a production environment and a more complex password should be used.

Step 3: Login to MySQL with the ‘BACKUP_USER’ to verify this user has access to only this specific database.

This worked. There are many databases in this MySQL Server but only the MEDIUM database can be viewed by this user we just created.

Step 4: Create a directory where we will store the backup files.

Step 5: Create bash script that dumps the database and stores it in the new directory

Step 6: Add cron job that executes the bash script to run at midnight daily

To modify the cron jobs use the command: ‘sudo crontab -e’

Step 7: Create bash script that removes the oldest file and limits the dir to 10 files

Step 8: Add a cron job that executes the limit script, and runs at 1 am

Step 9: Test the backup worked

You can test that the backup worked by locating the dump file and taking a head on it. For example I used ‘head -30'.

It worked! After all these steps you have 2 scripts that run daily. One is a backup of a database and the other limits the directory to the 10 newest backup files.