Ideal mysqldump options for backups

Question

I'm creating a backup script for our database servers, and thought I'd ask if there are any preferred options to include with mysqldump. The script will be called from cron every 6-24 hours.

We use InnoDB exclusively.

Our databases are rather large, and I'll most likely dump clusters of tables individually, as opposed to the entire database in one go.

For now, I'm thinking of including:

--opt # enabled by default
--quote-names # enabled by default
--single-transaction
--skip-comments

Any other suggestions or pointers for creating good, reliable dump files?

Answer 1

I propose you to just set up one more server (slave) with mysql and replicate data there (from the main, master one).

If you'll need a plain text dump - you can always do it from the slave without hurting master.