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

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.