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 InvolvedInstalling : 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)
갈레라 구성이 완료되었습니다.
안녕하세요 글을 인상깊게 잘 보았습니다.
궁금한게 있어서 이렇게 댓글을 남기게 되었습니다.
혹시 Centos에서 설치하셨는데
Windows Server에서도 동일하게 사용이 가능할까요?
아니면 Galera는 윈도우서버에서는 사용을 못하나요?
https://galeracluster.com/downloads/
갈레라 공홈에 가보시면 아시겠지만, 갈레라는 윈도우용 설치 파일을 지원하지 않습니다.
그리고 윈도우를 다중 노드로 구성하는경우 OS 라이선스 비용도 그만큼 들어가게 되니 오프소스를 이용한 시스템에 잘 사용하지 않습니다.