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

By default the inserts are dumped.

If you want all tables to be dumped, just precise database, else give table names.

mysqldump [options] [db_name [tbl_name ...]]

To earn space you can use the flag

--compact

And to process this quicker you can use

--quick

I presume that all this information is available doing man mysqldump.

Answer 2

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

Answer 3

You should definitively read the manual pages for mysqldump, there are many options that you should take care of (what to do with binary logs, locking of tables, format of binary fields, etc)

After that run mysqldump --help - it will tell you which options are on or off by default.

Finally, if setting various options does not bring you the required performance some people recommend setting up replication and backing up the slave, but this falls under category of hardware speed-up (as does getting faster faster storage, etc...)