MySQLdump: Got error: 1044 happens when trying to dump the database with the user which has not enough privileges to access the selected database.

Just Recently, one of our customer reported this error to us which occurred while trying to make a backup using the mysqldump command.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related MySQL queries.

In this context, we shall look into how to resolve this MySQLdump error.

Nature of MySQLdump: Got error: 1044 ?

While trying to make a backup using the mysqldump command, we may come across:

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

We will get this error if we try to dump the database with a user which has not enough privileges to access the selected database.

Here, the user is missing the LOCK privilege.

mysqldump requires at least the;

• SELECT privilege for dumped tables.
• SHOW VIEW for dumped views.
• TRIGGER for dumped triggers.
• LOCK TABLES if the single-transaction option is not used.

We ensure to lock all tables to be dumped before dumping them.

In addition, we lock the tables with READ LOCAL to permit concurrent inserts in the case of MyISAM tables.

Since the –lock-tables lock tables for each database separately, this does not guarantee the tables in the dump file are logically consistent between databases. 

Tables in different databases may dump in completely different states.

How to fix MySQLdump: Got error: 1044 ?

Apply the tips given below to resolve this error.

1. Initially, we assign proper privileges to the user to access the database.

To do so, log in to MySQL and run:

$ mysql -u root -p

Here, we provide the MySQL root password. Then we grant all privileges on the database to our user:

GRANT SELECT, LOCK TABLES ON DBNAME.* TO ‘username’@’localhost’;

Similarly, to grant all the privileges:

GRANT ALL PRIVILEGES ON DBNAME.* TO ‘username’@’localhost’;

Next, to flush the privileges and exit from the MySQL we run:

FLUSH PRIVILEGES;
EXIT;

2. Then we run the same mysqldump command, add the –single-transaction flag.

For example,

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

3. Also, you can create a new user in your PHPMyAdmin - users_account with a valid username and password. Also, check all the privileges.

[Need help in fixing MySQL errors? We are available 24*7. ]