MySQL – Single Transaction or Skip Lock Tables

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.

mysqldump –single-transaction –skip-lock-tables MyDB > MyDB_Dump.sql

Hope you find this article helpful.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s