Backup All MySQL Databases with mysqldump

#!/usr/bin/env bash
#
# mysql_backup.sh - Dump every MySQL database into a separate compressed file.
#
# Credentials are read from a MySQL option file (~/.my.cnf or one you pass in)
# so passwords never appear on the command line or in the process list.
#
# Usage: ./mysql_backup.sh <output_dir> [defaults-extra-file]
#
set -euo pipefail

# --- Argument handling --------------------------------------------------------
if [[ $# -lt 1 || $# -gt 2 ]]; then
    echo "Usage: $0 <output_dir> [defaults-extra-file]" >&2
    exit 1
fi

OUT_DIR="$1"
DEFAULTS_FILE="${2:-$HOME/.my.cnf}"

[[ -r "$DEFAULTS_FILE" ]] || { echo "Error: cannot read credentials file '$DEFAULTS_FILE'." >&2; exit 1; }
mkdir -p "$OUT_DIR"

TIMESTAMP="$(date +%Y%m%d-%H%M%S)"
MYSQL_AUTH=(--defaults-extra-file="$DEFAULTS_FILE")

# --- Enumerate databases, excluding internal schemas --------------------------
DATABASES="$(mysql "${MYSQL_AUTH[@]}" -N -e 'SHOW DATABASES;' \
    | grep -Ev '^(information_schema|performance_schema|mysql|sys)$')"

# --- Dump each database -------------------------------------------------------
for DB in $DATABASES; do
    OUTFILE="${OUT_DIR%/}/${DB}-${TIMESTAMP}.sql.gz"
    echo "Dumping '$DB' -> $OUTFILE"
    mysqldump "${MYSQL_AUTH[@]}" \
        --single-transaction --quick --routines --triggers --events \
        "$DB" | gzip -c > "$OUTFILE"
done

echo "All databases dumped to $OUT_DIR"