I have a bash script to backup MySQL databases and then rsync them to a remote location. Last night this failed spectacularly and filled up the home backup-db directory with 33GB! I have the script running on a cron job every 4 hours. When I looked at the folder it looked like the backup was running every minute of the hour every 4 hours. e.g not once at 4am, but every minutes of that hour. Same at 8am etc.
I think the problem is that the rsync is running before the backup is done and it's getting in a loop without exiting. Is there a way to get the script to exit on fail of backup, or confirm success of backup BEFORE running the rsync?
For now I have just stripped out the rsync to a separate little script (but I'd like to have it all in one place as I need to replicate this over many servers.
#!/bin/bash
NOW=$(date +"%Y-%m")
BACKUPDIR="/home/deploy/backup-db/$NOW"
BKUPSSH="[email protected]"
BKUPSERVDIR="/home/user/backups/databases"
# Remove files older than 30 days
find $BACKUPDIR/ -mtime +31 -exec rm {} \;
# DO NOT BACKUP these databases
IGNOREDB="
information_schema
mysql
test
"
#* MySQL binaries *#
MYSQL=$(which mysql)
MYSQLDUMP=$(which mysqldump)
GZIP=$(which gzip)
# assuming that BACKUPDIR exists
if [ ! -d $BACKUPDIR ]; then
mkdir -p $BACKUPDIR
else
:
fi
# get all database listing
DBS="$(mysql --login-path=dbbkup -Bse 'show databases')"
# SET DATE AND TIME FOR THE FILE
NOW=$(date +"%Y-%m-%d_%H.%M"); # year-month-day_hour.minute format
# start to dump database one by one
for db in $DBS
do
DUMP="yes";
if [ "$IGNOREDB" != "" ]; then
for i in $IGNOREDB # Store all value of $IGNOREDB ON i
do
if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
DUMP="NO"; # SET value of DUMP to "no"
#echo "$i database is being ignored!";
fi
done
fi
if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
FILE="$BACKUPDIR/$NOW-$db.sql.gz";
echo "BACKING UP $db";
$MYSQLDUMP --login-path=dbbkup --add-drop-database --opt --lock-all-tables --set-gtid-purged=OFF $db | $GZIP > $FILE
fi
done
# change permissions on files
chmod -R 755 $BACKUPDIR
# rsync backup to the backup server and append the log file
rsync -azv $BACKUPDIR -e ssh $BKUPSSH:$BKUPSERVDIR >> /home/deploy/db_rsync.log 2>&1
RESULT="$?"
# check result or rsync
if [ "$RESULT" != "0" ]; then
echo -e "Rsync exit Code:" $RESULT "\nFailed to rsync databases" >> /home/deploy/db_rsync.log 2>&1
else
echo "succesfully rsynced databases" >> /home/deploy/db_rsync.log 2>&1
fi
