adminMySQL Backup Automation Cron Checklist
Manual database backups are easy to forget. A system admin needs automated backups with logs, retention, offsite copy, restore testing and alerts when the job fails.
Core principle
Backup automation is a production workflow. It needs schedule, credentials, storage, verification, retention and monitoring.
Checklist
- Choose databases to back up.
- Create a backup script with safe permissions.
- Use a limited database backup user if possible.
- Compress backup files.
- Name backup files with date and database name.
- Redirect output and errors to logs.
- Schedule backup with cron.
- Apply local retention policy.
- Copy backup offsite if possible.
- Test restore regularly.
Reusable lesson
This applies to WordPress, n8n, analytics databases, app databases, staging refreshes and disaster recovery planning.
When to Use This Checklist
Use this checklist when automating MySQL or MariaDB backups for websites, apps or automation systems.
Required Tools
SSH access, mysqldump, cron, backup folder, offsite storage, restore test database, log file path
Before You Start
Do not consider backup automation complete until restore has been tested from an automated backup file.
Structured Checklist Steps
- Select databases.
- Create backup script.
- Use backup user.
- Compress backup.
- Name files consistently.
- Log output.
- Schedule cron.
- Apply retention.
- Copy offsite.
- Test restore.
Verification Steps
- Cron creates backup file.
- Backup log is readable.
- Old backups are cleaned safely.
- Offsite copy works if configured.
- Restore test succeeds.
Rollback Plan
If automated backup fails, keep the last good backup, disable risky cleanup, fix the script and run a manual backup before re-enabling cron.
Common Mistakes
- No restore test.
- No error log.
- Backup stored only on production disk.
- No retention policy.
- Plaintext credentials exposed with weak permissions.
Related Commands
mkdir -p /backups/mysql
mysqldump -u db_user -p database_name | gzip > /backups/mysql/database_name-$(date +%F).sql.gz
crontab -e
ls -lh /backups/mysql
find /backups/mysql -type f -mtime +14 -delete
gunzip -c backup.sql.gz | mysql -u db_user -p restore_test