Client does not support authentication protocol requested by server; consider upgrading MySQL client

[참고자료]

[MySQL 8.0] Nodejs 연동 시 에러 "Client does not support authentication protocol requested by server; consider upgrading My

 

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

---

[문제상황]

설치하고, mysql의 호스트가 로컬호스트인 데이터베이스도 실행 상태로 됐습니다.

 

그런데!! Node.js프로젝트와 연결시키려고 하고, node 명령어로 연결을 확인해보려고 하니까

이 글의 제목과 같은 에러가 떴습니다.

$ node ./db_mysql.js
/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/Parser.js:437
      throw err; // Rethrow non-MySQL errors
      ^

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
    at Handshake.Sequence._packetToError (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Handshake.ErrorPacket (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/sequences/Handshake.js:123:18)
    at Protocol._parsePacket (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:527:28)
    at addChunk (node:internal/streams/readable:315:12)
    --------------------
    at Protocol._enqueue (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Protocol.handshake (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at Connection.connect (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/Connection.js:116:18)
    at Object.<anonymous> (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/db_mysql.js:11:12)
    at Module._compile (node:internal/modules/cjs/loader:1105:14)
    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1159:10)
    at Module.load (node:internal/modules/cjs/loader:981:32)
    at Function.Module._load (node:internal/modules/cjs/loader:822:12)
    at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:77:12)
    at node:internal/main/run_main_module:17:47
Emitted 'error' event on Connection instance at:
    at Connection._handleProtocolError (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/Connection.js:423:8)
    at Protocol.emit (node:events:527:28)
    at Protocol._delegateError (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/Protocol.js:398:10)
    at Handshake.<anonymous> (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/Protocol.js:153:12)
    at Handshake.emit (node:events:527:28)
    at Handshake.Sequence.end (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/sequences/Sequence.js:78:12)
    at Handshake.ErrorPacket (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/sequences/Handshake.js:125:8)
    at Protocol._parsePacket (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/Users/ek/Documents/github/loveAlakazam/Practice_NodeJS_1/node_modules/mysql/lib/protocol/Parser.js:43:10) {
  code: 'ER_NOT_SUPPORTED_AUTH_MODE',
  errno: 1251,
  sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client',
  sqlState: '08004',
  fatal: true
}

---

[해결방안]

mysql8 부터는 플러그화된 인증방식을 지원합니다. 특히 이 인증방식이 caching_sha2_password 방식을 기본으로 합니다.

그런데 클라이언트 사이드인 Node.js에서의 mysql(mysqljs) 라이브러리는 이 인증방식을 지원하지 않아서 발생된거였습니다.

 

*저자의 경우 mysql db 유저네임을 'root'로 했습니다.

ALTER USER '유저네임'@'localhost' IDENTIFIED WITH mysql_native_password BY '비밀번호';