TopolearnBackups you can restore are non-negotiable, and a non-consistent dump is worse than useless.
mysqldump writes SQL statements that recreate schemas and data. The flags that matter for InnoDB:
--single-transactiondumps from one consistent snapshot without locking tables (InnoDB only). Without it you get either locked tables or an inconsistent backup.--routines --triggers --eventsinclude stored programs (triggers are included by default; routines and events are not).--source-data=2records the binary log position as a comment, so you can replay binlogs after restoring (point-in-time recovery) or start a replica. (It replaced the old--master-data.)--databases db1 db2or--all-databases.
Restore with mysql < dump.sql. It is single-threaded, so large restores are slow; mysqlpump was deprecated and removed in 8.4.
MySQL Shell (mysqlsh) has much faster parallel utilities: util.dumpInstance(), util.dumpSchemas() and util.loadDump(), with compression, chunking and progress, and they work well with managed services and object storage.
For large databases, physical backups are faster: MySQL Enterprise Backup, Percona XtraBackup, or the clone plugin for provisioning replicas. Combine any full backup with archived binary logs to recover to a point in time.
Always script a test restore and check row counts; dumps silently missing routines or users are common.
Example
# consistent logical dump of one database, with stored programs and binlog position
mysqldump -h 127.0.0.1 -u backup -p \
--single-transaction --routines --triggers --events \
--source-data=2 --databases shop | gzip > shop_$(date +%F).sql.gz
# restore into a scratch server and sanity-check
gunzip < shop_2026-05-03.sql.gz | mysql -h 127.0.0.1 -u root -p
mysql -h 127.0.0.1 -u root -p -e "SELECT COUNT(*) FROM shop.orders;"
# MySQL Shell: parallel, compressed dump and load
mysqlsh root@127.0.0.1 -- util dump-schemas shop --outputUrl=/backups/shop --threads=8
mysqlsh root@scratch-host -- util load-dump /backups/shop --threads=8Remember
- Always use --single-transaction for InnoDB dumps
- Add --routines and --events; --source-data records the binlog position
- MySQL Shell dump/load utilities are parallel and much faster
Watch out for
- Dumping without --single-transaction and getting tables from different points in time.