MySQL登录报错“Access denied for user 'xxx'@'localhost'”解决方法

Bertha 。 2022-05-23 12:44 261阅读 0赞

**报错条目：**

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

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

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

**解决方法：**

update user set host='%' whereuser='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）：updateuser set host='%' where user='mhauser';

2）：GRANTALL 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>  flushprivileges;

Query OK, 0 rows affected (0.12 sec)

查看mhauser被赋予的权限

mysql> select \* from user whereuser='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='%' whereuser='mhauser';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

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

mysql>  flushprivileges;

Query OK, 0 rows affected (0.00 sec)

查看字段值是否修改成功

mysql> select host, user, Password, grant\_priv fromuser;

\+---------------+----------+-------------------------------------------+------------+

| 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 Corporationand/or its

affiliates. Other names may be trademarks of theirrespective

owners.

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

mysql>

**方法2演示：**

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

mysql> update user set host='192.168.88.%' whereuser='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 fromuser;

\+---------------+----------+-------------------------------------------+------------+

| 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 fromuser;

\+---------------+----------+-------------------------------------------+------------+

| 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 Corporationand/or its

affiliates. Other names may be trademarks of theirrespective

owners.

Type 'help;' or '\\h' for help. Type '\\c' to clear thecurrent 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 fromuser;

\+----------------+----------+-------------------------------------------+------------+

| 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='%' whereuser='root';

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

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

发表评论取消回复

表情：

评论列表（有 0 条评论，261人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL 登录数据库报错'ERROR 1045 (28000): Access denied for user'..'@'localhost''

录数据库时报错 \[root@localhost install\]\ mysql -uzabbix -p Enter password: ERROR 10...

一时失言乱红尘/ 2024年04月18日 00:54/ 0 赞/ 9 阅读

相关 MySQL登录报错1045解决办法-1045-Access denied for user ‘root‘@‘‘(using password:YES)

此报错的原因则是没有开通权限 ![在这里插入图片描述][da7d6e88f72e48249b93646d1ef5c7b5.png] 解决方案： 1、打开cmd命令面板

小鱼儿/ 2024年03月27日 09:37/ 0 赞/ 10 阅读

相关 Mysql 8 Access denied for user root@localhost 报错

不多说，直接开整。我这里遇到这种问题是，使用navicat链接的时候，出现的问题这里，我们先查看一下，mysql密码方式登录myslq select ho

偏执的太偏执、/ 2023年07月18日 05:58/ 0 赞/ 76 阅读

相关解决mysql“Access denied for user 'root'@'localhost'”

\ mysql -uroot -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@

客官°小女子只卖身不卖艺/ 2022年10月01日 12:40/ 0 赞/ 151 阅读

相关【报错】SQLException: Access denied for user 'root'@'localhost'

【报错】 log4j:WARN No appenders could be found for logger (org.hibernate.type.BasicType

客官°小女子只卖身不卖艺/ 2022年09月22日 04:59/ 0 赞/ 124 阅读

相关 MYSQL报错：access denied for user 'odbc'@'localhost'(using password: NO)

几个可能： 1. 用户名、密码错误，所以无法访问 2. 数据库端设置了IP访问权限，不能用localhost访问。换成具体的IP地址试试。 3. 数据库端设置了相关

柔情只为你懂/ 2022年08月09日 00:44/ 0 赞/ 153 阅读

相关 MySQL登录报错“Access denied for user 'xxx'@'localhost'”解决方法

报错条目： ERROR 1045 (28000): Access denied foruser 'mhauser'@'localhost' (using password:

Bertha 。/ 2022年05月23日 12:44/ 0 赞/ 262 阅读

相关 MySQL-1698 : Access denied for user 'root'@'localhost'解决方法

用root用户连接MySQL 数据库报"ERROR 1698 : Access denied for user ‘root’@‘localhost’" 错误。 \\客户端：

川长思鸟来/ 2022年05月11日 07:40/ 0 赞/ 334 阅读

相关 Linux连接mysql报错：Access denied for user ‘root’@‘localhost’(using password: YES)的解决方法

[Linux连接mysql报错：Access denied for user ‘root’@‘localhost’(using password: YES)的解决方法][Li

短命女/ 2022年04月12日 03:12/ 0 赞/ 132 阅读

相关解决mysql“Access denied for user 'root'@'localhost'”

\ mysql -uroot -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@

叁歲伎倆/ 2022年02月16日 01:53/ 0 赞/ 193 阅读