Correct MySQL password but cannot log on locally

MySQL root password is correct, but how can not log in MySQL from the local, prompted

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

Here, after a high point to find the user table in the MySQL library is missing a root to host:localhost data items, only one root point to host: Host name data items, so how can not use the root account login MySQL.

The summary is that the root account is missing access to the localhost Host account information, resulting in the inability to log on locally.

What is the way to restore root login?

Here's a note of the tangled things that have happened today:

First kill the MySQL process and then add in the parameters to start MySQL

--skip-grant-tables

You will find that you can login to MySQL without a password.

Of course, we must also fix the missing data entry for the root account.

Here are a couple of solutions:

The first is because the root account initially has 3 records, containing the root corresponding to localhost,hostname,127.0.0.1 three account data, we can update the host for the other two items in the localhost can.

The second is to insert a record directly, host for localhost can

To sum up: even if the root host contains the hostname, 127.0.0.1 is still unable to log on normally, there must be localhost host to do so.

If the above method still fails to log in properly we can try another way

In the local MySQL command direct return can enter MySQL, but inside only test and INFORMATION_SCHEMA database, no MySQL and other databases, using use MySQL reported the following error:
mysql> use MySQL
ERROR 1044 (42000): Access denied for user @ ' localhost ' to database ' MySQL '
It means no user specified, no access to database MySQL.

Then log in with root, and enter the correct password to quote the following error:
[root@228827 ~]# mysql-uroot-p123456
ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:yes)
If the password is correct, the MySQL database has disabled the root user's local logon rights.

Using the root user through the host 127.0.0.1 login can be normal access to mysql,127.0.0.1 and localhost for the MySQL database is different host,
[root@228827 ~]# mysql-uroot-p123456-h 127.0.0.1
This reminds me of the user table under MySQL.

We want to go to MySQL to look at the user table, a way to go through the above command, if not, you can use the following command to start the database, the default password into

The code is as follows

Copy Code

[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), Oracl e 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 |% | *a50e066e106320cf4142 | | root | centos | *a50e066e106320cf4142 | | Root | 127.0.0.1 | *a50e066e1063608320cf4142 | +--+ ——————-+ ——————————————-+ 3 rows in Set (0.12 sec)

I found no localhost in the User table host field, but my understanding is that% of all hosts can log on, why localhost not, The same situation I do in 5.0.45 version of MySQL above the experiment will not happen localhost can not log in, I am currently using the 5.1.57 version, is not the version of the problem?

The next changes are obvious:

The code is as follows	Copy Code
mysql> Update user set host= ' localhost ' where user= ' root ' and host= '% '; mysql> flush Privileges;

OK, quit MySQL, restart MySQL to solve the problem