Today I was generating mysql dumps over a few MYSQL databases using the common mysqldump command, and got the following error: mysqldump: Got error: 1044: Access denied for user ‘user’@’localhost’ to database ‘database_name’ when using LOCK TABLES
The command was a simple mysqldump:
mysqldump -u user -p DBNAME > backup.sql
What causes mysqldump: 1044 Access denied error ?
mysqldump is a tool used to generate full mysql backups of any database, that includes all your tables, user privileges, etc. And it requires special privileges, for example:
SELECT privilege is needed for dumped tables.
SHOW VIEW for dumped views.
TRIGGER for triggers
LOCK TABLES is needed if –singe-transaction option is not used on the mysqldump command.
Before generating the dump, mysql lock all tables with READ LOCAL, and then generates the dumps. In order to achieve this, the user who is executing the mysqldump command, must have LOCK privileges granted.
If you are running MYISAM, granting LOCK tables will be enough, while if you are running InnoDB tables, using –single-transaction as solution will be way better because it doesn’t need to lock the tables while dumping the database into a .sql file.
Let’s explore the solutions for this error using both things (passing –singe-transaction and granting LOCK privileges)
How do I fix mysqldump: 1044 Access denied when using LOCK TABLES error?
To fix mysqldump: 1044 Access denied error you can pass an option called ‘–single-transaction’ to mysqldump command. The full mysqldump would look like this:
mysqldump --single-transaction -u user -p DBNAME > backup.sql
The other way to fix it is to grant LOCK TABLES to the database user:
mysql -u root -p
Then run this query:
mysql> GRANT SELECT,LOCK TABLES ON DBNAME.* TO 'username'@'localhost';
Conclusion
As you see, either passing –single-transaction or granting LOCK privileges, you can easily fix this common mysql error and generate your .sql dump backups.
Further reading: