Mysql backup script for all databases into zip file

mysql-db-backup.sh

#!/bin/bash

# Authors: 
# - https://stackoverflow.com/users/5444623/peterm (original script)
# - https://github.com/PassiHD2004 (improvements)

# This script will backup all mysql databases into 
# compressed file named after date, ie: /var/backup/mysql/2024-05-09_07:00.zip

# Setup variables used later

# Create date suffix with "F"ull date format and add hour:time
suffix=$(date +%F_%H:%M)
# Retrieve all database names except information schemas. Use sudo here to skip root password.
dbs=$(sudo mysql --defaults-extra-file=/etc/my.cnf --batch --skip-column-names -e "SHOW DATABASES;" | grep -E -v "(information|performance)_schema")
# Create temporary directory with "-d" option
tmp=$(mktemp -d)
# Set output dir here. /var/backups/ is used by system, 
# so intentionally used /var/backup/ for user backups.
outDir="<DIRECTORY>"
# Create output file name
out="$outDir/$suffix.zip"
# Set remote backup target
target="<REMOTE TARGET>"

# Actual script

# Check if output directory exists
if [ ! -d "$outDir" ];then
  # Create directory with parent ("-p" option) directories
  sudo mkdir -p "$outDir"
fi

# Loop through all databases
for db in $dbs; do
  # Dump database to temporary directory with file name same as database name + sql suffix
  sudo mysqldump --defaults-extra-file=/etc/my.cnf --databases "$db" > "$tmp/$db.sql"
done

# Go to tmp dir
cd $tmp

# Compress all dumps with bz2, discard any output to /dev/null
sudo zip -r "$out" * > "/dev/null"

# Backup to remote target
scp "$out" "$target"

# Cleanup
cd "/tmp/"
sudo rm -rf "$tmp"
cd "$outDir/"
find . -maxdepth 1 -mtime 7 -type f -name "*.zip" -exec rm -f {} ';'