mysqldump nightly backup with rotation (bash)

mysqldump writes a database out as SQL statements that recreate it. For InnoDB tables, --single-transaction gives a consistent snapshot without locking the application out: it starts a transaction in REPEATABLE READ and reads everything from that moment. This script runs it once per database, compresses the result, checks the gzip file and removes old dumps.

The script

#!/usr/bin/env bash
# mysql-nightly-backup.sh - one compressed mysqldump file per database, with rotation.
# License: CC0-1.0
set -euo pipefail

BACKUP_DIR="${BACKUP_DIR:-/var/backups/mysql}"
KEEP_DAYS="${KEEP_DAYS:-14}"
DEFAULTS_FILE="${DEFAULTS_FILE:-/etc/mysql-backup.cnf}"

umask 077
mkdir -p "$BACKUP_DIR"
stamp="$(date +%Y%m%d-%H%M%S)"
failed=0

log() { printf '%s %s\n' "$(date '+%F %T')" "$*"; }

if [[ ! -r "$DEFAULTS_FILE" ]]; then
  log "ERROR: cannot read $DEFAULTS_FILE"
  exit 1
fi

databases="$(mysql --defaults-extra-file="$DEFAULTS_FILE" -N -B -e 'SHOW DATABASES' \
  | grep -Ev '^(information_schema|performance_schema|sys|mysql)$' || true)"

if [[ -z "$databases" ]]; then
  log "ERROR: no databases found (check credentials in $DEFAULTS_FILE)"
  exit 1
fi

while IFS= read -r db; do
  out="$BACKUP_DIR/${db}-${stamp}.sql.gz"
  tmp="${out}.partial"
  log "dumping $db"
  if mysqldump --defaults-extra-file="$DEFAULTS_FILE" \
       --single-transaction --quick \
       --routines --events --triggers \
       --databases "$db" | gzip -c > "$tmp" && gzip -t "$tmp"; then
    mv "$tmp" "$out"
    log "ok $out ($(du -h "$out" | cut -f1))"
  else
    rm -f "$tmp"
    log "ERROR: dump of $db failed"
    failed=1
  fi
done <<< "$databases"

if [[ "$failed" -eq 0 ]]; then
  find "$BACKUP_DIR" -maxdepth 1 -type f -name '*.sql.gz' -mtime +"$KEEP_DAYS" -print -delete
else
  log "skipping rotation because at least one dump failed"
fi

exit "$failed"

Set up a backup account

According to the manual, mysqldump needs SELECT on the tables, SHOW VIEW for views, TRIGGER for triggers, PROCESS unless you pass --no-tablespaces, EVENT for --events and global SELECT for --routines. With GTIDs on, --single-transaction also needs RELOAD or FLUSH_TABLES.

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'CHANGE_ME';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, PROCESS, RELOAD ON *.* TO 'backup'@'localhost';

sudo install -m 600 -o root -g root /dev/null /etc/mysql-backup.cnf
sudo tee /etc/mysql-backup.cnf > /dev/null <<'EOF'
[client]
user=backup
password="CHANGE_ME"
EOF

Schedule it

sudo install -m 750 mysql-nightly-backup.sh /usr/local/sbin/

# /etc/cron.d/mysql-backup
30 2 * * * root KEEP_DAYS=14 /usr/local/sbin/mysql-nightly-backup.sh >> /var/log/mysql-backup.log 2>&1

How it behaves

• set -o pipefail makes a failing mysqldump fail the whole pipe, so a broken dump is never renamed to a final file.
• Dumps are written to *.partial first and renamed only after gzip -t passes.
• Rotation only runs when every dump succeeded, so a run of failures never deletes your last good backups.
• System schemas and the mysql schema are skipped. Keep a separate list of CREATE USER/GRANT statements (for example from SHOW GRANTS) to recreate accounts.
• --quick streams rows instead of buffering whole tables in memory, which the manual recommends with --single-transaction for large tables.

Only InnoDB tables are consistent with --single-transaction; MyISAM or MEMORY tables can still change during the dump. Avoid ALTER TABLE, DROP TABLE, RENAME TABLE and TRUNCATE TABLE while it runs: the manual warns they can make the dump wrong or fail.

Restore

Warning: each file contains CREATE DATABASE/USE and DROP TABLE IF EXISTS statements, so restoring it replaces the tables of that database. Restore into a test server first, or check the target with SHOW DATABASES before you run it.

gunzip -c /var/backups/mysql/shop-20260311-023000.sql.gz | mysql -u root -p

On a server with GTIDs enabled, each dump includes a SET @@GLOBAL.gtid_purged statement (the default --set-gtid-purged=AUTO). If you restore single databases into a server that already has its own GTID history, add --set-gtid-purged=OFF to the script. For recovery to a time after the dump, combine dumps with binary logs (see the point-in-time recovery article).

Official documentation

• MySQL 8.4: mysqldump
• MySQL 8.4: Using option files