[Ubuntu] Node.js DB 연동 ( Mysql )

1. NPM mysql 설치

ubuntu@ubuntu:~/work$ npm install mysql

added 11 packages, and audited 12 packages in 4s

found 0 vulnerabilities

 

2. 코드 추가

 

/*---------------------------------------------------------------------------------------------
Database
---------------------------------------------------------------------------------------------*/
const mysql = require('mysql');  // mysql 모듈 로드
const conn = {  // mysql 접속 설정
    host: 'localhost',
    port: '3306',
    user: 'root', // id
    password: 'root', // password
    database: 'user_db' // db name
};


/*---------------------------------------------------------------------------------------------
Execute Query
---------------------------------------------------------------------------------------------*/

    var connection = mysql.createConnection(conn); // DB 커넥션 생성
    connection.connect();   // DB 접속

    var testQuery = "select * from schema";

    connection.query(testQuery, function (err, results, fields) { // testQuery 실행
        if (err) {
            console.log(err);
        }
        console.log(results);
    });
     
    connection.end(); // DB 접속 종료

 

 

3-1 에러발생 - ER_NOT_SUPPORTED_AUTH_MODE

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
    at Handshake.Sequence._packetToError (/home/ubuntu/work/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Handshake.ErrorPacket (/home/ubuntu/work/node_modules/mysql/lib/protocol/sequences/Handshake.js:123:18)
    at Protocol._parsePacket (/home/ubuntu/work/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/ubuntu/work/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/ubuntu/work/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/home/ubuntu/work/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/ubuntu/work/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/home/ubuntu/work/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:314:20)
    at addChunk (_stream_readable.js:297:12)
    --------------------
    at Protocol._enqueue (/home/ubuntu/work/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Protocol.handshake (/home/ubuntu/work/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at Connection.connect (/home/ubuntu/work/node_modules/mysql/lib/Connection.js:116:18)
    at Server.<anonymous> (/home/ubuntu/work/db_test.js:119:16)
    at Object.onceWrapper (events.js:420:28)
    at Server.emit (events.js:314:20)
    at emitListeningNT (net.js:1344:10)
    at processTicksAndRejections (internal/process/task_queues.js:83:21) {
  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 접속 후 password plugin 변경

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

 

이후 동작