Dump All Users Databases

By Arch Brooks on January 22, 2026 in Programming

If you want to extract/backup one specific user database at a time (instead of dumping all user databases together), the cleanest and most common approach is a small bash loop that:

Lists all databases
Skips system ones (mysql, information_schema, performance_schema, sys, etc.)
Dumps each user database separately into its own .sql (or .sql.gz) file

This is very useful for:

Easier restores (you pick exactly which DB to restore)
Parallel backups if needed
Keeping backups small & manageable
Rotating / deleting old backups per database

Recommended: One database per file (with compression)

Bash

#!/usr/bin/env bash

# ────────────────────────────────────────────────
# Settings – change these as needed
# ────────────────────────────────────────────────

MYSQL_USER="root"
MYSQL_PASS="your_password_here"          # Better: use ~/.my.cnf or mysql_config_editor
DUMP_DIR="./backups"                     # Where to save files
DATE=$(date +%Y-%m-%d_%H%M%S)

# Add more if you have phpmyadmin, test, etc.
EXCLUDE="mysql|information_schema|performance_schema|sys"

OPTIONS="--single-transaction --quick --lock-tables=false --routines --triggers --events --set-gtid-purged=OFF"

# ────────────────────────────────────────────────

mkdir -p "$DUMP_DIR"

# Get list of user databases only
DATABASES=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -BNe "SHOW DATABASES" \
  | grep -vE "^($EXCLUDE)$" )

if [ -z "$DATABASES" ]; then
  echo "No user databases found."
  exit 1
fi

echo "Found user databases:"
echo "$DATABASES" | tr ' ' '\n'
echo "───────────────────────────────────────────────"

# Dump one by one
for db in $DATABASES; do
  echo "Dumping → $db"

  OUTPUT_FILE="$DUMP_DIR/${db}_${DATE}.sql"

  mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASS" $OPTIONS "$db" > "$OUTPUT_FILE"

  if [ $? -eq 0 ]; then
    echo "  → Success"
    gzip "$OUTPUT_FILE" && echo "  → Compressed to ${OUTPUT_FILE}.gz"
  else
    echo "  → FAILED!"
    rm -f "$OUTPUT_FILE"  # don't keep broken file
  fi

  echo ""
done

echo "Done."

Even shorter one-liner style (copy-paste friendly)

Bash

MYSQL_USER=root; MYSQL_PASS=yourpass; \
EXCLUDE="mysql|information_schema|performance_schema|sys"; \
DUMP_DIR=~/backups; mkdir -p "$DUMP_DIR"; \
mysql -u$MYSQL_USER -p$MYSQL_PASS -BNe "SHOW DATABASES" | grep -vE "^($EXCLUDE)$" | \
while read db; do \
  echo "→ $db"; \
  mysqldump -u$MYSQL_USER -p$MYSQL_PASS --single-transaction --routines --triggers --events "$db" \
    | gzip > "$DUMP_DIR/${db}_$(date +%Y%m%d_%H%M%S).sql.gz"; \
done

Views: 18 | Permalink: https://bcs.archman.us/blog.php?slug=dump-all-users-databases

Back to Blog ← Back

BCS Blog

Dump All Users Databases

Recommended: One database per file (with compression)

Even shorter one-liner style (copy-paste friendly)

Comments