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
Related posts:
Sreeram has more than 10 years of experience in web development, Python, Linux, SQL and database programming.