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