Question: I have been using
mysqldump to take a backup of my website. Today the command failed to execute with an error message “Access denied while using LOCK TABLES“. However, I’m able to log in to the database using the same username and password, but
mysqldump command fails for some reason. Can you please help me to solve this issue? – Ravi
Here’s the complete error message:
mysqldump: error : Access denied for user 'ravi'@'localhost' to database 'wordpress' when using LOCK TABLES
How to fix Mysqldump error – access denied while using LOCK TABLES
If you carefully read the error message, you will get the solution. The error says “Access denied for user ‘ravi’@’localhost’ to database ‘wordpress’ when using
LOCK TABLES. It means, the database user ‘ravi’ does not have privilege to execute
LOCK TABLES statement. So all you need to do is GRANT
LOCK TABLES privilege to the database user.
Here’s how it’s done.
GRANT LOCK TABLES ON `database_name`.* TO 'database_user'@'localhost';
Alternatively, you can use
--single-transaction option as shown below:
mysqldump --single-transaction -u database_user -p database_user_password database_name > dump.sql
--single-transaction is used with
mysqldump command, the dump process ensures no other connection should use the statements such as
RENAME TABLE etc…The
--lock-tables options are separate and works differently, because
LOCK TABLES causes any pending transactions to be committed implicitly.
According to the MySQL documentation, it’s good idea to use
--single-transaction option with
--quick option while dumping large tables.