MariaDB Galera and MariaDB MaxScale on CentOS

MariaDB Galera and MariaDB MaxScale on CentOS

MariaDB 10 이상 설치시 galera 별도 설치 필요없음 (YUM 설치시)

특징

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

사전작업

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

방화벽 구성 (firewalld)

firewall-cmd --permanent --zone=public --add-port=3306/tcp  
firewall-cmd --permanent --zone=public --add-port=4567/tcp  
firewall-cmd --permanent --zone=public --add-port=4568/tcp  
firewall-cmd --permanent --zone=public --add-port=4444/tcp

firewall-cmd --reload

서버 구성

maxscale (선택)  
MariaDB Galera Cluster node 1,2,3

maxscale

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

MariaDB 저장소 설정

curl -sS [https://downloads.mariadb.com/MariaDB/mariadb\\\_repo\\\_setup](https://downloads.mariadb.com/MariaDB/mariadb%5C_repo%5C_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