Skip to content

Instantly share code, notes, and snippets.

@avoelkl
Last active June 5, 2024 12:41
Show Gist options
  • Save avoelkl/49563c516d6cb318eb34 to your computer and use it in GitHub Desktop.
Save avoelkl/49563c516d6cb318eb34 to your computer and use it in GitHub Desktop.
Non-blocking and quick database dumps for large databases

How-to

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

--single-transaction

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.

--quick

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

Before

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

  • Dump time: ~6 Min.
  • Website blocked

After

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

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

Links:

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