- Instant help with your Sql coding problems

Backup MySQL database without locking tables

Question:
How to backup MySQL database without locking tables?
Answer:
mysqldump -u testuser -p testdb --single-transaction > /tmp/backup_without_lock.sql
Description:

If you want to backup a MySQL database in a way that tables are not locked during the dump process - for example, if you want to backup a live database -  use the --single-transaction option of the mysqldump utility.

The --single-transaction option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

Share "How to backup MySQL database without locking tables?"