Error: Access denied for user 'root'@'localhost' (sqlState: '28000') 에러 해결 사례

개발 환경

• ubuntu-18.04
• node v17.9.1
• npm v8.11.0
• mysql Ver 14.14 Distrib 5.7.40, for Linux (x86_64) using EditLine wrapper

문제 상황

• 로컬에서 개발하던 Express + MySQL + TypeORM API 서버를 네이버 클라우드 배포 환경으로 옮긴 상황이였다.
• 로컬의 MySQL은 기존에 계속 사용했던 상태였고, 오류 없이 잘 작동하던 상태였다.
• 서버에서는 아래 코드르 통해 TypeORM DataSource를 초기화하는 과정이 진행되었다.

// 오류가 발생한 부분의 코드. DATASOURCE는 환경변수들을 저장한 상수 변수이다.
export const AppDataSource = new DataSource({
  type: DATASOURCE.TYPE,
  host: DATASOURCE.HOST,
  port: DATASOURCE.PORT,
  username: DATASOURCE.USERNAME,
  password: DATASOURCE.PASSWORD,
  database: DATASOURCE.DATABASE,
  synchronize: true,
  logging: true,
  entities: [User],
  subscribers: [],
  migrations: [],
});

AppDataSource.initialize()
  .then(() => {
    console.log("DB 초기화 완료.");
  })
  .catch((e) => console.log("DB 초기화 오류", e));

• 이후 오류가 나서 서버의 로그를 확인했더니 아래와 같은 오류 메세지를 얻을 수 있었다.

DB 초기화 오류 Error: Access denied for user 'root'@'localhost'
    at Packet.asError (/javascript-p4-bmtown/server/node_modules/mysql2/lib/packets/packet.js:728:17)
    at ClientHandshake.execute (/javascript-p4-bmtown/server/node_modules/mysql2/lib/commands/command.js:29:26)
    at PoolConnection.handlePacket (/javascript-p4-bmtown/server/node_modules/mysql2/lib/connection.js:456:32)
    at PacketParser.onPacket (/javascript-p4-bmtown/server/node_modules/mysql2/lib/connection.js:85:12)
    at PacketParser.executeStart (/javascript-p4-bmtown/server/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/javascript-p4-bmtown/server/node_modules/mysql2/lib/connection.js:92:25)
    at Socket.emit (node:events:527:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9) {
  code: 'ER_ACCESS_DENIED_NO_PASSWORD_ERROR',
  errno: 1698,
  sqlState: '28000',
  sqlMessage: "Access denied for user 'root'@'localhost'",
  sql: undefined
}

해결 사례

1. mysql_native_password 로 접근할 수 있도록 처리한다.

• 참고 자료 : https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost

On some systems, like Ubuntu, MySQL is using the Unix auth_socket plugin by default.

Basically it means that: db_users using it, will be "authenticated" by the system user credentials.

• ubuntu와 같은 몇몇 시스템에서는 기본값으로 UNIX auth_socket 플러그인을 사용한다고 한다.
• 네이버 클라우드에 설정한 ubuntu 서버에서 새로 mysql을 설치했기 때문에 아마 기본값으로 사용할 것이라고 예상했고, 실제로 그러했기 때문에 변경해주었다. (아마 기존 로컬에서는 먼 옛날 설정했지 않을까? 하는 추측도 있다.)

1. 먼저 터미널에서 mysql에 접속한다.

$ mysql -u root 혹은 mysql -u root -p

2. 설정 값 확인 : root의 plugin이 auth_socket 으로 되어 있는 부분을 볼 수 있다.

mysql> USE mysql;
SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| root             | localhost | auth_socket           |
...

3. 설정 변경 및 저장 : 변경 후 root의 plugin이 mysql_native_password 으로 변경하고 저장한다.

mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| root             | localhost | mysql_native_password |
...

2. "Access denied for user 'root'@'localhost'" (using password: YES) 에러 해결

• 참고자료1 : https://stackoverflow.com/questions/49194719/authentication-plugin-caching-sha2-password-cannot-be-loaded

• 참고자료2 : https://www.lesstif.com/dbms/mysql-2054-mysql-8-authentication-policy-89556039.html

• 1번의 방법으로 에러를 해결한 줄 알았는데, 마찬가지로 에러가 발생했다.

• 기존 에러와 차이점은 using password: YES 라는 에러 메세지가 추가되어서 출력되었다.

• 간단한 해결 방법은 비밀번호를 바꾸는 것이었다.

• 변경할-비밀번호 부분에는 기존에 사용하던 비밀번호를 그대로 입력하면 된다.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '변경할-비밀번호';
mysql> FLUSTH PRIVILEGES;

• mysql 8버전 이상부터는 암호 인증 방식에 caching_sha2_password 방식을 도입했는데, 이를 통해 패스워드 해싱에 SHA256 방식을 적용한다고 한다.
• 예전 방식인 mysql_native_password 를 사용하기로 변경하면, 기존에 생성된 계정인 root는 비밀번호를 새로 생성해주어야 한다.