⚙️ 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;
diven.io 