[MariaDB - Galera Cluster 구성] part 1 편에 이어 maxscale 구성을 진행하도록 하겠습니다!
사전 구성
Galera Cluster 설치
Part 1 URL : https://jhdatabase.tistory.com/34
[MariaDB - Galera Cluster 구성] part 1
안녕하세요 이번글에선 galera cluster를 구성하도록 하겠습니다. 최종적인 아키텍처는 아래와 같으나 저는 DB node 3개와 그를 관리할 maxscale을 설치 진행하겠습니다. Galera Cluster란 갈레라 클러스터
jhdatabase.tistory.com
Maxscale
갈레라에서 MaxScale은 다수의 MariaDB간에 Read / Write 분산과 모니터링, change master Replication에서 Healthcheck를 통한 Failover등을 지원할 수 있는 DB proxy 툴입니다.
■ 필수 패키지 설치
[root@maxscale ~]# yum -y install libcurl libaio openssl gnutls libatomic
■ 유저 생성
[root@maxscale ~]# groupadd maxscale
[root@maxscale ~]# useradd -g maxscale maxscale
■ maxscale install
download URL : https://downloads.mariadb.com/MaxScale/2.5.0/centos/7/x86_64/
MaxScale/2.5.0/centos/7/x86_64/ - MariaDB
downloads.mariadb.com
[root@maxscale ~]# wget https://downloads.mariadb.com/MaxScale/2.5.0/centos/7/x86_64/maxscale-2.5.0.centos.7.tar.gz
--2022-02-22 14:03:39-- https://downloads.mariadb.com/MaxScale/2.5.0/centos/7/x86_64/maxscale-2.5.0.centos.7.tar.gz
Resolving downloads.mariadb.com (downloads.mariadb.com)... 104.17.191.14, 104.18.135.24, 2606:4700::6812:8718, ...
Connecting to downloads.mariadb.com (downloads.mariadb.com)|104.17.191.14|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 60848706 (58M) [application/octet-stream]
Saving to: ‘maxscale-2.5.0.centos.7.tar.gz’
100%[===================================================================================================================================================>] 60,848,706 16.5MB/s in 3.7s
2022-02-22 14:03:44 (15.5 MB/s) - ‘maxscale-2.5.0.centos.7.tar.gz’ saved [60848706/60848706]
[root@maxscale ~]# tar -zxvf maxscale-2.5.0.centos.7.tar.gz
[root@maxscale ~]# mv maxscale-2.5.0.centos.7 /maxscale
[root@maxscale ~]# mkdir -p /maxscale/var/mysql/plugin
■ cnf file 수정
[root@maxscale ]# cp /maxscale/etc/maxscale.cnf.template maxscale.cnf
[root@maxscale etc]# vi /maxscale/etc/maxscale.cnf
# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-24/
# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-maxscale-configuration-guide/
[maxscale]
threads=auto
# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#
[server1]
type=server
address=10.70.101.78
port=3306
protocol=MariaDBBackend
[server2]
type=server
address=10.70.101.79
port=3306
protocol=MariaDBBackend
[server3]
type=server
address=10.70.101.80
port=3306
protocol=MariaDBBackend
[Galera-Monitor]
type=monitor
module=galeramon
servers=server1, server2, server3
user=maxscale
password=1
monitor_interval=2000ms
[Splitter-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
password=1
##readwritesplit 라우터는 일관성을 유지하면서 클러스터의 읽기 전용 처리 능력을 높이기 위해 설계
[Splitter-Listener]
type=listener
service=Splitter-Service
protocol=MariaDBClient
port=3306
# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-monitor/
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-24-readconnroute/
[root@maxscale ~]# chown -R maxscale.maxscale /maxscale/
■ Maxsclae 사용자 생성 및 권한 부여 (node1)
MariaDB [(none)]> CREATE USER 'maxscale'@'%' IDENTIFIED BY '1';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.user TO 'maxscale'@'%';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.db TO 'maxscale'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE,SUPER,RELOAD on *.* to 'maxscale'@'%';
Query OK, 0 rows affected (0.00 sec)
■ maxscale start
[root@maxscale ~]# /maxscale/bin/maxscale --basedir=/maxscale --user=maxscale
■ 연결된 서버 조회
*Master는 Galeramon Module이 클러스터 내의 노드 중 show status like '%wsrep_local_index%'; 값이 가장 낮은 노드를 Master로 선정합니다.
[root@maxcale]# maxctrl list servers ┌─────────┬──────────────┬──────┬─────────────┬───│ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼───│ server1 │ 10.70.101.78 │ 3306 │ 0 │ Master, Synced, Running │ │ ├─────────┼──────────────┼──────┼─────────────┼───│ server2 │ 10.70.101.79 │ 3306 │ 0 │ Slave, Synced, Running │ │ ├─────────┼──────────────┼──────┼─────────────┼───│ server3 │ 10.70.101.80 │ 3306 │ 0 │ Slave, Synced, Running │ │ └─────────┴──────────────┴──────┴─────────────┴───
■ 서비스 리스너 조회
[root@maxcale ~]# maxctrl list listeners
┌───────────────────┬──────┬──────┬─────────┬─────
│ Name │ Port │ Host │ State │ Service │
├───────────────────┼──────┼──────┼─────────┼─────
│ Splitter-Listener │ 3306 │ :: │ Running │ Splitter-Service │
└───────────────────┴──────┴──────┴─────────┴─────
■ 서비스 확인
## maxsclae을 통해 DB접속
[root@node1 ~]# mysql -umaxscale -p1 -h10.70.101.81
[root@maxcale ~]# maxctrl list services
┌──────────────────┬────────────────┬─────────────
│ Service │ Router │ Connections │ Total Connections │ Servers │
├──────────────────┼────────────────┼─────────────
│ Splitter-Service │ readwritesplit │ 1 │ 1 │ server1, server2, server3 │
└──────────────────┴────────────────┴─────────────
※ maxscale을 통해 서비스 접속한 누적 수
■ Data insert시 Read/Write 분산 확인
maxscale에서 sakila 데이터 insert 시 master로만 write 되는것을 볼 수 있습니다.
<maxscale>
[root@maxcale ~]# mysql -umaxscale -p1 -h10.70.101.81 < /root/sakila-mv-schema.sql
[root@maxcale ~]# mysql -umaxscale -p1 -h10.70.101.81 < /root/sakila-mv-data.sql
<node1(MASTER)>
[root@node1 ~]# mysqladmin -uroot -proot -i 2 'process'
+----+-------------+--------------------+--------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+--------------------+--------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
| 1 | system user | | | Sleep | 82950 | wsrep aborter idle | | 0.000 |
| 2 | system user | | | Sleep | 82950 | | | 0.000 |
| 3 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 |
| 4 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 5 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 6 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 7 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 |
| 17 | maxscale | 10.70.101.81:52392 | | Sleep | 0 | | | 0.000 |
| 32 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
| 33 | maxscale | 10.70.101.81:52480 | sakila | Query | 0 | creating table | CREATE TABLE address (
address_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
address VARCHAR(50) | 0.000 |
+----+-------------+--------------------+--------+---------+-------+--------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+--------------------+--------+---------+-------+--------------------------+------------------+----------+
| 1 | system user | | | Sleep | 83171 | wsrep aborter idle | | 0.000 |
| 2 | system user | | | Sleep | 83171 | | | 0.000 |
| 3 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 |
| 4 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 5 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 6 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 7 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 |
| 17 | maxscale | 10.70.101.81:52392 | | Sleep | 1 | | | 0.000 |
| 34 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
| 35 | maxscale | 10.70.101.81:52488 | sakila | Query | 0 | init | COMMIT | 0.000 |
+----+-------------+--------------------+--------+---------+-------+--------------------------+------------------+----------+
<node2 (SLAVE)>
[root@node2 ~]# mysqladmin -uroot -proot -i 2 'process'
-----------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+--------------------+----+---------+-------+--------------------------+------------------+----------+
| 1 | system user | | | Sleep | 82787 | wsrep aborter idle | | 0.000 |
| 2 | system user | | | Sleep | 3 | committed 50 | | 0.000 |
| 3 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 4 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 |
| 5 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 6 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 7 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 |
| 13 | maxscale | 10.70.101.81:41728 | | Sleep | 0 | | | 0.000 |
| 25 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
+----+-------------+--------------------+----+---------+-------+--------------------------+------------------+----------+
<node3 (SLAVE)>
[root@node3 ~]# mysqladmin -uroot -proot -i 2 'process'
+----+-------------+--------------------+----+---------+-------+--------------------------+------------------+----------+| Id | User | Host | db | Command | Time | State | Info | Progress |+----+-------------+--------------------+----+---------+-------+--------------------------+------------------+----------+| 1 | system user | | | Sleep | 82783 | wsrep aborter idle | | 0.000 || 2 | system user | | | Sleep | 2 | committed 50 | | 0.000 || 3 | system user | | | Daemon | | InnoDB purge worker | | 0.000 || 4 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 || 5 | system user | | | Daemon | | InnoDB purge worker | | 0.000 || 6 | system user | | | Daemon | | InnoDB purge worker | | 0.000 || 7 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 || 12 | maxscale | 10.70.101.81:38658 | | Sleep | 0 | | | 0.000 || 24 | root | localhost | | Query | 0 | init | show processlist | 0.000 |+----+-------------+--------------------+----+---------+-------+--------------------------+------------------+----------+
Read & Failover Test
■ Read test
※ Slave 쪽으로 select 작업 확인
[root@maxcale ~]# for i in `seq 1 10`; do mysql -h 10.70.101.81 -u maxscale -p1 -e"select @@hostname;" 2>/dev/null & done
+------------+
| @@hostname |
+------------+
| garela-2 |
+------------+
+------------+
| @@hostname |
+------------+
| garela-2 |
+------------+
+------------+
| @@hostname |
+------------+
| garela-2 |
+------------+
+------------+
| @@hostname |
+------------+
| garela-1 |
+------------+
■ Failover test
master node kill시 wsrep_local_index가 가장 낮은 노드가 master로 선정되어 올라온것을 확인 할 수 있습니다.
## master node kill
[root@node1 ~]# ps -ef | grep mysql
root 1043 30042 0 10:27 pts/0 00:00:00 grep --color=auto mysql
mysql 14940 1 0 2월22 ? 00:01:55 /data/maria/bin/mysqld --basedir=/data/maria --datadir=/data/data --plugin-dir=/data/maria/lib/plugin --user=mysql --wsrep_on=ON --wsrep_provider=/data/maria/lib/galera/libgalera_smm.so --wsrep-new-cluster --log-error=/data/log/mariadb.log --pid-file=/data/log/mariadb.pid --socket=/tmp/mysql.sock --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
[root@node1 ~]# kill -9 14940
## node2
MariaDB [(none)]> show status like '%wsrep_local_index%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| wsrep_local_index | 1 |
+-------------------+-------+
1 row in set (0.00 sec)
## node3
MariaDB [(none)]> show status like '%wsrep_local_index%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| wsrep_local_index | 2 |
+-------------------+-------+
1 row in set (0.00 sec)
[root@maxcale ~]# maxctrl list servers
┌─────────┬──────────────┬──────┬─────────────┬───
│ Server │ Address │ Port │ Connections │ State │ GTID │
├─────────┼──────────────┼──────┼─────────────┼───
│ server1 │ 10.70.101.78 │ 3306 │ 0 │ Down │ │
├─────────┼──────────────┼──────┼─────────────┼───
│ server2 │ 10.70.101.79 │ 3306 │ 0 │ Master, Synced, Running │ │
├─────────┼──────────────┼──────┼─────────────┼───
│ server3 │ 10.70.101.80 │ 3306 │ 0 │ Slave, Synced, Running │ │
└─────────┴──────────────┴──────┴─────────────┴───
## log
2022-02-23 10:28:00 notice : Server changed state: server1[10.70.101.78:3306]: master_down. [Master, Synced, Running] -> [Down]
2022-02-23 10:28:00 notice : Server changed state: server2[10.70.101.79:3306]: new_master. [Slave, Synced, Running] -> [Master, Synced, Running]
2022-02-23 10:28:00 notice : Master switch detected: lost a master and gained a new one
2022-02-23 10:45:18 notice : Server changed state: server1[10.70.101.78:3306]: master_up. [Down] -> [Master, Synced, Running]
2022-02-23 10:45:18 notice : Server changed state: server2[10.70.101.79:3306]: new_slave. [Master, Synced, Running] -> [Slave, Synced, Running]
'DataBase > MySQL & MariaDB' 카테고리의 다른 글
| [MariaDB - Mariabackup error] failed to copy enough redo log (0) | 2022.02.24 |
|---|---|
| [MariaDB - Galera Cluster & sysbench 이용 동기화 테스트 ] part 3 (0) | 2022.02.24 |
| [MariaDB - Galera Cluster 구성] part 1 (0) | 2022.02.24 |
| [MySQL - MHA 구성 + failover/switchover] part 2 (0) | 2022.01.28 |
| [MySQL - MHA 구성 + VIP생성] part 1 (2) | 2022.01.28 |