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
When --single-transaction is used with mysqldump command, the dump process ensures no other connection should use the statements such as ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE etc…The --single-transaction and --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.
