Mysqldump Syntax

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: