When automating MySQL database backups with shell scripts, properly handling mysqldump's exit status is crucial for reliable operation. Like most Unix/Linux commands, mysqldump returns an exit status code that indicates success or failure.


The basic pattern for checking command exit status in shell scripts is:
mysqldump -u user -p password database > backup.sql
if [ $? -eq 0 ]; then
    echo "Backup succeeded"
else
    echo "Backup failed" >&2
    exit 1
fi

Here's a more robust implementation that includes error handling and logging:
#!/bin/sh

# Configuration
DB_USER="admin"
DB_PASS="securepass"
DB_NAME="production_db"
BACKUP_DIR="/var/backups/mysql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/mysql_backup.log"

# Create backup directory if not exists
mkdir -p $BACKUP_DIR || {
    echo "$(date) - ERROR: Failed to create backup directory" >> $LOG_FILE
    exit 1
}

# Execute mysqldump
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$TIMESTAMP.sql"
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_FILE 2>> $LOG_FILE

# Check exit status
case $? in
    0)  echo "$(date) - SUCCESS: Backup completed to $BACKUP_FILE" >> $LOG_FILE
        ;;
    1)  echo "$(date) - ERROR: General mysqldump failure" >> $LOG_FILE >&2
        exit 1
        ;;
    2)  echo "$(date) - ERROR: Access denied or connection issue" >> $LOG_FILE >&2
        exit 1
        ;;
    *)  echo "$(date) - ERROR: Unknown mysqldump error (code $?)" >> $LOG_FILE >&2
        exit 1
        ;;
esac


0: Success
1: General error (table doesn't exist, syntax error, etc.)
2: Connection/authentication error

For more complex scenarios, consider trapping errors:
#!/bin/sh

set -euo pipefail

cleanup() {
    if [ -f "$BACKUP_FILE" ]; then
        rm -f "$BACKUP_FILE"
    fi
}

trap cleanup EXIT

mysqldump -u user -ppassword dbname | gzip > backup.sql.gz

To verify your error handling works, you can test with:
# Test successful backup
mysqldump -u valid_user -pvalid_password existing_db

# Test failed backup
mysqldump -u invalid_user -pwrong_password non_existent_db



Yes, mysqldump follows standard UNIX conventions by returning an exit status code upon completion. This is crucial for scripting and automation scenarios where you need to verify backup success programmatically.

#!/bin/sh
mysqldump -u username -p database_name > backup.sql

# Check exit status immediately after execution
if [ $? -eq 0 ]; then
    echo "Backup succeeded"
else
    echo "Backup failed with error code $?"
fi

These are the most frequent exit codes you'll encounter:

0: Success
1: General error (authentication failure, invalid arguments)
2: Connection issues (server unavailable, wrong host/port)
3: Table lock issues

For production scripts, consider this more robust approach:

#!/bin/sh

# Set variables
DB_USER="admin"
DB_PASS="securepassword"
DB_NAME="production_db"
BACKUP_FILE="/backups/db_$(date +%Y%m%d).sql"

# Execute dump with error output
mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" > "$BACKUP_FILE" 2> backup_error.log

# Comprehensive status handling
case $? in
    0) echo "Backup completed successfully" ;;
    1) echo "General mysqldump error - check backup_error.log" ;;
    2) echo "Connection failure - verify database accessibility" ;;
    *) echo "Unknown error occurred during backup" ;;
esac


Always redirect stderr (2>) to capture detailed error messages
Include timestamp in backup filenames for versioning
Consider adding notification mechanisms (email, Slack) for failures
Validate backup file size/content after creation


# Example of post-backup validation
if [ -s "$BACKUP_FILE" ]; then
    echo "Backup file exists and has content"
else
    echo "Backup file empty or missing" >&2
    exit 1
fi

For temporary network issues, implement retry logic:

#!/bin/sh

MAX_RETRIES=3
RETRY_DELAY=5
attempt=1

while [ $attempt -le $MAX_RETRIES ]; do
    mysqldump -u user -p pass db > backup.sql
    if [ $? -eq 0 ]; then
        break
    fi
    echo "Attempt $attempt failed, retrying in $RETRY_DELAY seconds..."
    sleep $RETRY_DELAY
    attempt=$((attempt + 1))
done

[ $attempt -gt $MAX_RETRIES ] && echo "Max retries reached" && exit 1