Non-blocking and quick database dumps for large databases

gistfile1.md

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:

• http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_compact
• http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction
• http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_quick