When using mysqldump to backup databases, many people are unsure whether to use the –skip-lock-tables or –single-transaction options to prevent locking the tables. This article explains it.
–single-transaction specifies that – no modifications that occur to InnoDB tables during the dump will be included in the dump. Similar to the backup options in various RDBMSs, this means, the dump is actually a snapshot of the databases at the moment the dump started.
–skip-lock-tables is useful to avoid locking during the database backup. This option instructs the mysqldump tool to avoid issuing a LOCK TABLES command before generating the dump, which would have given every table a READ lock.
Conclusion: The –single-transaction flag instructs mysqldump to read the database in its current state, put everything in a transaction, and produce a consistent data dump. We require a second option to override the mysqldump default tables lock after wrapping everything up in a transaction: —skip-lock-tables.
Example:
mysqldump –single-transaction –skip-lock-tables MyDB > MyDB_Dump.sql
Hope you find this article helpful.
Big Data and SQL 