First create a script under the home directory of the account lets say

/var/www/vhosts/yourdomain.com/mysqldatabasebackupscript.sh

In this case I am taking the example of domain hosted on the Linux Plesk Server, please change the paths accordingly for cPanel servers

Then chmod 755 /var/www/vhosts/yourdomain.com/mysqldatabasebackupscript.sh

Now configure a cron

vi /var/spool/cron/username

Add the following command
—————————————
0 22 * * * sh /var/www/vhosts/yourdomain.com/mysqldatabasebackupscript.sh
—————————————

Now add the following script at /var/www/vhosts/yourdomain.com/mysqldatabasebackupscript.sh
===========================

#!/bin/sh
now=”$(date +’%d_%m_%Y_%H_%M_%S’)”
filename=”dbname_backup_$now”.sql.gz
backupfolder=”/var/www/vhosts/yourdomain.com/httpdocs/dbbackups”
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=mysqlusername –password=userpassword –opt dbname | gzip > “$fullpathbackupfile”
echo “mysqldump finished at $(date +’%d-%m-%Y %H:%M:%S’)” >> “$logfile”
chown username.psacln “$fullpathbackupfile”
chown username.psacln “$logfile”
echo “file permission changed” >> “$logfile”
find “$backupfolder” -name db_backup_* -mtime +8 -exec rm {} \;
echo “old files deleted” >> “$logfile”
echo “operation finished at $(date +’%d-%m-%Y %H:%M:%S’)” >> “$logfile”
echo “*****************” >> “$logfile”
exit 0
===========================

Note:- backupfolder path in in the root of the domain please set it according to the actual root of the domain

As the script was desined for plesk server I’ve set the ownership to username.psacln, please change it to username.username for cPanel server

In the above command now is reading the current time

backupfolder is reading the path of the folder where you wish the backup files to get stored

Define the database username like I did –user=mysqlusername –password=userpassword and name of the database to backed up in this case it is dbname .

Please use the script this might come helpful 🙂