소프트웨어 (과거)/웹 서버 등 개발 환경 세팅

9. mysql 사용자 추가 및 에러 잡는 방법

dgmayor 2022. 1. 20. 20:30
728x90

[Mysql] Client does not support authentication protocol requested by server 에러

 

Jpa와 Mysql을 이용하여 공부를 진행하던 중 만난 authentication관련 에러에 대해서 소개해드리도록 하겠습니다.

Client does not support authentication

이 이유가 발생하게 된 이유는 Mysql 8.0에서는 다양한 플러그 형태의 인증 방법을 제공합니다. 기본적으로 제공하는 인증 방법은 caching_sha2_password입니다.

에러 발생 원인은?

제가 설치한 Mysql Connector의 버전은 caching_sha2_password 인증 방법은 지원하지 않는 버전이었기 때문에 인증 에러가 발생했습니다.

에러 해결 방법

-- mysql_native_password 사용하도록 변경
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourRootPassword';

또는

-- mysql_native_password를 사용한 계정을 새로 생성
CREATE USER 'foo'@'%' IDENTIFIED WITH mysql_native_password BY 'bar';

FLUSH PRIVILEGES;

위와 같이 Mysql 서버에서 쿼리를 실행하여 Mysql 8.0 이전의 인증 방법으로 사용할 수 있도록 변경해주면 됩니다.

또 다른 방법으로는 caching_sha2_password 인증 방법을 지원하는 버전으로 Mysql Connector의 버전을 변경해주시면 됩니다.

맺음

간단하게 Client does not support authentication protocol requested by server에러가 발생하는 원인과 해결 방법에 대해서 알아보았습니다.

 

 

요약하자면

-- mysql_native_password를 사용한 계정을 새로 생성
CREATE USER 'foo'@'%' IDENTIFIED WITH mysql_native_password BY 'bar';

FLUSH PRIVILEGES;

위와 같이 Mysql 서버에서 쿼리를 실행하여 Mysql 8.0 이전의 인증 방법으로 사용할 수 있도록 변경해주면 됩니다.

또 다른 방법으로는 caching_sha2_password 인증 방법을 지원하는 버전으로 Mysql Connector의 버전을 변경해주시면 됩니다.

 

오라클에서 mysql deb으로 경로 잡아주고 설치는

install 로 sql-server 및 workbench 설치....

mysql_secure_installation은 sudo 권한으로 해주어야 하고...

mysql이 잘 되어도 workbench에서 access Denied 에러가 난다면...

다음과 같이 해결 하면 된다.

 

Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

 
Asked 8 years, 8 months ago
Modified 7 days ago
Viewed 1.1m times
Report this ad
 
181
60
 

I am continuously receiving this error.

I am using mySQL Workbench and from what I am finding is that root's schema privileges are null. There are no privileges at all.

I am having troubles across platforms that my server is used for and this has been all of a sudden issue.

root@127.0.0.1 apparently has a lot of access but I am logged in as that, but it just assigns to localhost anyways - localhost has no privileges.

I have done a few things like FLUSH HOSTS, FLUSH PRIVILEGES, etc but have found no success from that or the internet.

How can I get root its access back? I find this frustrating because when I look around people expect you to "have access" but I don't have access so I can't go into command line or anything and GRANT myself anything.

When running SHOW GRANTS FOR root this is what I get in return:

Error Code: 1141. There is no such grant defined for user 'root' on host '%'

Follow
radbyx
8,99218 gold badges79 silver badges121 bronze badges
asked Jul 31, 2013 at 15:54
Chase
1,8132 gold badges11 silver badges5 bronze badges

24 Answers

Sorted by:
 
 
 
230
 

If you have that same problem in MySql 5.7.+ :

Access denied for user 'root'@'localhost'

it's because MySql 5.7 by default allow to connect with socket, which means you just connect with sudo mysql. If you run sql :

SELECT user,authentication_string,plugin,host FROM mysql.user;

then you will see it :

+------------------+-------------------------------------------+-----------------------+-----------+
| 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 | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

To allow connection with root and password, then update the values in the table with command :

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';
FLUSH PRIVILEGES;

Then run the select command again and you'll see it has changed :

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *2F2377C1BC54BE827DC8A4EE051CBD57490FB8C6 | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

And that's it. You can run this process after running and completing the sudo mysql_secure_installation command.

For mariadb, use

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('manager');

to set password. More at https://mariadb.com/kb/en/set-password/

728x90
저작자표시 변경금지