반응형
Maria DB에서 Active-Active서버 구조로, 실시간 동기화 되는 2개의 서버를 운용할 수 있는 Galera Cluster기능이 있어서 실제 테스트한 것을 정리하였습니다.
VM은 버츄얼박스를 사용하였고, 기존에 사용중인 1번서버를 복제하여 2번서버를 만들었습니다.
1번노드 IP : 192.168.56.59
2번노드 IP : 192.168.56.60
DB 버전 : Maria 10.4.12(64bit) (YUM 설치)
[root@ysbae ~]# mysqladmin --version
mysqladmin Ver 9.1 Distrib 10.4.12-MariaDB, for Linux on x86_64
1./etc/hosts 수정 (1번노드 2번노드)
아래 ip부분을 추가하고 저장하였습니다.
[root@ysbae ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
#MariaDB Galera DB hosts
192.168.56.59 ysbae ysbae
192.168.56.60 ysbae2 ysbae22. /etc/my.cnf.d/galera.cnf 생성 후 내용 작성
DB를 open할때 읽어오는 my.cnf파일이 있는 디렉토리입니다. 이곳에 galera에 대한 정보를 입력합니다.
1번노드
1번
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
#wsrep_cluster_address="gcomm://First_Node_IP,Second_Node_IP,Third_Node_IP"
wsrep_cluster_address="gcomm://192.168.56.69,192.168.56.60"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
#wsrep_node_address="This_Node_IP"
#wsrep_node_name="This_Node_Name"
wsrep_node_address="192.168.56.59"
wsrep_node_name="ysbae"2번노드
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
#wsrep_cluster_address="gcomm://First_Node_IP,Second_Node_IP,Third_Node_IP"
wsrep_cluster_address="gcomm://192.168.56.69,192.168.56.60"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
#wsrep_node_address="This_Node_IP"
#wsrep_node_name="This_Node_Name"
wsrep_node_address="192.168.56.60"
wsrep_node_name="ysbae2"
3.DB 재기동
1번노드
1번노드는 아래 명령어로 기동시켜줍니다.
[root@ysbae ~]# galera_new_cluster2번노드
2번노드는 일반적인 기동 명령어를 사용합니다.
[root@ysbae2 my.cnf.d]# service mariadb start
Redirecting to /bin/systemctl start mariadb.service
4. 확인
1번노드,2번노드 동일
[root@ysbae my.cnf.d]# netstat -nlpt |grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 6487/mysqld
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 6487/mysqld
아래 명령어로 2가 나와야 정상적으로 연결된 것입니다.(2개의 노드이기 때문에 2입니다.)
MariaDB [(none)]> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 2 |
+--------------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> show status like 'wsrep_incoming_addresses';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| wsrep_incoming_addresses | AUTO,AUTO |
+--------------------------+-----------+
1 row in set (0.001 sec)
MariaDB [(none)]> show status like 'wsrep_incoming_addresses';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| wsrep_incoming_addresses | AUTO,AUTO |
+--------------------------+-----------+
1 row in set (0.001 sec)
MariaDB [(none)]> show status like ‘wsrep_%’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '‘wsrep_%’' at line 1
MariaDB [(none)]> show status like 'wsrep_%';
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| wsrep_local_state_uuid | ca2f1033-47bd-11ea-bb25-9f82c91c4c1d |
| wsrep_protocol_version | 10 |
| wsrep_last_committed | 8 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 6 |
| wsrep_received_bytes | 452 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 2 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.166667 |
| wsrep_local_cached_downto | 2 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0 |
| wsrep_apply_oooe | 0 |
| wsrep_apply_oool | 0 |
| wsrep_apply_window | 0 |
| wsrep_commit_oooe | 0 |
| wsrep_commit_oool | 0 |
| wsrep_commit_window | 0 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0 |
| wsrep_open_transactions | 0 |
| wsrep_open_connections | 0 |
| wsrep_incoming_addresses | AUTO,AUTO |
| wsrep_cluster_weight | 2 |
| 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_gcomm_uuid | 02199eaf-47d5-11ea-a7ac-2ef71d687b46 |
| wsrep_applier_thread_count | 1 |
| wsrep_cluster_capabilities | |
| wsrep_cluster_conf_id | 18446744073709551615 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | ca2f1033-47bd-11ea-bb25-9f82c91c4c1d |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_capabilities | :MULTI_MASTER:CERTIFICATION:PARALLEL_APPLYING:TRX_REPLAY:ISOLATION:PAUSE:CAUSAL_READS:INCREMENTAL_WRITESET:UNORDERED:PREORDERED:STREAMING:NBO: |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 26.4.3(r4535) |
| wsrep_ready | ON |
| wsrep_rollbacker_thread_count | 1 |
| wsrep_thread_count | 2 |
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
65 rows in set (0.001 sec)
5.동기화 테스트
1번 노드에서 DB 생성 후 2번노드에 동기화 되는지 테스트합니다.
1번노드
MariaDB [(none)]> create database galera_test;
Query OK, 1 row affected (0.010 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| galera_test |
| information_schema |
| mysql |
| performance_schema |
| selinux |
| test |
| xx1 |
+--------------------+
7 rows in set (0.000 sec)2번노드
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| galera_test |
| information_schema |
| mysql |
| performance_schema |
| selinux |
| test |
| xx1 |
+--------------------+
7 rows in set (0.001 sec)
Oracle의 RAC와는 다르게 각각의 서버 스토리지를 사용하며, 네트워크를 통해서 DB데이터가 동기화 됩니다.
(RAC는 공유스토리지를 사용하여 같은 데이터를 바라봄)
끝.
반응형
'다른 DBMS > MySQL&MariaDB' 카테고리의 다른 글
| 시스템 변수 변경방법 (0) | 2020.12.08 |
|---|---|
| MYSQL설치(바이너리) (0) | 2020.12.04 |
| MYSQL설치(RPM) (0) | 2020.12.03 |
| MYSQL 설치(YUM) (0) | 2020.12.02 |
| Column Size 증가시 테스트 (0) | 2020.10.15 |
댓글