Automate Backups with an SQL Dumper — Step-by-Step TutorialKeeping database backups reliable and automated is essential for disaster recovery, compliance, and peace of mind. This tutorial walks through the steps to automate backups using an SQL dumper. It covers choosing a dumper, creating consistent dumps, scheduling, secure storage, verification, and restoration testing. Examples use MySQL/MariaDB and PostgreSQL, but the principles apply to other relational databases and dump tools.
Why automate SQL dumps?
- Reduces human error: automated routines run on schedule without manual intervention.
- Ensures regular recovery points: you always have recent backups for rollback.
- Enables consistent retention policies: retain daily, weekly, and monthly backups automatically.
- Supports compliance and auditing: predictable backups help meet regulatory requirements.
Choose the right dumper tool
Common options:
- mysqldump (MySQL/MariaDB) — reliable, widely available.
- mysqlpump (MySQL 5.7+) — parallelism and performance features.
- pg_dump (PostgreSQL) — flexible formats (plain SQL, custom, directory).
- pg_dumpall (PostgreSQL) — for cluster-wide metadata (roles, globals).
- Percona XtraBackup — non-blocking physical backups for MySQL.
- Third-party/hosted agents (Barman, WAL-G, AutoMySQLBackup, Bacula, commercial services).
Choose based on:
- database size and activity,
- need for point-in-time recovery (logical vs physical backups),
- available storage and network bandwidth,
- lock behaviour and impact on production.
Design your backup strategy
Decide:
- Frequency: hourly, daily, weekly depending on data change rate.
- Retention: e.g., keep daily backups for 14 days, weekly for 12 weeks, monthly for 12 months.
- Consistency: full dumps vs. incremental or WAL-based continuous archives.
- Encryption and access controls.
- Offsite storage and redundancy.
Example strategy:
- Daily logical dump at 02:00 (keep 14 days).
- Weekly full dumps on Sunday (keep 12 weeks).
- Archive WAL/transaction logs for point-in-time recovery (retain 7 days).
Secure environment and credentials
- Create a dedicated backup user with minimal required privileges (SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT, RELOAD if needed for flushing).
- Store credentials securely — avoid plaintext in scripts. Use:
- MySQL option file (~/.my.cnf) with 600 permissions, or
- environment variables set by a protected service, or
- secrets manager (AWS Secrets Manager, HashiCorp Vault).
- Limit network access for backup storage endpoints and use TLS for transfers.
Example: Automate MySQL/MariaDB logical dumps
Script using mysqldump with compression, encryption, rotation, and remote upload (scp/SFTP). Save as /usr/local/bin/mysql_backup.sh and make executable.
#!/usr/bin/env bash set -euo pipefail # Config HOST="localhost" PORT="3306" USER="backup_user" # Use .my.cnf for password or uncomment and use environment variable retrieval # PASS="${MYSQL_BACKUP_PASS:-}" DATABASES=("db1" "db2") # empty or omit means all databases BACKUP_DIR="/var/backups/mysql" DATE="$(date +%F-%H%M)" RETENTION_DAYS=14 REMOTE_USER="backupuser" REMOTE_HOST="backup.example.com" REMOTE_DIR="/remote/backups/mysql" mkdir -p "$BACKUP_DIR" TMP="$BACKUP_DIR/tmp-$DATE" mkdir -p "$TMP" # Dump each DB separately to allow parallelism and partial restores for DB in "${DATABASES[@]}"; do OUT="$TMP/${DB}-${DATE}.sql.gz" mysqldump --single-transaction --quick --routines --events --triggers -h "$HOST" -P "$PORT" -u "$USER" "${DB}" | gzip > "$OUT" done # Combine metadata (optional) # mysqldump --no-data --databases "${DATABASES[@]}" > "$TMP/schema-${DATE}.sql" # Encrypt (optional, requires GPG setup) # for f in "$TMP"/*.gz; do gpg --batch --yes -e -r [email protected] "$f"; done # Move to permanent dir mv "$TMP"/* "$BACKUP_DIR" rmdir "$TMP" # Upload to remote rsync -az --remove-source-files "$BACKUP_DIR/" "${REMOTE_USER}@${REMOTE_HOST}:$REMOTE_DIR/" # Prune local and remote old files find "$BACKUP_DIR" -type f -mtime +"$RETENTION_DAYS" -delete ssh "${REMOTE_USER}@${REMOTE_HOST}" "find '$REMOTE_DIR' -type f -mtime +${RETENTION_DAYS} -delete" Notes:
- –single-transaction avoids long table locks for InnoDB (not safe for MyISAM).
- –quick and piping to gzip reduce memory usage and disk I/O.
- Consider parallel dumps (multiple mysqldump processes) for many databases.
Example: Automate PostgreSQL dumps with pg_dump
Script /usr/local/bin/pg_backup.sh:
#!/usr/bin/env bash set -euo pipefail PGHOST="localhost" PGPORT="5432" PGUSER="backup_user" # ensure .pgpass or env var for password DATABASE="mydb" BACKUP_DIR="/var/backups/postgres" DATE="$(date +%F-%H%M)" RETENTION_DAYS=14 REMOTE_USER="backupuser" REMOTE_HOST="backup.example.com" REMOTE_DIR="/remote/backups/postgres" mkdir -p "$BACKUP_DIR" # Use custom format for compressed, parallel dumps pg_dump -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -F c -b -v -f "$BACKUP_DIR/${DATABASE}-${DATE}.dump" "$DATABASE" # Upload rsync -az --remove-source-files "$BACKUP_DIR/" "${REMOTE_USER}@${REMOTE_HOST}:$REMOTE_DIR/" # Prune find "$BACKUP_DIR" -type f -mtime +"$RETENTION_DAYS" -delete ssh "${REMOTE_USER}@${REMOTE_HOST}" "find '$REMOTE_DIR' -type f -mtime +${RETENTION_DAYS} -delete" For point-in-time recovery, enable WAL archiving and use tools like WAL-G or pgBackRest.
Scheduling: cron, systemd timers, and orchestration
- Small setups: use cron. Example cron entry for daily backup at 02:00: 0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1
- Prefer systemd timers for better logging and reliability (Restart, CPU/IO limits).
- In containerized/orchestrated environments use Kubernetes CronJob or CI/CD pipelines.
Secure transfer and offsite storage
- Use rsync over SSH, SFTP, or object storage (AWS S3, Google Cloud Storage) with server-side encryption.
- For S3, use aws cli with SSE and lifecycle rules. Example: aws s3 cp /path/to/backup s3://mybucket/mysql/ –storage-class STANDARD_IA –sse AES256
- Keep at least one offsite copy; follow 3-2-1 rule: 3 copies, 2 different media, 1 offsite.
Verify backups automatically
- Check exit codes and log output in scripts. Send alerts on failure (email, PagerDuty, Slack).
- Perform integrity checks:
- For logical dumps: run gzip -t on compressed files; attempt a dry-restore to a test server periodically.
- For PostgreSQL custom dumps: use pg_restore –list or –schema-only to validate.
- For physical backups: use the backup tool’s verify commands (xbstream, xtrabackup –prepare).
Example verification snippet:
# Check gzip integrity for f in /var/backups/mysql/*.gz; do gzip -t "$f" || { echo "Corrupt: $f"; exit 1; }; done Test restores regularly
A backup is only as good as your ability to restore it. Schedule quarterly restore drills:
- Restore to an isolated test server.
- Verify application-level operations and data integrity.
- Time the full restore and optimize where necessary.
Monitoring and alerting
- Monitor backup job duration, success/failure, size trend, and storage utilization.
- Integrate with Prometheus/Grafana, Datadog, or simple log-based alerts.
- Alert on missed schedules, errors, and verification failures.
Common pitfalls and mitigations
- Locking large tables during dumps — use –single-transaction (MySQL) or snapshot isolation (Postgres).
- Running out of disk or network bottlenecks — stream dumps directly to remote object storage or use compression.
- Exposing credentials — use secret managers or protected option files.
- Not testing restores — schedule routine restore tests.
Example checklist before automating
- [ ] Choose dump tool and format.
- [ ] Create least-privilege backup user.
- [ ] Implement secure credential storage.
- [ ] Script dump, compression, encryption, upload, rotation.
- [ ] Schedule via cron/systemd/K8s.
- [ ] Implement verification and alerts.
- [ ] Test full restores on schedule.
Automating SQL dumps reduces risk and gives confidence that you can recover from failures. Start with a simple scheduled script, add secure storage and verification, and evolve toward incremental/WAL strategies as your needs grow.
Leave a Reply