How do I automate MySQL backups with mysqldump, rotation and offsite copies?

1. Credentials file (keeps the password out of ps output and shell history). Create ~/.my.cnf for the backup user and chmod 600 it:

[client]
user=backup
password=YOUR_PASSWORD

Create a limited MySQL user: CREATE USER 'backup'@'localhost' IDENTIFIED BY '...'; GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES ON *.* TO 'backup'@'localhost'; (on MySQL 8 either grant PROCESS or keep --no-tablespaces as above; add RELOAD only if your dump options need it).

2. Script /usr/local/bin/db-backup.sh:

#!/usr/bin/env bash
set -euo pipefail
DIR=/var/backups/mysql
mkdir -p "$DIR"
STAMP=$(date +%F_%H%M)

mysqldump --single-transaction --no-tablespaces --routines --triggers --events --databases app \
  | gzip -9 > "$DIR/app_$STAMP.sql.gz"

find "$DIR" -name 'app_*.sql.gz' -mtime +14 -delete
rclone copy "$DIR/app_$STAMP.sql.gz" remote:db-backups/   # offsite, optional

--single-transaction gives a consistent snapshot for InnoDB without locking tables. MyISAM tables would need locking.

3. Schedule

30 2 * * * /usr/local/bin/db-backup.sh >> /var/log/db-backup.log 2>&1

4. Test the restore (a backup you never restored is a hope, not a backup):

gunzip -c app_2026-11-09_0230.sql.gz | mysql -u root -p

Pitfalls

• Store backups on a different disk or provider than the database, otherwise one failure takes both.
• Encrypt before uploading anywhere you do not fully control (age or gpg).
• For large databases consider parallel tools such as mydumper, Percona XtraBackup (or MySQL Enterprise Backup), or replica-based dumps, and point-in-time recovery with binary logs.
• Monitor: alert if the newest file is older than 26 hours.
• Cron environment: use absolute paths (see the cron troubleshooting topic).