Reliable InnoDB hot backups

A Tech article with View Comments posted 27 October 2006.
Tags: backup, ideaworks, mysql

There are number of blog posts out there that mention --lock-all-tables as a good option to backup MySQL tables with mysqldump – even InnoDB tables. Don’t do that! That option is for MyISAM tables that don’t do transactions. InnoDB has ooey-gooey transactional goodness that will take a consistant backup without locking all your tables and keeping your applications from running in the meantime. Just do your InnoDB hot backups like this:

mysqldump -u USERNAME -p --add-drop-table --add-locks --create-options --disable-keys --extended-insert --quick --set-charset --single-transaction --all-databases | gzip > OUTPUTFILE

Then viola! No nasty table locks!