[카테고리:] MySQL

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

⚙️ 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;

9월 22, 2025

MariaDB ➡️ MySQL Migration: mydumper & myloader 사용법 정리(스키마 제외, 데이터만)

📌 개요

이 글에서는 MySQL에서 특정 데이터베이스의 데이터만 덤프하고, 다른 서버에 복원하는 과정을 mydumper / myloader 툴로 설명합니다.

✅ 병렬 덤프 및 복원 지원
✅ 데이터만 추출 및 복원
✅ 병렬 덤프 및 복원 지원

🧱 설치

🧤 ubuntu

sudo apt update
sudo apt install mydumper -y

🧤 데이터 덤프 (Source DB 에서)

mydumper \
  -h [DB-IP] \
  -u [User] \
  -p ['password'] \
  -B [DataBase_name] \
  --no-schemas \
  --outputdir /home/ubuntu/[output_dir] \
  --threads 4

🔖 옵션 설명

옵션	설명
-h	DB 서버 주소
-u, -p	접속 계정 정보
-B	백업할 데이터베이스 이름
–no-schemas	스키마 없이 데이터만 덤프
–outputdir	덤프 저장 디렉토리
–threads	병렬 처리할 스레드 수

🧤 데이터복원 (Target DB에서)

⚙️ FK 제약조건 끄고 복원

myloader \
  -h [DB-ip] \
  -u [User] \
  -p ['password'] \
  -B [DataBase_name] \
  --directory /home/ubuntu/[output_dir] \
  --disable-keys \
  --threads 4 \
  --verbose

⚙️ FK 제약조건 유지하며 복원

myloader \
  -h [DB-ip] \
  -u [User] \
  -p ['password'] \
  -B [DataBase_name] \
  --directory /home/ubuntu/[output_dir] \
  --threads 4 \
  --verbose

🔖 옵션 설명

옵션	설명
–directory	덤프 디렉토리 지정
–disable-keys	foreign key 체크 비활성화 (성능 향상)
–verbose	자세한 로그 출력

📌 TIP

❗ -v는 유효하지 않음 → –verbose 사용
❗ –no-schemas를 빼먹으면 DDL까지 같이 덤프됨
❗ mydumper/myloader 버전이 서로 다르면 에러 발생 가능

7월 31, 2025