1

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="user@server.com"
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
  • Please, edit your question to show the cron job the script runs from. – ilkkachu Oct 14 '16 at 11:21
1

Please post your cronjob task as well as you may have a problem there. What I would also suggest doing is to create a lock file somewhere by just touching the file in some location which you can check if exists before running any backups. I'm not sure how big your database is but in my case sometimes databases are so big that the backup process can overlap with another already running hence I always touch like a /tmp/backup.lock file and before I run anything in the script I put something like if [ -e /tmp/backup.lock ]; then...

In your case I'm pretty sure you have an error in your cronjob and you run it every minute every 4 hours or something like that.

  • * */4 * * * chronic /usr/local/bin/db_bkup.sh – eekfonky Oct 14 '16 at 10:24
  • 1
    As I expected... */4 means every 4 hours. you need 0 4 * * * to run it at 4AM full stop. Your cron means run every minute every 4 hours. – user195073 Oct 14 '16 at 10:42
  • I just want it to run once every 4 hours. How do you do that then? – eekfonky Oct 14 '16 at 10:45
  • 1
    0 */4 * * * chronic /usr/local/bin/db_bkup.sh crisis averted! – eekfonky Oct 14 '16 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.