Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

All my databases are innodb. The databases are small enough to not need binary log files and incremental updates. So i'd like to do simple backup dumps.

What options do you guys recommend for mysqldump when using only innodb databases>

I looked around and didnt find any advice i trust. It seems i can get away with --all-databases --single-transaction

share|improve this question

Have you heard of XtraBackup? It's an online tool for backing up InnoDB databases live, without locking any tables or interrupting service.

It works remarkably well and has many many options. It's made by Percona, who are leaders in the MySQL consulting community.

It's probably worth a try.

share|improve this answer

If you have no problems to "locking" all databases for the time take by backup to complete, I guess the single-transaction option is all you need to have a consistent backup.

Take care of this (from Mysql manual): A consistent read is not isolated from those (ALTER, CREATE, ETC..) statements.

share|improve this answer
to make use of it I suppose you should add --skip-lock-tables (because --lock-tables included in --opt and it's on by default) – SaveTheRbtz Aug 16 '10 at 13:49

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.