Automate MySQL Backups with Checked Failures and Private Credentials

Automatic MySQL backups need checked failures, private credentials and a tested restore path. A cron entry that creates a file every night is not proof of recovery. This Linux/Bash example backs up one local database at a time and does not delete older copies automatically.

Prepare the account and private credentials

Use an authorized account with MySQL client tools, Bash, gzip and flock installed. Confirm backup privileges and free storage. Prepare $HOME/.mysql-backup.cnf using the private MySQL backup setup and restrict that credential file to its owner. Do not put passwords in scripts, command arguments, cron entries or public web directories.

The snapshot options below are for InnoDB and a local database connection. Nontransactional tables, concurrent schema changes, remote TLS, MariaDB tool differences and database routines/events need an administrator-specific plan. A per-database export does not back up the website files, mail or database-user grants.

Create a failure-aware backup script

Create $HOME/bin/mysql-backup.sh privately with a text editor and the following content. It accepts one database name using letters, digits and underscores, typical of prefixed hosting databases. It rejects other names rather than treating them as command options.

#!/bin/bash
set -euo pipefail
umask 077
PATH=/usr/local/bin:/usr/bin:/bin
for tool in mysqldump gzip flock mktemp date; do
  command -v "$tool" >/dev/null || { printf 'Required tool missing: %s\n' "$tool" >&2; exit 1; }
done

db=${1:?Usage: mysql-backup.sh database_name}
[[ "$db" =~ ^[A-Za-z0-9_]+$ ]] || { printf 'Unsupported database name\n' >&2; exit 1; }
credentials="$HOME/.mysql-backup.cnf"
[[ -r "$credentials" ]] || { printf 'Private credential file missing\n' >&2; exit 1; }
backup_dir="$HOME/private-backups/mysql"
mkdir -p "$backup_dir"
chmod 700 "$backup_dir"

exec 9> "$backup_dir/.backup.lock"
flock -n 9 || { printf 'Cannot acquire backup lock: another run or lock error\n' >&2; exit 75; }
tmp=$(mktemp "$backup_dir/.pending.XXXXXX")
trap 'rm -f -- "$tmp"' EXIT

if mysqldump --defaults-extra-file="$credentials" \
  --single-transaction --quick --routines --events --triggers \
  --no-tablespaces "$db" | gzip -c > "$tmp"; then
  gzip -t "$tmp"
  final="$backup_dir/${db}-$(date -u +%Y%m%dT%H%M%SZ)-${tmp##*.}.sql.gz"
  [[ ! -e "$final" ]] || { printf 'Output path already exists\n' >&2; exit 1; }
  mv -- "$tmp" "$final"
  trap - EXIT
  printf 'Backup completed: %s\n' "$final"
else
  printf 'Backup pipeline failed; no completed archive published\n' >&2
  exit 1
fi

pipefail makes a failed dump fail the pipeline even when gzip finishes. The script publishes a completed filename only after success and compression validation. Its exit trap removes only its own temporary file. The lock prevents overlapping runs in this backup directory.

Test before adding a schedule

Replace account/database placeholders and run as the same account that cron will use. Verify a success, then verify that an intentional nonexistent test database returns a failure without producing a completed archive. Do not test by breaking a production credential or database.

/bin/bash "$HOME/bin/mysql-backup.sh" account_database

Restore a representative archive into an isolated database and inspect the required rows and objects. Compression integrity and a recent timestamp are not proof of a complete application recovery.

Schedule and monitor deliberately

After testing, use your authorized crontab or hosting scheduler. The following example runs at 02:00 in the scheduler’s configured timezone; replace both paths and the database name. Run the script once first so the private log directory exists.

0 2 * * * /bin/bash /home/account/bin/mysql-backup.sh account_database >> /home/account/private-backups/mysql/cron.log 2>&1

Monitor failures and archive freshness through a channel you actually receive. Redirecting output to a log does not itself send an alert. Cron environment, permissions and executable paths can differ from your interactive shell. Keep logs private, rotate them deliberately and do not discard all errors to /dev/null.

For multiple databases, invoke the script separately with each authorized name and verify every result. Separate runs do not produce a single cross-database consistency point; an application spanning databases needs a coordinated snapshot strategy.

Retention and off-server recovery

Copy verified archives through authenticated encrypted delivery to protected off-server storage. Define retention only after considering failure recovery and your actual obligations. Do not delete the last known-good backup because an untested new file exists. This example intentionally has no age-based deletion command.

Reference: MySQL backup options and consistency limits. Reviewed October 14, 2026. Local synthetic tests cover success, failed dumps, overlap protection and a disposable restore; no production cron, remote delivery or customer database was tested.