mysql mariadb galera cluster 설정 방법

mysql mariadb galera cluster  설정 방법

 

server 2대 준비

 

server node1 : 192.168.217.130

server node2 : 192.168.217.129

 

0. Selinux disabled(선택사항)

나의 경우 VM 실행시 Selinux관련 Error가 발생하여 Selinux disabled하고 시작.

 

--Disable selinux(전 서버 실행)

# sestatus --> selinux 상태 확인 명령어

SELinux status:                 enabled

SELinuxfs mount:                /sys/fs/selinux

SELinux root directory:         /etc/selinux

Loaded policy name:             targeted

Current mode:                   enforcing

Mode from config file:          enforcing

Policy MLS status:              enabled

Policy deny_unknown status:     allowed

Max kernel policy version:      31

 

  # setenforce 0 

  # sed -i --follow-symlinks 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux

 

# sestatus

SELinux status:                 enabled

SELinuxfs mount:                /sys/fs/selinux

SELinux root directory:         /etc/selinux

Loaded policy name:             targeted

Current mode:                   permissive

Mode from config file:          disabled

Policy MLS status:              enabled

Policy deny_unknown status:     allowed

Max kernel policy version:      31

 

 

1.host 이름 설정(Server 2대 공통 작업)

 

# vi /etc/hosts

 

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

 

192.168.217.130 node1

192.168.217.129 node2

 

(hosts를 설정하지 않아도 실제로 테스트시 galera cluster 작동시에는 문제 없었음. 선택사항으로 봐도 무방 <내경우에는 하지 않았음>)

 

 

2.Server 2대에 Mariadb 혹은 mysql 설치 및 ROOT암호 설정 & 계정생성 등 기타 설정 후 DB STOP (내 티스토리내에 있으므로 생략)

 

3.각 서버의 설정

 

# vi /etc/my.cnf.d/server.cnf

 

[node1 Server(Master)]

~~

wsrep_on=ON

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

wsrep_cluster_address='gcomm://'

wsrep_cluster_name='cluster'

wsrep_node_address='192.168.217.130'

wsrep_node_name='cluster01'

wsrep_sst_method=rsync

binlog_format=row

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

bind-address=0.0.0.0

 

 

[node2 Server(Slave)]

wsrep_on=ON

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

wsrep_cluster_address='gcomm://192.168.217.130'

wsrep_cluster_name='cluster'

wsrep_node_address='192.168.217.129'

wsrep_node_name='cluster02'

wsrep_sst_method=rsync

binlog_format=row

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

bind-address=0.0.0.0

 

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

wsrep_provider=/usr/lib64/galera/libgalera_smm.so --> yum으로 mysql 설치하였는데 경로 맞았음. 다른방법으로 설치하였다면 확인

 

-Master의 경우

wsrep_cluster_address='gcomm://' 

 

-Slave의 경우

wsrep_cluster_address='gcomm://[masterIp]' 

 

wsrep_cluster_name=[변수명] 

변수명이긴 하나 Matser와 Slave동일하게 입력.

 

wsrep_node_address 

현재 작업하고 있는 서버 IP

 

wsrep_node_name=[각서버의 고유변수명] 

변수명이긴 하나 Matser와 Slave다르게 입력.(고유해야 함) 

혹은 현재 사용하고 있는 hosts명 기재

(검색시 호스트명 설정한 자료는 host명을 기재하였으나 그냥 고유변수명으로 해도 작동 문제없음. <선택사항으로 생각>)

 

그외의 항목은 적힌 그대로 입력하면된다.

 

4.DB시작

 

-마스터 DB부터 실행

 

# service mysql start --wsrep-new-cluster

혹 서비스 등록이 되지 않았다면

# /etc/rc.d/init.d/mysql start --wsrep-cluster

 

Starting mysql (via systemctl):                            [  OK  ]

 

 

 

 

-슬레이브 DB 실행

# service mysql start

 

 

5.확인

 

(Master 또는 Slave 어디서든 실행해도 무관)

 

mysql 접속 후 

MariaDB [(none)]> show status like 'wsrep%';

+------------------------------+-------------------------------------------+

| Variable_name                | Value                                     |

+------------------------------+-------------------------------------------+

| wsrep_apply_oooe             | 0.000000                                  |

| wsrep_apply_oool             | 0.000000                                  |

| wsrep_apply_window           | 0.000000                                  |

| wsrep_causal_reads           | 0                                         |

| wsrep_cert_deps_distance     | 0.000000                                  |

| wsrep_cert_index_size        | 0                                         |

| wsrep_cert_interval          | 0.000000                                  |

| wsrep_cluster_conf_id        | 2                                         |

| wsrep_cluster_size           | 2                                         |

| wsrep_cluster_state_uuid     | cc11c73d-1daa-11eb-951f-6e68a2ada2b6      |

| wsrep_cluster_status         | Primary                                   |

| wsrep_cluster_weight         | 2                                         |

| wsrep_commit_oooe            | 0.000000                                  |

| wsrep_commit_oool            | 0.000000                                  |

| wsrep_commit_window          | 0.000000                                  |

| wsrep_connected              | ON                                        |

| wsrep_desync_count           | 0                                         |

| wsrep_evs_delayed            |                                           |

| wsrep_evs_evict_list         |                                           |

| wsrep_evs_repl_latency       | 0/0/0/0/0                                 |

| wsrep_evs_state              | OPERATIONAL                               |

| wsrep_flow_control_paused    | 0.000000                                  |

| wsrep_flow_control_paused_ns | 0                                         |

| wsrep_flow_control_recv      | 0                                         |

| wsrep_flow_control_sent      | 0                                         |

| wsrep_gcomm_uuid             | cc11531a-1daa-11eb-af2c-aee91856616b      |

| wsrep_incoming_addresses     | 192.168.217.129:3306,192.168.217.130:3306 |

| wsrep_last_committed         | 0                                         |

| wsrep_local_bf_aborts        | 0                                         |

| wsrep_local_cached_downto    | 18446744073709551615                      |

| wsrep_local_cert_failures    | 0                                         |

| wsrep_local_commits          | 0                                         |

| wsrep_local_index            | 1                                         |

| wsrep_local_recv_queue       | 0                                         |

| wsrep_local_recv_queue_avg   | 0.166667                                  |

| wsrep_local_recv_queue_max   | 2                                         |

| wsrep_local_recv_queue_min   | 0                                         |

| wsrep_local_replays          | 0                                         |

| wsrep_local_send_queue       | 0                                         |

| wsrep_local_send_queue_avg   | 0.000000                                  |

| wsrep_local_send_queue_max   | 1                                         |

| wsrep_local_send_queue_min   | 0                                         |

| wsrep_local_state            | 4                                         |

| wsrep_local_state_comment    | Synced                                    |

| wsrep_local_state_uuid       | cc11c73d-1daa-11eb-951f-6e68a2ada2b6      |

| wsrep_open_connections       | 0                                         |

| wsrep_open_transactions      | 0                                         |

| wsrep_protocol_version       | 9                                         |

| wsrep_provider_name          | Galera                                    |

| wsrep_provider_vendor        | Codership Oy <info@codership.com>         |

| wsrep_provider_version       | 25.3.30(r827e681)                         |

| wsrep_ready                  | ON                                        |

| wsrep_received               | 6                                         |

| wsrep_received_bytes         | 431                                       |

| wsrep_repl_data_bytes        | 0                                         |

| wsrep_repl_keys              | 0                                         |

| wsrep_repl_keys_bytes        | 0                                         |

| wsrep_repl_other_bytes       | 0                                         |

| wsrep_replicated             | 0                                         |

| wsrep_replicated_bytes       | 0                                         |

| wsrep_thread_count           | 2                                         |

+------------------------------+-------------------------------------------+

 

위의 wsrep항목이 master,slave댓수 및 IP가 맞다면 성공.

 

테스트로 Master DB나 Slave DB 중에서 무작위로 Insert하여 Insert하지 않은 DB에서 select 하여 확인하였음.

 

 

6. Galera Cluster DB재시작시

 

장애로 인하여 galera cluster DB 재시작시에는 

최종 살아있는 DB를 확인한후 최종살아있는 DB를 Matser로 설정 후 실행한다.

 

최종살아있는 DB확인

# vi /var/lib/mysql/grastate.dat

[grastate.dat]

# GALERA saved state

version: 2.1

uuid:    881e3b46-1dae-11eb-a299-bbe43bec0b72

seqno:   -1

safe_to_bootstrap: 1

 

safe_to_bootstrap:1 <- 해당 항목이 1인 서버가 최종DB(데이터가 마지막까지 쌓인 DB)

 

또는 

 

# vi /etc/my.cnf.d/server.cnf

wserp에 관련된 항목 전부 주석처리 후 및 wserp_mode=OFF 처리후

각자 DB 실행 후 데이터 비교하여 최신화 데이터 DB를 확인한다.

 

 

master와 slave가 바꼇을시에는

3.각 서버 설정 

입력값을 각 서버 역할에 맞게 올바르게 입력 후

 

마스터서버와 슬레이브서버의 galera관련 파일삭제(2개파일)

# cd /var/lib/mysql/

# rm -Rf gvwstate.dat galera.cache

 

삭제후

4.DB시작 처럼 master DB와 slave DB를 재시작한다.

참고사항 : Slave서버 DB Data는 Master 서버 DB Data를 덮어쓰기됨.(Master DB 데이터로 통합됨<rsync>)

 

galera Cluster 재시작 검색시 많은 방법이 나오지만 아예 초기화하여 재시작하는게 나의 상황의 경우 제일 괜찮았던거 같다.

 

--재시작시 에러관련

-cluster Node추가시 stop-final-sigterm timeout Killing인 경우

(아마rsync데이터가 매우 클경우 뜨는것 같음)

 

 

# vi /usr/lib/systemd/system/mariadb.service

또는

# vi /lib/systemd/system/mariadb.service

 

아니라면 mariadb.service 찾은 후 

 

TimeoutSec=0  

추가(타임아웃무한대로 변경하는 설정으로 추측)

 

#systemctl daemon-reload

 

wserp관련 파일 삭제

# cd /var/lib/mysql/

# rm -Rf gvwstate.dat galera.cache

4.DB시작

 

과정을 거쳐서 Galera Cluster를 정상화 하였음.