Introduction
Dumping and restoring every MySQL database in one pass is useful for backups, migrations, and environment cloning, but the command itself is only the easy part. A reliable full export also needs object coverage, consistency settings, compression, and a real restore test. Otherwise you do not have a backup process, only a large SQL file.
Export All Databases With the Right Flags
The normal starting point is mysqldump --all-databases, but a serious full export usually needs more than the default table data and schema.
1mysqldump -u backup_user -p \
2 --all-databases \
3 --routines \
4 --triggers \
5 --events \
6 --single-transaction \
7 --set-gtid-purged=OFF \
8 > all_databases.sql
These flags matter:
'--routines, --triggers, and --events preserve database behavior, not just rows.'
'--single-transaction gives a consistent snapshot for InnoDB-heavy environments with minimal locking.'
'--set-gtid-purged=OFF avoids surprises in environments where GTID handling needs to stay controlled.'
If your server still contains non-transactional tables, document that limitation because --single-transaction does not solve every consistency problem.
Full dumps get large quickly, so it often makes sense to compress them as they are written.
1mysqldump -u backup_user -p \
2 --all-databases \
3 --routines \
4 --triggers \
5 --events \
6 --single-transaction \
7| gzip > all_databases.sql.gz ``` A usable backup also needs metadata. At minimum, keep a checksum and the server version next to the dump: ```bash sha256sum all_databases.sql.gz > all_databases.sql.gz.sha256 mysql --version > all_databases.version.txt ``` That metadata becomes valuable later when you need to verify file integrity or understand which MySQL version produced the export. ## Import the Dump Carefully Restoring is technically simple: ```bash mysql -u root -p < all_databases.sql ``` For compressed files: ```bash gunzip -c all_databases.sql.gz | mysql -u root -p ``` But do not treat the import command as the whole recovery plan. In most teams, the right place for the first restore is an isolated environment where you can check compatibility and timing without risking a production system. ## Validate More Than Command Success A completed import command only proves that the client finished processing input. It does not prove that the restored environment is complete or healthy. At minimum, verify: - the database list - representative table counts - triggers, routines, or scheduled events ```bash mysql -u root -p -e "SHOW DATABASES;" mysql -u root -p -e "SELECT COUNT(*) FROM app_db.orders;" mysql -u root -p -e "SHOW TRIGGERS FROM app_db;" ``` For important systems, go further and run a small application smoke test. A backup is only good if the restored environment behaves the way the application expects. ## Think About Recovery Time, Not Just Backup Time Teams often focus on how quickly the dump can be created and ignore how long restore actually takes. Large logical restores can be slow, especially when the target hardware is weaker than production or when index rebuilding dominates the import. That means a mature backup process answers three questions: 1. How long does export take? 2. How long does restore take? 3. What compatibility assumptions does the restore depend on? Those answers are more useful than the existence of a dump file in object storage. ## Keep the Process Rehearsed A dump command in a wiki is not the same thing as a backup capability. Run restore drills. Time them. Note any privilege issues, missing objects, version mismatches, or application-level surprises. Then fix the runbook while the failure is cheap. The operational value of a full export is not just that it can be created. It is that your team knows how to bring it back. ## Common Pitfalls The most common mistake is forgetting routines, triggers, or events and discovering missing behavior only after the restore. Another is assuming a dump file is valid without ever restoring it. Teams also underestimate restore time, ignore version mismatches, or store large dumps without checksums and discover corruption only when recovery is urgent. If a full restore has never been rehearsed, the real operational cost is still unknown. ## Summary - Use `mysqldump --all-databases` with explicit object-preservation flags. - Compress large exports and store checksums plus version metadata. - Restore into an isolated environment before trusting the process in production. - Validate data and database objects after import, not just command success. - Measure restore time so backup practice matches recovery expectations.
