[mysql 8] ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

환경

OS: mac arm64 (M1)

 

SERVER: nest.js

원인

1. MySQL 서버에서 외부 접속이 허가되어 있지 않다.

 

2. MySQL 8버전부터 새로운 패스워드 플러그인 'caching_sha2_password' 를 사용하는데,

    현재(2023.08.30 시점) nest.js 에서 지원되지 않는다.

해결방법

1. 외부 접속 허용 확인

# host 에 localhost 만 있으면 외부 접속 허용이 불가능한 상태!

mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

# MySQL에서 %는 와일드카드로 사용된다!
# 연결하고자 하는 서버 등, 외부 접속을 허용하기 위해 host주소에 %를 추가하면 된다!

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 제대로 추가되었는지 확인!
mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

 

2. 비밀번호 플러그인 확인

# 패스워드 플러그인 확인
mysql> select host, user, plugin, authentication_string from mysql.user;

+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| %         | root             | caching_sha2_password | $A$005$jpaap`,U(D>=a3`8Z13gFfM1FgVsPoI..t12b1BMZG5ApI/F38xd6Z3S9C |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | caching_sha2_password | $A$005$@x"@w
c`Y?miQ2CEvUPKiPfCs9MTLD.ijIipW923C4W/LK8WBYSFpcvY3 |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+

# 플러그인 변경!
mysql> select host, user, plugin, authentication_string from mysql.user;

# 변경 사항 확인!
mysql> select host, user, plugin, authentication_string from mysql.user; 

+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| %         | root             | mysql_native_password | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | caching_sha2_password | $A$005$@x"@w
c`Y?miQ2CEvUPKiPfCs9MTLD.ijIipW923C4W/LK8WBYSFpcvY3 |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+

 

접속이 잘 된다!

 ✘ SaHy 🌙   ~/workspace/nest-practice/board   main ±  npm run start

> board@0.0.1 start
> nest start

[Nest] 42245  - 08/30/2023, 2:33:20 AM     LOG [NestFactory] Starting Nest application...
[Nest] 42245  - 08/30/2023, 2:33:20 AM     LOG [InstanceLoader] TypeOrmModule dependencies initialized +31ms
[Nest] 42245  - 08/30/2023, 2:33:21 AM     LOG [InstanceLoader] TypeOrmCoreModule dependencies initialized +54ms
[Nest] 42245  - 08/30/2023, 2:33:21 AM     LOG [InstanceLoader] TypeOrmModule dependencies initialized +0ms
[Nest] 42245  - 08/30/2023, 2:33:21 AM     LOG [InstanceLoader] AppModule dependencies initialized +1ms
[Nest] 42245  - 08/30/2023, 2:33:21 AM     LOG [RoutesResolver] BoardController {/boards}: +5ms
[Nest] 42245  - 08/30/2023, 2:33:21 AM     LOG [RouterExplorer] Mapped {/boards, GET} route +2ms
[Nest] 42245  - 08/30/2023, 2:33:21 AM     LOG [RouterExplorer] Mapped {/boards, POST} route +0ms
[Nest] 42245  - 08/30/2023, 2:33:21 AM     LOG [NestApplication] Nest application successfully started +1ms