29

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

3 Answers 3

55

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 ...
0
4

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

0
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.