Stuck with the error, MySQLdump: Got error: 1044? We can help you.

Recently, we had a customer who came across this error while trying to make a backup using the mysqldump command.

As part of our Server Management Services, we assist our customers with several MySQL queries.

Today, let us see how to solve this error.

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, and
• 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.

Solution

Moving ahead, let us see an effective method our Support Techs employ to fix this error for our customers.

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

[Need help with the error? We are available 24*7]

Conclusion

In short, how our Support Techs resolve the mysqldump error.