MariaDB 10.5 Galera Cluster 설정

 

MariaDB 10.5 Galera Cluster 설정

MariaDB가 10.5 버전이 정식으로 릴리즈 되서 오늘(2020.07.02) 기준 10.5.4 버전을 내려 받을수 있습니다.

10.5 버전부터 Galera의 wsrep gtid를 지원합니다.

따라서 wsrep gtid를 이용한 Galera Cluster를 세팅해보겠습니다.

CentOS 7.8 에서 구성했습니다.

 

–update–

2020.07.20 갈레라 설정 부분을 rsync에서 mariabackup으로 변경하였습니다.

 

10.5 repository 추가

vi /etc/yum.repo.d/mariadb.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.5/centos74-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

 

yum을 이용한 mariadb 설치

보안 이슈로 root로 설치하는 것은 권장드리지 않지만, 테스트 용도니까 그냥 root 계정으로 진행합니다.

$ yum install MariaDB-server MariaDB-backup

Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.kakao.com
 * extras: mirror.kakao.com
 * updates: mirror.kakao.com
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-backup.x86_64 0:10.5.4-1.el7.centos will be installed
--> Processing Dependency: MariaDB-common for package: MariaDB-backup-10.5.4-1.el7.centos.x86_64
---> Package MariaDB-server.x86_64 0:10.5.4-1.el7.centos will be installed
--> Processing Dependency: MariaDB-client for package: MariaDB-server-10.5.4-1.el7.centos.x86_64
--> Running transaction check
---> Package MariaDB-client.x86_64 0:10.5.4-1.el7.centos will be installed
---> Package MariaDB-common.x86_64 0:10.5.4-1.el7.centos will be installed
--> Processing Dependency: MariaDB-compat for package: MariaDB-common-10.5.4-1.el7.centos.x86_64
--> Running transaction check
---> Package MariaDB-compat.x86_64 0:10.5.4-1.el7.centos will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===========================================================================================================================================================================================================================================
 Package                                                    Arch                                               Version                                                           Repository                                           Size
===========================================================================================================================================================================================================================================
Installing:
 MariaDB-backup                                             x86_64                                             10.5.4-1.el7.centos                                               mariadb                                             6.8 M
 MariaDB-server                                             x86_64                                             10.5.4-1.el7.centos                                               mariadb                                              26 M
Installing for dependencies:
 MariaDB-client                                             x86_64                                             10.5.4-1.el7.centos                                               mariadb                                              13 M
 MariaDB-common                                             x86_64                                             10.5.4-1.el7.centos                                               mariadb                                              81 k
 MariaDB-compat                                             x86_64                                             10.5.4-1.el7.centos                                               mariadb                                             2.2 M

Transaction Summary
===========================================================================================================================================================================================================================================
Install  2 Packages (+3 Dependent packages)

Total download size: 48 M
Installed size: 48 M
Is this ok [y/d/N]: y
Downloading packages:
(1/5): MariaDB-backup-10.5.4-1.el7.centos.x86_64.rpm                                                                                                                                                                | 6.8 MB  00:00:07
(2/5): MariaDB-common-10.5.4-1.el7.centos.x86_64.rpm                                                                                                                                                                |  81 kB  00:00:00
(3/5): MariaDB-compat-10.5.4-1.el7.centos.x86_64.rpm                                                                                                                                                                | 2.2 MB  00:00:02
(4/5): MariaDB-client-10.5.4-1.el7.centos.x86_64.rpm                                                                                                                                                                |  13 MB  00:00:12
(5/5): MariaDB-server-10.5.4-1.el7.centos.x86_64.rpm                                                                                                                                                                |  26 MB  00:00:21
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                                                      1.5 MB/s |  48 MB  00:00:31
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : MariaDB-compat-10.5.4-1.el7.centos.x86_64                                                                                                                                                                               1/5
  Installing : MariaDB-common-10.5.4-1.el7.centos.x86_64                                                                                                                                                                               2/5
  Installing : MariaDB-client-10.5.4-1.el7.centos.x86_64                                                                                                                                                                               3/5
  Installing : MariaDB-server-10.5.4-1.el7.centos.x86_64                                                                                                                                                                               4/5


Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb or the
MySQL manual for more instructions.

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.
You can find additional information about the MySQL part at:
https://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
Get Involved
Installing : MariaDB-backup-10.5.4-1.el7.centos.x86_64 5/5 Verifying : MariaDB-backup-10.5.4-1.el7.centos.x86_64 1/5 Verifying : MariaDB-client-10.5.4-1.el7.centos.x86_64 2/5 Verifying : MariaDB-common-10.5.4-1.el7.centos.x86_64 3/5 Verifying : MariaDB-compat-10.5.4-1.el7.centos.x86_64 4/5 Verifying : MariaDB-server-10.5.4-1.el7.centos.x86_64 5/5 Installed: MariaDB-backup.x86_64 0:10.5.4-1.el7.centos MariaDB-server.x86_64 0:10.5.4-1.el7.centos Dependency Installed: MariaDB-client.x86_64 0:10.5.4-1.el7.centos MariaDB-common.x86_64 0:10.5.4-1.el7.centos MariaDB-compat.x86_64 0:10.5.4-1.el7.centos Complete!

 

각의 노드별로 my.cnf.d/server.cnf 설정을 해줍니다.

node 01

[mysqld]
bind-address                    = 0.0.0.0
character-set-server            = utf8
datadir                         = /var/lib/mysql

#innodb
default-storage-engine          = InnoDB
innodb_buffer_pool_size         = 4G
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 256M
innodb_io_capacity              = 6000
innodb_io_capacity_max          = 6000

##logs
slow_query_log                  = ON
long_query_time                 = 10
log_error                       = /var/lib/mysql/mysqld.err

##limits
join_buffer_size                = 1M
read_buffer_size                = 256K
tmp_table_size                  = 256M
max_heap_table_size             = 256M
lock_wait_timeout               = 300
innodb_monitor_enable           = all
performance_schema              = ON

innodb_flush_log_at_trx_commit  = 0


[galera]
# Mandatory settings
wsrep_on                        = ON
wsrep_provider                  = /usr/lib64/galera-4/libgalera_smm.so
wsrep_provider_options          = gcache.size=1024M
wsrep_cluster_address           = gcomm://172.16.70.42,172.16.70.43,172.16.70.44
binlog_format                   = row
wsrep_forced_binlog_format      = row
wsrep_gtid_mode                 = 1
innodb_autoinc_lock_mode        = 2
innodb_doublewrite              = 1
query_cache_size                = 0
wsrep_gtid_domain_id            = 1
wsrep_debug                     = 0
wsrep_cluster_name              = galera-cluster
wsrep_node_address              = 172.16.70.42
wsrep_node_name                 = mariadb01
wsrep_sst_method                = mariabackup
wsrep_sst_auth                  = mysql:
wsrep_sst_receive_address       = 172.16.70.42
wsrep_log_conflicts             = 1
wsrep_slave_threads             = 4
log_slave_updates

Node 02

[mysqld]
bind-address                    = 0.0.0.0
character-set-server            = utf8
datadir                         = /var/lib/mysql
#innodb
default-storage-engine          = InnoDB
innodb_buffer_pool_size         = 4G
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 256M
innodb_io_capacity              = 6000
innodb_io_capacity_max          = 6000
##logs
slow_query_log                  = ON
long_query_time                 = 10
log_error                       = /var/lib/mysql/mysqld.err
##limits
join_buffer_size                = 1M
read_buffer_size                = 256K
tmp_table_size                  = 256M
max_heap_table_size             = 256M
lock_wait_timeout               = 300
innodb_monitor_enable           = all
performance_schema              = ON
innodb_flush_log_at_trx_commit  = 0
[galera]
# Mandatory settings
wsrep_on                        = ON
wsrep_provider                  = /usr/lib64/galera-4/libgalera_smm.so
wsrep_provider_options          = gcache.size=1024M
wsrep_cluster_address           = gcomm://172.16.70.42,172.16.70.43,172.16.70.44
binlog_format                   = row
wsrep_forced_binlog_format      = row
wsrep_gtid_mode                 = 1
innodb_autoinc_lock_mode        = 2
innodb_doublewrite              = 1
query_cache_size                = 0
wsrep_gtid_domain_id            = 1
wsrep_debug                     = 0
wsrep_cluster_name              = galera-cluster
wsrep_node_address              = 172.16.70.43
wsrep_node_name                 = mariadb02
wsrep_sst_method                = mariabackup
wsrep_sst_auth                  = mysql:
wsrep_sst_receive_address       = 172.16.70.43
wsrep_log_conflicts             = 1
wsrep_slave_threads             = 4
log_slave_updates

Node 03

[mysqld]
bind-address                    = 0.0.0.0
character-set-server            = utf8
datadir                         = /var/lib/mysql
#innodb
default-storage-engine          = InnoDB
innodb_buffer_pool_size         = 4G
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 256M
innodb_io_capacity              = 6000
innodb_io_capacity_max          = 6000
##logs
slow_query_log                  = ON
long_query_time                 = 10
log_error                       = /var/lib/mysql/mysqld.err
##limits
join_buffer_size                = 1M
read_buffer_size                = 256K
tmp_table_size                  = 256M
max_heap_table_size             = 256M
lock_wait_timeout               = 300
innodb_monitor_enable           = all
performance_schema              = ON
innodb_flush_log_at_trx_commit  = 0
[galera]
# Mandatory settings
wsrep_on                        = ON
wsrep_provider                  = /usr/lib64/galera-4/libgalera_smm.so
wsrep_provider_options          = gcache.size=1024M
wsrep_cluster_address           = gcomm://172.16.70.42,172.16.70.43,172.16.70.44
binlog_format                   = row
wsrep_forced_binlog_format      = row
wsrep_gtid_mode                 = 1
innodb_autoinc_lock_mode        = 2
innodb_doublewrite              = 1
query_cache_size                = 0
wsrep_gtid_domain_id            = 1
wsrep_debug                     = 0
wsrep_cluster_name              = galera-cluster
wsrep_node_address              = 172.16.70.44
wsrep_node_name                 = mariadb03
wsrep_sst_method                = mariabackup
wsrep_sst_auth                  = mysql:
wsrep_sst_receive_address       = 172.16.70.44
wsrep_log_conflicts             = 1
wsrep_slave_threads             = 4
log_slave_updates

 

1번 노드에서만 galera_new_cluster 명령으로 구동하고, 나머지 노드에서는 systemctl start mariadb 명령으로 구동을 합니다.

$ galera_new_cluster

1번 노드에서 mariabackup을 이용할 계정 설정을 해줍니다.

mysql 계정으로 진행했으며, 비밀번호 대신 unix-socket을 이용한 로그인을 하도록 변경했습니다.

alter user 'mysql'@'localhost' IDENTIFIED VIA unix_socket;
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mysql'@'localhost';

2번, 3번 노드에서 DB 구동

$ systemctl start mariadb

MariaDB에 mysql로 접속해서 아래와 같은 명령을 실행하면 클러스터링의 결과값을 확인할 수 있습니다.

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.015 sec)

갈레라 구성이 완료되었습니다.

You may also like...

2 Responses

  1. Galera 궁금 말해보세요:

    안녕하세요 글을 인상깊게 잘 보았습니다.
    궁금한게 있어서 이렇게 댓글을 남기게 되었습니다.

    혹시 Centos에서 설치하셨는데
    Windows Server에서도 동일하게 사용이 가능할까요?

    아니면 Galera는 윈도우서버에서는 사용을 못하나요?

    • Rasta Lion 말해보세요:

      https://galeracluster.com/downloads/
      갈레라 공홈에 가보시면 아시겠지만, 갈레라는 윈도우용 설치 파일을 지원하지 않습니다.
      그리고 윈도우를 다중 노드로 구성하는경우 OS 라이선스 비용도 그만큼 들어가게 되니 오프소스를 이용한 시스템에 잘 사용하지 않습니다.

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 항목은 *(으)로 표시합니다