[MySQL] Nodejs 연동 시 에러 "Client does not support

* 샘플 코드

사용한 코드는 아래와 같습니다. (예제 링크)

// test-mysql.js 파일
var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'host-IP',
  user     : 'root',
  password : 'Xptmxm1212!@',
  database : 'my-Database'
});

connection.connect();

connection.query('select * from sample_table', function (error, results, fields) {
  if (error){
    console.log(error);
  }
  console.log(results);
});

connection.end();

 

* 에러 내용

 code: 'ER_NOT_SUPPORTED_AUTH_MODE',
  errno: 1251,
  sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client',

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
    at Handshake.Sequence._packetToError (/dir/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Handshake.ErrorPacket (dir/node_modules/mysql/lib/protocol/sequences/Handshake.js:123:18)
    at Protocol._parsePacket (dir/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (dir/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (dir/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (dir/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (dir/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (dir/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:315:20)
    at addChunk (internal/streams/readable.js:309:12)
    --------------------
    at Protocol._enqueue (dir/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Protocol.handshake (dir/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at Connection.connect (dir/node_modules/mysql/lib/Connection.js:116:18)
    at Object.<anonymous> (dir/nodejs/mysql.js:9:12)
    at Module._compile (internal/modules/cjs/loader.js:1063:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1092:10)
    at Module.load (internal/modules/cjs/loader.js:928:32)
    at Function.Module._load (internal/modules/cjs/loader.js:769:14)
    at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:72:12)
    at internal/main/run_main_module.js:17:47 {
  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
}

 

* 해결 방법

이 문제는 클라이언트 프로그램에서 mysql 패스워드 플러그인 "caching_sha2_password"을 소화하지 못해서 생기는 오류입니다.
클라이언트 프로그램에서 사용할 수 있도록 유저의 패스워드 Plusin을 바꿔줍시다.

mysql에 접속하시고, 아래와 같이 수정합니다.

 

<출처>

https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password ...

mysql>  ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Xptmxm1212!@';

 

수정이 완료 되었다면, 아래 사진과 같이 Plugin이 변경되었을거에요.

mysql> SELECT Host,User,plugin,authentication_string FROM mysql.user;