MySQL-Nodejs 원격접속 총정리

프로젝트를 진행하다보니 내 로컬 DB인 MySQL에 외부에서 접근할 수 있어야 했다. 이틀동안의 삽질 끝에 외부접속을 성공시켰고, 까먹기 전에 호다닥 정리해봤다. 아 그리고 윈도우11에서 진행했음을 미리 알려드린다.

CMD에서 MySQL 접속

mysql -u 유저명 -p

cmd창에서도 mysql에 접속할 수 있다. 유저명과 패스워드는 보통 mysql을 설치할 때 설정한다. 유저명을 바꾸지 않았다면 root가 기본값이다. mysql 명령어가 먹지 않으면 환경변수를 설정해야 한다.

환경변수 설정

• ‘내 컴퓨터’ 우클릭 후 ‘속성’ 선택 → 고급 시스템 설정 → 새로 뜬 창 하단의 환경변수 → 시스템 변수의 Path 편집 → MySQL 설치 시 경로 변경을 하지 않았다면, C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin 을 추가하면 된다.
• cmd 창을 껐다가 새로 연다.
• MySQL에 접속한다. 정상적으로 접속되면 입력줄 앞부분이 mysql> 로 바뀌게 된다.

자주 쓰일 명령어

// 사용할 수 있는 데이터베이스 확인
show databases;
// 데이터베이스 선택
// mysql 데이터베이스에서는 db의 관련정보를 확인할 수 있다.
use mysql;
// 사용자 목록 보기 (사용자명, 호스트, 플러그인, 비밀번호 순)
select user, host, plugin, authentication_string from user;
// 사용자 생성
create user '사용자명'@'접근할ip' identified by '비밀번호';
// 권한 설정
grant 부여할권한 on '데이터베이스'.'테이블' to '사용자명'@'접근할ip';
// 변경사항 적용
flush privileges;
// mysql 종료
exit   // or quit

외부접속 허용 유저 생성

MySQL은 설치 시 기본적으로 외부에서의 접속을 허용하지 않기 때문에, 외부접속을 허용하는 사용자를 직접 생성해야 한다.

CREATE USER '유저명'@'%' IDENTIFIED BY '비밀번호';

@ 바로 뒤에 어떤 아이피를 통해 mysql에 접속할 수 있는지 설정할 수 있다. 이를 호스트라고 한다.

• % : 아이피 관계없이 접근 허용
• localhost : 로컬에서 접근 허용
• 특정 아이피 : 특정 아이피에서 접근해야만 허용
  생성한 사용자에게 권한을 부여한다.

// GRANT [부여할 권한] ON '데이터베이스명'.'테이블명' TO '유저명'@'접속호스트';
GRANT ALL ON *.* TO '유저명'@'%';

환경 설정을 변경할 경우 반드시 아래의 명령어를 입력해야 한다. 아니면 적용되지 않는다!

FLUSH PRIVILEGES;

현재 사용 중인 MySQL의 캐시를 지우고 새로운 설정을 적용하기 위해 사용합니다. 이 명령어를 사용하려는 사용자는 reload권한을 가지고 있어야 합니다.
즉 MySQL의 환경 설정을 변경할 경우, MySQL의 재시작없이 변경한 설정부분을 적용시키고자 할 때 사용한다.

서버 외부접속 설정

외부의 사용자가 내 컴퓨터의 로컬DB에 접근하기 위해서는 먼저 방화벽을 열어줘야 한다.

검색 중 my.ini 파일(맥의 경우 my.cnf)의 bind_address 부분을 주석처리 해야한다는 글이 있었는데, mysql 8.0 이상부터는 따로 설정할 필요가 없다.

방화벽 열기

1. 제어판의 Windows Defender 방화벽에 들어간다(윈도우에 방화벽 검색하면 된다). 그 후 고급 설정을 클릭한다.
   
2. 인바운드 규칙을 클릭하고 오른쪽에 새 규칙을 클릭한다.
   
3. 프로그램이 선택된 것을 보고 다음으로 넘어간다.
   
4. ‘찾아보기’ 버튼을 눌러 MySQL이 설치된 폴더를 찾아서 mysqld.exe를 선택한다. 설치 시 경로 변경이 없었다면 “C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe”에 있을 겁니다.
   
5. ‘연결 허용’이 선택된 것을 확인하고 ‘다음’버튼을 누른다.
   
6. 어떤 네트워크에서 이 규칙을 적용할 건지 선택할 수 있다.
   
7. 적당한 이름을 짓고 ‘마침’을 눌러 마무리한다.
   

공유기 DDNS(선택) 및 포트포워딩 설정

로컬 PC가 공유기와 연결이 되어있다면 DDNS 설정을 해두면 좋다. DDNS란 유동 IP를 고정 IP처럼 사용하게 해주는 서비스로, DDNS 설정으로 test.iptime.org라는 도메인을 등록해두면 IP가 변경되어도 test.iptime.org를 입력하면 변경된 IP로 접속을 시켜주는 역할을 하는 것이다. DDNS 설정과 포트포워딩 방법을 설명하겠다.

참고로 DDNS에 등록되는 IP Address는 https://ip.pe.kr/ 에 나오는 공인 IP다. Nodejs에서 Mysql 연결하는 부분의 Host에도 이 공인 IP를 넣어줘야 한다.

공유기 DDNS 설정
iptime 공유기를 기준으로 설명드린다.
1. http://192.168.0.1 주소로 접속 후 관리 도구 클릭한다.

2. 펌웨어 최신 버전 확인
좌측 고급설정 — 시스템 관리 — 펌웨어 업그레이드에서 펌웨어 버전을 확인 후 최신 버전으로 업그레이드되어 있는지 확인한다. DDNS 설정 시 펌웨어 버전에 따라 지원을 하지 않거나 등록을 해도 접속이 차단되는 경우가 있기 때문이다.

3. DDNS 설정
고급설정 — 특수기능 — DDNS 설정에 들어가서 다음 빈 항목들을 채우면 된다.

• Host Name : 도메인 네임으로 사용할만한 적당한 것으로 작성한다.
• User ID : 실제 메일 수신이 가능한 이메일을 작성한다.
  DDNS를 등록하신 후 “Successfully updated(정상등록)” 되면 잘 등록된 것이다.

포트포워딩 설정
MySQL의 포트는 3306으로 외부 포트와 내부 포트를 설정해 주면 된다.

Internal IP는 cmd에 ipconfig 입력해서 나오는 “무선 LAN 어댑터 Wi-Fi의 IPv4주소” 값을 적어주면 된다.
이후 MySQL에 원격 접속을 시도해보면 된다. 터미널을 사용해서 접속이 가능한지 테스트 해보자. 만약 DDNS 설정이 번거롭다면 호스트 부분에 공인IP를 대신 넣어주면 된다.

// 형식 : mysql -u 유저명 -h 접근할DB호스트 -p
mysql -u 유저명 -h test.iptime.org -p
or
mysql -u 유저명 -h 공인IP -p

Nodejs 설정하기

위의 과정을 통해 외부에서 나의 로컬 DB로의 접속을 성공했다면 이제 Nodejs에도 DB접속을 위한 변수들을 등록해주면 된다. .env 파일에 다음의 내용을 작성하고 DB를 연결하는 부분에 값들을 불러오면 된다.

SERVER_PORT = 3000
DB_HOST = 공인IP or DDNS 도메인
DB_PORT = 3306
DB_DB = 사용할 데이터베이스의 이름
DB_USER = 유저명
DB_PASSWD = 해당유저의 비밀번호

나는 sequelize를 사용했기 때문에 config.js에서 값을 불러왔다.

만났던 에러들

프로토콜 관련 에러

‘Client does not support authentication protocol requested by server; …’

이 문제는 클라이언트 프로그램에서 mysql 패스워드 플러그인 “caching_sha2_password”를 소화하지 못해서 생기는 오류다. 해당 유저의 패스워드 플러그인을 바꿔주면 된다.

mysql > ALTER USER '사용자명'@'%' IDENTIFIED WITH mysql_native_password BY 'Xptmxm1212!@';
mysql > FLUSH PRIVILEGES;

이후 플러그인이 바뀌어있는 것을 확인할 수 있다.

mysql > SELECT user, host, plugin FROM user;

비밀번호 에러

(28000): Access denied for user ‘root’@‘localhost’ (using password: YES)

mysql에서 옳은 비밀번호를 입력했음에도 불구하고 위와 같은 에러가 발생할 수 있다. 저 에러는 두 가지 경우로 확인할 수 있다.
1. 비밀번호를 틀리게 입력한 경우
2. 설정한 비밀번호의 플러그인이 mysql 패스워드 플러그인과 맞지 않아 인식하지 못하는 경우
1번의 경우는 비밀번호를 다시 재입력하거나, 아래 명령어로 비밀번호를 바꿀 수 있다.

// 버전에 따라 password가 authentication_string으로 되어있다.
select host, user, authentication_string from user;
// 비밀번호 정책 확인 후 비밀번호를 등록할 수도 있다.
show variable like 'validate_password%';
// 비밀번호 변경
// 필드명이 password인 경우 password=password('비밀번호')로 바꿔서 작성한다.
update user set authentication_string=password('비밀번호') where user='유저명';
// 변경사항 적용
flush privileges;

2번의 경우에는 등록했던 비밀번호의 플러그인을 바꿔서 다시 등록해야 한다. 기존의 비밀번호를 그대로 입력해도 되고, 새 비밀번호로 바꿀 수도 있다.