Run mysqldump without locking the tables

The default behaviour

By default, in order to ensure the data is in a consitent state during the dump, mysqldump will lock all the tables included in the dump.

This means that your databases will only be able to perform read queries.

Why all tables are locked?

This lock is required only for MyISAM tables, because the support for transactions is missing.

However, InnoDB, which is the default engine starting with MySQL 5.5.5 (released in 2010), has support for transactions.

Unless you are using MyISAM for a specific reason, InnoDB should be your default engine.

How to avoid tables locking?

Since InnoDB has support for transactions, we can use the flag --single-transaction to let mysqldump put everything in a transaction, read the database in the current state and create a consistent data dump.

After wrapping everything into a transaction, we need a second argument to disable the default tables lock applied by mysqldump: --skip-lock-tables.

The command to create a consistent dump without locking the tables is:

# Backup a single database without locking the tables
mysqldump --single-transaction --skip-lock-tables my_database > my_database.sql

# Backup multiple databases without locking the tables
mysqldump --single-transaction --skip-lock-tables --databases my_db1 my_db2 > my_database.sql

# Backup all databases without locking the tables
mysqldump --single-transaction --skip-lock-tables --all-databases > my_database.sql

This way of dumping a database without locking the tables works for both MySQL and MariaDB databases.

Info

If you have a mix of InnoDB and MyISAM tables, using the above command can leave your MyISAM tables in an inconsistent state, since it does not lock reads/writes of MyISAM tables.