ProxySQL 설치하기

우리 프로젝트에서는 개발/사무망의 각 PC에서 내부망의 DB 서버로 접속하려 할 때 중간에서 검사? 및 보안 기능을 수행하는 역할로 ProxySQL을 골랐다.

원래는 DB세이퍼? 라고 하는 좋은 솔루션이 있다고 하길래 찾아봤으나 매우 비싸며 기업 대상이라는 정보를 보고 단념한 뒤 대체용으로 해당 솔루션을 고르게 되었다.

 

ProxySQL이란?

MySQL 및 MySQL 바탕의 포크 데이터베이스(MariaDB) 등을 대상으로 호환되는 솔루션이며, 애플리케이션과 DB의 중간에 위치하는 중계기 역할을 맡는다.

DB에 연결되어 이용하는 서비스의 수가 늘어나면 복잡도가 늘어나고 관리하기도 힘들어지는데, 이를 해결하기 위해 ProxySQL을 사용한다고 한다. 쿼리 라우팅이나 로드 밸런싱, 헬스 체크 기능 등을 통해 효율적인 연결 및 고성능/고가용성 서비스를 보장해주는 것이 바로 ProxySQL이란 오픈소스 프록시 솔루션이다.

 

일단 우리가 조사하면서 알아본 정보 한에서 최대한 활용하기 위해 노력해 봤으나, 부족한 부분이 많을 수 밖에 없으므로 감안이 필요하다.

 

설치하기

우분투에서 진행하였다.

프록시를 운용할 서버를 하나 만든 뒤, 레포지토리 키를 추가하고 등록한다.

sudo apt install -y gnupg2 xmlstarlet
wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key' | sudo apt-key add - 
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/$(lsb_release -sc)/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list

 

이후 ProxySQL을 이용하기 위한 패키지를 설치한 뒤 실행해 준다.

sudo apt-get update 
sudo apt-get install proxysql 
sudo systemctl start proxysql 
sudo systemctl enable proxysql

 

SQL 관리 계정으로 접속한 뒤, 연결할 DB 정보를 넣는다.

이후 불러오기와 저장을 실행한다;.

mysql -u admin -padmin -h 127.0.0.1 -P 6032
insert into mysql_servers(hostgroup_id, hostname, port) VALUES (1, ‘DB_IP', 3306);
load mysql servers to runtime;
save mysql servers to disk;

 

각 개발/사무PC에서 DB에 접근할 때 쓸 사용자 계정을 만든다.

(values 뒤에 DB의 ID/PW를 넣는다)

insert into mysql_users(username, password, default_hostgroup) values ('user_이름', 'user_비밀번호', 1);

우리는 서비스의 방대성보다는 각각의 기능 구현에 중점을 뒀으므로 DB는 2개를 만들었다.

각 DB마다 접근할 수 있는 계정을 나누기 위해 2개를 만들었다.

(여기서 지정하는 호스트그룹 번호랑 proxy에서 지정하는 호스트그룹 번호는 같아야 함)

 

추가한 계정들의 설정은 반드시 불러오기 및 저장을 해 준다.

load mysql servers to runtime;
save mysql servers to disk;
load mysql servers to runtime;
save mysql servers to disk;

 

연결할 DB서버로 가서 똑같이 계정을 만들어 준다.

(프록시에서 만든 계정이랑 똑같이)

create user 'user_이름'@’WAN_IP' identified by 'user_비밀번호';

 

접근할 수 있는 데이터베이스 권한을 부여한다.

(일단 여기선 모든 DB 목록에 접근할 수 있도록 만듦)

grant all privileges on *.* to ‘user_이름’@‘WAN_IP’;
flush privileges;

 

 

기본 세팅하기

우리는 DB를 비롯한 내부망은 PFsense 아래, DB에 접속할 개발/사무PC들은 OPNsense 아래 물려있다. 두 UTM 사이를 왔다갔다 해야 하므로, 서로의 WAN IP를 게이트웨이로 추가하는 작업을 진행했다.

 

(1) PF에서

 

(2) OPN에서

 

이후 db에서 외부 접속을 허용해 주기 위해 db서버의 콘솔창에서 /etc/mysql/mariadb.conf/ 경로로 들어간 뒤, bind-address 값을 127.0.0.1 =>0.0.0.0으로 바꿔주었다.

 

각 방화벽에서 서로를 향한 규칙을 열어준다.

(1) PFsense에서

하위에 DB가 있으므로 WAN 룰에서 DB로 들어오는 방화벽 규칙을 설정
- 개발망 쪽에서 프록시에 접속할 땐 6033번 포트를 쓰지만, 프록시에서 db로 갈 땐 본래의 3306번 포트를 쓰므로 도착지 포트를 3306으로 지정해 준다.

(왜 WAN IP?: 각 방화벽이 서로의 WAN IP를 게이트웨이로 지정 + 서로의 방화벽으로 갈 때 NAT를 쓰게 되어있기 때문에 ProxySQL IP에서 출발해 OPN을 빠져나가도 PF쪽에선 그 프록시가 속해 있는 WAN IP가 출발지로 찍히므로 OPN의 WAN IP만 인식하게 됨.

그 때문에 DB 쪽이나 PF 쪽에서 개발/사무망 쪽 계정 생성 및 권한 부여를 할 때 IP를 ProxySQL로 잡으면 인식을 못 함)

 

DB 쪽 룰에서도 프록시 방향으로 룰을 열어준다.

 

(2) OPNsense에서

하위에서 Proxysql이 개발pc에서 요청하는 db 접근 및 수정 요청을 대신 받을 것이므로 개발망 vlan에서 db로 향하는 규칙을 열어준다.

관리자 계정인 6032 포트는 프록시서버에서만 접속할 수 있도록 제한했다.

 

서로를 실제로 인식하는지 확인하기 위해 proxysql에서 ping과 nc 테스트 진행해 봤다.
(ping 192.168.30.xx<DB IP 대역> / nc -zv 192.168.30.xx 3306)
(주의: 이 땐 DB서버에서 쓰는 포트를 통해 테스트하기 때문에 pf에서 룰의 포트를 잠깐 모두 열어줘야 함, 테스트가 잘 끝나면 다시 6033만 열어두기

 

이번엔 DB 쪽에서 ProxySQL과의 계정 연동이 잘 되어있는지 확인했다.

(아까 위에서 등록한 계정 및 WAN IP가 잘 적혀있는지 확인하기)

 

이번엔 다시 Proxy 쪽에서 각 db와 연결된 프록시들의 상태를 확인한다.
상태 확인 쿼리

select hostgroup_id, hostname, status from runtime_mysql_servers;

(만약 ONLINE이 아닌 SHUNNED 등으로 떠 있다면 아래 사진처럼 load mysql servers to runtime; / save mysql servers to disk; 를 순서대로 친 뒤 다시 확인하기)

 

이제 테스트용 개발pc에 접속하여, 프록시를 통해서 db에 접속이 되는지 확인해 본다.
mysql -u (접속할 계정 이름) -p -h 192.168.120.10 -P 6033
(proxysql의 IP를 거쳐가야 하므로 프록시 서버의 IP를 입력, 포트는 프록시 포트인 6033)

 

proxysql 서버에서도 접속할 수 있다.
자신의 ip를 통해서 db에 접근하므로 명령어는 아래와 같이 치기(ip를 루프백으로)
mysql -u (접속할 계정 이름) -p -h 127.0.0.1 -P 6033

 

 

모니터링

각 DB들의 서비스 정상 가동 여부 등을 확인하는 모니터링 계정도 만들어 주자.

먼저 각 DB에서 아래와 같이 쿼리를 입력해 DB를 헬스체크할 모니터링 계정을 만든다.

# 권한을 최소화하기 위해 usage, repliccation client 쿼리문을 이용함
create user '모니터링 계정 이름'@'프록시SQL의 IP' identified by '모니터링 계정 비밀번호';
grant usage, replication client on *.* TO '모니터링 계정 이름'@'프록시SQL의 IP';
flush privileges;

(usage: 로그인만 가능하며, 데이터에 대한 조회 및 수정 권한은 없음)

(replication client: 백엔드 DB가 Master인지 Slave인지, 정상 가동 중인지, 로그 상태가 어떤지 등을 모니터링만 하는 권한)

 

 

그런 다음 이번엔 프록시 서버의 관리자 계정(6032번 포트)에 접속하여 아래와 같이 입력해 모니터링 계정을 인식시킨다.

SET mysql-monitor_username='모니터링 계정 이름';
SET mysql-monitor_password='모니터링 계정 비밀번호';

 

언제나 그렇듯이 설정을 해 준 다음엔 서버에 적용과 저장하는 것을 잊지 말자.

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

 

실전에선 매우 빠른 주기로 DB의 헬스체크를 한다고 하지만, 우리는 서버 자원의 한계에 따른 UTM 부하 고려, 직접 눈으로 천천히 보며 모니터링 여부를 체크해보고 싶어 헬스체크 주기를 10초(10000ms)로 설정하였다.

# 백엔드 DB의 헬스체크 주기를 10000(10초)로 설정하기
UPDATE global_variables SET variable_value='10000' WHERE variable_name='mysql-monitor_ping_interval';

 

그런 다음 모니터링 주기가 잘 설정되었는지 확인한다.

# 헬스체크 주기 설정 상태 살펴보기
SELECT * FROM global_variables WHERE variable_name='mysql-monitor_ping_interval';

 

이제 두 가지 쿼리문을 이용해 각각 DB로 접속이 잘 되는지, DB와 통신이 잘 되는지를 체크해보면 된다.

# DB로의 접속 테스트 현황 살펴보기
# 최근 10번 동안의 접속 헬스체크 결과 확인
select * from monitor.mysql_server_connect_log order by time_start_us desc limit 10;

 

# DB와의 통신 테스트 현황 살펴보기
# 최근 10번 동안의 통신 헬스체크 결과 확인
select * from monitor.mysql_server_ping_log order by time_start_us desc limit 10;

 

만약 연결이나 핑이 잘 이뤄지지 않는다면, 아래와 같이 쿼리문을 입력하여 통신 에러 기록을 확인해 볼 수 있다.

# 연결 실패 기록
SELECT hostname, port, connect_error, time_start_us 
FROM monitor.mysql_server_connect_log 
WHERE connect_error IS NOT NULL 
ORDER BY time_start_us DESC;

# 통신 실패 기록
SELECT hostname, port, ping_error, time_start_us 
FROM monitor.mysql_server_ping_log 
WHERE ping_error IS NOT NULL 
ORDER BY time_start_us DESC;

 

예시

딱히 연결 과정에서 문제가 생기지 않아서 지금은 기록이 비어 있다.

 

만약 각 모니터링 기록을 관리하는 테이블의 컬럼명을 모르겠다면 아래와 같이 입력하여 어떻게 지정되어 있는지 확인하면 된다.

# 서버 연결 로그 테이블 안의 컬럼들 확인하기
show create table monitor.mysql_server_connect_log;

# 서버 핑 로그 테이블 안의 컬럼들 확인하기
show create table monitor.mysql_server_ping_log;

 

 

ProxySQL과 연결된 UTM에서도 모니터링 통신 현황을 확인해 볼 수 있다.

 

트러블

(1)

프록시나 개발pc에서 db로 접속할 때 접속만 되고 조회가 안 되는 경우가 있었다.(timeout이 뜬다든지)

이럴 땐  유저의 호스트그룹 번호와 서버의 호스트그룹 번호가 맞게 짝지어져 있는지 확인해 본다.
(프록시 서버의 관리자 계정<6032>에서 확인할 수 있음)

select username, default_hostgroup from mysql_users;
select hostgroup_id, hostname, status from mysql_servers;

 

예를 들어 prsq는 현재 192.168.30.10 db 서버와 같은 1번으로 묶여 있음, 그런데 저 번호가 서로 다르게 부여되어 있으면 db의 내용을 조회할 수가 없게 되는 것이다.

본인의 경우 prsq 계정의 호스트그룹 번호가 뜬금 없이 10번으로 지정되어 있는 바람에 db의 내용을 전혀 조회할 수가 없었음.

이를 해결하기 위해 아래와 같이 호스트그룹을 재지정 해주면 된다.

set mysql_users set default_hostgroup=1 WHERE username='prsq';

 

(2)

각 방화벽에 서로의 ip를 게이트웨이로 지정한 뒤에도 프록시가 db의 내용을 불러오지 못하는 경우

위를 통해 호스트그룹의 미스 매칭을 해결한 뒤에도 계속해서 DB의 내용을 불러오지 못 했다.

이를 해결하기 위해 각 방화벽에서 정적 라우팅을 지정해 주었더니 해결 되었다.

(예를 들어 아래와 같이 OPM에서 DB 서버로 가는 네트워크 대역에 DB 서버가 거쳐가는 PF의 게이트웨이를 붙여주면 정상적으로 통신이 이루어짐)