[MariaDB] Proxy SQL 셋팅 (on Docker)

* 개요

마리아 DB 사용시 Active-Active 구조로 사용하기 위해 DB 앞단에서 분기 해줄수 있는 시스템이 필요 하다. 오픈소스로는 지금 설명하는 ProxySQL이 있고, MaxScale이 대중적으로 사용되고 있다. 

* Architecture

* 방화벽 오픈

# 현재 방화벽 상태 확인
firewall-cmd --state

# 현재 방화벽 정책 확인
firewall-cmd --list-all

# 서비스 기동 or 정지
systemctl start firewalld.service
systemctl stop firewalld.service

# 자동 기동 상태 확인 및 자동 기동 셋팅
systemctl is-enabled firewalld.service
systemctl enable firewalld.service

firewall-cmd --permanent --zone=public --add-port=6032/tcp
firewall-cmd --permanent --zone=public --add-port=6033/tcp

firewall-cmd --permanent --zone=public --add-port=16032/tcp
firewall-cmd --permanent --zone=public --add-port=16033/tcp

firewall-cmd --reload

* Proxy SQL 셋팅

# 폴더 생성
mkdir -p /db/proxysql/data /db/proxysql/conf
chmod -R 777 /db/proxysql

# config 파일 생성
cd /db/proxysql/conf
vi proxysql.cnf

# 설정 내용
datadir="/var/lib/proxysql"
admin_variables=
{
    admin_credentials="admin:admin;radmin:radmin"
    mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.5.30"
    connect_timeout_server=3000
    monitor_username="monitor"
    monitor_password="monitor"
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
}

# 파일 권한 수정
chmod 644 proxysql.cnf

* 컨테이너 기동

docker run -it --name proxysql -h proxysql \
-p 16032:6032 -p 16033:6033 \
-v /db/proxysql/data:/var/lib/proxysql \
-v /db/proxysql/conf/proxysql.cnf:/etc/proxysql.cnf \
-d proxysql/proxysql

* proxysql 접속 및 세팅

: MariaDB 에 ProxySQL -> DB 에 접속 할 계정 생성 (어플리케이션용, 모니터링용)

# 어플리케이션용 계정
create user 'appuser'@'%' identified by 'apppass';
grant select, insert, update, delete on *.* to 'appuser'@'%';

# 모니터링용 계정 생성
create user 'monitor'@'%' identified by 'monitor';
grant REPLICATION CLIENT on *.* to 'monitor'@'%';

flush privileges;

: 접속

# 컨테이너 내부 접속 후
# 아래와 같이 접속 안될 시 컨테이너 내부 아이피 확인하여 넣어줌
mysql -h127.0.0.1 -P6032 -uradmin -pradmin --prompt "ProxySQL Admin>"

: Proxy SQL 설정 정보 셋팅

- hostgroup에 db 서버 정보 입력

- 어플리케이션 user 정보 입력

- 쿼리 룰 정보 입력

# 호스트 정보 등록
# 호스트 그룹 : 10번 write, 20번 read
# 1번 DB = write & read
insert into mysql_servers(hostgroup_id, hostname, port) values (10, '172.30.1.80', 3306);
insert into mysql_servers(hostgroup_id, hostname, port) values (20, '172.30.1.80', 3306);

# 2번 DB = read only
insert into mysql_servers(hostgroup_id, hostname, port) values (20, '172.30.1.81', 3307);

# 파라미터 설명
# 1번: write용 호스트 그룹, 2번: read용 호스트 그룹, 
# 3번 write냐 read냐를 판단 하는 기준을 read_only파라미터로 하겠다.
insert into mysql_replication_hostgroups values(10,20,'read_only','');

# 설정 적용
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

# 어플리케이션에서 사용하는 유저 정보 등록
insert into mysql_users(username, password, default_hostgroup, transaction_persistent) values('appuser', 'apppass', 10, 0);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

# ProxySQL 이 받은 쿼리를 DB서버로 분기 할 룰 등록
insert into mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup) values (1,1,'^SELECT.*FOR UPDATE$',10);
insert into mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup) values (2,1,'^SELECT',20);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

: 위 예제에서는 쿼리의 형태를 분석해서 분기 하도록 하였으나, 제작사에서는 해당 방법을 실제 운영 환경에서의 사용을 지양 하고 있으며, 실제로 호출되는 쿼리를 분석해서 digest로 등록, 관리 할것을 권장 하고 있다!!!!

상세 내용은 https://proxysql.com/documentation/proxysql-read-write-split-howto/ 링크를 참고!!

* ProxySQL (분기)서비스 테스트

: READ 테스트

#!/bin/bash
while true;
do
    mysql -u계정명 -p패스워드 -h아이피 -P포트 -N -e "select @@hostname, now()" 2>&1 | grep -v "Warning"
    sleep 1
done

: READ/WRITE 테스트

#!/bin/bash

while true;
do
    mysql -u계정명 -p패스워드 -h아이피 -P포트 -N -e "insert into testdb.insert_test select @@hostname, now()" 2>&1 | grep -v "Warning"
    sleep 1
done

 

* 참고자료

마리아DB 이중화(레플리케이션) 구성

https://gombach7.tistory.com/13

[MariaDB] Replication 구성 (on Docker)

ProxySQL VS Maxscale

https://proxysql.com/blog/proxysql-vs-maxscale-persistent-connection-response-time/

ProxySQL vs MaxScale: Persistent Connection, response time, and bugs - ProxySQL

- query split guide

https://proxysql.com/documentation/proxysql-read-write-split-howto/