What should I do if the MySQL password is correct but cannot be logged on locally?

The MySQLroot password is correct, but you cannot log on to MySQL from the local machine. The prompt is 1ERROR1045 (28000): Accessdeniedforuserroot @ localhost (usingpassword: YES) the user table in the mysql database is missing a data item whose root points to host: localhost. Only one root points to h.

The MySQL root password is correct, but you cannot log on to MySQL from the local machine. The following ERROR occurs: 1 ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: YES) the user table in the mysql database is missing a data item whose root points to host: localhost. Only one root points to h.

The MySQL root Password is correct, but you cannot log on to MySQL locally.

1 ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: YES)

Later, the user table in the mysql database was found to be missing a data item whose root points to host: localhost, and only one data item whose root points to host: host Name ,, therefore, you cannot use the root account to log on to MySQL.

In summary, the root account does not have the account information for accessing the localhost host, resulting in local login failure.

So how can I recover the root login?

Here is a record of today's Tangle:

Kill the MySQL process and add it to the mysql startup parameters.

-- Skip-grant-tables

You can log on to mysql without a password.

Of course, we must also repair the lost data items of the root account.

There are two solutions:

First, the root account has three records at the beginning, including the root account's localhost, hostname, and 127.0.0.1 account data. We can update the host to the localhost of the other two items.

The second method is to directly insert a record, and the host is localhost.

To sum up, even if the root host contains the host name, 127.0.0.1 still cannot be logged on normally. Therefore, you must have the host of localhost.

If the above method still fails to log on normally, we can try another method.

Run the mysql command locally and press enter to access mysql. However, there are only databases test and information_schema, and there are no databases such as mysql. the following error is reported when you use mysql:

Mysql> use mysql

ERROR 1044 (42000): Access denied for user "@ 'localhost' to database 'mysql'

This means that no user is specified and you are not authorized to access mysql.

If you use root to log on, enter the correct password and report the following error:

[Root @ 228827 ~] # Mysql-uroot-p123456

ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: YES)

When the password is correct, the mysql database has disabled the root user's local logon permission.

Using the root user to log on to mysql through the host 127.0.0.1, 127.0.0.1 and localhost are different hosts for the mysql database,

[Root @ 228827 ~] # Mysql-uroot-p123456-h 127.0.0.1

This reminds me of the user table in mysql.

To view the user table in mysql, run the preceding command. If not, run the following command to start the database. The default password is used.

The Code is as follows:

[Root @ 228827 ~] #/Etc/init. d/mysql stop
[Root @ 228827 ~] #/Usr/local/mysql/bin/mysqld_safe-skip-grant-tables &
[Root @ 228827 ~] # Mysql
Welcome to the MySQL monitor. Commands end with; or g.
Your MySQL connection id is 1
Server version: 5.1.57 Source distribution
Copyright (c) 2000,201 0, Oracle and/or its affiliates. All rights reserved.
Type 'help; 'or 'H' for help. Type 'C' to clear the current input statement.

Mysql> use mysql
Database changed
Mysql> select user, host, password from user where user = 'root ';
+ -- + ------- + --------------- +
| User | host | password |
+ -- + ------- + --------------- +
| Root | % | * a50e066e0000320cf4142 |
| Root | centos | * a50e066e0000320cf4142 |
| Root | 127.0.0.1 | * a50e066e4243608320cf4142 |
+ -- + ------- + --------------- +
3 rows in set (0.12 sec)

I found that there is no localhost in the host field of the user table, but my understanding is that % indicates that all hosts can log on. Why can't localhost be used, in the same case, if I did an experiment on mysql 5.0.45, localhost could not be logged on. I am currently using mysql 5.1.57. Is it a version issue?

The following changes are obvious:

The Code is as follows:

Mysql> update user set host = 'localhost' where user = 'root' and host = '% ';
Mysql> flush privileges;

OK. Exit mysql. Restart mysql to solve the problem.