[Solved] ERROR 1045 (28000): Access denied for user 'user'@'host' (using password: YES)

Error message

Error 1045 (28000): Access denied for user ‘user’@’host’ (using password: YES)

Debugging Summary

• Check for typo error: username or password. 
• Check the host name and compare it with mysql.user table host name. 
• Check user exists or not. 
• Check whether host contains IP address or host name.

There is a great chance that, you might have encountered this issue multiple times in your work. This issue occurred to me most of times due to the incorrectly entering user name or password. Though this is one of the reasons, there are other many chances you might get this issue. Sometimes, it looks very similar, but when you dig deeper, you will realize multiple factors contributing to this error. This post will explain in detail, most of the common reasons and work around to fix this issue. 

Possible reasons:

• Case 1: Typo error: username or password.
• Case 2: Accessing from wrong host.
• Case 3: User does not exists on the server.
• Case 4: Mix of numeric and name based hosts.

Before get into the solutions, let us take a general scenario with a username, password, host and a database with a table. For example, let us consider the following details:

• Username: admin
• Host: 127.0.0.1
• Password: admin@123
• Database: demodb
• Table: demotable

Let us create the database and user details in MySQL:

User creation in MySQL

Error message:

Error 1045 (28000): Access denied to user

Case 1: Typo error: username or password

This is the most common reason for this error. If you entered the username or password wrongly, surely you will get this error. Let us reproduce this type error by entering the incorrect username and password and let see the output.

Case 1.1: Typo error in username: demousr

Typo error in user name

Case 1.1: Typo error in password: demouser123

Typo error in password

Solution: 

Solution for this type of error is very simple. Just enter the correct username and password. This error will be resolved. In case if you forget the password you can reset the username/password.

If you forget the password for admin / root account, there are many ways to reset / recapture the root password. I will publish another post on how to reset the root password in-case if you forget root password.

Case 2: Accessing from wrong host

MySQL provides host based restriction for user access as a security features. In our production environment, we used to restrict the access request only to the Application servers. This feature is really helpful in many production scenarios.  

I am trying to access MySQL server from my laptop, my laptop host name is different from the defined host. Let us see the output.

Accessing from wrong host

In our example, we have defined the host name for the demouser. Let us see the user details for the demouser from mysql.user table.

User info from mysql.user

Here, my laptop host name is 'RATHISH'. So access denied to the demouser from the host 'RATHISH'

Solution:

When you face this type of issue, first check whether your host is allowed or not by checking the mysql.user table.

If it is not defined, you can update or insert new record to mysql.user table.

Generally, accessing as a root user from remote machine is disabled and it is not a best practice, because of security concerns.

If you have requirements to access your server from multiple machines, give access only to those machines. It is better not to use wildcards (%) and gives universal accesses.

Let me update the mysql.user table, now the demouser can access MySQL server from any host.

Updating mysql.user table

Case 3: User does not exists:

This type of error occurs when the user, which you are trying to access not exist on the MySQL server. For example, I am trying to login as user ‘rathish’ and see, what happens:

User not exist issue

Solutions:

When you face this type of issue, just check whether the user is exists in mysql.user table or not. If the record not exists, user cannot access. If there is a requirement for that user to access, create a new user with that username.

Scanning mysql.user table

Case 4: Mix of numeric and name based hosts:

Always remember that, MySQL treat the following users, demouser@localhost, demouser@127.0.0.1, demouser@%, demouser@’rathishlaptop’ as different users, not same user. Let me show you this.

Remember we have given SELECT privilege on demodb.demotable to demouser@127.0.0.1.:

User creation - MySQL

Now, login as demouser and check the grants for demouser@%:

Grants of demouser@%

We have updated only the host details, but entire access given to demouser@127.0.0.1 is not available to demouser@%.

Important points

It is not advisable to use wildcards while defining user host. Try to use the exact hostnam

• It is not advisable to use wildcards while defining user host, try to use the exact host name.
• Disable root login from remote machine.
• Use proxy user concept.

There are few other concepts related with this topic and getting into details of those topics is very different scope of this article. We will look into the following related topics in the upcoming articles.

• What to do, if you forgot root password in of MySQL server.
• MySQL Access privilege issues and user related tables.
• MySQL security features with best practices.

I hope this post will help for you to fix the MySQL Error Code 1045 Access denied for user in MySQL. If I missed any other scenarios where this error could occur or if you encountered this error in some other scenario, please add it on the comment section and I will edit and update the post, it will be help for our MySQL readers. Please click on FOLLOW ME button to receive my latest MySQL articles.