ProxySQL로 읽기/쓰기 분리 및 replica 상태 점검

명령어

-- On MySQL (source): monitor user
CREATE USER 'proxysql_mon'@'%' IDENTIFIED BY 'M0n#itor2024';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'proxysql_mon'@'%';

-- ProxySQL admin: mysql -u admin -p -h 127.0.0.1 -P 6032
UPDATE global_variables SET variable_value = 'proxysql_mon' WHERE variable_name = 'mysql-monitor_username';
UPDATE global_variables SET variable_value = 'M0n#itor2024' WHERE variable_name = 'mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment) VALUES (10, 20, 'read_only', 'shop');
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_replication_lag) VALUES (10, 'db1', 3306, 0), (20, 'db2', 3306, 30), (20, 'db3', 3306, 30);
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES ('app', 'App#Pass2024', 10, 1);
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (100, 1, '^SELECT .* FOR (UPDATE|SHARE)', 10, 1), (200, 1, '^SELECT ', 20, 1);
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

-- Verify
SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;
SELECT hostgroup, srv_host, status, ConnUsed, Queries, Latency_us FROM stats_mysql_connection_pool;
SELECT hostgroup, substr(digest_text, 1, 60) AS digest, count_star, sum_time / 1000 AS sum_ms FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 10;

하는 일

read_only 플래그를 기준으로 writer(10)와 reader(20) hostgroup을 자동 관리한다. SELECT는 replica로, 잠금 읽기와 쓰기는 source로 라우팅하며, max_replication_lag를 초과한 replica는 일시적으로 제외한다(SHUNNED).

사용 시점

애플리케이션을 건드리지 않고 비동기 복제 토폴로지에서 읽기 부하를 분산하기 위해. 커넥션 풀링과 쿼리별 라우팅 / 캐싱 / 방화벽용.

주의

구성은 3개 계층(MEMORY → RUNTIME → DISK)이다. LOAD ... TO RUNTIME 없이는 아무것도 바뀌지 않고, SAVE ... TO DISK 없이는 재시작 시 사라진다. read_only 점검에는 replica의 super_read_only=ON이 필요하다. 그렇지 않으면 replica가 writer hostgroup으로 옮겨진다. 모든 SELECT를 replica로 보내면 지연 때문에 read-after-write 오류가 발생한다. stats_mysql_query_digest를 기반으로 무거운 / 보고용 쿼리에만 규칙을 작성하는 것이 더 안전하다. 관리 인터페이스는 SQLite이므로 LEFT() 같은 MySQL 함수가 없다. substr()을 사용한다. 기본 admin:admin 계정을 변경한다(admin-admin_credentials).