Cron job for backing up MYSQL database from SSH

Backing up MYSQL databases became very necessary especially if you own a shopping cart or e commerce website.

Hackers are always trying to gain access to your database and sometimes they gain access and the most action they perform is to modify databases and change the information stored. In that case you cannot restore your database if you do not have backup and if you have backups mostly they are daily or weekly backups taken by cPanel or by any other control panel.

There is no feature in any hosting panel which allows you to take backup every minute or by custom time span.

The following script allows you to configure an time interval for taking backup of mysql database and it does not replace any backup stored already. This means you can restore any backup available accordingly.

Features are discussed in the comment# lines of the scripts:

Just copy and paste this script in any file like: backup.sh and upload it in any folder giving root access to it.

 

#!/bin/bash -x
#backup file location, it is the location where backup will be stored, change it to your own
backupDir=/home/USERNAME/public_html/backups/
myDate=$(date '+%Y-%m-%d'-'%I-%M-%S-%p')

#this is the backup file name, generates different names of files to avoid rewriting
dbName=backupSQL-$myDate

# This is temporary directory, change it accordingly if you have this somewhere else.
cd /tmp

# MYSQL command to take backup with valid username/password
mysqldump -u root -pPASSWORD_HERE DATABASE_NAME_HERE > $dbName

# it compresses the file into tar to save the disk space.
tar -cvf $dbName.tar $dbName

# moves the file to backup directory from temporary directory
mv $dbName.tar $backupDir

# delete backups older than 2 days, change -mtime +2 to any digit like +7 for files older than 7 days.
find $backupDir -mtime +2 | xargs rm -rf

<br/ >
Now come to a cron job, Follow the following steps to set a cron job for auto backups.
<br/ >
Login to SSH with root access.

Execute following command:

$ crontab -e

A text editor will be opened just write the following cron job line at the end of file:

*/5 * * * * /FILE-PATH/backup.sh

In above code */5 is the time interval for auto backups. Auto backup script will run every 5 minutes. You can change it accordingly.
Just save the cron job and you’re done.

Feedback are welcome 🙂