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

关注

原创最新推荐文章于 2025-05-27 08:42:52 发布 3.3w 阅读 AI 写同款· GEO 优化 ›

解决：Access denied for user ‘root‘@‘localhost‘ (using password: YES) 文章目录Xshell 命令访问MySQL数据库，密码连接报错！一、报错截图示下：二、分析：三、解决方案：3.1. Windows系统：3.2. Linux 系统 Xshell 命令访问MySQL数据库，密码连接报错！报错内容：Access denied for user 'root'@'localhost' (using password: YES) 在MySQL登录时出现Access denied for user 'root'@'localhost' (using password: YE 阅读详情

报错条目：

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;

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

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

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

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;

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

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

| 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

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;

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

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

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

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

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

| 127.0.0.1 | root | | Y |

| 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

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;

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

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

| 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服务器。

收录于

mysql数据库

本内容遵循 CC 4.0 BY-SA 版权协议

mysql数据库空密码导致 Access denied for user ‘‘@‘localhost‘：解决办法问题描述在使用远程开发机上下载mysql后发现重新空密码连接时，会导致数据库表显示不全并且报错，查了相关问题，总结一下自己的解决方案。解决办法配置mysql vim ${JUMBO_ROOT}/etc/mysql/my.cnf （注意{JUMBO_ROOT}是我安装mysql的路径，注意替换为自己的路径）修改配置文件在[mysql]下增加 skip-grant-tables （跳过权限无密码登录，此时登录成功后再用show databases;指令，可以看到原来所有的数据库）重启mysql 阅读详情

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

相关推荐