Auto Backup for MySQL Standard: Automated Scripts & Scheduling

Secure Auto Backup for MySQL Standard — Tools and Tips

Reliable, secure backups are essential for any MySQL deployment. This guide explains a practical, secure auto-backup strategy for MySQL Standard, lists tools you can use, and gives actionable tips to implement and maintain a robust backup system.

1. Backup objectives and strategy

• RPO (Recovery Point Objective): Aim for minimal data loss — e.g., 5–15 minutes for transactional apps.
• RTO (Recovery Time Objective): Determine acceptable restoration time (minutes to hours).
• Backup types: Use a combination of full backups (daily/weekly) and incremental/transactional (binary logs) to meet RPO/RTO.
• Security goals: Ensure backups are encrypted at rest and in transit, access-controlled, and integrity-verified.

2. Recommended tools

• mysqldump — built-in logical backup (good for small DBs, simple restoration).
• mariabackup / Percona XtraBackup — physical, hot backups for large DBs without blocking writes.
• mysqlpump — parallel logical dump with improved performance over mysqldump.
• pt-backup (Percona Toolkit) — helpers and orchestration utilities.
• Binlog management — use MySQL binary logs with mysqlbinlog for point-in-time recovery.
• Orchestration: cron/systemd timers, Ansible, or backup managers (Borg, Restic) to schedule and store snapshots.
• Cloud storage tools — rclone, AWS CLI, Azure CLI, Google Cloud SDK for offsite copy.

3. Practical automated backup architecture (example)

1. Full backup: Run physical full backup with Percona XtraBackup nightly.
2. Continuous capture: Keep MySQL binary logs enabled and ship them to backup server in near real-time.
3. Incrementals: If using logical dumps, run hourly mysqlpump dumps or periodic binlog-based incremental backups.
4. Store offsite: Copy backups to encrypted cloud storage and keep at least 3 retention points (e.g., last 7 days daily, 4 weekly, 6 monthly).
5. Catalog & metadata: Maintain a manifest (JSON/YAML) for each backup: timestamp, binlog position, checksum, encryption metadata.

4. Secure automation steps (script + schedule)

• Use a dedicated, least-privileged MySQL user for backups (GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS as needed).
• Example cron schedule (assume using XtraBackup):
  • 02:00 — Full backup (daily)
  • Every 15 minutes — Binary log shipper
  • 03:30 — Upload previous day’s full to offsite
• Encrypt backups before upload with GPG or AES (use strong keys stored in a secure key manager).
• Verify backups after creation with checksums and test restores regularly (at least monthly).

5. Sample backup script (conceptual, shell + xtrabackup)

bash
Copy CodeCopied
#!/bin/bash
BACKUP_DIR=/var/backups/mysql/\((</span><span class="token" style="color: rgb(57, 58, 52);">date</span><span class="token" style="color: rgb(54, 172, 170);"> +%F</span><span class="token" style="color: rgb(54, 172, 170);">)</span><span> </span><span></span><span class="token" style="color: rgb(57, 58, 52);">mkdir</span><span> -p </span><span class="token" style="color: rgb(163, 21, 21);">"</span><span class="token" style="color: rgb(54, 172, 170);">\)BACKUP_DIR“
xtrabackup –backup –target-dir=”\(BACKUP_DIR</span><span class="token" style="color: rgb(163, 21, 21);">"</span><span> --user</span><span class="token" style="color: rgb(57, 58, 52);">=</span><span>backupuser --password</span><span class="token" style="color: rgb(57, 58, 52);">=</span><span class="token" style="color: rgb(163, 21, 21);">'REDACTED'</span><span> </span><span>xtrabackup --prepare --target-dir</span><span class="token" style="color: rgb(57, 58, 52);">=</span><span class="token" style="color: rgb(163, 21, 21);">"</span><span class="token" style="color: rgb(54, 172, 170);">\)BACKUP_DIR“
tar -C ”\(BACKUP_DIR</span><span class="token" style="color: rgb(163, 21, 21);">"</span><span> -czf </span><span class="token" style="color: rgb(163, 21, 21);">"</span><span class="token" style="color: rgb(54, 172, 170);">\)BACKUP_DIR“.tar.gz .
gpg –batch –yes –recipient [email protected] –encrypt ”\(BACKUP_DIR</span><span class="token" style="color: rgb(163, 21, 21);">"</span><span>.tar.gz </span><span>rclone copy </span><span class="token" style="color: rgb(163, 21, 21);">"</span><span class="token" style="color: rgb(54, 172, 170);">\)BACKUP_DIR“.tar.gz.gpg remote:mysql-backups/\((</span><span class="token" style="color: rgb(57, 58, 52);">date</span><span class="token" style="color: rgb(54, 172, 170);"> +%F</span><span class="token" style="color: rgb(54, 172, 170);">)</span><span>/ </span><span>sha256sum </span><span class="token" style="color: rgb(163, 21, 21);">"</span><span class="token" style="color: rgb(54, 172, 170);">\)BACKUP_DIR”.tar.gz.gpg > “$BACKUP_DIR”.sha256 

• Replace inline passwords with secured retrieval from environment variables or a secret manager.

6. Permissions, keys, and secrets

• Store credentials in a secrets manager (HashiCorp Vault, AWS Secrets Manager).
• Use IAM roles or temporary cloud credentials for uploads.
• Rotate backup keys and credentials regularly and after personnel changes.

7. Encryption and integrity

• In transit: Use TLS for MySQL connections and HTTPS/SFTP for uploads.
• At rest: Encrypt backup files with GPG or repository-level encryption (e.g., server-side encryption with customer-managed keys).
• Integrity: Generate and store checksums (SHA-256) and optionally sign backups with GPG.

8. Retention, pruning, and compliance

• Define retention policy mapping to business needs and compliance (e.g., PCI, GDPR).
• Automate pruning: delete expired backups and their metadata; keep catalog consistent.
• Maintain an audit log of backup actions (who ran, when, result).

9. Testing and restore procedure

• Document and script restores for common scenarios:
  • Point-in-time recovery using base backup + binlogs.
  • Full database restore from physical backup.
• Regularly perform test restores in an isolated environment and measure RTO/RPO.

10. Monitoring and alerts

• Monitor backup job success/failure, storage usage, encryption status, and age of latest backup.
• Send alerts for failures, missed schedules, or integrity verification failures (PagerDuty/Slack/Email).

11. Troubleshooting checklist

• If backup fails: check MySQL permissions, disk space, network, and error logs.
• If restore fails: verify backup integrity, correct binlog range, and compatible MySQL versions.

12. Quick checklist (copyable)

• Enable binary logging.
• Create least-privileged backup user.
• Schedule full + incremental strategy.
• Encrypt and checksum backups.
• Store offsite with retention rules.
• Automate tests of restore regularly.
• Monitor and alert on backup health.

If you want, I can generate a ready-to-run script tailored to your MySQL version and storage target (local, S3, GCS, etc.).