mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES

Question

mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES

I am able to loging properly and able to take full backup from SQLyog Tool but when i am trying to take backup from MySQL Enterprise Workbencg then getting error message.

mysqldump: Got error: 1044: Access denied for user 'myuserid'@'%' to database 'mydatabasename' when doing LOCK TABLES

Operation failed with exitcode 2

And MySQL Connection status is ok.

mysql> status;

mysql.exe Ver 14.14 Distrib 5.6.19, for Win32 (x86)

Connection id: 23921 Current database: databasename Current user: user id is ok here SSL: Not in use Using delimiter: ; Server version: 5.6.17-log MySQL Community Server (GPL) Protocol version: 10 Connection: connection is ok here Server characterset: utf8 Db characterset: utf8 Client characterset: cp850 Conn. characterset: cp850 TCP port: 3306 Uptime: 21 days 21 hours 11 min 37 sec

Threads: 24 Questions: 20500671 Slow queries: 3212 Opens: 121998 Flush tables: 1 Open tables: 2000 Queries per second avg: 10.842

Answer 1

I found two possible solutions, either:

1. your user is missing the LOCK privilege, so you should ask your database administrator to grant it to you
2. run the same mysqldump command, simply adding the --single-transaction flag, eg. mysqldump --single-transaction -u user -p ...

Answer 2

A quick workaround is to pass the –-single-transaction option to mysqldump:

$ mysqldump --single-transaction -u user -p DBNAME > backup.sql

Just for sharing, the below article have a good information about the same issue.

mysqldump: 1044 Access denied when using LOCK TABLES

Answer 3

IF yours password contains special characters then; the error

mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: YES)" when trying to connect might occur.

---

Solution is simple; instead of giving the password in quotes at the time of issuing the command (for example mysql or mysqldump) just use -p. and it would ask to enter the password in terminal.

mysqldump -h localhost -u root --password='password' --add-drop-database --add-drop-table --add-drop-trigger --dump-date --single-transaction --routines --events db > /path/db_name.sql

FYI: I know this is for errors 1045 (and not 1044 using locking tables), but might help.