报错条目：

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

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

ERROR 1045 (28000): Access denied for user 'root'@'192.168.88.130' (using password: NO)

解决方法：

update user set host='%' where user='xxx';

GRANT ALL ON *.* TO 'xxx'@'localhost' IDENTIFIED BY '888888';

 

1、普通账户无法登录

[root@www ~]# mysql -u mhauser -p

Enter password:

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

出现该错误的原因为给mhauser分配可以登录的主机时使用SQL：

mysql> GRANT ALL ON *.* TO 'mhauser'@'192.168.88.%' IDENTIFIED BY 'mhapass';

允许远程登录的主机为"192.168.88.%"，在同一台服务器登录时主机识别为localhost，故无法登录。

此处使用

1）：update user set host='%' where user='mhauser';

2）：GRANT ALL ON *.* TO 'mhauser'@'localhost' IDENTIFIED BY '888888';

即可解决问题

 

方法1演示：

mysql> use mysql

更新密码，可不操作

mysql> update user set password=password('888888') where user='mhauser';

Query OK, 1 row affected (0.05 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> show full processlist;

+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

| Id | User     | Host                 | db      | Command     | Time | State                                                          | Info                  |

+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

|  7 | root     | localhost            | hellodb | Sleep       | 6544 |                                                                | NULL                  |

|  9 | root     | localhost            | mydb    | Sleep       | 6897 |                                                                | NULL                  |

| 16 | root     | localhost            | mysql   | Query       |    0 | NULL                                                           | show full processlist |

| 18 | repluser | 192.168.88.130:45967 | NULL    | Binlog Dump |   49 | Has sent all binlog to slave; waiting for binlog to be updated | NULL                  |

+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

4 rows in set (0.00 sec)

mysql>  flush privileges;

Query OK, 0 rows affected (0.12 sec)

查看mhauser被赋予的权限

mysql> select * from user where user='mhauser'\G

*************************** 1. row ***************************

                 Host: 192.168.88.%   <--可以发现，host为192.168.88.%

                 User: mhauser

             Password: *DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB

          Select_priv: Y

          Insert_priv: Y

          Update_priv: Y

          Delete_priv: Y

          Create_priv: Y

            Drop_priv: Y

          Reload_priv: Y

        Shutdown_priv: Y

         Process_priv: Y

            File_priv: Y

           Grant_priv: N    <--不具备授权权限

      References_priv: Y

           Index_priv: Y

           Alter_priv: Y

         Show_db_priv: Y

           Super_priv: Y

Create_tmp_table_priv: Y

     Lock_tables_priv: Y

         Execute_priv: Y

      Repl_slave_priv: Y

     Repl_client_priv: Y

     Create_view_priv: Y

       Show_view_priv: Y

  Create_routine_priv: Y

   Alter_routine_priv: Y

     Create_user_priv: Y

           Event_priv: Y

         Trigger_priv: Y

             ssl_type:

           ssl_cipher:

          x509_issuer:

         x509_subject:

        max_questions: 0

          max_updates: 0

      max_connections: 0

 max_user_connections: 0

1 row in set (0.00 sec)

重设能够登录的主机,%即为所有。

mysql> update user set host='%' where user='mhauser';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

清空缓存中的权限，刷新权限

mysql>  flush privileges;

Query OK, 0 rows affected (0.00 sec)

查看字段值是否修改成功

mysql> select host, user, Password, grant_priv from user;

+---------------+----------+-------------------------------------------+------------+

| host          | user     | Password                                  | grant_priv |

+---------------+----------+-------------------------------------------+------------+

| localhost     | root     |                                           | Y          |

| www.field.com | root     |                                           | Y          |

| 127.0.0.1     | root     |                                           | Y          |

| 192.168.%.%   | repluser | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 | N          |

| 192.168.88.%  | repluser | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF | N          |

| %             | mhauser  | *DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB | N          |

+---------------+----------+-------------------------------------------+------------+

6 rows in set (0.18 sec)

测试登录，成功

[root@www ~]# mysql -u mhauser -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 23

Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 

方法2演示：

把host改回'192.168.88.%'，令其仍旧不能登录。

mysql> update user set host='192.168.88.%' where user='mhauser';

Query OK, 0 rows affected (0.01 sec)

Rows matched: 1  Changed: 0  Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, Password, grant_priv from user;

+---------------+----------+-------------------------------------------+------------+

| host          | user     | Password                                  | grant_priv |

+---------------+----------+-------------------------------------------+------------+

| localhost     | root     |                                           | Y          |

| www.field.com | root     |                                           | Y          |

| 127.0.0.1     | root     |                                           | Y          |

| 192.168.%.%   | repluser | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 | N          |

| 192.168.88.%  | repluser | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF | N          |

| 192.168.88.%  | mhauser  | *DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB | N          |

+---------------+----------+-------------------------------------------+------------+

6 rows in set (0.00 sec)

mysql> exit

Bye

[root@www ~]# mysql -u mhauser -p888888

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

此处采用grant方式授权登录

[root@www ~]# mysql -u root

Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use mysql

mysql> GRANT ALL ON *.* TO 'mhauser'@'localhost' IDENTIFIED BY '888888';

Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, Password, grant_priv from user;

+---------------+----------+-------------------------------------------+------------+

| host          | user     | Password                                  | grant_priv |

+---------------+----------+-------------------------------------------+------------+

| localhost     | root     |                                           | Y          |

| www.field.com | root     |                                           | Y          |

| 127.0.0.1     | root     |                                           | Y          |

| localhost     | mhauser  | *DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB | N          |

| 192.168.%.%   | repluser | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 | N          |

| 192.168.88.%  | repluser | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF | N          |

| 192.168.88.%  | mhauser  | *DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB | N          |

+---------------+----------+-------------------------------------------+------------+

localhost     | mhauser  <--可见以添加该条目

7 rows in set (0.00 sec)

mysql> exit

Bye

测试登录，成功。

[root@www ~]# mysql -u mhauser -p888888

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 35

Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 

2、使用root无法远程登录

该错误原因则是root不具备远程登录其他服务器的权限

[root@test ~]# mysql -u root -h 192.168.88.131

ERROR 1045 (28000): Access denied for user 'root'@'192.168.88.130' (using password: NO)

[root@test ~]# mysql -u root

mysql> select host, user, Password, grant_priv from user;

+----------------+----------+-------------------------------------------+------------+

| host           | user     | Password                                  | grant_priv |

+----------------+----------+-------------------------------------------+------------+

| localhost      | root     |                                           | Y          |

| test.field.com | root     |                                           | Y          |

| 127.0.0.1      | root     |                                           | Y          |

| %              | field    |                                           | N          |

| 192.168.88.%   | repluser | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF | N          |

| %              | mhauser  | *DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB | N          |

+----------------+----------+-------------------------------------------+------------+

6 rows in set (0.00 sec)

由上表可见，root账户能登录的主机为localhost，test.field.com，127.0.0.1,。

显然，不能登录的原因类似于上述mhauser账户，root账户不具备远程登录其它MySQL服务器的权限，

使用以下任意一个命令即可解决问题：

update user set host='%' where user='root';

GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '888888';

当然笔者强烈建议不要使用root远程登录其他MySQL服务器。