MaxScale은 MariaDB 에서 지원하는 DB Proxy 입니다.
HA proxy 와 동일하나 DB read/ write 쿼리를 지정한 db 서버로 나눠주는 기능이 있는 등 DB에 더 특화되어있습니다.
read / write 분산이 필요한 아키텍처에서 MaxScale 을 사용하면 효율적으로 분산 구조를 구성할 수 있습니다.
-1. MaxScale 설치
[root@ip-172-31-27-142 /]# docker search maxscale NAME DESCRIPTION STARS OFFICIAL AUTOMATED asosso/maxscale MariaDB MaxScale for docker based on CentOS 7 15 [OK]
=> 위 이미지가 centos7 환경이라 해당 이미지 사용
RPM 으로 설치 가능하나 편하게 docker로 진행함
[root@ip-172-31-27-142 /]# docker run -it --name MS_test asosso/maxscale -p 3314:3312 Info : MaxScale will be run in the terminal process. Configuration file : /etc/maxscale.cnf Log directory : /var/log/maxscale Data directory : /var/lib/maxscale Module directory : /usr/lib64/maxscale Service cache : /var/cache/maxscale
=> host의 포트 3314를 컨테이너의 3312와 매핑하는데 maxscale 의 R/W split 하는 기능의 listener를 3312 port 사용하도록 설정할 것이기 때문
[root@ip-172-31-27-142 /]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 0fba8392e0ab 97ba6e5133ea "/usr/bin/maxscale -…" 16 hours ago Up 2 hours 4006/tcp, 4008/tcp, 4442/tcp, 6603/tcp, 0.0.0.0:3314->3312/tcp maxscale
=> 설치완료
-2. MaxScale 설정 및 기동
[root@ip-172-31-27-142 /]# docker exec -it 0fba8392e0ab /bin/bash bash-4.2$
=> container 접속
bash-4.2$ cat /etc/maxscale.cnf [maxscale] threads=auto [Splitter Service] type=service router=readwritesplit servers=dbserv1, dbserv2, dbserv3 weightby=serv_weight max_slave_connections=100% max_slave_replication_lag=120 router_options=slave_selection_criteria=LEAST_CURRENT_OPERATIONS,master_accept_reads=false ### client의 요청을 받아 read / write 구분하여 해당하는 DB에 분기시키는 기능 ###slave connection을 100%사용하며 복제지연이 120초이상이면 slave 로 넘기지 않음 user=maxscale passwd=test123 ### maxscale <-> DB 간 연동 계정, DB에 생성해야함 [Splitter Listener] type=listener service=Splitter Service protocol=MySQLClient port=3312 ### 위의 서비스를 listen 하는 listener 설정 ###3312 포트를 설정하며 위에서 host의 3314로 요청이오면 docker container의 이 리스너로 forward됨 [dbserv1] type=server address=11.111.111.111 port=3312 protocol=MySQLBackend serv_weight=1 [dbserv2] type=server address=22.222.222.222 port=3312 protocol=MySQLBackend serv_weight=3 [dbserv3] type=server address=33.333.333.333 port=3312 protocol=MySQLBackend serv_weight=3 ### DB설정 ### serv_weight은 가중치로 위에서는 1+3+3이 분모가 됨 ### ex) dbserv3은 3/7의 지분을 가짐 [Replication Monitor] type=monitor module=mysqlmon servers=dbserv1, dbserv2, dbserv3 user=maxscale passwd=test123 ### 위의 DB들의 replication 상태를 모니터링하는 기능 [CLI] type=service router=cli [CLI Listener] type=listener service=CLI protocol=maxscaled address=localhost port=6603 ### maxadmin 이라는 관리기능 설정
-3. MaxScale 기능
bash-4.2$ maxadmin -uadmin -pmariadb MaxScale> list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- dbserv1 | 52.78.212.75 | 3312 | 0 | Master, Running dbserv2 | 52.79.251.183 | 3312 | 0 | Slave, Running dbserv3 | 54.180.116.199 | 3312 | 0 | Slave, Running -------------------+-----------------+-------+-------------+-------------------- MaxScale> list services Services. --------------------------+-------------------+--------+----------------+------------------- Service Name | Router Module | #Users | Total Sessions | Backend databases --------------------------+-------------------+--------+----------------+------------------- Splitter-Service | readwritesplit | 1 | 9 | dbserv1, dbserv2, dbserv3 CLI | cli | 3 | 3 | --------------------------+-------------------+--------+----------------+------------------- MaxScale> list monitors ---------------------+--------------------- Monitor | Status ---------------------+--------------------- Replication-Monitor | Running ---------------------+--------------------- MaxScale>
=> 위 maxscale.cnf 설정파일에서 설정한 DB, Service, Monitoring들이 제대로 등록됨을 확인
-4. DB server 상태 확인
MaxScale> show servers
Server 0x1c580f0 (dbserv1)
Server: 11.111.111.111
Status: Master, Running
Protocol: MySQLBackend
Port: 3312
Server Version: 10.3.13-MariaDB-log
Node Id: 1
Master Id: -1
Last event: new_master
Triggered at: Fri, 22 Mar 2019 15:34:32 GMT
Slave Ids: 2, 3
Repl Depth: 0
Server Parameters:
serv_weight 1
Number of connections: 8
Current no. of conns: 0
Current no. of operations: 0
Number of routed packets: 22
Server 0x1c56df0 (dbserv2)
Server: 22.222.222.222
Status: Slave, Running
Protocol: MySQLBackend
Port: 3312
Server Version: 10.3.13-MariaDB-log
Node Id: 2
Master Id: 1
Last event: new_slave
Triggered at: Fri, 22 Mar 2019 15:34:32 GMT
Slave Ids:
Repl Depth: 1
Server Parameters:
serv_weight 3
Number of connections: 8
Current no. of conns: 0
Current no. of operations: 0
Number of routed packets: 34
Server 0x1c55af0 (dbserv3)
Server: 33.333.333.333
Status: Slave, Running
Protocol: MySQLBackend
Port: 3312
Server Version: 10.3.13-MariaDB-log
Node Id: 3
Master Id: 1
Last event: new_slave
Triggered at: Fri, 22 Mar 2019 15:34:32 GMT
Slave Ids:
Repl Depth: 1
Server Parameters:
serv_weight 3
Number of connections: 8
Current no. of conns: 0
Current no. of operations: 0
Number of routed packets: 19
=> DB 서버들 간 replication 상태, 현재 처리중인 session등을 볼 수 있음
-5. 쿼리 수행
[ip-172-31-27-142:masvc01]/] mysql -umaxscale -p -P3314 -h52.78.212.75 -e "use maxscale; select count(*) from split_test;" Enter password: [ip-172-31-27-142:masvc01]/] mysql -umaxscale -p -P3314 -h52.78.212.75 -e "use maxscale; insert into split_test values(1,1,'a','a'); commit;" Enter password:
MaxScale> show service Splitter-Service
Service: Splitter-Service
Router: readwritesplit
State: Started
use_sql_variables_in: all
slave_selection_criteria: LEAST_CURRENT_OPERATIONS
master_failure_mode: fail_instantly
max_slave_replication_lag: 120
retry_failed_reads: true
strict_multi_stmt: false
strict_sp_calls: false
disable_sescmd_history: true
max_sescmd_history: 0
master_accept_reads: false
Number of router sessions: 9
Current no. of router sessions: 1
Number of queries forwarded: 30
Number of queries forwarded to master: 6 (20.00%)
Number of queries forwarded to slave: 24 (80.00%)
Number of queries forwarded to all: 18 (60.00%)
Connection distribution based on serv_weight server parameter.
Server Target % Connections Operations
Global Router
dbserv1 14.2% 0 0 0
dbserv2 42.8% 0 0 0
dbserv3 42.8% 0 0 0
Started: Fri Mar 22 15:34:33 2019
Root user access: Disabled
Backend databases:
[11.111.111.111]:3312 Protocol: MySQLBackend Name: dbserv1
[22.222.222.222]:3312 Protocol: MySQLBackend Name: dbserv2
[33.333.333.333]:3312 Protocol: MySQLBackend Name: dbserv3
Routing weight parameter: serv_weight
Total connections: 10
Currently connected: 1
=> SELECT는 설정한대로 slave에만
DML은 master에서만 수행됨을 확인
-1. DB server 상태 확인
MaxScale> show servers
Server 0x1c580f0 (dbserv1)
Server: 11.111.111.111
Status: Master, Running
Protocol: MySQLBackend
Port: 3312
Server Version: 10.3.13-MariaDB-log
Node Id: 1
Master Id: -1
Last event: new_master
Triggered at: Fri, 22 Mar 2019 15:34:32 GMT
Slave Ids: 2, 3
Repl Depth: 0
Server Parameters:
serv_weight 1
Number of connections: 8
Current no. of conns: 0
Current no. of operations: 0
Number of routed packets: 22
Server 0x1c56df0 (dbserv2)
Server: 22.222.222.222
Status: Slave, Running
Protocol: MySQLBackend
Port: 3312
Server Version: 10.3.13-MariaDB-log
Node Id: 2
Master Id: 1
Last event: new_slave
Triggered at: Fri, 22 Mar 2019 15:34:32 GMT
Slave Ids:
Repl Depth: 1
Server Parameters:
serv_weight 3
Number of connections: 8
Current no. of conns: 0
Current no. of operations: 0
Number of routed packets: 34
Server 0x1c55af0 (dbserv3)
Server: 33.333.333.333
Status: Slave, Running
Protocol: MySQLBackend
Port: 3312
Server Version: 10.3.13-MariaDB-log
Node Id: 3
Master Id: 1
Last event: new_slave
Triggered at: Fri, 22 Mar 2019 15:34:32 GMT
Slave Ids:
Repl Depth: 1
Server Parameters:
serv_weight 3
Number of connections: 8
Current no. of conns: 0
Current no. of operations: 0
Number of routed packets: 19
=> DB 서버들 간 replication 상태, 현재 처리중인 session등을 볼 수 있음
-2. 쿼리 수행
[ip-172-31-27-142:masvc01]/] mysql -umaxscale -p -P3314 -h52.78.212.75 -e "use maxscale; select count(*) from split_test;" Enter password: [ip-172-31-27-142:masvc01]/] mysql -umaxscale -p -P3314 -h52.78.212.75 -e "use maxscale; insert into split_test values(1,1,'a','a'); commit;" Enter password:
MaxScale> show service Splitter-Service
Service: Splitter-Service
Router: readwritesplit
State: Started
use_sql_variables_in: all
slave_selection_criteria: LEAST_CURRENT_OPERATIONS
master_failure_mode: fail_instantly
max_slave_replication_lag: 120
retry_failed_reads: true
strict_multi_stmt: false
strict_sp_calls: false
disable_sescmd_history: true
max_sescmd_history: 0
master_accept_reads: false
Number of router sessions: 9
Current no. of router sessions: 1
Number of queries forwarded: 30
Number of queries forwarded to master: 6 (20.00%)
Number of queries forwarded to slave: 24 (80.00%)
Number of queries forwarded to all: 18 (60.00%)
Connection distribution based on serv_weight server parameter.
Server Target % Connections Operations
Global Router
dbserv1 14.2% 0 0 0
dbserv2 42.8% 0 0 0
dbserv3 42.8% 0 0 0
Started: Fri Mar 22 15:34:33 2019
Root user access: Disabled
Backend databases:
[11.111.111.111]:3312 Protocol: MySQLBackend Name: dbserv1
[22.222.222.222]:3312 Protocol: MySQLBackend Name: dbserv2
[33.333.333.333]:3312 Protocol: MySQLBackend Name: dbserv3
Routing weight parameter: serv_weight
Total connections: 10
Currently connected: 1
=> SELECT는 설정한대로 slave에만
DML은 master에서만 수행됨을 확인