mysql_backup.sh

#!/usr/bin/env bash
# set -x
set -euo pipefail

readonly SKIP_DB_REGEX="^(information_schema|performance_schema|mysql|sys)$"
readonly MYSQL_COMMON_OPTS=(--silent --skip-column-names)

# Color codes
readonly RED='\033[0;31m'
readonly NC='\033[0m'

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*"
}

error() {
    echo -e "${RED}[$(date '+%Y-%m-%d %H:%M:%S')] ERROR: $*${NC}" >&2
}

die() {
    error "$*"
    exit 1
}

usage() {
    cat <<EOF
Usage: $(basename "$0") -h <host> -u <user> -d <db|'all'> [-o <output_dir>]

Backs up one or all MySQL/MariaDB databases

Options:
  -h, --host       MySQL hostname (required)
  -u, --user       MySQL username (required)
  -d, --database   Database to back up, or 'all' for all non-system DBs (required)
  -o, --output-dir Directory to save backups (default: '.')
  -H, --help       Display this help message

Environment Variables:
  MYSQL_PWD        The MySQL password (required for non-interactive mode)
EOF
    exit 1
}

detect_mysql_client() {
    # Prefer mariadb client if available
    if command -v mariadb &>/dev/null; then
        MYSQL_CLIENT="mariadb"
        MYSQLDUMP_CLIENT="mariadb-dump"
        log "Using MariaDB client"
    elif command -v mysql &>/dev/null; then
        MYSQL_CLIENT="mysql"
        MYSQLDUMP_CLIENT="mysqldump"

        # Check if it's actually MariaDB masquerading as mysql
        if mysql --version 2>&1 | grep -qi mariadb; then
            log "Using MySQL client (MariaDB distribution)"
        else
            log "Using MySQL client (Oracle distribution)"
        fi
    else
        die "Neither 'mysql' nor 'mariadb' client found in PATH"
    fi
}

parse_args() {
    # Set defaults
    MYSQL_HOST=""
    MYSQL_USER=""
    MYSQL_DB=""
    BACKUP_DIR="."

    while [[ $# -gt 0 ]]; do
        local opt="$1"
        local opt_value="$2"

        case "$opt" in
        -h | --host)
            MYSQL_HOST="$opt_value"
            shift 2
            ;;
        -u | --user)
            MYSQL_USER="$opt_value"
            shift 2
            ;;
        -d | --database)
            MYSQL_DB="$opt_value"
            shift 2
            ;;
        -o | --output-dir)
            BACKUP_DIR="$2"
            shift 2
            ;;
        -H | --help)
            usage
            ;;
        *)
            die "Unknown option: $opt"
            ;;
        esac
    done

    # Interactive prompts if running in a TTY
    if [[ -t 0 ]]; then
        [[ -z "$MYSQL_HOST" ]] && read -r -e -p "Hostname: " MYSQL_HOST
        [[ -z "$MYSQL_USER" ]] && read -r -e -p "User: " MYSQL_USER
        [[ -z "$MYSQL_DB" ]] && read -r -e -p "Database (or 'all'): " MYSQL_DB
        [[ -z "${MYSQL_PWD:-}" ]] && {
            read -r -s -p "Password: " MYSQL_PWD
            echo ""
            export MYSQL_PWD
        }
    fi

    # Validation
    [[ -n "$MYSQL_HOST" ]] || die "Hostname cannot be empty"
    [[ -n "$MYSQL_USER" ]] || die "User cannot be empty"
    [[ -n "$MYSQL_DB" ]] || die "Database cannot be empty"
}

test_connection() {
    if ! $MYSQL_CLIENT "${MYSQL_COMMON_OPTS[@]}" \
        --host="$MYSQL_HOST" --user="$MYSQL_USER" \
        --execute="SELECT 1;" &>/dev/null; then
        die "Unable to connect to MySQL server: $MYSQL_HOST"
    fi
    log "MySQL connection successful"
}

backup_database() {
    local db="$1"
    local timestamp
    timestamp=$(date +%Y%m%d-%H%M%S)
    local dump_file="${BACKUP_DIR}/mysqldump-${db}-${timestamp}.sql"

    # Skip databases with no tables
    local table_count
    table_count=$($MYSQL_CLIENT "${MYSQL_COMMON_OPTS[@]}" \
        --host="$MYSQL_HOST" --user="$MYSQL_USER" \
        --execute="SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='${db}'")

    if [[ "$table_count" -eq 0 ]]; then
        log "Skipping '$db' - no tables found"
        return 0
    fi

    # Perform backup
    local stderr_file
    stderr_file=$(mktemp)
    $MYSQLDUMP_CLIENT --host="$MYSQL_HOST" --user="$MYSQL_USER" \
        --events --opt \
        --single-transaction "$db" >"$dump_file" 2>"$stderr_file" || {
        error "Database '$db' - mysqldump failed: $(cat "$stderr_file")"
        rm -f "$dump_file" "$stderr_file"
        return 1
    }
    rm -f "$stderr_file" # Clean up temp on success

    # Compress
    gzip -f "$dump_file"

    # Verify gzipped file
    if [[ ! -s "${dump_file}.gz" ]]; then
        error "Database '$db' - backup file is empty or missing"
        return 1
    fi

    local size
    size=$(du -h "${dump_file}.gz" | awk '{print $1}')
    log "Database '$db' backed up successfully ($size)"

    return 0
}

main() {
    trap 'unset MYSQL_PWD' EXIT
    trap 'unset MYSQL_PWD; log "Backup interrupted"; exit 1' INT TERM

    parse_args "$@"
    detect_mysql_client
    test_connection

    log "Starting backup on ${MYSQL_HOST}"

    # Determine which databases to back up
    local databases
    if [[ "$MYSQL_DB" == all ]]; then
        databases=$($MYSQL_CLIENT "${MYSQL_COMMON_OPTS[@]}" \
            --host="$MYSQL_HOST" --user="$MYSQL_USER" \
            --execute='SHOW DATABASES;' | grep -viE "${SKIP_DB_REGEX}" || true)
    else
        databases="$MYSQL_DB"
    fi

    if [[ -z "$databases" ]]; then
        log "No databases to backup"
        exit 0
    fi

    local db_count
    db_count=$(echo "$databases" | wc -w)
    log "Found $db_count database(s) to backup"

    # Prepare backup directory
    mkdir -p "$BACKUP_DIR"
    log "Backup directory: $(realpath "$BACKUP_DIR")"

    # Backup each database
    local db_failed=0
    local db_success=0

    for db in $databases; do
        if backup_database "$db"; then
            ((++db_success))
        else
            ((++db_failed))
        fi
    done

    log "Backup complete. Success: $db_success, Failed: $db_failed"
    [[ $db_failed -eq 0 ]] && exit 0 || exit 1
}

main "$@"