Ошибка access denied for user localhost using password yes

During our work in support, we see this again and again: “I try to connect to MySQL and am getting a 1045 error”, and most times it comes accompanied with “…but I am sure my user and password are OK”.  So we decided it was worth showing other reasons this error may occur.

MySQL 1045 error Access Denied triggers in the following cases:

1) Connecting to wrong host:

If not specifying the host to connect (with -h flag), MySQL client will try to connect to the localhost instance while you may be trying to connect to another host/port instance.

Fix: Double check if you are trying to connect to localhost, or be sure to specify host and port if it’s not localhost:

2) User does not exist:

Fix: Double check if the user exists:

If the user does not exist, create a new user:

3) User exists but client host does not have permission to connect:

Fix: You can check to see which host user/host MySQL allows connections with the following query:

If you need to check from which IP the client is connecting, you can use the following Linux commands for server IP:

or for public IP:

You can then create a user with correct Host (client IP), or with ‘%’ (wildcard) to match any possible IP:

4) Password is wrong, or the user forgot his password:

Fix: Check and/or reset password:

You cannot read user passwords in plain text from MySQL as the password hash is used for authentication, but you can compare hash strings with “PASSWORD” function:

We can see that PASSWORD(‘forgotten’) hash does not match the authentication_string column, which means password string=’forgotten’ is not the correct password to log in. Also, in case the user has multiple hosts (with different password), he may be trying to connect using the password for the wrong host.

In case you need to override the password you can execute the following query:

5) Special characters in the password being converted by Bash:

Fix: Prevent bash from interpreting special characters by wrapping password in single quotes:

6) SSL is required but the client is not using it:

Fix: Adding –ssl-mode flag (–ssl flag is deprecated but can be used too)

You can read more in-depth on how to configure SSL in MySQL in the blog post about “Setting up MySQL SSL and Secure Connections” and “SSL in 5.6 and 5.7“.

7) PAM backend not working:

Fix: Double check user/password is correct for the user to authenticate with the PAM currently being used.

In my example, I am using Linux shadow files for authentication. In order to check if the user exists:

To reset password:

Finally, if you are genuinely locked out and need to circumvent the authentication mechanisms in order to regain access to the database, here are a few simple steps to do so:

1. Stop the instance
2. Edit my.cnf and add skip-grant-tables under [mysqld] (this will allow access to MySQL without prompting for a password). On MySQL 8.0, skip-networking is automatically enabled (only allows access to MySQL from localhost), but for previous MySQL versions it’s suggested to also add –skip-networking under [mysqld]
3. Start the instance
4. Access with root user (mysql -uroot -hlocalhost); 

5. Issue the necessary GRANT/CREATE USER/SET PASSWORD to correct the issue (likely setting a known root password will be the right thing: SET PASSWORD FOR ‘root’@’localhost’ = ‘S0vrySekr3t’). Using grant-skip-tables won’t read grants into memory and GRANT/CREATE/SET PASSWORD statements won’t work straight away. First, you need to execute “FLUSH PRIVILEGES;” before executing any GRANT/CREATE/SET PASSWORD statement, or you can modify mysql.users table with a query which modifies the password for User and Host like “UPDATE mysql.user SET authentication_string=PASSWORD(‘newpwd’) WHERE User=’root’ and Host=’localhost’;”

6. Stop the instance
7. Edit my.cnf and remove skip-grant-tables and skip-networking
8. Start MySQL again
9. You should be able to login with root from the localhost and do any other necessary corrective operations with root user.
   

Learn more about Percona Server for MySQL

​​​​​​​​​​Here is a quick-and-dirty method for checking out how MySQL performs successful authentication.

Please run this query:

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in mysqld

CURRENT_USER() reports how you were allowed to authenticate by mysqld

Sometimes, USER() and CURRENT_USER() are different. That’s because mysql authentication follows a specfic protocol.

According to MySQL 5.0 Certification Study Guide

pages 486,487 state the following on mysql’s authentication algorithm:

There are two stages of client access control:

In the first stage, a client attempts to connect and the server either
accepts or rejects the connection. For the attempt to succeed, some
entry in the user table must match the host from which the client
connects, the username, and the password.

In the second stage (which occurs only if a client has already
connected sucessfully), the server checks every query it receives from
the client to see whether the client has sufficient privileges to
execute it.

The server matches a client against entries in the grant tables based
on the host from which the client connects and the user the client
provides. However, it’s possible for more than one record to match:

Host values in grant tables may be specified as patterns contains
wildcard values. If a grant table contains entries from
myhost.example.com, %.example.com, %.com, and %, all of them
match a client who connects from myhost.example.com.

Patterns are not allowed for the User values in grant table entries,
but a username may be given as an empty string to specify an anonymous
user. The empty string matches any username and thus effectively acts
as a wildcard.

When the Host and the User values in more than one user table record
match a client, the server must decide which one to use. It does this
by sorting records with the most specific Host and User column values
first, and choosing the matching record that occurs first in the
sorted list, Sorting take place as follows:

In the Host Column, literal values such as localhost, 127.0.0.1,
and myhost.example.com sort ahead of values such as %.example.com
that have pattern characters in them. Pattern values are sorted
according to how specific they are. For example, %.example.com is
more specific than %.com, which is more specific than %.

In the User column, non-blank usernames sort ahead of blank usernames.
That is, non-anonymous users sort ahead of anonymous users.

The server performs this sorting when it starts. It reads the grant
tables into memory, sorts them, and uses the in-memory copies for
access control.

From this description, you do not need to worry about the order of the mysql.user tables since there is an in-memory copy of the grant tables which is sorted as previously mentioned.

With regard to how you logged in, only mysql -u a worked. Go back and login again and run these commands

SELECT USER(),CURRENT_USER();
SELECT user,host,password FROM mysql.user;

Make sure that

• every user has a password.
• there are no anonymous users (when user is blank)

This is just a guess, but I suspect mysql -u a of connecting via localhost because when the connection protocol is not specified, the default is to connect via the socket file. There may exist an entry in mysql.user that allow anonymous localhost connection.

Run this query:

SELECT user,host,password FROM mysql.user WHERE user='' AND host='localhost';

If you get back a row with no password, that fully explains why mysq -u a works.

UPDATE 2012-01-19 11:12 EDT

Craig Efrein brought up an interesting question: if two identical usernames exist in the mysql.user table, one with a password and one without, does that mean that MySQL denies authentication when not using a password?

This question is an excellent heads up about MySQL user authentication.

Please note that the primary key of mysql.user is host,user. There are no other indexes. This allows multiple occurrences of a username. Each occurrence can have a different password or no password. This allows user ‘dbuser’ to login locally (dbuser@localhost) using no password and the same user login from another server within a given netblock (dbuser@’10.1.2.20′) with a password like ‘pass1′ and that user to login remotely from anywhere (dbuser@’%’) with a remote password like ‘pass2’.

Given the authentication algorithm that MySQL uses, there are no restrictions placed on users with the presence or absense of a password.

This is why MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints brings out how to cleanup the authentication process:

On Unix, MySQL comes with a mysql_secure_installation script that can
perform several helpful security-related operations on your
installation. The script has the following capabilities:

• Set a password for the root accounts
• Remove any remotely accessible root accounts.
• Remove the anonymous user accounts. This improves security because
  it prevents the possibility of anyone connecting to the MySQL server
  as root from a remote host. The results is that anyone who wants to
  connect as root must first be able to log in on the server host, which
  provides an additional barrier against attack.
• Remove the test database (If you remove the anonymous accounts, you
  might also want to remove the test database to which they have
  access).

Oops!! Frustrated with ‘Access denied for user ‘root’@’localhost’ (using password yes) in MySQL?

This error may occur due to many reasons which include insufficient privileges for the user to access the database, and when MySQL root password is wrong and so on.

At Bobcares, we often get requests to fix MySQL errors, as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix this MySQL for our customers.

Why does access denied error occurs in MySQL?

This error may occur due to different reasons in different scenarios. Some of them are:

• When a user who doesn’t exist on the MySQL server tries to access the database.
• When there is no privilege for the user.
• If the username or password is wrong.

Today, we’ll discuss in detail how we fix this Access denied for user ‘root’@’localhost’ (using password yes) in MySQL.

How we fix ‘access denied for user ‘root’@’localhost’ (using password yes) in MySQL’?

Let’s see, how we fix this access denied error for our customers in detail.

a. Not able to login

Recently, one of our customers approached us saying that he is getting an error like the one shown below while he is trying to log in to MySQL.

So, our Support Engineers checked in detail and found that the root password is wrong. So, to reset the password we did the following:

1. Initially, we opened /etc/mysql/my.cnf

2. Then we added skip-grant-tables under [mysqld] section.

Here we skipped grant tables as there is no other way to log in.

By skipping the grant table option it allows anyone to log in from anywhere and can do anything on the database.

Note: skip-grant-tables is dangerous, so we will remove this at the end.

3 Then, we restarted the MySQL service using the command below:

service mysql restart

Now MySQL is configured to skip the grant-tables. So, we can log in now.

4. We logged into MySQL using:

mysql -u root 

5. Then, we flushed the privileges using:

6. After that, we set a new password using the command below: UPDATE user SET

Password=PASSWORD('my_password') where USER='root';
FLUSH PRIVILEGES;

7. Then, we removed skip-grant-tables from /etc/mysql/my.cnf

8. Finally, we restarted the MySQL service and logged in to MySQL using the new password.

This fixes the error.

b. No global privilege

If there is no global privilege then this error can happen. In such cases, our Engineers add the privilege by:

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';

This fixes the error.

[Need more assistance to fix MySQL Error?- We’re available 24/7.]

Conclusion

In short, this error occurs mainly due to insufficient privileges to the user and can be fixed by granting the privilege. Also, today we discussed in detail how our Support Engineers fix this error for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;