Linux shell script for database backup

After hours and hours work, I created a solution like the below. I copy paste for other people that can benefit.

First create a script file and give this file executable permission.

# cd /etc/cron.daily/# touch /etc/cron.daily/dbbackup-daily.sh# chmod 755 /etc/cron.daily/dbbackup-daily.sh# vi /etc/cron.daily/dbbackup-daily.sh

Then copy following lines into file with Shift+Ins

#!/bin/shnow="$(date +'%d_%m_%Y_%H_%M_%S')"filename="db_backup_$now".gzbackupfolder="/var/www/vhosts/example.com/httpdocs/backups"fullpathbackupfile="$backupfolder/$filename"logfile="$backupfolder/"backup_log_"$(date +'%Y_%m')".txtecho "mysqldump started at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"mysqldump --user=mydbuser --password=mypass --default-character-set=utf8 mydatabase | gzip > "$fullpathbackupfile"echo "mysqldump finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"chown myuser "$fullpathbackupfile"chown myuser "$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

Edit:
If you use InnoDB and backup takes too much time, you can add "single-transaction" argument to prevent locking. So mysqldump line will be like this:

mysqldump --user=mydbuser --password=mypass --default-character-set=utf8          --single-transaction mydatabase | gzip > "$fullpathbackupfile"

Create a script similar to this:

#!/bin/sh -elocation=~/`date +%Y%m%d_%H%M%S`.dbmysqldump -u root --password=<your password> database_name > $locationgzip $location

Then you can edit the crontab of the user that the script is going to run as:

$> crontab -e

And append the entry

01 * * * * ~/script_path.sh

This will make it run on the first minute of every hour every day.

Then you just have to add in your rolls and other functionality and you are good to go.

I got the same issue.But I manage to write a script.Hope this would help.

#!/bin/bash# Database credentialsuser="username"password="password"host="localhost"db_name="dbname"# Other optionsbackup_path="/DB/DB_Backup"date=$(date +"%d-%b-%Y")# Set default file permissionsumask 177# Dump database into SQL filemysqldump --user=$user --password=$password --host=$host $db_name >$backup_path/$db_name-$date.sql# Delete files older than 30 daysfind $backup_path/* -mtime +30 -exec rm {} \;#DB backup logecho -e "$(date +'%d-%b-%y  %r '):ALERT:Database has been Backuped"    >>/var/log/DB_Backup.log