Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Non-blocking and quick database dumps for large databases


Add --single-transaction and --quick to your mysqldump command.


sets the isolation mode to REPEATABLE READ and starts a transaction before dumping data. useful for InnoDB tables, dumps the consistent state without blocking any applications.


retrieves ta row at a time from the server instead of the entire table, buffering it and writing it out.

From the mysldump doc:

To dump large tables, combine the --single-transaction option with the --quick option.

Statements and Performance


mysqldump -h'hostname' -u'username' -p'password' 'livedbname'

  • Dump time: ~6 Min.
  • Website blocked


mysqldump -h'hostname' -u'username' -p'password' --single-transaction --quick 'livedbname'

  • Dump time: 3 Min.
  • Website not blocked!


Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment