Mariadb Maxscale 구성

- DB Version : mariadb-10.5.9-linux

- OS Version : Centos 7.9

- DB 2 node (Master 1대- Slave 1대) Maxscale 2대 구성

Maxscle 설치 파일 다운로드

https://mariadb.com/downloads/community/maxscale/

MariaDB MaxScale: Advanced data proxy | MariaDB

 

- 설치 정보

MariaDB 버전	10.5.9
설치경로: Engine(basedir)	/usr/local/mariadb
Data(datadir)	/data/mariadb/DATA
Error log	/data/mariadb/ADMIN/error.log
BIN LOG	/data/mariadb/BINLOG
RELAY LOG	/data/mariadb/RELAYLOG
InnoDB LOG	/data/mariadb/IBLOG
socket	/data/mariadb/TMP/mysql.sock
config	/etc/my.cnf

 

MaxScale 버전	2.5.13
설치경로: Data	/maxscale/data
Error log	/maxscale/log/maxscale.log
config	/etc/maxscale.cnf

- Mariadb 설치

mariadb 설치과정(각 node별 동일하게 설치)

https://itexit.tistory.com/6

 

- MariaDB replication 구성

https://itexit.tistory.com/11

 

- DB node별(master,slave) maxscale 계정 생성

# maxscale 계정 생성 및 권한 설정
root@localhost:mysql >CREATE USER 'maxscale'@'192.168.10.%' IDENTIFIED BY 'xxxxx';
Query OK, 0 rows affected (0.007 sec)

root@localhost:mysql >GRANT SELECT ON mysql.user TO 'maxscale'@'192.168.10.%';
Query OK, 0 rows affected (0.006 sec)

root@localhost:mysql >GRANT SELECT ON mysql.db TO 'maxscale'@'192.168.10.%';
Query OK, 0 rows affected (0.004 sec)

root@localhost:mysql >GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'192.168.10.%';
Query OK, 0 rows affected (0.005 sec)

root@localhost:mysql >GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'192.168.10.%';
Query OK, 0 rows affected (0.005 sec)

root@localhost:mysql >GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'192.168.10.%';
Query OK, 0 rows affected (0.007 sec)

root@localhost:mysql >GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'192.168.10.%';
Query OK, 0 rows affected (0.005 sec)

root@localhost:mysql >GRANT SELECT ON mysql.procs_priv TO 'maxscale'@'192.168.10.%';
Query OK, 0 rows affected (0.005 sec)

root@localhost:mysql >GRANT SHOW DATABASES ON *.* TO 'maxscale'@'192.168.10.%';
Query OK, 0 rows affected (0.006 sec)

root@localhost:mysql >flush privileges;
Query OK, 0 rows affected (0.003 sec)

- Maxscale 구성(Maxscale 서버 2대 동일하게 설치)

# 유저, 그룹 maxscale로 생성
[root@TestMax1 ~]# groupadd maxscale
[root@TestMax1 ~]# useradd -g maxscale maxscale

# maxsclae DATA, LOG 디렉토리 생성
[root@TestMax1 ~]# mkdir -p /maxscale/data
[root@TestMax1 ~]# mkdir -p /maxscale/log

# 유저, 그룹 maxscale로 변경
[root@TestMax1 ~]# chown -R maxscale.maxscale /maxscale

# 패키지 설치
[root@TestMax1 ~]#  rpm -qa |grep nettle
nettle-2.7.1-8.el7.x86_64
[root@TestMax1 ~]# rpm -qa |grep trousers
trousers-0.3.14-2.el7.x86_64
[root@TestMax1 ~]# rpm -qa |grep gnutls
gnutls-3.3.29-9.el7_6.x86_64
[root@TestMax1 ~]# rpm -qa |grep libatomic
[root@TestMax1 ~]# rpm -ivh libatomic-4.8.5-44.el7.x86_64.rpm 
경고: libatomic-4.8.5-44.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
준비 중...                         ################################# [100%]
Updating / installing...
   1:libatomic-4.8.5-44.el7           ################################# [100%]
[root@TestMax1 ~]# rpm -qa |grep maxscale
[root@TestMax1 ~]# rpm -ivh maxscale-2.5.13-1.rhel.7.x86_64.rpm
경고: maxscale-2.5.13-1.rhel.7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID e3c94f49: NOKEY
준비 중...                         ################################# [100%]
Updating / installing...
   1:maxscale-2.5.13-1.rhel.7         ################################# [100%]
Created symlink from /etc/systemd/system/multi-user.target.wants/maxscale.service to /usr/lib/systemd/system/maxscale.service.
[root@TestMax1 ~]# rpm -qa |grep maxscale
maxscale-2.5.13-1.rhel.7.x86_64

- maxscale config 셋팅(Maxscale 서버 2대 동일하게 설치)

[root@TestMax1 ~]# vi /etc/maxscale.cnf
[maxscale]
threads=auto
datadir=/maxscale/data
logdir=/maxscale/log

[server1]
type=server
address=192.168.10.101 #address=10.0.0.6 ip로 정의 해야함 hostname 으로 하면 maxadmin list servers; 조회하면 slave 가 Slave of External Server, Running 이렇게 됨
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=192.168.10.102
port=3306
protocol=MariaDBBackend

[Replication-Monitor]
type=monitor
module=mariadbmon
servers=server1, server2
user=repl_user
password=비밀번호
auto_failover=true
auto_rejoin=true
monitor_interval=2000
enforce_read_only_slaves=1

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscale
password=비밀번호
use_sql_variables_in=master
enable_root_user = 1 #enable_root_user = 1 옵션으로 인해 root는 slave 서버에서도 insert 허용

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006

※. Error Code: 1064. Routing query to backend failed. See the error log for further details. 

오류 발생시 해결책 : MaxScale, ReadWriteSplit 설정 수정
vi /etc/maxscale.cnf
use_sql_variables_in=master 추가

- Maxscale 기동 및 상태 체크

# maxscale 기동
[root@TestMax1 ~]# systemctl start maxscale.service

# maxscale 프로세스 확인
[root@TestMax1 ~]# ps -ef | grep maxscale
root       8793   5648  0 14:45 pts/0    00:00:00 grep --color=auto maxscale
maxscale 237840      1  0  6월10 ?      00:04:04 /usr/bin/maxscale

# maxscale 상태 확인
[root@TestMax1 ~]# systemctl status maxscale.service
● maxscale.service - MariaDB MaxScale Database Proxy
   Loaded: loaded (/usr/lib/systemd/system/maxscale.service; enabled; vendor preset: disabled)
   Active: active (running) since 목 2021-06-10 15:46:32 KST; 14s ago
  Process: 237837 ExecStart=/usr/bin/maxscale (code=exited, status=0/SUCCESS)
  Process: 237835 ExecStartPre=/usr/bin/install -d /var/lib/maxscale -o maxscale -g maxscale (code=exited, status=0/SUCCESS)
  Process: 237832 ExecStartPre=/usr/bin/install -d /var/run/maxscale -o maxscale -g maxscale (code=exited, status=0/SUCCESS)
 Main PID: 237840 (maxscale)
   CGroup: /system.slice/maxscale.service
           └─237840 /usr/bin/maxscale

 6월 10 15:46:32 TestMax1 maxscale[237840]: Server 'server1' charset: utf8
 6월 10 15:46:32 TestMax1 maxscale[237840]: Server 'server2' charset: utf8
 6월 10 15:46:32 TestMax1 maxscale[237840]: Server changed state: server1[192.168.10.101:3306]: master_up. [Auth Error, Down] -> [Master, Running]
 6월 10 15:46:32 TestMax1 maxscale[237840]: Server changed state: server2[192.168.10.102:3306]: slave_up. [Auth Error, Down] -> [Slave, Running]
 6월 10 15:46:32 TestMax1 maxscale[237840]: [mariadbmon] read_only set to ON on 'server2'.
 6월 10 15:46:32 TestMax1 maxscale[237840]: Starting a total of 1 services...
 6월 10 15:46:32 TestMax1 maxscale[237840]: (Read-Write-Listener) Listening for connections at [::]:4006
 6월 10 15:46:32 TestMax1 maxscale[237840]: Service 'Read-Write-Service' started (1/1)
 6월 10 15:46:32 TestMax1 systemd[1]: Started MariaDB MaxScale Database Proxy.
 6월 10 15:46:33 TestMax1 maxscale[237840]: Read 7 user@host entries from 'server1' for service 'Read-Write-Service'.

# maxscale 서버 확인 
[root@TestMax1 ~]# maxctrl list servers
┌─────────┬───────────────┬──────┬─────────────┬────────
│ Server  │ Address       │ Port │ Connections │ State           │ GTID     │
├─────────┼───────────────┼──────┼─────────────┼────────
│ server1 │192.168.10.101 │ 3306 │ 0           │ Master, Running │ 1-101-65 │
├─────────┼───────────────┼──────┼─────────────┼────────
│ server2 │192.168.10.102 │ 3306 │ 0           │ Slave, Running  │ 1-101-65 │
└─────────┴───────────────┴──────┴─────────────┴────────

# maxscale 서비스 확인 
[root@TestMax1 ~]# maxctrl list services
┌────────────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐
│ Service            │ Router         │ Connections │ Total Connections │ Servers          │
├────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ Read-Write-Service │ readwritesplit │ 0           │ 0                 │ server1, server2 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘


# maxscale 모니터 확인  
[root@TestMax1 ~]# maxctrl list monitors
┌─────────────────────┬─────────┬──────────────────┐
│ Monitor             │ State   │ Servers          │
├─────────────────────┼─────────┼──────────────────┤
│ Replication-Monitor │ Running │ server1, server2 │
└─────────────────────┴─────────┴──────────────────┘

참조 :
- https://mariadb.com/kb/en/documentation/
- https://mariadb.com/docs/