[Docker] Replication 을 위한 Proxy 셋업

* 아키텍처

 

* ProxySQL Container 실행

- Docker Host에 컨피그를 위한 폴더 생성 (권한 777)

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

* config file 생성

- Path : /shared/proxysql/conf/proxysql.cnf (file permission 644)

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_timeout=500
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    connect_retries_on_failure=10
}

* ProxySQL Container 실행

> docker run -it --name proxysql -h proxysql \
--net [연결할 네트워크] --net-alias=proxysql \
-p 16032:6032 -p 16033:6033 \
-v /shared/proxysql/data:/var/lib/proxysql \
-v /shared/proxysql/conf/proxysql.cnf:/etc/proxysql.cnf \
-d proxysql/proxysql

- 실행 예제

> docker run -it --name proxysql -h proxysql --net replbridge --net-alias=netproxysql -p 16032:6032 -p 16033:6033 -v /shared/proxysql/data:/var/lib/proxysql -v /shared/proxysql/conf/proxysql.cnf:/etc/proxysql.cnf -d proxysql/proxysql

- 컨테이너 실행 확인

> docker ps --format "table {{.ID}}\t{{.Names}}\t{{.Status}}"

- ProxySQL 접속

> mysql -h127.0.0.1 -P16032 -uradmin -pradmin --prompt "ProxySQL Admin>"

* ProxySQL 용 DB 계정 생성

: db001에 접속 하여 계정을 생성한다.

# 테스트용 데이터베이스 생성
mysql> create database testdb default character set utf8;

# 어플리케이션에서 사용할 계정 생성 및 읽기/쓰기 권한 부여
mysql> create user appuser@'%' identified by 'apppass';
mysql> grant select, insert, update, delete on testdb.* to appuser@'%';

# ProxySQL 에서 사용할 모니터링 용도 계정 생성 및 필요한 권한 부여
mysql> create user 'monitor'@'%' identified by 'monitor';
mysql> grant REPLICATION CLIENT on *.* to 'monitor'@'%';

# 권한 반영
mysql> flush privileges;

* ProxySQL 에 각 서버 정보 등록

- ProxySQL 접속

> mysql -h127.0.0.1 -P16032 -uradmin -pradmin --prompt "ProxySQL Admin>"

- 정보 등록

# hostgroup_id -> 10:Read/Write, 20:Read
# Read/Write group
ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES(10,'db001',3306);

# Read group
ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES(20,'db001',3306);
ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES(20,'db002',3306);
ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES(20,'db003',3306);

# replication group
ProxySQL Admin> INSERT INTO mysql_replication_hostgroups VALUES(10,20,'read_only','');

# 정보 적용
ProxySQL Admin> LOAD MYSQL SERVERS TO RUNTIME;
ProxySQL Admin> SAVE MYSQL SERVERS TO DISK;

# application 용 유저 정보 등록
ProxySQL Admin> INSERT INTO mysql_users(username,password,default_hostgroup,transaction_persistent) VALUES('appuser','apppass',10,0);

# user 정보 등록
ProxySQL Admin> LOAD MYSQL USERS TO RUNTIME;
ProxySQL Admin> SAVE MYSQL USERS TO DISK;

# 쿼리 룰 등록
## SELECT, UPDATE는 10번그룹으로
ProxySQL Admin> INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup)VALUES(1,1,'^SELECT.*FOR UPDATE$',10);
## SELECT 는 모두 20번 그룹으로 가도록 등록
ProxySQL Admin> INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup)VALUES(2,1,'^SELECT',20);

# 쿼리 룰 적용
ProxySQL Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
ProxySQL Admin> SAVE MYSQL QUERY RULES TO DISK;

* DB Connection 체킹

: 어느 DB로 접속해서 쿼리를 요청 하는지 확인 하는 스크립트

> vi app_test_conn.sh

#!/bin/bash
while true;
do
    mysql -uappuser -papppass -h127.0.0.1 -P16033 -N -e "select @@hostname, now()" 2>&1|grep -v "Warning"
    sleep 1
done