Kevin Warrington

Loading clever subtitle...

Non-Blocking MySQL Database Export for InnoDB Tables

To quickly dump a large InnoDB database to file without locking it up:

mysqldump --single-transaction --quick -u webuser -h example.com 'dbname' > dbname.sql

This will issue a START TRANSACTION and as long as the following commands are not issued before your export completes, you will have a perfect snapshot:

ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE

MyISAM or MEMORY tables dumped while using this option may still change state.

Comments