Human errors can occur at any time, especially when we are in a hurry to do something.

But, sometimes it can lead to problems. One such error is mysqldump error 1045.

At Bobcares, we help server owners resolve MySQL errors as part of out Server Management Services.

Today, let’s discuss the top 4 reasons for this error and how we fix them.

mysqldump error 1045 – What’s this?

Before we move on to the reasons for this error, let’s first get an idea of this error.

Server owners often face this error when they try to use mysqldump utility for taking backup of the databases or transfer it to another server.

For instance, users see the error message like this:

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

This means that MySQL server disallows the user to connect it from localhost.

mysqldump error 1045 – Causes & Fixes

Now, let’s see the main reasons for this error and how our Dedicated Engineers fix them.

1) Wrong mysqldump syntax

One of the common mistakes that we see is that customers use wrong format of mysqldump command. For example, this is the general syntax for taking database backup using mysqldump.

mysqldump -u username -p'password' database > dump.sql

However, we’ve seen instances where customers put extra space after -p switch and give the password. But, MySQL considers this syntax as invalid and throws this error.

How we fix?

Here, our Support Engineers check the syntax of the mysqldump command and correct the errors.

We often recommend customers not to paste the MySQL password as plain text in their commands. Instead, we suggest them to use -p switch alone, and then give the password when it prompts for.

mysqldump -u username -p

In addition to that, some database servers won’t accept passwords with special characters such as $, &, etc. So, we reset the password and update it to the customer.

In some cases, we need to explicitly specify the hostname to which the user is connecting using the -h switch.

mysqldump -h hostname.com -u username -p'password' database > dump.sql

2) Wrong user credentials

Likewise, customers may use wrong username and password while connecting to the database. Therefore, MySQL can’t verify the authenticity of the account and throws this error.

Similarly, some customers create custom file .my.cnf and add their MySQL username and password in this file. So, they can easily use the mysql, mysqldump commands without giving the password. However, wrong information in this file can break the connection and lead to this error.

How we fix?

In such cases, our Database Experts first check whether correct username and password is used by the customer. If not, we reset the password of the database user. In addition to that, for database driven websites like WordPress, Drupal, etc.  we update the new password in the website configuration files.

Moreover, if customer uses any custom .my.cnf file for the user, we update the new password there also.

3) Insufficient privileges to the database user

Another common reason for this error is that the user trying to connect to the database doesn’t have sufficient privileges. And, they receive the error as given below.

mysqldump: Got error: 1045; "Access denied for user 'test-user'@'localhost' (using password: YES)" when trying to connect

This error means that the database user ‘test-user’ doesn’t have enough privileges to run the mysqldump command on the database.

How we fix?

In such cases, our Support Experts assign proper privileges to the user to access the database. For instance, see the below command.

GRANT ALL PRIVILEGES ON *.* TO test-user@'localhost' IDENTIFIED BY 'password' with grant option;

This will grant all privileges to the user test-user on the selected database. Most importantly, we use the below command to save the privileges.

flush privileges;

Similarly, on cPanel servers, we manage user privileges from cPanel > Mysql databases > Current databases > Privileged users > Click on the database user.

How to give user privileges in from cPanel?

[Missing database user privileges? Click here, and get one of our Database Experts to fix it for you.]

4) Remote host not allowed in server

Similarly, users commonly see this error when they try to push and pull the databases remotely.

mysqldump: Got error: 1045: Access denied for user 'username'@'161.22x.1xx.2xx' (using password: YES) when trying to connect

This is because most web hosts disallow external connections and they only allow to connect from localhost. So, server owners must allow the IP address of the server from which they are accessing the database.

How we fix?

Our Support Engineers get the remote IP address of the customer and add it to the allowed hosts list of the server, so that the IP can connect to the database remotely.

For example, on cPanel servers, we whitelist an IP address from cPanel > Databases > Remote MySQL > Add Access Host for remote connection. If the customer’s IP address changes regularly, we allow the IP address using a wildcard(%). This will add all the IP addresses in that range.

[Are you facing mysqldump error 1045? No worries, Our Support Experts can help you. ]

Conclusion

In short, mysqldump error 1045 can occur due to mysqldump syntax errors, insufficient privileges for the database user, and more. Today, we’ve discussed the top 4 reasons for this error and how our Dedicated Engineers fix them.