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

Question

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?

Bill Karwin · Accepted Answer · 2022-01-05 07:44:46Z

4

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

answered Jan 5, 2022 at 7:44

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Davisson Over a year ago

Note that using if [[ $? != 0 ]] ... will replace the exit status of mysqldump with the status of the [[ ]] conditional; if you want to check for specific error codes, save the status with something like status=$? immediately after the mysqldump command, and then check $status to see what error (if any) occurred.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related