[퍼옴]MySQL (MariaDB) 외부 접속하기 with 삽질기

MariaDB(MySQL)를 설치 후, sql 콘솔을 통해 DB를 관리할 수도 있겠지만, 그건 저처럼 라이트한 사용자에겐 너무 귀찮고 불편한 일입니다. 웹호스팅을 이용하면 보통 phpmyadmin 이라는 웹전용 sql 툴을 기본적으로 제공하고 있지만, 웹전용이라 느리고 답답한 감이 없지 않았는데요. 좀 더 편하고 반응도 빠르게 작업할 수 있도록 전용 프로그램을 사용하기로 했습니다. 전용 프로그램을 사용하려면 외부접속이 가능하도록 설정을 몇가지 해주어야 하는데요. 이 작업을 하면서 두어시간 삽질을 하게 된 내용까지 포함해서 포스팅을 남깁니다.

먼저, MariaDB(MySQL)을 설치하면 기본적으로 외부 접속이 불가능하게 되어 있습니다. 외부 접속을 허용한다는건 보안상 위험성이 있는 일이니 당연한 거겠죠. 일단, 관리툴을 사용하려면 원격 접속을 가능하도록 해야합니다. 먼저 로컬상에서 mysql 콘솔로 들어가서 아래 쿼리를 순서대로 입력하여, 외부 접속이 가능하도록 루트 계정의 사용자 호스트 값을 추가해 줍니다.

#1.

INSERT INTO mysql.user (host,user,password) VALUES ('192.168.%','root',password('패스워드'));

위 내용에서 확인할 점은, 192.168.% 부분입니다. 제 경우에는 CentOS가 설치된 서버와 같은 내부 망에 있는 IP 대역에게만 허용하도록 설정한 겁니다. 만약 모든 IP를 허용해주려면 이 부분을 % 이렇게 변경해 주면 됩니다. 물론, 어느 특정 IP에게만 허용할 수도 있습니다.

#2.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.%';

위에서 추가한 사용자 환경에서 모든 DB와 테이블에 접근할 수 있도록 권한을 설정해 줍니다. 이 부분도 위와 동일하게 IP를 설정을 해주면 됩니다.

#3.

FLUSH PRIVILEGES;

권한 설정을 적용합니다.

일단, 이것으로 외부에서 접속할 수 있는 root 계정이 준비가 되었습니다. 하지만 끝이 아닙니다. MariaDB의 설정 파일에서 외부접속을 차단하고 있는지 확인하여 설정을 변경해야 합니다. vi 등의 편집기를 통해 /etc/my.cnf 파일을 열어줍니다. 설정 파일 안에 아래와 같은 내용이 있다면 해당 라인 맨 앞에 # 을 넣어 주석 처리 해줍니다. 만약, 해당 내용이 없다면 넘어갑니다.

bind-address = 127.0.0.1

bind-address는 보안을 위해 DB에 직접 연결할 수 있는 IP를 잡아주는 속성입니다. 고로, 이 부분이 활성화되어 있으면 외부에서의 접근이 불가능하게 됩니다. 이 부분이 있다면, 주석 처리 해줍니다. 수정 후 저장하였다면, 데몬을 재시작해주어야 합니다.

service mysql restart

이제, 마지막 작업이 남았습니다. 

리눅스(CentOS) 방화벽에서 mysql이 사용하는 3306 포트를 열어주어야 합니다. 제가 이 부분에서 문제가 생겨 두어시간동안 삽질을 했습니다;;

방화벽 설정은 매우 간단합니다. iptables를 통해 3306 포트를 ACCEPT 시켜주고 설정값을 저장한 후 iptables를 재시작 해주면 끝납니다.

iptables -A INPUT -p tcp --dport 3306 -j ACCEPT service iptables save service iptables restart

*여기서부터 저의 삽질기 시작입니다.

원리상 DB 외부 접속에 대한 설정을 마치고 방화벽을 해제해 주면 외부 접속이 되어야 하는데, 제 경우에는 계속 Host '***' is not allowed to connect to this MySQL server 라는 에러만 토해내며 외부에서 접속이 되지 않았습니다. 처음엔, 외부 접속 가능 IP 대역을 192.168.로 해놓은게 문제였나 싶어 모두 허용(%)으로 다시 바꿔도 봤지만 소용 없었습니다.

도대체 어디에 문제가 있나 싶어 다른 서버에서 telnet 으로 CentOS 서버에 포트별로 접근을 시도해 보았습니다. CentOS에서 현재 허용해준 http, ssh, mysql 세 포트 중, http와 ssh 포트로는 접근이 되는데 mysql(3306) 포트만 접근이 되질 않았습니다.

[root@myCentOS /]# iptables -L Chain INPUT (policy ACCEPT) target prot opt source destination ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED ACCEPT icmp -- anywhere anywhere ACCEPT all -- anywhere anywhere ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:ssh ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:http REJECT all -- anywhere anywhere reject-with icmp-host-prohibited ACCEPT tcp -- anywhere anywhere tcp dpt:mysql

iptables -L 명령으로 확인해 봐도 mysql ACCEPT라고 되어있는데 대체 왜 되질 않는건지 알 수가 없었는데요... 문제는 바로, 그 윗줄에 있었다는 걸 한참 구글신께 빌고 빌어 알게 되었습니다. 문제는 mysql 설정이 REJECT 하위에 존재하기 때문에 반영되지 않았던 겁니다. 이 순서를 바꿔줘야 하는데요. vi 등의 편집기로 /etc/sysconfig/iptables 파일을 열면 설정값들이 저장되어 있는데, 여기서 mysql 포트의 정책을 REJECT 상위에 입력해주면 됩니다. 수정 후, service iptables restart 로 재시작을 해주면 됩니다.

[root@myCentOS /]# iptables -L Chain INPUT (policy ACCEPT) target prot opt source destination ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED ACCEPT icmp -- anywhere anywhere ACCEPT all -- anywhere anywhere ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:ssh ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:http ACCEPT tcp -- anywhere anywhere tcp dpt:mysql REJECT all -- anywhere anywhere reject-with icmp-host-prohibited

정책의 순서를 변경한 이후의 목록입니다. mysql 정책이 REJECT 위로 올라가 있습니다. 이렇게 설정을 변경하고 나니 언제 그랬냐는듯이 외부에서 mysql 접속이 가능해졌습니다;;; 

이제 HeidiSQL 등과 같은 mysql UI 툴을 이용해서 빠르고 편하게 DB를 관리하실 수 있게 되었습니다.

*훗날, DB의 외부접속을 차단하고자 한다면 방화벽 설정에서 mysql 포트를 차단해주고, /etc/my.cnf 파일에서 bind-address 속성을 설정해 주면 됩니다. 가장 처음에 작업했던 root 계정의 호스트 설정 내용을 지우고 싶다면 mysql 콘솔 상태에서 아래와 같은 쿼리를 입력해 주시면 됩니다.

DELETE FROM mysql.user WHERE host='192.168.%' AND user='root';
FLUSH PRIVILEGES;

이상으로 mysql 원격 접속을 위한 설정과 삽질기를 마치겠습니다.

원본 : http://blog.naver.com/PostView.nhn?blogId=comtylove&logNo=220716050177&redirect=Dlog&widgetTypeCall=true