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

# 최신 릴리스 페이지에서 버전 확인
# 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> '

-- 백앤드 서버 등록 하는 법
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: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;

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;

-- 기본 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;

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

SELECT hostgroup, srv_host, status, ConnOK, ConnERR
FROM stats_mysql_connection_pool ORDER BY hostgroup, srv_host;