这几天在编写shell脚本过程中遇到mysql报错: java.sql.SQLException: Access denied for user 'test_name'@'127.0.0.1' (using password: YES) 网上没找到靠谱资料, 在此将我解决问题的思路及方法分享给大家。
• 报错信息翻译: 用户 'test_name'@'127.0.0.1'拒绝访问(使用密码:是)
1. 由上报错信息推断是 test_name 用户权限不足导致, 那我们查mysql用户表看下具体情况:
- mysql -uroot -p # 输入密码登录mysql客户端
- select user, host from mysql.user; # 查询mysql系统用户表的sql
[root@localhost cdh]# mysql -uroot -p
Enter password:
mysql> select user, host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| test_name | localhost |
+---------------+-----------+
4 rows in set (0.01 sec)
看到 test_name 在mysql用户表中的权限级别是localhost后就验证了我们之前的推断, 果然是test_name用户的权限问题。
2. 解决方法: 通过如下sql语句给test_name用户赋予权限:
- grant all privileges on *.* to 'test_name'@'%' identified by 'scm' with grant option; # 更改test_name用户权限为%
- select user, host from mysql.user; # 再次查询用户权限
[root@localhost cdh]# mysql -uroot -p
Enter password:
mysql> grant all privileges on *.* to 'test_name'@'%' identified by 'scm' with grant option;
mysql> select user, host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| test_name | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| test_name | localhost |
+---------------+-----------+
5 rows in set (0.01 sec)
更改test_name用户权限为%后, 重试产生报错的环节即可解决 Access denied for user 'test_name'@'127.0.0.1' (using password: YES) 问题。
原文地址: https://blog.csdn.net/DelevinData/article/details/100731000