원격 IP로 MySQL(MariaDB)에 접속이 되지 않을 때의 해법
※ Ubuntu 16.04 MariaDb 10.0의 상황과 원격 IP의 주소가 111.222.33.44라고 가정한다.
Java에서 다음과 같이 원격의 MySQL에 접속을 시도할때
String url = “jdbc:mysql://111.222.33.44:3306/나의DB명”;
Connection refused 에러가 발생하거나 telnet을 이용해서 MySQL이 설치된 IP의 3306포트로 접속시 연결이 되지 않을 때의 해법이다.
네트워크의 연결 상태에따라 다양한 이유가 있을 수 있다. 예를들어 공유기 아래 서버가 물려있을 때 공유기에서 3306에 대한 포트 포워딩이 되어있지 않다면 아래의 방법이 통할수 없을 것이다.
다양한 원인들이 있으나 본 포스트는 3306 포트에 listening이 외부 IP로 설정되지 않고 127.0.0.1로 설정되어 있을 경우에 대한 해법이다.
telnet을 이용해서 해당 IP로 3306포트로 접속이 되는지부터 확인해보자.
일단 Windows에서는 기본적으로 telnet을 사용할수 없는 상태로 설정되어 있다. 다음과 같이 하여 telnet을 사용할수 있도록 변경한다.
제어판 – 프로그램 제거 또는 변경 – Windows 기능 켜기/끄기 – 텔넷 클라이언트 체크
MS-DOS 창에서 telent으로 다음과 같이 먼저 확인해 본다.
telnet 111.222.33.44 3306
연결 대상 111.222.33.44…호스트에 연결할 수 없습니다. 포트 3306: 연결하지 못했습니다.
SQLyog에서 접속시도해 보면 다음과 같은 에러가 발생한다.
오류 번호 2003 Can’t connect to MySQL server on ‘111.222.33.44’
이럴 경우 MySQL이 외부 IP의 3306포트로 접속이 가능하지 않은 상태이다.
아래와 같이 자기 자신의 IP(여기서는 111.222.33.44)로 3306 포트로 접속시 접속을 시도했을 때 접속이 되지 않는다면 외부 IP에서 3306포트로 접속이 가능하지 않은 상태라는 뜻이다.
물론 아래의 접속으로 접속이 되기 위해서는 사전에 아래 명령어에 의해서 root가 외부 IP로 접속할수 있는 권한 설정이 mysql 데이터베이스에 설정 되어 있어야 하는 걸 전제로 한다.
MariaDB [(none)]> grant all privileges on *.* to ‘root’@’%’ identified by ‘비번’;
MariaDB [(none)]> flush privileges;
만일 위와 같이 root가 외부 IP로의 자유로운 접속이 허락되어 있지 않으면 아래의 명령으로 접속이 안되는 건 당연한 일이다.
다음과 같이 MySQL에서는 권한 설정이 아래와 같이 정상적으로 되어 있다고 가정해 보자.
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> select host, user, password from user;
+———–+——+——————————————-+
| host | user | password |
+———–+——+——————————————-+
| localhost | root | *8EA3CE4E6710007B6FD72BC385D48F3D985FD258 |
| % | joe | *8EA3CE4E6710007B6FD72BC385D48F3D985FD258 |
| % | root | *8EA3CE4E6710007B6FD72BC385D48F3D985FD258 |
+———–+——+——————————————-+
3 rows in set (0.00 sec)
MariaDB [mysql]> select host, user, db from db;
+——+——+——-+
| host | user | db |
+——+——+——-+
| % | joe | mysql |
+——+——+——-+
1 row in set (0.00 sec)
일단 여기서는 root에 대한 외부 IP로의 접속 권한이 설정되어 있다는 걸 전제로 했을 때 아래와 같이 접속이 안된다면 현재 MySQL(MariaDB)는 외부 IP로의 접속을 위한 설정에 문제가 있다는 것이다.
root@xxx:/home/joe# mysql -h 111.222.33.44 -P 3306 -uroot -p
Enter password:
이 경우 다음과 같은 에러가 발생한다.
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.0.107’ (111 “Connection refused”)
이상과 같이 정상적으로 외부 IP로의 접속이 허용되어 있음에도 접속 불능사태가 발생한다면 다음 명령으로 3306 포트에 대해 listening 상태를 확인해 보자.
# netstat -ntlp | grep 3306
을 하면
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 1235/mysqld
이렇게 나오면 localhost에서는 접속이 되지만 원격 IP에서는 접속이 안된다.
이 문제에 대한 해법은 아래 파일에서
/etc/mysql/mariadb.conf.d/50-server.cnf
bind-address = 127.0.0.1 를 주석처리하고
bind-address = 0.0.0.0 을 추가한다. 다음과 같이
#bind-address = 127.0.0.1
bind-address = 0.0.0.0
변경된 사항을 적용하기 위해서
# service mysql restart
를 하면된다고 검색해 보면 나와있는데 이 방법으로는 netstat으로 확인해보면 여전히 3306에 대한 listening을 127.0.0.1로되어 있다.
현재 가장 확실한 방법은 서버를 재부팅하면 변경된 설정 값이 제대로 적용이되고 외부 IP로 접속이 정상적으로 된다.
재부팅 없이 되는 방법을 아직은 모르겠다….;;;