• 목차

  1. MySql 원격접속 시도

  2. 서버에서의 문제

  3. 결과..

  4. 요약

  ---

집에 있는 데스크탑을 DB 서버로 활용하고 학교 근로나 밖에서 공부할 때 잠깐 이용하기 위해 원격 접속을 시도하던 중 접속 오류가 계속되어 글로 보기 위해 작성하게 됐다.

1.MySql 원격접속 시도

여태까지 집에서 서버 컴퓨터로 계속 DB를 이용해 왔고 노트북으로 외부접속을 한다해도 어디까지나 같은 LocalHost인 상황에서 접속시도를 했다.

오늘 처음 데스크탑을 켜놓고 근로를 와서 공부겸 Mysql을 접속하려 시도하니오류발생..

Failed to Connect to MySQL at [SERVER IP]:[PORT] with user [USER NAME]
Unable to connect to [SERVER IP]:[PORT]

구글링으로 오류문을 찾아보니 아무래도 원격접속은 허용이 되지 않은것 같다.
원격접속 허용이 안되는 경우는 생각보다 꽤 많은데

1. MySQL 서버에서 로컬접속만 허용하는 경우
2. 서버측 방화벽이 닫혀 있는 경우
3. 클라이언트측에서 outbound 방화벽이 막힌경우 (외부 3306포트 접속 막혀있는 경우)

WHY?! 접속이 안되는지 파악이 되지 않아서 맥북 터미널로 외부 접속을 시도해 보면

❯ mysql -h [SERVER IP] -u hoseok -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.31.171:3306' (60)

❯ mysql
ERROR 1045 (28000): Access denied for user 'lhoseok'@'localhost' (using password: NO)

첫 번째는 서버에 접속을 하기 위해 클라이언트에서 시도한 방법이고
두 번째는 클라이언트에서 로컬로 접속하기 위해 시도한 방법이다.

두 번째 오류는 간단하게 로컬로 접속하면 해결된다

❯ mysql -hlocalhost -u root -p

첫 번째 오류가 결국 문제의 핵심인 것 같다. 문제를 파악 했으니 차근차근 찾아 가보자!!

2. 서버에서의 문제

[2-1] MySQL 서버에서 로컬접속만 허용하는 경우

• cmd에서 mysql을 로컬(Windows)로 접속한다.

❯ mysql -uroot -p
[PASSWORD 입력 이후 mysql shell 접속]
mysql> use mysql
Database changed

mysql> select user, host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| newuser          | localhost |
| root             | localhost |
+------------------+-----------+

mysql은 설치시 기본으로 로컬에서 밖에 접근을 허용하지 않는다 host 목록을 보면 알 수 있다.

• 새로운 유저생성 및 모든 권한, 외부 아이피에서 접근가능하게 하는 명령어
  

mysql> CREATE USER '[USER_NAME]'@'%' IDENTIFIED BY '[USER_PWD]';
mysql> GRANT ALL PRIVILEGES ON *.* TO '[USER_NAME]'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

MySQL 8 버젼 이상부터는 GRANT문을 따로 적용해야함

본인은 hoseok이라는 user로 생성했다.

mysql> select user, host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| hoseok           | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| newuser          | localhost |
| root             | localhost |
+------------------+-----------+

위와 같이 hoseok이라는 user와 모든 아이피 접속을 허용함(%)

mysql 8.0이상 부터는 my.ini(windows), my.cnf(ubuntu, os X) 파일에 bind-address를 주석처리 할 필요가 없다.


my.ini파일은 C:\Program Files\MySQL\MySQL Server 8.0\my.ini 파일을 보면 된다. 설치경로를 따로 저장하거나 버전이 다르다면 변경해서 열어서 확인해보시길!!

이후 cmd에서 netstat -ano | find "3306" 으로 3306을 이용하는 포트를 보면 모든 아이피에 대해 LISTENING 상태임을 알 수 있다.

[2-2]. 서버의 방화벽이 닫혀 있는 경우

문제를 파악 했으니 차근차근 찾아 가보자!!
2번 문제의 경우 우선 서버 측(자취방데스크탑..) 방화벽 상태를 알기위해 nmap, 텔넷접속을 이용해보자
nmap, telnet이 설치되어 있지 않다면 brew명령어를 이용해 설치하자
OS X에 brew 설치하기

• nmap, telnet 설치

❯ brew install nmap
❯ brew install telnet

• 서버 상태와 방화벽 확인하기

❯ nmap [SERVER IP] -p 3306 -Pn
  Starting Nmap 7.92 ( https://nmap.org ) at 2021-11-19 15:32 KST
  Nmap scan report for [SERVER IP]
  Host is up.
  PORT     STATE    SERVICE
  3306/tcp filtered mysql

• 텔넷으로 방화벽 확인하기

❯ telnet [SERVER IP] 3306
Trying [SERVER IP]...
telnet: connect to address [SERVER IP]: Operation timed out
telnet: Unable to connect to remote host

STATE = filtered: 방화벽이나 필터에 막혀 해당 포트의 open, close 여부를 알 수 없을 때
클라이언트에서 수행시 위와 같이 결과가 나왔다 즉, 서버는 열려있으나 서버의 방화벽이 닫혀있음을 알 수 있다. (방화벽은 열려있었다.. 왜 안되는 걸까 ㅠ)
Windows에서 개발자 모드 on 및 telnet 허용까지 마침 방화벽도 해제하고 시도해봤지만 동일한 결과 ㅠㅠ
텔넷 결과에 따른 상태확인 참조
방화벽 설정 하기

3. 결과..

일단 나는 같은 IP내에서는 접속이 가능하지만 다른 IP를 이용했을때(휴대폰 핫스팟 포함)
위에서 생성한 hoseok계정을 이용해 접속을 시도하면

mysql -h[SERVER IP] -uhoseok -p

ERROR 2003 (HY000): Can't connect to MySQL server on '[IP주소]:3306'
오류가 발생한다.

1. 방화벽 포트 열고
2. 모든 권한 부여한 유저 생성
3. bind-address처리 << my.ini파일에 해당항목 존재하지 않음
4. netstat -ano | find "3306" 에서 모든 IP에 대해 Listening 확인..
5. 서버 측 cmd에서 접속 시도시 접속 가능 (TCP연결 양호)
   -h 옵션에 localhost가 아닌 자신의 IP Address 입력시 접속 가능

왜 안되는지 모르겠습니다.. 학교 IP나 휴대폰 핫스팟이 3306포트를 막는걸까..
해결되면 추후에 내용을 추가하겠습니다.. ㅎㅎ 혹시 방법을 아시면 댓글로 알려주세요!!

++2021-11-22 문제해결!!

포트포워딩 미설정 문제로 밝혀졌습니다..
랜선을 직접 연결해서 사용하는 줄 알았는데 공유기를 거쳐서 사용하고 있었습니다 ㅠ
다만 의문인 점은 공유기 설정에서 설정한 IP주소는 192.168로 시작되고 실제 접속이 가능한 IP 주소는 121.127로 시작하네요 데스크탑이 와이파이가 아니라 랜선을 이용해서 실질적인 IP가 다른걸까요?? 더 찾아보고 알게되면 내용 추가하겠습니다!

4. 요약

내가 한 일 :


서버 측 (WIndows)
1. mysql 원격접속 허용 : 서버 측에서 user에 모든 권한 부여 및 모든 IP에서 접근 가능하게 grant option을 주었습니다 > %


2. 서버 측 방화벽 포트를 열었습니다. (인바운드 규칙에 3306포트 존재 확인, 방화벽을 아예 꺼보기도 함)


3. window라 my.ini파일에 bind-address옵션을 주석처리 하려했지만 mysql 8.0 버전이상은 파일내 목록에 존재하지 않습니다.


4. netstat -ano | find "3306" 으로 0.0.0.0:0 이 Listening상태입니다. (다만 걸리는건 0.0.0.0:* 이 아니라 0으로 표시되는데 이게 문제가 될까..?



클라이언트 측(OS X)

1. 서버를 열고 클라이언트 터미널에 아래 명령어 입력

❯ nmap [SERVER IP] -p 3306 -Pn  

방화벽을 해제해도 STATE상태가 계속 filtered로 나오더라구요 이것도 관련이 있는지 의심이 됩니다.

Starting Nmap 7.92 ( https://nmap.org ) at 2021-11-21 01:18 KST
Nmap scan report for 192.168.nate.com ([SERBER IP])
Host is up.
PORT     STATE    SERVICE
3306/tcp filtered mysql

2. 텔넷 접속해보기

❯ telnet [SERVER IP] 3306
Trying [SERVER IP]...
telnet: connect to address [SERVER IP]: Network is unreachable
telnet: Unable to connect to remote host

위의 텔넷 결과에 따른 상태확인 글을 참조해서 해석한 결과 방화벽을 열었음에도 방화벽이 열려있지 않다고 해석됨
(이 부분도 방화벽을 끈 상태에서 진행도 해보고, 개발자 모드 ON, 텔넷 허용까지 마친 후 진행했습니다.. ㅠ)

++2021-11-19 실패

결국 최종적으로 클라이언트 터미널에서
mysql -h[SERVER IP] -uhoseok -p로 접속 시도시

ERROR 2003 (HY000): Can't connect to MySQL server on '[IP주소]:3306 오류가 발생

++2021-11-22 성공!!

포트포워딩 미설정 문제로 밝혀졌습니다 아예 신경도 안쓰고 있던 부분인데 뒷통수 맞은 기분입니다 ㅎㅎㅎ 자세한 사항은 3.결과를 확인해보면 될 것 같습니다! 드디어 삽질이 끝났다