쿼리 라우팅 설정하기: ProxySQL 활용법

Written by

d_dive

in

,

⚙️ Version

OS: rocky 9.5

ProxySQL 3.0.2

백앤드 DB: MySQL 8.4.X 버전

서비스 API 에서 붙어야하는 백앤드 DB(master[Writer/Read], ReadReplica[Read])가 두개였다. 서비스 API 에서 보내는 쿼리 CRUD에 맞게 라우팅을 해야하는 상황이었다.

🔖 사전정보

  • Admin 포트 6032 (기본 계정 admin/admin)
  • MySQL 프록시 포트 6033
  • ProxySQL 설정은 메모리(Runtime) ↔ 디스크(SQLite, /var/lib/proxysql/proxysql.db) 레이어로 관리하고, 필요시 config 파일도 사용. Admin에서 변경 후 LOAD … TO RUNTIME; SAVE … TO DISK;가 관례.

⚙️ 설치 (Rocky/Alma/RHEL 8/9 계열)

📌 3.0.2 설치 & 기동

# 최신 릴리스 페이지에서 버전 확인
# https://github.com/sysown/proxysql/releases

# 1) wget으로 rpm 다운로드
wget https://github.com/sysown/proxysql/releases/download/v3.0.2/proxysql-3.0.2-1-centos8.x86_64.rpm

# 2) 설치
sudo dnf install -y ./proxysql-3.0.2-1-centos8.x86_64.rpm

# 3) 서비스 등록/시작
sudo systemctl enable --now proxysql

# 4) 버전 확인
proxysql --version
# ProxySQL version 3.0.2 ... 나와야 정상

📌 관리자 접속 & 기본 보안

mysql -h 127.0.0.1 -P6032 -u admin -padmin --prompt='Admin> '

📌 백엔드(MySQL) 서버 등록 (HG10: Writer, HG20: Reader) + SSL

-- 백앤드 서버 등록 하는 법
DELETE FROM mysql_servers;
INSERT INTO mysql_servers (hostgroup_id, hostname, port, use_ssl, max_connections)
VALUES
  (10, '0.0.0.0', 3306, 1, 2000),   -- Writer
  (20, '0.0.0.0' , 3306, 1, 2000);   -- Reader

LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

-- (선택) 헬스체크 튜닝
SET mysql-monitor_username = 'monuser';
SET mysql-monitor_password = 'monpass!';
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

-- 모니터링 계정은 백엔드 MySQL에도 생성 필요(각 노드DB에서):
-- CREATE USER 'monuser'@'%' IDENTIFIED BY 'monpass!';
-- GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monuser'@'%';

-- 연결 확인
SELECT hostgroup_id, hostname, port, status, use_ssl FROM runtime_mysql_servers ORDER BY hostgroup_id, hostname;

📌 애플리케이션 계정(ProxySQL 프런트 사용자) 생성

-- 앱이 ProxySQL:6033 으로 들어올 자격
DELETE FROM mysql_users WHERE username='app_user';
INSERT INTO mysql_users (username,password,active,default_hostgroup,transaction_persistent)
VALUES ('app_user','StrongAppPW!',1,10,1);

-- default_hostgroup=10(master), 트랜잭션 중엔 Writer로 붙게 룰이 조정됨
-- (필요 시 'frontend SSL'도 설정 가능)

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

-- 앱 쪽 DSN 예:
-- mysql -h 127.0.0.1 -P6033 -u app_user -pStrongAppPW!

⚠️ 백앤드 접근 유저 등록 체크하는 법

-- admin 에서(6032포트)

SELECT username, default_hostgroup, transaction_persistent, active
    -> FROM mysql_users
    -> ORDER BY username;

📌 글로벌 변수 (정규식/다이제스트/멀티플렉싱)

SET mysql-query_processor_regex = 1;
SET mysql-query_digests         = true;
SET mysql-multiplexing          = 1;

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

⚙️ 쿼리 라우팅 룰

  • 읽기(Read 전용 DB)
  • 쓰기(Writer 전용 DB)
  • 트랜잭션 중 쓰기 작업 발생(Reader DB ➡️ Writer DB) 이후 트랜잭션이 끝날때 까지 이후 리드 작업(Writer DB)
DELETE FROM mysql_query_rules;
DELETE FROM mysql_query_rules_fast_routing;

-- 10) WRITE류 → HG10, flag=2 + next_query_flagIN=2 (다음 쿼리에 플래그 확실히 전달)
INSERT INTO mysql_query_rules
(rule_id,active,apply,match_pattern,re_modifiers,
 destination_hostgroup,flagOUT,next_query_flagIN,comment,multiplex)
VALUES
(10,1,1,'^[[:space:]]*(/\\*.*?\\*/[[:space:]]*)*(INSERT|UPDATE|DELETE|REPLACE|MERGE)','CASELESS',
 10,2,2,'WRITE → HG10, flag=2 + next_query_flagIN=2',0);

-- 20/21) TX 시작/종료 → Writer (TX 중 SELECT 강제용 flag=1/0)
INSERT INTO mysql_query_rules
(rule_id,active,apply,match_pattern,re_modifiers,
 destination_hostgroup,flagOUT,comment,multiplex)
VALUES
(20,1,1,'^[[:space:]]*(/\\*.*?\\*/[[:space:]]*)*(BEGIN|START[[:space:]]+TRANSACTION)','CASELESS',
 10,1,'BEGIN/START → HG10, flag=1',0),
(21,1,1,'^[[:space:]]*(/\\*.*?\\*/[[:space:]]*)*(COMMIT|ROLLBACK)','CASELESS',
 10,0,'COMMIT/ROLLBACK → HG10, flag reset',0);

-- 30) SELECT ... FOR UPDATE/SHARE → Writer
INSERT INTO mysql_query_rules
(rule_id,active,apply,match_pattern,re_modifiers,
 destination_hostgroup,comment,multiplex)
VALUES
(30,1,1,'^[[:space:]]*(/\\*.*?\\*/[[:space:]]*)*(SELECT|WITH)(/\\*.*?\\*/|[[:space:]])*.*FOR[[:space:]]+(UPDATE|SHARE)','CASELESS',
 10,'SELECT/CTE ... FOR UPDATE|SHARE → HG10',0);

-- 40) TX 중 READ(SELECT/WITH) → Writer (flagIN=1)
INSERT INTO mysql_query_rules
(rule_id,active,apply,match_pattern,re_modifiers,
 destination_hostgroup,flagIN,comment,multiplex)
VALUES
(40,1,1,'^[[:space:]]*(/\\*.*?\\*/[[:space:]]*)*(SELECT|WITH)(/\\*.*?\\*/|[[:space:]])*','CASELESS',
 10,1,'flag=1(TX) READ → HG10',0);

-- 41) 쓰기 직후 ‘첫 READ(SELECT/WITH)’ 1회 → Writer & reset (flagIN=2)
INSERT INTO mysql_query_rules
(rule_id,active,apply,match_pattern,re_modifiers,
 destination_hostgroup,flagIN,flagOUT,comment,multiplex)
VALUES
(41,1,1,'^[[:space:]]*(/\\*.*?\\*/[[:space:]]*)*(SELECT|WITH)(/\\*.*?\\*/|[[:space:]])*','CASELESS',
 10,2,0,'flag=2 → next READ(SELECT/WITH) is HG10 & reset',0);

-- 42) 쓰기 직후 SELECT 전에 끼는 임의 쿼리(SET/SHOW/USE 등) → Writer (flag 유지)
INSERT INTO mysql_query_rules
(rule_id,active,apply,match_pattern,re_modifiers,
 destination_hostgroup,flagIN,flagOUT,comment,multiplex)
VALUES
(42,1,1,'^','CASELESS',
 10,2,2,'flag=2 → non-SELECT goes HG10 (keep flag=2)',0);

-- 50) 일반 READ(SELECT/WITH) → HG20 (공백/주석 유무 무관)
INSERT INTO mysql_query_rules
(rule_id,active,apply,match_pattern,re_modifiers,
 destination_hostgroup,comment)
VALUES
(50,1,1,'^[[:space:]]*(/\\*.*?\\*/[[:space:]]*)*(SELECT|WITH)(/\\*.*?\\*/|[[:space:]])*','CASELESS',
 20,'READ(SELECT/CTE) → HG20');

-- 90) 폴백 → HG10
INSERT INTO mysql_query_rules
(rule_id,active,apply,match_pattern,destination_hostgroup,comment)
VALUES
(90,1,1,'.*',10,'fallback → HG10');

-- 적용/저장
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- (확인) 런타임 상태
SELECT rule_id, active, apply, match_pattern, re_modifiers,
       flagIN, flagOUT, next_query_flagIN, multiplex, destination_hostgroup, comment
FROM runtime_mysql_query_rules
ORDER BY rule_id;

🔖 검증 체크리스트(Port: 6033)

-- 기본 Reader
SELECT @@read_only AS ro0, @@hostname AS h0;

-- 쓰기 → Writer (10)
INSERT INTO test_rw_split VALUES (1) ON DUPLICATE KEY UPDATE id=id;

-- 첫 READ → Writer & reset (41)
SELECT@@read_only AS ro1, @@hostname AS h1;

-- 다음 READ → Reader (50)
SELECT@@read_only AS ro2, @@hostname AS h2;

-- 트랜잭션 중 READ → Writer (40)
BEGIN;
SELECT@@read_only AS ro_tx, @@hostname AS h_tx;
COMMIT;
SELECT@@read_only AS ro_after, @@hostname AS h_after;

관리자 포트에서 확인(6032):

SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY rule_id;
-- 기대: 10 → (중간 쿼리 있었으면 42) → 41 → 50,  그리고 TX 경로는 20 → 40 → 21 → 50

🔗 운영 체크리스트

  • 전역 멀티플렉싱: 운영 기본은 1(ON) — 우리가 확인 끝낸 동작은 이 전제에서 정상 동작
  • HG20 리더의 ConnOK 증가 확인:
SELECT hostgroup, srv_host, status, ConnOK, ConnERR
FROM stats_mysql_connection_pool ORDER BY hostgroup, srv_host;

댓글

댓글 남기기

More posts