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

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

 

Table of Contents

• Client does not support authentication
  • 에러 발생 원인은?
  • 에러 해결 방법
• 맺음

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의 버전을 변경해주시면 됩니다.

 

MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client - Stack Overflow

stackoverflow.com

맺음

간단하게 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 '%'

mysql root mysql-workbench workbench

Share

Follow

edited Jul 23, 2019 at 9:40

radbyx

8,9921818 gold badges7979 silver badges121121 bronze badges

asked Jul 31, 2013 at 15:54

Chase

1,81322 gold badges1111 silver badges55 bronze badges

•  
  Run SHOW GRANTS FOR root in a query, post the result in your question.

  – NobleUplift

  Jul 31, 2013 at 16:00

•  
•  
  You're trying to access the database from the localhost or from another host using MySQL Workbench?

  – Andy Jones

  Jul 31, 2013 at 16:02
•  
  restart server with --skip-grant-table and login with any user and provide full privileges to root and restart server again without the above option

  – vidyadhar

  Jul 31, 2013 at 16:05
•  
  @AndyJones no, it's on a dedicated server I am using.

  – Chase

  Jul 31, 2013 at 16:07
•  
  @vidyadhar I'm sorry, I'm not sure where I would do that?

  – Chase

  Jul 31, 2013 at 16:09

Show 3 more comments

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/