ProxySQL 을 통한 MySQL 쿼리 라우팅 설정

지난 게시글에서는 MySQL connector for Java 의 Replication 커넥션 기능을 사용하여 쓰기/읽기 DB로 쿼리를 분산하는 내용으로 공유를 진행했습니다. 하지만 MySQL connector for Java 의 Replication 커넥션 기능을 사용하는 경우 쿼리를 쓰기/읽기 DB 중 어느 DB로 보낼지 소스상에서 일일이 지정해줘야하는 점이 단점이었는데요. 이번 게시글에서는 쿼리 라우팅 기능이 포함된 오픈 소스 MySQL 프록시인 ProxySQL을 이용하여 Query Rule을 설정하고 설정할 룰에 따라 자동으로 쿼리를 라우팅하는 설정 방법을 공유하도록 하겠습니다.

1. ProxySQL이란

ProxySQL은 쿼리 라우팅 기능을 제공하는 MySQL 및 MySQL 포크(Percona, Mariadb)의 오픈소스 프록시입니다.

공식 사이트 주소 - https://www.proxysql.com/
ProxySQL 설치 및 사용 가이드 - https://github.com/sysown/proxysql/wiki
ProxySQL 도커 이미지 - https://hub.docker.com/r/proxysql/proxysql

2. 테스트 환경

ProxySQL을 서버에 직접 설치하여 사용해도 관계 없으나 저는 도커 컨테이너로 실행하였으며, 쿼리를 프록시할 백엔드 데이터베이스 2개도 도커 컨테이너로 실행하였습니다. 마스터 DB 컨테이너와 슬레이브 DB 컨테이너는 리플리케이션 설정이 이미 되어 있는 상태입니다.

proxysql 컨테이너 - proxysql.wp
마스터 DB 컨테이너 - mysql.wp
슬레이브 DB 컨테이너 - mysql_rep.wp

3. proxysql 컨테이너에 적용할 설정 파일 작성

vim /etc/proxysql.cnf

datadir="/var/lib/proxysql"

admin_variables= # proxysql 관리자 설정
{
    admin_credentials="admin:admin;radmin:radmin" # 관리자 접속 정보(아이디:비밀번호), radmin의 경우 원격 접속에 사용되는 관리자 계정
    mysql_ifaces="0.0.0.0:6032"
}

mysql_variables= # 실제 proxy 를 수행하는 proxysql 데몬 관련 설정
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.5.30"
    connect_timeout_server=3000
    monitor_username="monitor"
    monitor_password="monitor"
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
}

4. proxysql 컨테이너 생성

아래 기본 용법이 도커 이미지에 있는 proxysql 컨테이너 사용 예시이며, 제 테스트 환경에서는 도커 네트워크 내부에서 접속이 이뤄질 것이기 때문에 "테스트 환경 컨테이너 생성" 부분과 같이 컨테이너를 실행했습니다.

# 기본 용법
docker run -p 16032:6032 -p 16033:6033 -p 16070:6070 -d -v /path/to/proxysql.cnf:/etc/proxysql.cnf proxysql/proxysql

# 테스트 환경 컨테이너 생성
docker run -d --network wp -v /var/lib/proxysql:/var/lib/proxysql -v /etc/proxysql.cnf:/etc/proxysql.cnf --name proxysql proxysql/proxysql

6032 - 관리자 포트
6033 - 사용자 포트
6070 - proxysql REST API 포트

5. proxysql admin 접속

mysql 명령어를 통해 proxysql 어드민으로 접속이 가능합니다. 제 테스트 환경에서는 mysql 컨테이너를 통해 proxysql 컨테이너로 접속했습니다. 어드민 접속 계정은 /etc/proxysql.cnf 파일에 설정한 대로 radmin/radmin 으로 접속 가능하며, 어드민 접속 포트는 6032입니다. (프로덕션 환경에서는 어드민 접속 정보를 변경하는 게 보안상 좋을 것 같습니다.)

# 원격지에서 proxysql admin 접속
mysql -uradmin -pradmin -h [proxysql_서버_주소] -P 6032 --prompt "ProxySQL Admin> "

# mysql 컨테이너에서 proxysql 컨테이너 관리자 포트로 접속
docker exec -it mysql mysql -uradmin -pradmin -h proxysql.wp -P 6032 --prompt "ProxySQL Admin> "

접속 시 아래와 같이 mysql 과 동일한 형태의 프롬프트가 보여집니다.

6. proxyql 설정

1) proxysql 의 작업 공간

proxysql 의 작업 공간은 크게 런타임, 메모리, 디스크 3개로 나뉘어집니다. 어드민을 통해 백엔드 서버, 유저 등의 설정을 변경하는 경우 변경 사항은 메모리에 저장되며 실제 서버에 바로 적용이 되진 않습니다. 이 경우 메모리에 발생한 변경사항을 런타임으로 불러와야 실제 서버에 적용이 됩니다. 디스크는 메모리의 변경 사항을 영구적으로 저장하기 위한 공간으로 proxysql 재시작 시 불러올 정보가 저장됩니다.

RUNTIME - 현재 서버에 로드된 설정
MEMORY - 서버가 로드된 이후 설정의 변경 사항(메모리에 변경된 내용으로는 실제 적용은 안됨)
DISK - 설정의 영구 저장을 위한 공간

2) 백엔드 서버 관련 작업

proxysql 이 쿼리를 프록시할 대상이 되는 백엔드 서버를 설정합니다. proxysql 은 설정 관련 테이블에 설정 내용을 레코드의 형태로 INSERT하는 방식으로 설정합니다. 백엔드 서버 설정이 기록되는 테이블은 mysql_servers 테이블입니다. 여기서는 hostgroup_id 컬럼을 통해 다수 호스트를 호스트그룹으로 묶을 수 있으며, 쿼리는 라운드로빈 방식으로 동일 호스트그룹에 있는 호스트에게 분산됩니다.

예를 들어 쓰기 DB 1개를 호스트그룹 0번으로 설정한 뒤, 읽기 DB 여러개를 호스트그룹 1번으로 설정하면 읽기 DB가 포함된 호스트그룹 1번으로 쿼리가 발생할 때, 다수 읽기 DB로 쿼리가 분산됩니다. 현재 테스트 환경에서는 쓰기 DB 1개, 읽기 DB 1개만 있기 때문에 쓰기 DB는 호스트그룹 0번으로, 읽기 DB는 호스트그룹 1번으로 설정하겠습니다.

# 기존 테이블 내용 확인
SELECT * FROM mysql_servers;

# 백엔드 서버 추가
INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (0, 'mysql.wp');
INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (1, 'mysql_rep.wp');

이제 "SELECT * FROM mysql_servers;" 쿼리로 백엔드 서버 리스트를 확인하면 아래와 같이 보여집니다.

이전 proxysql 의 작업 공간 관련 설명하였을 때에, 변경 사항은 메모리에 저장되며, 바로 서버에 반영되지는 않는다고 하였습니다. 변경 사항을 서버에 적용하기 위해 메모리에 저장된 변경사항을 런타임으로 불러옵니다. 만약 proxysql 서버 재시작 시 현재의 설정 사항을 불러올 수 있도록 하려면 메모리에 저장된 사항을 디스크로 저장합니다.

# 메모리에 있는 변경 사항을 런타임으로 불러오기
LOAD MYSQL SERVERS TO RUNTIME;

# 메모리에 로드된 변경 사항을 디스크에 저장
SAVE MYSQL SERVERS TO DISK;

3) 모니터 설정 관련 작업

/etc/proxysql.cnf 설정 파일 작성 시 모니터링에 대한 설정 값들을 넣어준게 있습니다.
모니터 용도의 계정으로 지속적인 백엔드 서버 체크가 가능하도록 백엔드 서버에서 monitor 사용자를 생성해줍니다.

(백엔드 DB에서)
create user 'monitor'@'%' identified by 'monitor';

마스터 서버 및 슬레이브 서버에서 제너럴 로그 확인 시 지속적으로 monitor 계정을 통해 접속하여 서버 상태를 체크하는 것을 확인할 수 있습니다. 이미지 중 위의 화면은 마스터 DB의 제너럴 로그이고, 아래 화면은 슬레이브 DB의 제너럴 로그입니다.

4) 유저 관련 작업

백엔드 서버 설정을 완료하였으니 이제 클라이언트에서 proxysql로, 또 proxysql 에서 백엔드 DB로 접근할 수 있도록 유저 설정을 추가해줍니다. 유저 관련 설정은 mysql_users 테이블을 통해 가능합니다. 유저 정보 입력 시 평문으로 암호를 입력하여도 런타임으로 불러오는 과정에서 암호화되지만, 가능하다면 먼저 원본 DB에서 암호화된 비밀번호를 확인하여 암호화된 비밀번호 자체를 입력하는 것이 좋습니다.

먼저 원본 DB에서 premisan 유저의 암호화된 비밀번호를 확인합니다.

확인한 패스워드를 통해 proxysql 어드민에서 사용자를 생성해줍니다.

기존 테이블 내용 확인
SELECT * FROM mysql_users;

premisan 유저에 대한 정보 추가
INSERT INTO mysql_users(username,password) VALUES ('premisan','*F61AA9AEDF4F84FD11EC634104B48D68528DB664');

백엔드 서버 정보를 추가할 때도 말씀드렸지만, 방금 설정한 내용은 바로 서버에 적용되지 않습니다. 메모리에 변경 사항을 런타임에 로드하는 명령어를 통해 실제 서버에 적용하고, 서버 재시작 시에도 적용되도록 디스크에 저장합니다.

# 메모리에 있는 변경 사항을 런타임으로 불러오기
LOAD MYSQL USERS TO RUNTIME;

# 메모리에 로드된 변경 사항을 디스크에 저장
SAVE MYSQL USERS TO DISK;

5) 클라이언트에서 접속하여 테스트

백엔드 서버 정보, 유저 정보 설정 완료 후 mysql 클라이언트에서 proxysql로 접속하여 쿼리 테스트를 해봅니다. 원격지에서 일반적인 mysql 서버로 접속하는 방법으로 접속합니다. 제 테스트 환경에서는 mysql 컨테이너에서 proxysql 서버로 접속했습니다. 접속 포트는 proxysql 어드민 포트(6032)가 아닌 proxysql 사용자 포트(6033)으로 접속합니다.

# 원격지에서 proxysql admin 접속
mysql -u premisan -p -h [proxysql_서버_주소] -P 6033 --prompt "ProxySQL> "

# mysql 컨테이너에서 proxysql 컨테이너 관리자 포트로 접속
docker exec -it mysql mysql -u premisan -p -h proxysql.wp -P 6033 --prompt "ProxySQL> "

동시에 마스터 DB의 제너럴 로그에 아래와 같이 로그가 기록되는 것을 확인할 수 있습니다.

 

현재 상태에서는 proxysql 로 접속하여 발생하는 모든 쿼리가 마스터 DB로 보내지는 것을 확인할 수 있습니다. 이제 다음 단계에서 쿼리 룰을 설정하여 쿼리 종류에 따라 쓰기/읽기 DB로 자동으로 라우팅되도록 설정합니다.

6) 쿼리 룰(쿼리 라우팅) 설정

백엔드 서버 설정 시 마스터(쓰기) DB는 hostgroup_id를 0으로, 슬레이브(읽기) DB는 hostgroup_id를 1로 설정했습니다. 이제 SELECT 쿼리는 읽기 DB로 전달하며, INSERT/UPDATE/DELETE 쿼리는 쓰기 DB로 전달하도록 쿼리 룰을 구성해보도록 하겠습니다. 쿼리 룰에 대한 설정은 mysql_query_rules 테이블에 저장됩니다.

# 기존 쿼리 룰 리스트 확인
SELECT * FROM mysql_query_rules;

# 정규표현식을 통해 쿼리 종류에 따라 다른 호스트 그룹으로 전달하도록 설정 추가
INSERT INTO mysql_query_rules(match_pattern,destination_hostgroup,active) VALUES ('^INSERT',0,1);
INSERT INTO mysql_query_rules(match_pattern,destination_hostgroup,active) VALUES ('^UPDATE',0,1);
INSERT INTO mysql_query_rules(match_pattern,destination_hostgroup,active) VALUES ('^DELETE',0,1);
INSERT INTO mysql_query_rules(match_pattern,destination_hostgroup,active) VALUES ('^SELECT',1,1);

백엔드 서버, 유저 설정과 동일하게 모든 설정 관련 변동사항은 메모리에서 런타임으로 로드시켜줘야합니다. 또한 서버 재시작을 대비하여 디스크에도 변경 사항을 저장해줍니다.

# 메모리에 있는 변경 사항을 런타임으로 불러오기
LOAD MYSQL QUERY RULES TO RUNTIME;

# 메모리에 로드된 변경 사항을 디스크에 저장
SAVE MYSQL QUERY RULES TO DISK;

이제 쿼리 룰이 정상적으로 적용되었는지 테스트 진행합니다. 이미지 중 위의 화면은 마스터 DB의 제너럴 로그이고, 아래 화면은 슬레이브 DB의 제너럴 로그입니다. 클라이언트에서 proxysql로 SELECT 쿼리를 여러번 날렸을 때, 모두 슬레이브 DB로 쿼리가 라우팅되는 것을 확인할 수 있습니다. 반대로 INSERT 쿼리를 여러번 날렸을 때, 모두 마스터 DB로 쿼리가 라우팅되는 것을 확인할 수 있습니다.

쿼리 룰을 이용하여 더 복잡한 쿼리에 대해서도 정규표현식을 통해 어떤 백엔드 서버로 쿼리를 라우팅할지 디테일하게 설정이 가능합니다.

7) jsp 소스에 적용

proxysql 을 통해 데이터베이스 커넥션 설정 시 다른 소스는 수정할 필요 없이 jdbc 커넥션 엔드포인트만 수정해주면 됩니다. 아래는 기존 mysql.wp 마스터 DB 컨테이너로 연결하도록 구성한 설정을 proxysql 컨테이너로 연결하도록 수정한 내용입니다.

# 기존
String jdbcDriver = "jdbc:mysql://mysql.wp:3306/premisan_test?useUnicode=true&characterEncoding=utf8";

# 수정
String jdbcDriver = "jdbc:mysql://proxysql.wp:6033/premisan_test?useUnicode=true&characterEncoding=utf8";

웹을 통해 접속하여 테스트 진행합니다. 정상적으로 SELECT 결과를 가져온 것을 확인할 수 있습니다.

위의 결과가 어떤 DB에서 쿼리한 결과인지 확인합니다. 이미지 중 위의 화면은 마스터 DB의 제너럴 로그이고, 아래 화면은 슬레이브 DB의 제너럴 로그입니다. 아래 화면에서 보이는 것과 같이 SELECT 쿼리가 슬레이브 DB로 전달된 것을 확인할 수 있습니다. (SET NAMES utf8 로 보이는 클라이언트 캐릭터 셋 관련 쿼리가 마스터 DB로 전달된 것을 확인할 수 있으나,
해당 부분은 서버 설정 및 쿼리 룰을 디테일하게 설정해줄 필요가 있을 것 같습니다.)

7. 결론

이렇게 proxysql 서버를 컨테이너로 구성하여 두 백엔드 DB 서버를 구성하고, 쿼리 종류에 따라 쓰기/읽기 DB로 각각 전달되도록 구성을 완료하였습니다. proxysql 을 통해 쿼리 라우팅 구성 시 아래와 같은 장점이 있습니다.

1) 쿼리 룰 설정을 통해 소스 수정 없이 쓰기/읽기 쿼리에 대한 쿼리 라우팅 설정 가능
2) 클라이언트는 proxysql 서버와 커넥션을 맺고, proxysql 에서 백엔드 서버와 커넥션을 맺기 때문에 클라이언트로부터 DB에 대한 커넥션을 빈번하게 맺고 끊더라도, 실제 백엔드 서버에서는 proxysql과 커넥션을 유지하며 쿼리를 수행하기 때문에 백엔드 서버의 커넥션 오버헤드가 감소합니다.
3) 읽기 DB가 추가되더라도 소스 수정 필요 없이 proxysql 설정만 수정하면 서비스 투입이 가능합니다.

8. AWS RDS Proxy와 비교한 차이점

AWS RDS 에서도 2020년 7월 2일 서비스 오픈한 AWS RDS Proxy 서비스가 있으나 proxysql 과는 달리 백엔드 서버를 1개만 등록 가능하고, 쿼리 룰 등의 설정이 불가하여 쿼리 라우팅의 기능은 없으며, 커넥션 관리 기능과 다중 AZ 인스턴스를 통한 Failover 시간 단축 기능만 가지고 있어 proxysql 과 기능적으로 다르기 때문에 이 점 혼동하지 않도록 유의가 필요합니다.