ProxySQL

이 구성은 DB를 쓰기(Write)와 읽기(Read)로 분리하고, GTID 기반의 복제를 사용하는 환경을 가정합니다. 가장 큰 특징은 애플리케이션이 DB에 직접 붙는 것이 아니라, 중간에 위치한 ProxySQL을 통해 연결한다는 점입니다. 즉, ProxySQL이 에이전트가 되어 DB 커넥션과 쿼리 라우팅을 관장합니다.

• 클라이언트는 DB 서버에 직접 연결하지 않고 ProxySQL을 통해 연결되며, ProxySQL이 중간 계층으로서 모든 쿼리와 커넥션을 관리 및 제어합니다.

왜 ProxySQL이 필요한가요?

1. DB Failover 시 애플리케이션 영향 최소화

Primary 서버에 장애가 발생하면 쓰기 작업이 중단됩니다. Orchestrator 같은 도구가 Replica를 Primary로 승격시켜 DB단은 복구되어 서비스가 지속되지만, 애플리케이션은 여전히 죽은 Primary IP를 바라보고 있을 수 있습니다.

문제는 이때 애플리케이션이 여전히 이전 Primary의 IP로 접속을 시도한다는 점입니다.

이를 해결하기 위해서는

• 애플리케이션 설정을 변경하고 재배포(Reload)하거나,

• DNS를 변경할 수 있지만, DNS 캐시(TTL) 문제로 인해 즉각적인 반영이 어려울 수 있습니다.

2. 유연한 아키텍처 구성 (DB가 추가되는 경우)

DB 서버를 증설(Scale-out)하더라도 애플리케이션 코드를 수정하고 싶지 않을 때가 있지만, 애플리케이션 코드나 설정에서 해당 DB 정보를 갱신하고 재배포하는 과정이 필요합니다.

• 기존: 애플리케이션이 Primary/Replica IP를 모두 알고 있어야 하므로 DB 변경 시 앱 설정도 바꿔야 합니다.

• 개선: ProxySQL을 도입하면 애플리케이션은 ProxySQL만 바라보면 됩니다. 뒤단에 DB가 10대가 되든, IP가 바뀌든 애플리케이션은 알 필요가 없어집니다.

이처럼 IP 기반 접근 구조에서는 클러스터 확장 시마다 배포 작업이 반복되는 비효율이 발생합니다.

이 문제를 해결하기 위해 중간 계층 프록시(ProxySQL)를 둠으로써, 애플리케이션은 항상 ProxySQL만 바라보고 접속하며 프록시가 내부적으로 트래픽을 DB로 라우팅합니다.

이 구조는 서비스 운영 중에도 유연한 DB 증설, 장애 전환, 라우팅 정책 변경이 가능하게 해줍니다.

기존 MHA 구성의 한계와 ProxySQL 도입 배경

MHA는 MySQL 고가용성 환경에서 비교적 단순한 구성과 자동화로 서비스 다운타임을 크게 줄일 수 있는 Failover 솔루션입니다.

이전에 Orchestrator를 사용해 MySQL 복제 환경에서 자동 Failover를 구성했습니다. 이를 통해 고가용성은 확보했지만, 애플리케이션 단의 커넥션 관리 문제는 여전히 남아 있었습니다.

예를 들어 다음과 같은 문제가 발생할 수 있습니다.

• Primary가 장애 발생 → Replica가 Primary로 승격됨

• 그러나 애플리케이션은 여전히 이전 Primary에 write 요청을 보냄

• Read 요청은 새로운 Primary(slave1)에 전달되어 consistency 문제가 발생

이처럼 클라이언트 레벨에서 DB 연결을 매번 수정해야 하는 불편함을 줄이기 위해 ProxySQL을 도입하여 미들웨어 계층에서 커넥션과 쿼리 라우팅을 통합 관리하도록 구성했습니다.

ProxySQL 개요

ProxySQL은 클라이언트와 DB 서버 사이의 중개자(Proxy) 역할을 수행하는 미들웨어입니다.

이 시스템은 단순 커넥션 프록시를 넘어서 다음과 같은 고급 기능을 제공합니다:

• Connection multiplexing (다중 연결 재사용)

  • 수천 개의 클라이언트 연결을 소수의 DB 커넥션으로 재사용하여 DB 부하를 줄입니다.

• Query rule-based 라우팅

  • 쿼리 내용에 따라 Primary(쓰기)나 Replica(읽기)로 보냅니다.

• Query 캐싱 및 미러링

• 트래픽 기반 동적 우선순위 제어

• 다중 서비스 운용을 위한 확장성 구조

주요 모듈 구성

User autehnstication:

• 기본 데이터베이스에 대한 사용자 자격 증명이 해시되어 프록시에 저장

Hostgroup manager:

• 트래픽을 보낼 서버 그룹을 관리하고 해당 상태를 추적

Connection pool:

• 백엔드 데이터베이스에 대한 연결을 관리. 연결 풀은 백엔드를 향해 설정되며 모든 애플리케이션에서 공유/재사용

Monitoring:

• 백엔드 DB를 모니터링하고 지표를 수집. 응답하지 않는 호스트 또는 복제 지연에 대해 필요에 따라 트래픽을 제어

Query processor/rules engine:

• Mysql 프로토콜(쿼리)를 해석

• 룰 엔진은 들어오는 트래픽을 일치시키고 쿼리를 캐시할지 또는 호스트 그룹 대상에 쿼리를 차단, 경로 재지정, 쿼리 재작성 또는 미러링 할지 여부를 정의

Queries Cache:

• 쿼리 대기 시간을 줄일 수 있도록 정의된 시간(TTL) 동안 캐시, 사용자의 정의에 따라 쿼리 제한 가능

• 성능에 영향을 미치는 무거운 쿼리들을 초당 한 번 또는 분당 한 번만 실행되도록 커스텀하게 설정 가능

모듈	설명
User Authentication	DB 사용자 자격 증명을 Proxy에 저장하여 인증을 수행합니다.
Hostgroup Manager	래픽을 보낼 서버 그룹(Writer 그룹, Reader 그룹 등)을 관리하고 상태를 실시간으로 추적합니다.
Connection Pool	백엔드 DB와의 연결을 미리 맺어두고 재사용합니다.
Monitoring	DB 상태(Ping, Replication Lag 등)를 주기적으로 체크하여, 응답 없는 서버나 지연이 심한 서버를 트래픽에서 제외합니다.
Query Processor / Rules Engine	들어오는 SQL을 분석하여 캐싱하거나, 특정 호스트 그룹으로 라우팅하거나, 쿼리를 재작성(Rewrite)합니다. 쿼리 규칙(match/filter)에 따라 트래픽을 차단, 재작성, 미러링, 또는 특정 Hostgroup으로 라우팅
Query Cache	반복적인 읽기 쿼리를 설정된 시간(TTL) 동안 캐싱하여 DB 부하를 줄입니다. 성능에 영향을 주는 무거운 쿼리들을 초당 한 번 또는 분당 한 번만 실행되도록 커스텀하게 설정 가능합니다.

ProxySQL 설정

ProxySQL의 설정은 Runtime, Memory, Disk 세 영역으로 나뉩니다. 그래서 설정을 변경하는 경우, 변경된 내용은 메모리에 저장되며 실제 서버에 바로 적용되지 않습니다.

따라서 메모리에 저장된 변경 사항을 런타임으로 불러와야 proxysql 서버에 실제로 적용되며, 디스크는 메모리의 변경 사항을 영구적으로 저장하기 위한 공간으로, proxysql 서버를 재시작할 때 이 정보를 불러와 적용합니다.

영역	설명
Runtime	현재 서버에 적용되어 실행 중인 설정
Memory	변경 이후 메모리에 반영된 임시 설정 (실행 전 단계)
Disk	영구 저장소. 재시작 시 Memory로 로드됨

• Runtime: 현재 메모리에 로드되어 실제로 동작 중인 설정입니다.

• Memory: 사용자가 수정 명령을 내리면 저장되는 영역입니다. (아직 반영 안 됨)

• Disk: 재시작 시에도 설정이 유지되도록 영구 저장하는 파일입니다.

설정 흐름: 수정(Memory) → LOAD to RUNTIME(적용) → SAVE to DISK(영구저장)

1. 쿼리 전송 DB 서버 설정

ProxySQL CLI(admin interface, 기본 포트 6032)에 접속 후 서버를 등록합니다.

proxysql은 설정 정보를 테이블에 레코드 형태로 저장하며 insert 쿼리를 통해 설정합니다.

// db 정보를 저장하고 실시간으로 반영하는 경우
proxysql>

// 메모리에 새로운 정보 추가
insert into mysql_servers (hostgroup_id, hostname, ..) values (0, 'primary', ..);
insert into mysql_servers (hostgroup_id, hostname, ..) values (1, 'replica1', ..);

// 메모리에 저장된 변경 사항을 런타임으로 적용
load mysql servers to runtime;

// 메모리 변경 사항을 디스크에 영구 저장
save mysql servers to disk;

여기서 hostgroup_id는 read/write 작업을 구분하기 위한 설정 값입니다.

hostgroup_id=1로 쿼리가 발생하면 Write db로, id=2으로 쿼리가 발생하면 Read로 쿼리를 분산됩니다.

2. 모니터링 사용자 설정

mysql> create user 'monitor'@'%' identified by 'monitor';

Connect	monitor@proxysql-ip on  using TCP/IP
Quit

ProxySQL이 DB 상태를 체크할 때 사용할 계정을 DB에 생성하고, ProxySQL 설정 파일(/etc/proxysql.cnf)이나 변수 설정을 통해 알려줍니다.

설정 결과는 ProxySQL 로그 또는 MySQL general log에서 접속 로그를 확인할 수 있습니다.

3. 유저 매핑 설정

ProxySQL이 MySQL에 접근할 수 있도록 사용자 매핑을 등록합니다.

proxysql> 
INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent)
VALUES ('root', '*2DDAEF3B65786A93C61DADB91A3A58EFBE826D995F5', 0, 0);

mysql의 계정 정보를 확인하고 proxysql에서 사용자 정보를 추가합니다.

테스트: (6033은 클라이언트가 ProxySQL을 통해 접속하는 포트입니다.)

mysql -P 6033 -uroot -p

4. 쿼리 라우팅 설정

hostgroup_id=0 은 쓰기 작업을 Primary db로 향하도록 설정한 설정 값입니다.

그렇게하기 위해서는 쓰기 쿼리를 Primary 그룹으로 라우팅하도록 패턴 기반 규칙을 등록합니다. 설정은 mysql_query_rules 테이블의 레코드로 저장할 수 있습니다.

proxysql>
INSERT INTO mysql_query_rules (match_pattern, destination_hostgroup, active)
VALUES ('^INSERT', 0, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

버전 호환 문제

Flyway나 특정 드라이버 사용 시 Unknown system variable 'query_cache_size' 같은 에러가 발생할 수 있습니다. 이는 ProxySQL이 클라이언트에게 자신을 신고할 때 사용하는 MySQL 버전과, 실제 백엔드 MySQL 버전 혹은 드라이버가 기대하는 버전이 달라서 발생합니다. 아래 ProxySQL 관리 페이지나 문서를 참조하여 mysql-server_version 파라미터를 실제 환경에 맞게 조정해야 합니다.

• ProxySQL의 mysql-server_version 변수 값을 실제 사용하는 MySQL 버전(예: 8.0.28)과 비슷하게 맞춰주면 해결됩니다.

TusaCentral - ProxySQL server version impersonationwww.tusacentral.net

쓰기/읽기 분리 결과 확인

MySQL general log를 보면 write 쿼리(INSERT)는 Primary 서버에서, read 쿼리(SELECT)는 Replica 서버에서 처리되는 것을 확인할 수 있습니다.

Auto Failover 시나리오

시나리오 1. Replica DB 장애

Replica 서버가 장애로 응답하지 않으면 ProxySQL이 트래픽을 해당 서버에서 제외합니다.

Connection is not available, request timed out after 15004ms (total=0, active=0, idle=0, waiting=0)
Servlet.service() for servlet [dispatcherServlet] in context with path [/api] threw exception [Request processing failed: org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction] with root cause
Caused by: java.net.SocketTimeoutException: Read timed out

하지만, 읽기 그룹(Hostgroup 1)에 서버가 Replica 단 한 대뿐이라면, 해당 그룹으로 향하는 모든 SELECT 쿼리는 처리될 곳이 없어 실패하게 됩니다. 애플리케이션에서는 위와 같이 커넥션 풀을 얻지 못했다는 타임아웃 에러가 발생합니다.

이처럼 Replica가 장애로 응답하지 못하는 경우, ProxySQL은 read 라우팅 트래픽의 대상에서 해당 노드를 제거하지만 다른 대체 노드가 없다면 쿼리를 처리할 수 없습니다.

이를 방지하기 위해 Primary 서버를 Replica 그룹(hostgroup_id=1) 에 낮은 가중치(weight) 로 추가하여 Replica 장애 시 읽기 트래픽이 Primary로 자동 우회되도록 구성합니다.

가중치 설정 로직

ProxySQL은 각 서버에 설정된 weight 비율에 따라 트래픽을 분산합니다. Primary 1대, Replica 2대로 구성된 환경에서, ProxySQL이 어떻게 트래픽을 분배하는지 계산해 보겠습니다.

설정 상황

• Primary: 가중치 1

• Replica 1: 가중치 100

• Replica 2: 가중치 100

트래픽 분산 비율 계산

• 총 가중치 합: 1(Primary) + 100(Replica) + 100(Replica) = 201

• Replica 1 점유율: 100 / 201 ≈ 49.75%

• Replica 2 점유율: 100 / 201 ≈ 49.75%

• Primary 점유율: 1 / 201 ≈ 0.5%

즉, 전체 읽기 트래픽의 99.5%는 두 대의 Replica가 나누어 처리하고, Primary는 약 0.5%의 아주 미미한 트래픽만 처리하게 됩니다. 만약 Replica들이 장애로 인해 제외되면, 남은 Primary가 유일한 처리 대상(100%)이 되어 읽기 트래픽을 받아내게 됩니다.

가중치 설정 적용 (SQL)

MySQL 서버를 Primary와 Replica로 구성했다고 가정합니다.

• Primary(hostgroup_id=0)는 쓰기 대상

• Replica(hostgroup_id=1)는 읽기 대상

Replica 그룹에 Primary를 낮은 가중치로 추가합니다.

pryoxysql>
UPDATE mysql_servers SET weight = 1000 WHERE hostgroup_id = 1;

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (1, 'mysql-master', 3306, 1);

SELECT hostgroup_id, hostname, port, weight, max_replication_lag FROM mysql_servers;
+--------------+--------------+------+--------+---------------------+
| hostgroup_id | hostname     | port | weight | max_replication_lag |
+--------------+--------------+------+--------+---------------------+
| 0            | mysql-master | 3306 | 1000   | 0                   |
| 1            | mysql-slave  | 3306 | 1000   | 30                  |
| 1            | mysql-master | 3306 | 1      | 0                   |
+--------------+--------------+------+--------+---------------------+

설정 후 조회해보면 위와 같이 구성됩니다.

장애 테스트 및 검증

이제 강제로 Replica 서버를 다운시키고 애플리케이션 로그나 ProxySQL 모니터링을 확인해 봅니다. Replica가 죽자마자 읽기 요청이 Primary로 라우팅되어 에러 없이 서비스가 유지되는 것을 확인할 수 있습니다.

복구 후에도 트래픽이 돌아오지 않는다면? (복제 상태와 라우팅 조건)

Replica 서버를 다시 살렸는데도 불구하고, 여전히 모든 읽기 트래픽이 Primary로만 가고 있는 경우가 있습니다. 이때는 **복제 지연(Replication Lag)**을 확인해야 합니다.

ProxySQL은 max_replication_lag 설정값(기본값 등)을 초과하여 지연이 발생하는 서버는 **'데이터가 최신이 아니다'**라고 판단하여 트래픽을 보내지 않습니다 (Shunned 상태). 따라서 Replica가 막 기동되어 밀린 데이터를 동기화하는 중이라면, 이 지연이 해소될 때까지 기다리거나 복제 오류가 없는지 SHOW REPLICA STATUS 명령어로 확인해야 합니다.

만약 Replica를 복구시켰음에도 여전히 읽기 트래픽이 Primary로 향한다면, 해당 Replica의 복제 상태(Replication Delay, Error) 를 점검해야 합니다.

ProxySQL은 Replica가 ONLINE 상태로 표시되어 있어도 다음 조건 중 하나라도 해당되면 읽기 라우팅 대상에서 제외합니다.

• 복제 지연(Seconds_Behind_Master)이 허용 범위를 초과한 경우

  • ProxySQL은 max_replication_lag 설정값(기본값 등)을 초과하여 지연이 발생하는 서버는 '데이터가 최신이 아니다'라고 판단하여 트래픽을 보내지 않습니다 (Shunned 상태).

  • 이 지연이 해소될 때까지 기다리거나 복제 오류가 없는지 SHOW REPLICA STATUS 명령어로 확인

• 복제 오류가 발생한 경우

• 네트워크 ping 실패로 응답이 불안정한 경우

이런 조건 덕분에 ProxySQL은 단순한 장애 감지만이 아니라, 복제 상태의 건전성을 기준으로 트래픽을 자동으로 최적 분배하는 역할까지 수행합니다.

시나리오 2. 마스터 DB 장애

이번 시나리오는 쓰기 작업을 담당하는 Primary 서버를 강제로 다운시켜, 실제 서비스 환경에서의 Failover 동작을 검증합니다. 테스트를 위해 Primary가 다운된 상태에서 쓰기(Write) 요청이 포함된 애플리케이션 API를 호출합니다.

Primary 서버가 응답하지 않으면 Orchestrator는 즉시 장애를 감지하고 Auto Failover 프로세스를 시작합니다.

• 응답 없는 기존 Primary는 클러스터 토폴로지에서 분리됩니다.

• 가장 적합한 Replica 서버가 선정되어 새로운 Primary로 승격됩니다.

MySQL General Log를 확인해 보면, 애플리케이션이 보낸 INSERT 쿼리가 유실되지 않고 새로운 Primary 서버의 로그에 정상적으로 기록된 것을 확인할 수 있습니다. 이는 ProxySQL이 적절한 곳으로 트래픽을 돌려주었음을 의미합니다.

이 과정에서 ProxySQL 내부에서는 어떤 일이 일어났는지 런타임 서버 상태를 통해 확인할 수 있습니다.

• 기존 Primary: 연결이 불가능하므로 상태가 SHUNNED(일시적 격리)로 변경되어, 더 이상 트래픽을 받지 않게 서비스에서 제외됩니다.

• 새로운 Primary (구 Replica): read_only가 해제된 것을 ProxySQL이 감지하고, hostgroup_id=0 (Writer 그룹)으로 역할을 변경하여 쓰기 트래픽을 처리하기 시작합니다.

이 테스트 결과를 통해 mysql_servers 테이블은 관리자가 초기에 설정한 호스트 그룹 정보(정적 설정)를 담고 있는 용도로 활용되고, runtime_mysql_servers 테이블은 현재 실제로 서비스되고 있는 DB의 상태(Status)와 목록을 실시간으로 관리하고 있습니다.