Here is the standard mysqldump syntax I always use. This dumps routines and triggers, does so in the most expeditious manner possible, and adds DROP xxxxx statements before all CREATE statements, just in case. It also adds the FORCE parameter in case it runs into issues with views. The “xxxx” at the end can be either one schema or a space-delimited list of schemata.
mysqldump --add-drop-database --add-drop-table --triggers --routines --single-transaction --opt --quick --dump-date --force --databases xxxx > dumpfile.sql
Production dump timing:
[mysqladm@dbvrp83383:/mysbackupdd] time mysqldump --add-drop-database --add-drop-table --triggers --routines --single-transaction --opt --quick --dump-date --force --databases sms > sms.sql real 5m47.223s user 3m23.495s sys 0m19.139s
In dumping from a 8.x server via a lower version mysqldump, add…
--column-statistics=0
…to avoid those odd stats errors.