Mysql – Bash script for thesql backup – error handling

backupbashMySQLscripting

I'm trying to backup a bunch of MyISAM tables in a way that would allow me to rsync/rdiff the backup directory to a remote location. I've came up with a script that dumps only the recently changed tables and sets the date of the file so that rsync can pick up only the changed ones, but now I don't know how to do the error handling – I would like the script to exit with a non 0 value if there are errors. How could I do that?

#/bin/bash   
BKPDIR="/var/backups/db-mysql"
mkdir -p $BKPDIR
ERRORS=0

FIELDS="TABLE_SCHEMA, TABLE_NAME, UPDATE_TIME"
W_COND="UPDATE_TIME >= DATE_ADD(CURDATE(), INTERVAL -2 DAY) AND TABLE_SCHEMA<>'information_schema'"
mysql --skip-column-names -e "SELECT $FIELDS FROM information_schema.tables WHERE $W_COND;" | while read db table tstamp; do

    echo "DB: $db: TABLE: $table: ($tstamp)"
    mysqldump $db $table | gzip > $BKPDIR/$db-$table.sql.gz
    touch -d "$tstamp" $BKPDIR/$db-$table.sql.gz
done
exit $ERRORS

Best Answer

The mysqldump command returns 0 for success and >0 for warning/error conditions. As you are looping you would need to protect $ERRORS from being overwritten by subsequent successful commands so a bit of logic is needed

mysqldump ...
EXITSTATUS=$?
if [ "$ERRORS" -eq "0" -a "$EXITSTATUS" -ne "0" ]
   then 
       ERRORS=$EXITSTATUS 
   fi

Now, when your script exits it will exit with 0 or the status from the first error encountered.

Related Topic