CODEWITHSUNDEEP
mysqlbasherror-handling
Michael VanDeMar
Michael VanDeMar

Reputation: 167

How can I detect if mysqldump fails in a bash script?

I have a small down and dirty script to dump one of the tables all of a client's databases nightly:

#!/bin/bash

DB_BACKUP="/backups/mysql_backup/`date +%Y-%m-%d`"
DB_USER="dbuser"
DB_PASSWD="dbpass"

# Create the backup directory
mkdir -p $DB_BACKUP

# Remove backups older than 10 days
find /backups/mysql_backup/ -maxdepth 1 -type d -mtime +10 -exec rm -rf {} \;

# Backup each database on the system
for db in $(mysql --user=$DB_USER --password=$DB_PASSWD -e 'show databases' -s --skip-column-names|grep -viE '(staging|performance_schema|information_schema)');
do echo "dumping $db-uploads"; mysqldump --user=$DB_USER --password=$DB_PASSWD --events --opt --single-transaction $db uploads > "$DB_BACKUP/mysqldump-$db-uploads-$(date +%Y-%m-%d).sql";
done

Recently we've had some issues where some of the tables get corrupted, and mysqldump fails with the following message:

mysqldump: Got error: 145: Table './myDBname/myTable1' is marked as crashed and should be repaired when using LOCK TABLES

Is there a way for me to check if this happens in the bash script, and log the errors if so?

Also, as written would such an error halt the script, or would it continue to backup the rest of the databases normally? If it would halt execution is there a way around that?

Upvotes: 1

Views: 1596

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

Every program has an exit status. The exit status of each program is assigned to the $? builtin bash variable. By convention, this is 0 if the command was successful, or some other value 1-255 if the command was not successful. The exact value depends on the code in that program.

You can see the exit codes that mysqldump might issue here: https://github.com/mysql/mysql-server/blob/8.0/client/mysqldump.cc#L65-L72

You can check for this, and log it, output an error message of you choosing, exit the bash script, whatever you want.

mysqldump ...
if [[ $? != 0 ]] ; then
  ...do something...
fi

You can alternatively write this which does the same thing:

mysqldump ... || {
  ...do something...
}

The || means to execute the following statement or code block if the exit status of the preceding command is nonzero.

By default, commands that return errors do not cause the bash script to exit. You can optionally make that the behavior of the script by using this statement, and all following commands will cause the script to exit if they fail:

set -e

Upvotes: 4

Related Questions