Home / server / Questions / 434719
In Process
Jure1873
Asked: 2012-10-04 23:21:43 +0800 CST

Bash script for mysql backup - error handling

  • 772

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
mysql

3 Answers

  1. 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.

    • 3

  2. Ok, I had to reorder things a little. Iain's answer didn't work, as mysqldump is piped to gzip so I got the return value of gzip so I used PIPESTATUS and then I had to reorder the command for the while loop to get the Errors variable outside of the subshell.

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

FIELDS="TABLE_SCHEMA, TABLE_NAME, UPDATE_TIME"
W_COND="UPDATE_TIME >= DATE_ADD(CURDATE(), INTERVAL -2 DAY) AND TABLE_SCHEMA<>'information_schema'"

while read db table tstamp; do

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

        EXITSTATUS=${PIPESTATUS[0]}
        if [ "$EXITSTATUS" -ne "0" ]
        then 
                 echo "ERROR when backing up $db $table!"
                 ERRORS=$((ERRORS+1))
        fi

        touch -d "$tstamp" $BKPDIR/$db-$table.sql.gz

done < <(mysql --skip-column-names -e "SELECT $FIELDS FROM information_schema.tables WHERE $W_COND;")

if [ "$ERRORS" -ne "0" ]
then 
        echo "ERRORS when backing up MySQL!" 
fi

exit $ERRORS
    • 1

  3. I use 

    ERRORS=$?

    after mysqldump command.

    $? returns status of the last executed command.

    If you want you could check results after all commands and set ERRORS based on multiple results.

    • 0