maria db cluster centos 7

configuration_step.txt

Hello everybody. Today I will show you how to install and config galera-cluster on centos 7
we have 3 CentOS 7 with example network card on brigde and LAN 172.16.2.0/24

Video Refrences But in this video is used Centos 6 probably step is same with some little change.
https://www.youtube.com/watch?v=PuYKc4uxeC0
   
   Topology - example
   galera-db01 - 172.16.2.20
   galera-db02 - 172.16.2.13
   haproxy - 172.16.2.24
   

/* Do it on all 3 servers */   
Step 1: remove mysql-server if had 
#sudo yum erase mysql-server mysql mysql-devel mysql-libs
#sudo rm -rf /var/lib/mysql

Step 2: Add MariaDB Repositories
vim /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1


Step 3: disable selinux (refrence https://www.tecmint.com/disable-selinux-temporarily-permanently-in-centos-rhel-fedora/)
vi /etc/sysconfig/selinux

edit that file change "enforcing" to "disabled"
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted
reboot and check status 
# sestatus

Step 4 Disable Firewall *optional
# systemctl disable firewalld

reboot and check status firewall

/* End do it on all 3 servers */

Step 5: Install MariaDB Galera Cluster 10.0 software (both galera-db servers)
sudo yum -y install MariaDB-server MariaDB-Galera-server MariaDB-client rsync galera

Step 6: Config MariaDB Security 
sudo service mysql start
sudo /usr/bin/mysql_secure_installation

Step 7: Create MariaDB Galera Cluster User
#mysql -u root -p //mysql root password : dbpass
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'cluster-user'@'%' IDENTIFIED BY 'clusterpass' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit

Stop Mysql service 
service mysql stop

Step 8: Config MariaDB Galera Cluster on galera-db01 ( 172.16.20.10/24)
vim /etc/my.cnf.d/server.cnf

[mariadb-10.1]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://172.16.2.20,172.16.2.13,172.16.2.24"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_name="cluster1"
wsrep_node_address="172.16.2.20"
wsrep_sst_method=rsync
wsrep_sst_auth=cluster_user:clusterpass

Step 9: Start MariaDB cluster 
[root@galera-db01 ~]# galera_new_cluster

Step 10: Checking result on galera-db01
[root@galera-db01 ~]# mysql -u root -p -e "show status like 'wsrep%'"

Step 11: stop service mysql on galera-db02
[root@galera-db02 ~]# service mysql stop

Step 12: Add galera-db02 ( node 2 ) in MariaDB Cluster
[root@galera-db02 ~]# vim /etc/my.cnf.d/server.cnf

[mariadb-10.1]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://172.16.2.20,172.16.2.13,172.16.2.24"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_name="cluster1"
wsrep_node_address="172.16.2.13"
wsrep_sst_method=rsync
wsrep_sst_auth=cluster-user:clusterpass

Step 13: Startup mysql on galera-db02
[root@galera-db02 ~]# service mysql start

Step 14: Checking result on galera-db02
[root@galera-db02 ~]# mysql -u root -p -e "show status like 'wsrep%'"

Step 15: install and config Galera Arbitrator on Haproxy server 172.16.2.24
sudo yum erase mysql-server mysql mysql-devel mysql-libs
sudo rm -rf /var/lib/mysql
yum -y install galera
which garbd

Step 16: Startup Galera Arbitrator (garbd) 
garbd -a gcomm://172.16.2.20:4567 -g cluster1 -l /tmp/1.out -d

Step 17: Testing DB replication
+ On galera-db01 create db and table 
[root@galera-db01 ~]# mysql -uroot -p
MariaDB [(none)]> CREATE DATABASE clustertest;
MariaDB [(none)]> USE clustertest;
MariaDB [clustertest]> CREATE TABLE employees(number int, name varchar(50));
MariaDB [clustertest]> INSERT INTO employees values(1,'NGUYEN HOANG NAM');
MariaDB [clustertest]> SELECT * FROM employees;

+ On galera-db02 
[root@galera-db02 ~]# mysql -uroot -p
MariaDB [(none)]> USE clustertest;
MariaDB [clustertest]> SELECT * FROM employees;

Step 18: Power off or kill -9 mysql_pid galera-db01 and on galera-db02 insert value to employees table
MariaDB [clustertest]> INSERT INTO employees values(2,'LE XUAN LAM');
Query OK, 1 row affected (0.13 sec)

Step 19: Power on or startup mysql service galera-db01 and checking 
[root@galera-db02 ~]# mysql -uroot -p
MariaDB [(none)]> USE clustertest;
MariaDB [clustertest]> SELECT * FROM employees;

we finished config galera cluster db now we will config haproxy loadbalancer for galera cluster

II. Config haproxy ( loadbalancer ) for cluster db - ip 172.16.2.24
Step 1: install haproxy 
yum -y install haproxy

Step 2: config haproxy  del all and copy content 
vim /etc/haproxy/haproxy.cfg 
#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

defaults
    mode                    tcp
    log                     global
    option                  dontlognull
    option                  redispatch
    retries                 3
    timeout queue           45s
    timeout connect         5s
    timeout client          1m
    timeout server          1m
    timeout check           10s
    maxconn                 3000
#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------
frontend  main *:3306
    bind 172.16.20.5:3306
    default_backend             app
#---------------------------------------------------------------------
# round robin balancing between the various backends
#---------------------------------------------------------------------
backend app
    balance     roundrobin
    server  app1 172.16.2.20:3306 maxconn 151 check
    server  app2 172.16.2.13:3306 maxconn 151 check
	
	
Step 3: Startup haproxy service
chkconfig haproxy on && service haproxy restart	

Step : install Mysql Client in haproxy
yum -y install MariaDB-client

Step 4: Checking haproxy loadbalancing for galera cluster 
[root@haproxy ~]# mysql -u cluster-user -pclusterpass -h 172.16.2.24 -P 3306 -e "select @@hostname";

as we see, the loadbalancer use roundrobin 

Now we finished install and config galera cluster on centos 7