A scheduled database dump creates a repeatable backup without running the command manually each day. Retention rules can remove older copies, but only after a new backup has completed and your recovery requirements are understood.

Prepare private storage and credentials

Use the account that will own the cron job and a dedicated backup directory outside public websites. The following example uses /home/username/private-backups/mysql; replace the username and database name throughout.

Create the directory with owner-only access. Put credentials for an appropriately privileged backup database user in a separate file, /home/username/.mysql-backup.cnf:

[client]
user=backup_user
password=REPLACE_WITH_THE_ACTUAL_SECRET

Set that file to mode 600 and the private directories to 700. Keep credentials out of the script and command line. Confirm the actual paths to mysqldump, gzip, flock and other tools before scheduling.

The --single-transaction method below is appropriate for transactional tables such as InnoDB. Non-transactional tables and concurrent schema changes need a different consistency plan. Check required privileges for routines, triggers and events.

Create the backup script

Save this as /home/username/scripts/mysql-backup.sh, adjusting every example value:

#!/bin/bash
set -euo pipefail
umask 077
PATH=/usr/bin:/bin
backup_dir=/home/username/private-backups/mysql
credentials=/home/username/.mysql-backup.cnf
database=example_database

test -d "$backup_dir"
test -r "$credentials"
exec 9>"$backup_dir/.backup.lock"
flock -n 9 || exit 0

stamp=$(date +%Y-%m-%d_%H-%M-%S)
archive="$backup_dir/$database-$stamp.sql.gz"
partial=$(mktemp "$backup_dir/.dump.XXXXXX")
trap 'rm -f -- "$partial"' EXIT

/usr/bin/mysqldump --defaults-extra-file="$credentials" \
    --single-transaction --quick --routines --triggers --events \
    "$database" | gzip > "$partial"
gzip -t "$partial"
test -s "$partial"
mv -- "$partial" "$archive"
trap - EXIT

A failed dump or compression exits before publishing the archive. Test the script and restore a sample to a separate database before scheduling it.

Schedule and monitor

Edit this account’s crontab with crontab -e and add:

0 0 * * * /bin/bash /home/username/scripts/mysql-backup.sh >> /home/username/private-backups/mysql/backup.log 2>&1

This runs at midnight in the scheduler’s time zone. Check logs, archive dates and failure alerts; redirecting output to a log does not notify you automatically.

Preview retention before enabling deletion

After deciding the retention period and keeping independent recovery copies, preview matching files:

find /home/username/private-backups/mysql -maxdepth 1 -type f -name 'example_database-*.sql.gz' -mtime +10 -print

With GNU find, -mtime +10 selects files older than ten complete 24-hour periods, effectively at least eleven days old. Review the actual matches.

If those are the intended deletions, add the same restricted command with -delete in place of -print at the end of the successful backup script. Do not schedule an independent cleanup that keeps deleting while backups fail.

Compression integrity is not a database restore test. Keep off-server copies and periodically test recovery, including objects and data the application needs.