How to Run MySQLdump without Locking Tables

MySQLdump is the most popular tool to take backup of your MySQL databases. Even third-party MySQL tools run this command under the hood to backup your databases. But whenever you run MySQLdump, by default, it locks all the tables in the specified database, while it takes a backup of it. This can be problematic if you are running a mission critical database that needs to be available all the time, or high transaction database that needs to be updated frequently. As such, it can be a problem to dump your production databases too. In such cases, you can try to run MySQLdump without locking tables. In this article, we will learn how to run MySQLdump without locking tables.

How to Run MySQLdump without Locking Tables

Here is the default syntax of MySQLdump command.

mysqldump -u username -p password -d database_name > /path/to/file.sql

If you don’t want to lock tables you can use the –lock-tables=false option.

mysqldump -u username -p password -d database_name --lock-tables=false > /path/to/file.sql

If you are using InnoDB databases, you can also use –single-transaction option to prevent locking of tables.

mysqldump -u username -p password -d database_name --single-transaction=TRUE > /path/to/file.sql

In fact, for InnoDB databases using –single-transaction is a better option.

You can also use –quick option in above queries to speed up backup.

mysqldump -u username -p password -d database_name --lock-tables=false --quick > /path/to/file.sql

mysqldump -u username -p password -d database_name --single-transaction=TRUE --quick > /path/to/file.sql

If you are using older versions of MySQL or operating systems, or if the above commands simply don’t work for you, you can try using –skip-add-locks or –skip-lock-tables options, in case it is supported by your MySQL database.

mysqldump -u username -p password -d database_name --skip-add-locks > /path/to/file.sql

mysqldump -u username -p password -d database_name --skip-lock-tables > /path/to/file.sql

In this article, we have learnt several different ways to prevent locking of tables while running MySQLdump command.

Also read:

How to Check if MySQL database exists
How to Send Email from JavaScript
How to Check if String Starts With Another String
How to Check if Variable is Undefined in JavaScript
How to Detect Invalid Date in JavaScript