ProxySQL

RayHong·2023년 9월 7일

MySQL

목록 보기

1/5

ProxySQL

MySQL 프록시 서버로 C++로 작성된 Open Source 프로그램으로 MySQL 서버에 접근하는 클라이언트의 요청을 분석하고, 서버에 요청을 전달하는 Middle Ware 개념의 역할을 합니다.

이로인해 아래와 같이 장점을 가질 수 있습니다.

많은 수의 Sessions Connections 을 효율적으로 관리 할 수 있습니다.
DataBase/ 사용자/ Tables 별 Data를 분리 저장 할 수 있습니다.
Read / Write 업무를 분리할 수 있습니다.

다른 솔루션 비교시 (NHN Cloud 세미나 자료 참고)

ProxySQL Sharding 종류

ㅁ User Based Sharding
사용자를 기반으로 적절한 샤드로 쿼리를 라우팅
ㅁ DataBase based sharding
DB 를 기반으로 적절한 샤드로 쿼리를 라우팅
ㅁ Data based sharding
쿼리 규칙을 기반으로 Write(DML), Read(Select) 쿼리를 분리
쿼리 규칙을 기반으로 특정 테이블을 적절한 샤드로 라우팅
별도의 application필요 없이 쿼리 규칙 적용으로 캐싱 가능

1) User Based Sharding

Appication에서 ProxySQL End Point 하나를 설정하여 사용자별로 DataBase를 분리 할 수 있다.

- 예 > 주문(ORD)/ 상품(PRD) / 이벤트(EVNT) 계정을 분리하여 물리적으로 DataBases 분리

Configuration

UPDATE mysql_users SET default_hostgroup = 1;
DELETE FROM mysql_query_rules;
INSERT INTO mysql_users (username, password, active, default_hostgroup, comment)
VALUES
('ord_user', 'ord_user', 1, 1, 'Routed to the order DB'),
('prd_user', 'prd_user', 1, 2, 'Routed to the production DB'),
('evnt_user', 'evnt_user', 1, 3, 'Routed to the event DB');
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
DB Layer
사용자 생성

create user ord_user@'%' identified by 'ord_user';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON . to ord_user@'%';
create user prd_user@'%' identified by 'prd_user';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON . to prd_user@'%';
create user evnt_user@'%' identified by 'evnt_user';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON . to evnt_user@'%';
테이블 생성
create database ord_db ; use ord_db; create table ord_table (id int);
create database prd_db ; use prd_db;create table prd_table (id int);
create database evnt_db ; use evnt_db;create table evnt_table (id int);
Client
Connection
mysql -uord_user -pord_user -h 172.21.40.63 -P6033;
mysql -uprd_user -pprd_user -h 172.21.40.63 -P6033;
mysql -uevnt_user -pevnt_user -h 172.21.40.63 -P6033;

2) DataBase Based Sharding

Appication에서 ProxySQL End Point 하나를 설정하여 DataBase 별로 서버를 분리 할 수 있다.

- 예 > 주문(ORD_DB)/ 상품(PRD_DB) / 이벤트(EVNT_DB) DB를 분리

Configuration

DELETE FROM mysql_query_rules;
INSERT INTO mysql_query_rules (rule_id, active, schemaname,destination_hostgroup, apply)
VALUES
(1, 1, 'ord_db', 1, 1),
(2, 1, 'prd_db', 2, 1),
(3, 1, 'evnt_db', 3, 1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
DB Layer
사용자 생성

create user tmall@'%' identified by 'tmall1234';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON . to tmall@'%';
테이블 생성
create database ord_db ; use ord_db; create table ord_table (id int);
create database prd_db ; use prd_db;create table prd_table (id int);
create database evnt_db ; use evnt_db;create table evnt_table (id int);
Client
Connection
mysql -utmall -ptmall1234 -h 172.21.40.63 -P6033;

3) Data Based Sharding - 쿼리 규칙에 따른 Read / Write 업무 분산

Appication에서 ProxySQL End Point 하나를 설정하여 Write(DML) 쿼리와 Read(Select) 쿼리를 분리 할 수 있다.

- Write(DML) Mater Node 에서 Query 실행

- Read(Select) Slave Node 에서 Query 실행

Configuration

ProxySQL
Connection
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
ProxySQL에서 MySQL로 원격 접속할 수 있는 모니터링 계정
UPDATE globalvariables SET variable_value = 'tmall' WHERE variable_name = 'mysql-monitor_username';
UPDATE global_variables SET variable_value = 'tmall1234' WHERE variable_name = 'mysql-monitor_password';
SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor%';
서버 등록
DELETE FROM mysql_servers;
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.29.235.44', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '172.29.235.45', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '172.29.235.46', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
사용자 group 지정 및 read query 분리
UPDATE mysql_users SET default_hostgroup = 1;
DELETE FROM mysql_query_rules;
INSERT INTO mysql_query_rules (rule_id, active, apply, match_digest, destination_hostgroup) VALUES (1, 1, 1, '^SELECT', 2);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
클라이언트 접속 계정 추가
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('tmall', 'tmall1234', 1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
DB Layer
사용자 생성

create user tmall@'127.21.40.63' identified by 'tmall1234';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON . to tmall@'127.21.40.63';
Client
Connection
mysql -utmall -ptmall1234 -h 172.21.40.63 -P6033;

4) Data Based Sharding - 쿼리 규칙에 따른 테이블 기반 샤딩

특정 테이블을 기준으로 DataBase를 분리 저장 할 수 있다.

- 예 > 주문(ORD_table)/ 상품(PRD_table) / 이벤트(EVNT_table) 계정을 분리하여 물리적으로 DataBases 분리

Configuration
ProxySQL
Connection
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
ProxySQL에서 MySQL로 원격 접속할 수 있는 모니터링 계정
UPDATE globalvariables SET variable_value = 'tmall' WHERE variable_name = 'mysql-monitor_username';
UPDATE global_variables SET variable_value = 'tmall1234' WHERE variable_name = 'mysql-monitor_password';
SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor%';
서버 등록
DELETE FROM mysql_servers;
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.29.235.44', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '172.29.235.45', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (3, '172.29.235.46', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
ORD/ PRD/ EVNT 테이블 등록

DELETE FROM mysql_query_rules;
INSERT INTO mysql_query_rules (rule_id, active,destination_hostgroup, apply,match_digest)
VALUES
(1, 1, 1, 1,'ord_table'),
(2, 1, 2, 1,'prd_table'),
(3, 1, 3, 1, 'evnt_table');
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
DB Layer
사용자 생성

최종 아키텍쳐

RayHong

Oracle/ MySQL/ AWS DBA. 사람을 존중하고 공감하려 노력하고 있습니다. 잘못된 방식의 업무는 과정과 결과가 반복되지 않으려 개선하고 있습니다.

다음 포스트

ProxySQL

MySQL

ProxySQL

ProxySQL Sharding 종류

1) User Based Sharding

2) DataBase Based Sharding

3) Data Based Sharding - 쿼리 규칙에 따른 Read / Write 업무 분산

4) Data Based Sharding - 쿼리 규칙에 따른 테이블 기반 샤딩

최종 아키텍쳐

MySQL 5.7 vs 8.0 성능비교

0개의 댓글