x

Single-database backup scripts are common; mysqldump all databases is the next search when one server hosts WordPress, app schemas, and reporting DBs. This loop dumps each user database to its own compressed file.

What this script does

Reads database list from SHOW DATABASES
Skips information_schema, performance_schema, mysql, sys
One .sql.gz per schema under a dated directory
Uses ~/.my.cnf for credentials (mode 600)
Deletes dumps older than retention days

Prerequisites

mysql client and mysqldump
MySQL user with SELECT and LOCK TABLES or –single-transaction for InnoDB
Disk space under BACKUP_DIR

Step 1: Save the script

sudo nano /usr/local/bin/mysqldump-all.sh
sudo chmod +x /usr/local/bin/mysqldump-all.sh

Step 2: Full script (scroll to read)

mysqldump-all.sh

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

BACKUP_DIR="/backups/mysql"
RETENTION_DAYS=14
SKIP_RE='^(information_schema|performance_schema|mysql|sys)$'
LOG="/var/log/mysqldump-all.log"
STAMP=$(date +%F)
DEST="$BACKUP_DIR/$STAMP"

log(){ echo "[$(date '+%F %T')] $*" | tee -a "$LOG"; }

mkdir -p "$DEST"
mapfile -t dbs < <(mysql -N -e 'SHOW DATABASES')

for db in "${dbs[@]}"; do
  [[ "$db" =~ $SKIP_RE ]] && continue
  log "Dumping $db"
  mysqldump --single-transaction --routines --triggers "$db" | gzip -9 > "$DEST/${db}.sql.gz"
done

find "$BACKUP_DIR" -mindepth 1 -maxdepth 1 -type d -mtime +"$RETENTION_DAYS" -exec rm -rf {} + 2>/dev/null || true
log "DONE -> $DEST"

Scroll inside the box to read the full script.

Step 3: Configure settings

Single DB version: MySQL backup script
Off-site: rsync backup script
Never commit .my.cnf — root-only permissions

Shell script mysqldump all MySQL databases

Step 4: Test manually

sudo /usr/local/bin/mysqldump-all.sh

ls -lh /backups/mysql/$(date +%F)/

Schedule with cron

sudo crontab -e

Add:

15 2 * * * /usr/local/bin/mysqldump-all.sh >> /var/log/mysqldump-all.log 2>&1

MySQL Dump All Databases Shell Script (mysqldump Loop, gzip, Cron)