mysqldump without table locks (MyISAM and InnoDB)

Profile image of Mattias Geniar

Mattias Geniar, July 17, 2017

Follow me on Twitter as @mattiasgeniar

It’s one of those things I always have to Google again and again, so documenting for my own sanity.

Here’s how to run mysqldump without any locks, which works on both MyISAM and InnoDB data engines. There are no READ nor WRITE locks, which means the dump will have little to no influence on the machine (except for CPU & disk I/O for taking the back-up), but your data will also not be consistent (!!!).

This is useful for testing migrations though, where you might not need locks, but are more interested in timing back-ups or spotting other timeout-related bugs.

$ mysqldump --compress --quick --triggers --routines --lock-tables=false --single-transaction {YOUR_DATABASE_NAME}

The key parameters are --lock-tables=false (for MyISAM) and --single-transaction (for InnoDB).

If you need more options or flexibility, xtrabackup is a tool to check out.

Want to subscribe to the cron.weekly newsletter?

I write a weekly-ish newsletter on Linux, open source & webdevelopment called cron.weekly.

It features the latest news, guides & tutorials and new open source projects. You can sign up via email below.

No spam. Just some good, practical Linux & open source content.