Automated MySQL backups: rotation, checks and restore

Schedule consistent MySQL dumps, protect copies and test a real restore.

What a dump covers

mysqldump --single-transaction --quick suits transactional InnoDB tables; it does not ensure one consistent snapshot for nontransactional tables or concurrent DDL. Also back up configuration, secrets and uploaded application files. Consider physical backups or MySQL Shell for larger databases.

Reliable sequence

Use a dedicated least-privilege account and a credential file readable only by the backup process. In Bash set umask 077 and set -euo pipefail, create a temporary file in the target directory, pipe mysqldump --defaults-extra-file=/path/credentials.cnf --single-transaction --quick --routines --events --triggers --databases app_db through gzip -c, run gzip -t, then rename only on success. The client option file must be its first option. Clean up temporary files on failure.

Retention and alerts

Run under cron or a systemd timer and record status, duration and size. Delete only files matching the intended backup pattern beyond your retention window. Keep encrypted off-host copies with separate access. Alert on dump, compression or upload failures; a silent job is not a check.

Restore drill

gzip -t checks the compressed container, not SQL correctness or completeness. Restore regularly into an isolated database, inspect tables and application behavior and measure recovery time. Define acceptable data loss; point-in-time recovery requires a suitable binary-log plan and tested procedure.

Source

https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html