Linux shell script for database backup

Question

I tried many scripts for database backup but I couldn't make it. I want to backup my database every hour.
I added files to "/etc/cron.hourly/" folder, changed its chmod to 755, but it didn't run. At least I write my pseudo code.

I would be happy if you can write a script for this operation and tell me what should I do more ? After adding this script file to /etc/cron.hourly/ folder.

• Get current date and create a variable, date=date(d_m_y_H_M_S)
• Create a variable for the file name, filename="$date".gz
• Get the dump of my database like this mysqldump --user=my_user --password=my_pass --default-character-set=utf8 my_database | gzip > "/var/www/vhosts/system/example.com/httpdocs/backups/$("filename")
• Delete all files in the folder /var/www/vhosts/system/example.com/httpdocs/backups/ that are older than 8 days
• To the file "/var/www/vhosts/system/example.com/httpdocs/backup_log.txt", this text will be written: Backup is created at $("date")
• Change the file owners (chown) from root to "my_user". Because I want to open the backup and log files from the "my_user" FTP account.
• I don't want an email after each cron. >/dev/null 2>&1 will be added.

Answer 1

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/sh now="$(date +'%d_%m_%Y_%H_%M_%S')" filename="db_backup_$now".gz backupfolder="/var/www/vhosts/example.com/httpdocs/backups" 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=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" 

Answer 2

Create a script similar to this:

#!/bin/sh -e  location=~/`date +%Y%m%d_%H%M%S`.db  mysqldump -u root --password=<your password> database_name > $location  gzip $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.