Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

When using mysqldump should I be using --single-transaction? should i always use -q?

What are best practices for dumping multiple MySQL databases? (--all-databases)

share|improve this question
What are the purpose of the dumps? Just for backup purposes? – Swish Jul 14 '10 at 0:07
yes. Just for backup. – acidzombie24 Jul 14 '10 at 0:23
How big are the databases? The strategies for a small database will be quite different to one containing terabytes of data. – John Gardeniers Jul 14 '10 at 3:14
up vote 2 down vote accepted

It is entirely going to depend on your requirements. Read the manpage, use the flags to create the type of dumps you need, and then test the restoration. Be careful with locking, as it will likely prevent your application(s) from working on varying levels.

There are numerous backup strategies for MySQL, which differ between engine types. The same methods to backup MyISAM are not always able to be applied to InnoDB. This topic is well covered on Serverfault, I suggest you search the site.

One of my preferred strategies:

  • Replication slave for backups and a crontab to create nightly tarballs.
  • Custom scripts that use mysqldump and mysqlhotcopy to dump small databases, the schemas, and the mysql database.

Xtrabackup is a useful tool. If you have replication, additional tools such as Maatkit will be extremely helpful.

share|improve this answer
As well as the man page there's the documentation on the MySQL site. The seem to know something about this stuff. :) – John Gardeniers Jul 14 '10 at 3:12

I would recommend reading through some of the posts on the MySQL Performance Blog (Author of the excellent book on the subject of MySQL Performance) relating to backups: (Xtrabackup is covered).

Beware that the Maatkit tools are great for many tasks including dumps but probably not a good backup tool. -ab

share|improve this answer
If you want to verify your replication slave and the resulting backup, Maatkit can be used as part of a backup strategy. – Warner Jul 14 '10 at 2:19

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.