how to backup all DB info using mysqldump?

Question

I want not only the "Create table" statements, but also the Inserts. What flags should i pass to mysqldump?

In addition - the database is relatively large (2-2.5G). Obviously, i want the backup to be as fast as possible. Any flags that might help?

Thanks

Answer 1

The mysqldump command dumps data by default. Just issuing mysqldump dbname > dump will produce a dump with the schema and data.

To speed up the dump, use --opt parameter which turns on some options like table locking, quick, extended inserts and charset. Using --quick (which is set by --opt too) is a good way to speed up the dump if your database does not fit into memory. Use --quick alone if you can't afford to lock tables during the dump.

If your disks are slow, compress the data before writing it to disk:

mysqldump --opt dbname | gzip - > dump