[MariaDB] Galera Cluster 구성

(MariaDB 버전) 11.3.2-MariaDB

(OS 버전) Rocky Linux release 8.9 (Green Obsidian)

 

====================================  공통 작업  ====================================

 

Step 1. 패키지 업데이트

# yum -y update

(+) 기타 패키지 다운로드 (선택)

# yum -y install net-tools

# yum -y install epel-release cmake socat rsync

# yum -y groupinstall 'Development Tools'

 

Step 2. repo 및 MariaDB 다운로드

# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

# ls -lh /etc/yum.repos.d/

# yum install -y mariadb-server

# yum list installed *Maria*

 

Step 3. Galera Cluster 및 maria-backup 다운로드

# yum install -y galera

# yum install -y mariadb-backup

 

Step 4. MariaDB 실행

# systemctl enable mariadb

# systemctl start mariadb

# systemctl status mariadb

 

Step 5. MariaDB 접속 및 Galera Cluster 계정 생성

#  /usr/bin/mariadb -u root -p

> <-ENTER->

> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY '[PW 설정]';

> SHOW GRANTS FOR 'sstuser'@'localhost';

> GRANT ALL ON *.* TO 'sstuser'@'localhost';

> FLUSH PRIVILEGES;

 

Step 6. 방화벽 설정

# firewall-cmd --state

# firewall-cmd --permanent --add-service=mysql

# firewall-cmd --permanent --add-port={3306,4444,4567,4568}/tcp

# firewall-cmd --permanent --add-port=4567/udp

# firewall-cmd --reload

# firewall-cmd --list-all

 

Step 7. SELinux 허용

# setenforce 0

# sestatus

※ (Current mode)  enforcing ->  permissive

 

Step 8. Galera Cluster 설정

(경로) vi /etc/my.cnf.d/server.cnf

 

--------------------------------------------------------------------------------------------

[mysqld]

port=3306 ※ 다음 설정 하지 않으면 포트 0으로 연결됩니다.

 

[galera]

wsrep_on=ON

wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so

wsrep_cluster_address='gcomm://[서버 1 IP],[서버 2 IP],[서버 3 IP]'

binlog_format=row

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

wsrep_cluster_name='galera'

wsrep_node_address='[각 서버 IP]'

wsrep_node_name='[각 서버 이름'

wsrep_sst_method=mariabackup

wsrep_sst_auth=sstuser:dbsafer00

bind-address=0.0.0.0

--------------------------------------------------------------------------------------------

(+) Galera Plugin 위치 확인

# find / -name 'libgalera_smm.so'

 

Step 9. my.cnf, my.cnf.d 권한 및 소유자 변경

# chmod 775 /etc/my.cnf

# chmod 775 /etc/my.cnf.d

# chown mysql:mysql /etc/my.cnf

# chown mysql:mysql /etc/my.cnf.d

 

Step 10. MariaDB 중지

# systemctl stop mariadb

 

======================================  서버 1  =====================================

# galera_new_cluster

※ mariadb 가동 됩니다.

=====================================  서버 2, 3  ====================================

# systemctl start mariadb

=====================================  구성 확인  ===================================

> show status like 'wsrep%';

> show status like 'wsrep_incoming%';

 

# lsof -i :3306

# netstat -anp | grep mariadb

# netstat -anp | grep 192.168.112

=====================================  테스트  ======================================

Step 1. 서버 1, MariaDB 접속 및 데이터베이스/테이블 생성

# /usr/bin/mariadb -u sstuser -p

> <-ENTER->

> create database galera;

> use galera;

> create table test(

user_id int auto_increment promary key, 
user_name varchar(50) not null
);

> insert into test (user_name) valuse('yjyoo');

 

Step 2. 서버 2, 3 확인

※ 모두 정상적으로 동기화되었다.

==================================================================================

 

[참고]

https://da-new.tistory.com/53

https://da-new.tistory.com/84

https://jhdatabase.tistory.com/entry/MariaDB-Galera-Cluster-%EA%B5%AC%EC%84%B1-part-1

https://dbknowledge.tistory.com/18

https://blog.naver.com/theswice/221477714415