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에서만 수행됨을 확인