Backing up your entire data regularly is very important. In the event of a disaster, you can easily recover if you have a good backup plan in place. Hence, backups of your entire data should be regularly taken, with a suitable retention period planned. You can automate backups with cron. Automating the backup process with cron can be easily achieved, by creating a cron job that will take backup at a specified time automatically. Entries can be added to /etc/crontab to regularly schedule database backups.

Below are the steps to automate the backup process of all your databases.

How to automate backups with cron?

1. Create a file (.my.cnf) in the user home directory, to add the login credentials of the MySQL root user who will be taking the backup.

2. Once the file is created, go to that directory and perform the below steps.

3. Open the file using the below command

vi .my.cnf

4. On the file, copy and paste the below command. Replace the


user = root

password = MySQL root user password

5. Restrict the permission to the file to only the root user.

chmod 600 .my.cnf

6. Create a directory to store the backups

mkdir ~/ mysql_bkp

7. Open your crontab file using the below command:

crontab -e

8. On that file, create a new cron job to backup at a specified time using the below command:

0 3 * * * /usr/bin/mysqldump –defaults-extra-file=/home/example_user/.my.cnf -u root – -single-transaction – -quick – -lock-tables=false – -all-database > full-backup – $(date +\%Y\%m\%d).sql

Follow the above to automate backups with cron for all databases. However, if you want to take the backup of a single database remove –-all-database and mention the database name, for which you want to take the backup.

Backup Retention

The backups which are taken automatically will need to be removed after a specific period. Since we have a limit to the storage space available, we need to plan on a backup retention policy. So a backup retention policy refers to how long we keep a backup in our storage. Hence, plan it according to your data volume and frequency of data updation. Also, your organization’s recovery process should define the retention policy. Listed below are few example retention rates:

  • A daily backup can have a retention period of 1 week.
  • A weekly backup can have a retention period of 1 month.
  • A monthly backup can have a retention period of 3 months.

The criticality of each application is different, so choose the retention policy as per your application’s requirement. The above, is an example retention period. So, You can set the retention period as per your data size and storage space available.


In the event of a failure, an effective backup management system is essential to restore your system. Hence planning an automatic backup and retention policy will ensure a fool proof recovery process.

By Liz Mathew

Founder, InsightDials

error: Content is protected !!