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...

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다