MySQL Backup Strategies for Production Databases in 2026
MySQL Backup Methods Compared
MySQL offers several backup approaches, each with distinct trade-offs for production environments.
Logical Backups with mysqldump
mysqldump exports SQL statements that recreate your database. It is the most portable and widely used method.
mysqldump --single-transaction --routines --triggers --result-file=backup.sql mydb
Key flags for production use:
--single-transaction— consistent snapshot without locking InnoDB tables--routines— includes stored procedures and functions--triggers— includes triggers--set-gtid-purged=OFF— avoids GTID conflicts on restore
Pros: portable, human-readable, works across MySQL versions. Cons: slower for large databases, full restore required.
Physical Backups with Percona XtraBackup
For databases over 100 GB, physical backups copy data files directly. Percona XtraBackup performs hot backups without locking tables.
Pros: fast backup and restore, supports incremental. Cons: less portable, version-specific, more complex setup.
Compression and Encryption
Raw MySQL dumps compress exceptionally well with zstd:
| Database Size | Raw Dump | zstd Compressed | Ratio |
|---|---|---|---|
| 1 GB | 1.2 GB | 340 MB | 72% reduction |
| 10 GB | 12 GB | 3.4 GB | 72% reduction |
| 100 GB | 120 GB | 34 GB | 72% reduction |
After compression, encrypt with AES-256-GCM before uploading to cloud storage. Never upload unencrypted backups.
Automated Verification
The biggest gap in most MySQL backup strategies is verification. A backup file that cannot be restored is worthless.
BackupAgent automates this by spinning up a fresh MySQL container and restoring every backup:
- Pull
mysql:8Docker image - Start ephemeral container
- Create database and import dump
- Verify row counts match source
- Check schema integrity
- Destroy container
This runs automatically after every backup, so you always know your backups are recoverable.
Recommended Production Setup
For most production MySQL databases, this combination works well:
- Nightly full backups with
mysqldump --single-transaction - Compressed with zstd (70% size reduction typical)
- Encrypted with AES-256-GCM
- Uploaded to S3 with versioning enabled
- Verified by automated restore in Docker sandbox
- Monitored for size anomalies and schema drift
- Retention of 30 days minimum
BackupAgent handles all of this with a single YAML config file.