Schedule MySQL Database backup on CPanel or Linux

If you running a business online and you are using MySQL database to keep your customer and product related information. Then, you always try to keep database backed up, just in case, if your database got crashed, you will have a copy of data. 

Taking manual backup will never be a good idea as it takes time and resource whereas you will find many paid services which will help you to keep your database backed up on regular interval. But, if you are looking for something, which can do same job without paying single penny to any third party. Then, this might be useful article for you

First of all, we create a backup folder, where we will keep our database backup dump file. And then, we will write the shell script, which will create database backup file and place it to our backup folder. 

#!/bin/sh
now="$(date +'%d_%m_%Y_%H_%M_%S')"
filename="db_backup_$now".gz
backupfolder=“"
fullpathbackupfile="$backupfolder/$filename"
logfile="$backupfolder/"backup_log_"$(date +'%Y_%m')".txt
echo "mysqldump started at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
mysqldump —user= —password= --default-character-set=utf8  | gzip > "$fullpathbackupfile"
echo "mysqldump finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
chown  "$fullpathbackupfile"
chown  "$logfile"
echo "file permission changed" >> "$logfile"
find "$backupfolder" -name db_backup_* -mtime +2 -exec rm {} \;
echo "old files deleted" >> "$logfile"
echo "operation finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
echo "*****************" >> "$logfile"
exit 0

Above script will create backup file and place it to your backup folder and also it will remove dump file, which is older than 2 days. You can keep this shell script file to any location (but I would recommend to keep this file in same backup folder)

Now, depending on your requirement, you should schedule this script to run on specific time/interval by adding it to CRON job. And to add new CRON job, you need to provide following details, path of shell script file (i.e. command) and email id where you want to receive notification for any failure/success.

Once, you have successfully created CRON job, then you will see a CRON job entry created, which will be executed only specified time/interval 




Upon your specified time/interval, your backup folder will start getting populated with your database backup and log files.

That's it. !!

Let me know, if you got into any issue while configuring it.