티스토리 뷰


MariaDB Galera and MariaDB MaxScale on CentOS




특징


Galera Cluster will not work with MyISAM or similar nontransactional storage engines.




사전작업



Disable SELinux

Disable firewalld

hostname 지정 (선택) : 서버 구분

 





서버 구성



maxscale (선택)

MariaDB Galera Cluster node 1,2,3

 


*maxscale

클러스터에 대한 라우터 역활, 트래픽을 적절한 서버에 분배, 클러스터의 Out-of-Band 모니터링,





MariaDB 저장소 설정



curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

 




패키지 설치



yum -y install MariaDB-server

 





vim /etc/my.cnf/server.cnf



[galera]

# Mandatory settings

wsrep_cluster_name=galera-cluster

wsrep_on=ON

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

wsrep_cluster_address=gcomm://192.168.0.181,192.168.0.182,192.168.0.183

wsrep_node_name=newrun-cluser1

wsrep_node_address="192.168.10.177"

wsrep_provider_options="gcache.recover=yes"


wsrep_sst_method=rsync

wsrep_log_conflicts=ON

wsrep_provider_options="cert.log_conflicts=ON"

wsrep_debug=ON

wsrep-forced-binlog-format=ROW


wsrep_slave_threads=4

innodb_flush_log_at_trx_commit=1


[mariadb]


binlog_format=row

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

log-bin="/var/lib/mysql/bin"


general-log

general-log-file=queries.log

log-output=file

 

ssl

ssl-ca=/home/ssl/ca-cert.pem

ssl-cert=/home/ssl/server-cert.pem

ssl-key=/home/ssl/server-key.pem





옵션



binlog_format=ROW


BINLOG TYPE

ROW — 열 기반 리플리케이션을 디폴트로 설정한다.

STATEMENT — 명령문 기반 리플리케이션을 디폴트로 설정한다.

MIXED — 혼합 기반 리플리케이션을 디폴트로 설정한다.



innodb_autoinc_lock_mode=2


* Do not change this value. Other modes may cause INSERT statements on tables with AUTO_INCREMENT columns to fail.


0또는 1의 값으로 설정된 경우, 교착상태 및 시스템 응답 발생하지 않는 경우 발생 



innodb_flush_log_at_trx_commit=0


Note Warning: While setting innodb_flush_log_at_trx_commit to a value of 0 or 2 improves performance, it also introduces certain dangers. Operating system crashes or power outages can erase the last second of transaction. Although normally you can recover this data from another node, it can still be lost entirely in the event that the cluster goes down at the same time, (for instance, in the event of a data center power outage).


swap


Memory requirements for Galera Cluster are difficult to predict with any precision. The particular amount of memory it uses can vary significantly, depending upon the load the given node receives. In the event that Galera Cluster attempts to use more memory than the node has available, the mysqld instance crashes.


The way to protect your node from such crashing is to ensure that you have sufficient swap space available on the server, either in the form of a swap partition or swap files. To check the available swap space, run the following command:






스왑 생성







첫번째 노드 시작



galera_new_cluster

 





mysql -u root 


MariaDB [(none)]> show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+
1 row in set (0.001 sec)

 





두번째, 세번째 노드 마리아 DB 시작


MariaDB [(none)]> show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2      |
+--------------------+-------+
1 row in set (0.001 sec)

 


* value 1, -> 2 -> 3 시작된 서버 대수 만큼 증가함




ps -ef


mysql     2029     1  0 17:12 ?        00:00:03 /usr/sbin/mysqld --wsrep_start_position=feeda1c5-a68e-11e8-b891-8fe36d1c5b3c:16

 



여기까지만 하면 Galera 설정 완료




galera cluster 에서 사용하는 포트



3306 For MySQL client connections and State Snapshot Transfer that use the mysqldump method.


4567 For Galera Cluster replication traffic, multicast replication uses both UDP transport and TCP on this port.


4568 For Incremental State Transfer.


4444 For all other State Snapshot Transfer.

 





MaxScale 연동



maxscale 에서 사용할 mysql 계정 생성




create user 'myuser'@'192.168.0.180' identified by 'mypwd';

 grant select on mysql.user to 'myuser'@'192.168.0.180';

grant select on mysql.db to 'myuser'@'192.168.0.180';

grant select on mysql.tables_priv to 'myuser'@'192.168.0.180';

grant show databases on *.* to 'myuser'@'192.168.0.180'; 






maxscale 설치



yum -y install maxscale


yum -y install MariaDB-client

 





vim /etc/maxscale.cnf



# Globals

[maxscale]

threads=1

 

# Servers

[server1]

type=server

address=192.168.0.181

port=3306

protocol=MySQLBackend

 

[server2]

type=server

address=192.168.0.182

port=3306

protocol=MySQLBackend

 

[server3]

type=server

address=192.168.0.183

port=3306

protocol=MySQLBackend

 

# Monitoring for the servers

[Galera Monitor]

type=monitor

module=galeramon

servers=server1,server2,server3

user=myuser

passwd=mypwd

monitor_interval=1000

 

# Galera router service

[Galera Service]

type=service

router=readwritesplit

servers=server1,server2,server3

user=myuser

passwd=mypwd

 

# MaxAdmin Service

[MaxAdmin Service]

type=service

router=cli

 

# Galera cluster listener

[Galera Listener]

type=listener

service=Galera Service

protocol=MySQLClient

port=3306

 

# MaxAdmin listener

[MaxAdmin Listener]

type=listener

service=MaxAdmin Service

protocol=maxscaled

socket=default

 


*maxscale 라이선스 관련 체크




계정 생성 (첫번째  클러스터 노드에서 작업)



create user 'takakocap'@'192.168.56.7' identified by 'speed99';

grant select on mysql.user to 'takakocap'@'192.168.56.7';

grant select on mysql.db to 'takakocap'@'192.168.56.7';

grant select on mysql.tables_priv to 'takakocap'@'192.168.56.7';

grant show databases on *.* to 'takakocap'@'192.168.56.7';

 




테스트



mysql -h 192.168.0.180 -u myuser -pmypwd


show variables like 'hostname';

 


* 첫번째 노드, 두, 세번째 노드 stop 하면서 테스팅 하면 hostname이 실시간으로 바뀌는것을 확인 할 수 있다.




이슈



기타 다른 client PC 에서 maxscale 거쳐서 쿼리 입력


maxscale 에서 지정한 계정이 root 권한 처럼 설정 가능?


maxscale 설정 없이 LVS 로 바로 붙인 경우 설정 가능?


첫번째 노드의 mariadb 데몬이 실행되지 않으면 기타 두, 세번재 노드의 마리아디비 시작되지 않음

 




원문 



https://mariadb.com/resources/blog/getting-started-mariadb-galera-and-mariadb-maxscale-centos

 


'데이터베이스' 카테고리의 다른 글

sharding  (0) 2018.08.29
nosql  (0) 2018.08.29
MariaDB Galera and MariaDB MaxScale on CentOS  (0) 2018.08.23
트랜잭션 (Transaction)  (0) 2018.08.21
storage engine  (0) 2018.08.21
데이터베이스 엔진  (0) 2018.08.21
댓글
댓글쓰기 폼