Mysql Access denied for user ‘root‘@ ‘...‘ (using password: YES)异常处理

- 日理万妓 2024-03-16 17:14 52阅读 0赞

#### 目录 ####

*  一、异常错误
 *  二、原因
 *  三、解决方法

## 一、异常错误 ##

PS C:\Users\10568> mysql -u root -p
    Enter password: ****
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Access denied表示拒绝访问，using password：NO/YES是否输入了密码。

![在这里插入图片描述][c58cefdc4995455f95ffec04d3815d6e.png]

## 二、原因 ##

其中一种原因是mysql自启动的bug，mysql的3306端口号被自己占用，需要关闭mysql服务，再重新启动，我目前这样做可以解决，当然，也可能是因为MySQL服务器上不存在的用户尝试访问数据库。可以参考stack overflow的问题去解决。

> stack overflow的回答是在默认情况下，根MySQL用户设置为使用auth\_socket插件进行身份验证，而不是使用密码进行身份验证。在许多情况下，这允许更高的安全性和可用性，但是当您需要允许外部程序（例如，phpMyAdmin）访问用户时，它也会使事情复杂化。为了使用密码以root身份连接到MySQL，您需要将其身份验证方法从auth\_socket切换到mysql\_native\_password。  
> 参考：Access Denied for User ‘root’@‘localhost’ (using password: YES) -No Privileges?：[https://stackoverflow.com/questions/17975120/access-denied-for-user-rootlocalhost-using-password-yes-no-privileges][https_stackoverflow.com_questions_17975120_access-denied-for-user-rootlocalhost-using-password-yes-no-privileges]

## 三、解决方法 ##

查看3306端口情况，发现PID进程为5296

netstat -ano|findstr 3306

![在这里插入图片描述][acbfa4217d6b491c90f4c61be7bd7367.png]

使用命令`taskkill /PID 5964 -t -f`将进程杀死，或者直接在任务管理器中结束任务

之后重新启动mysql

net start mysql

![在这里插入图片描述][637f4099cf90472bbcb19c4b4532a7f1.png]

重新进入mysql即可

![在这里插入图片描述][95e4849bfeaa4a0a8567790f7809a571.png]

如果是Ubuntu系统可以参考以下回答：

> MySQL Error: : 'Access denied for user ‘root’@‘localhost’：[https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost][https_stackoverflow.com_questions_41645309_mysql-error-access-denied-for-user-rootlocalhost]  
> In Ubuntu systems running MySQL 5.7 (and later versions), the root  
> MySQL user is set to authenticate using the auth\_socket plugin by  
> default rather than with a password. This allows for some greater  
> security and usability in many cases, but it can also complicate  
> things when you need to allow an external program (e.g., phpMyAdmin)  
> to access the user.
> 
> In order to use a password to connect to MySQL as root, you will need  
> to switch its authentication method from auth\_socket to  
> mysql\_native\_password. To do this, open up the MySQL prompt from your  
> terminal:
> 
> sudo mysql
> 
> Next, check which authentication method each of your MySQL user  
> accounts use with the following command:
> 
> SELECT user,authentication\_string,plugin,host FROM mysql.user;
> 
> Output
> 
> ±-----------------±------------------------------------------±----------------------±----------+ | user | authentication\_string |  
> plugin | host |  
> ±-----------------±------------------------------------------±----------------------±----------+ | root | |  
> auth\_socket | localhost | | mysql.session |  
> \*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql\_native\_password | localhost | | mysql.sys |  
> \*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql\_native\_password | localhost | | debian-sys-maint |  
> \*CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql\_native\_password | localhost |  
> ±-----------------±------------------------------------------±----------------------±----------+ 4 rows in set (0.00 sec) In this example, you can see that the root  
> user does in fact authenticate using the auth\_socket plugin. To  
> configure the root account to authenticate with a password, run the  
> following ALTER USER command. Be sure to change password to a strong  
> password of your choosing, and note that this command will change the  
> root password you set in Step 2:
> 
> ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql\_native\_password BY  
> ‘password’;
> 
> Then, run FLUSH PRIVILEGES which tells the server to reload the grant  
> tables and put your new changes into effect:
> 
> FLUSH PRIVILEGES;
> 
> Check the authentication methods employed by each of your users again  
> to confirm that root no longer authenticates using the auth\_socket  
> plugin:
> 
> SELECT user,authentication\_string,plugin,host FROM mysql.user;
> 
> Output
> 
> ±-----------------±------------------------------------------±----------------------±----------+ | user | authentication\_string |  
> plugin | host |  
> ±-----------------±------------------------------------------±----------------------±----------+ | root | \*3636DACC8616D997782ADD0839F92C1571D6D78F |  
> mysql\_native\_password | localhost | | mysql.session |  
> \*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql\_native\_password | localhost | | mysql.sys |  
> \*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql\_native\_password | localhost | | debian-sys-maint |  
> \*CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql\_native\_password | localhost |  
> ±-----------------±------------------------------------------±----------------------±----------+ 4 rows in set (0.00 sec) You can see in this example output that the  
> root MySQL user now authenticates using a password. Once you confirm  
> this on your own server, you can exit the MySQL shell:
> 
> exit

[c58cefdc4995455f95ffec04d3815d6e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/cb06b8c011d84beb8b855e92ae81bf5e.png
[https_stackoverflow.com_questions_17975120_access-denied-for-user-rootlocalhost-using-password-yes-no-privileges]: https://stackoverflow.com/questions/17975120/access-denied-for-user-rootlocalhost-using-password-yes-no-privileges
[acbfa4217d6b491c90f4c61be7bd7367.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/ec990e6ae2304963ade244a3d7b52166.png
[637f4099cf90472bbcb19c4b4532a7f1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/7d076ec9ca234e23b00fdf0e6db3f14b.png
[95e4849bfeaa4a0a8567790f7809a571.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/b311674d6a614ba68169400970718c7c.png
[https_stackoverflow.com_questions_41645309_mysql-error-access-denied-for-user-rootlocalhost]: https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost