mariadb galera cluster 10.0 centos 6.x + LVS(LB)

cluster node 1 -> 10.0.0.1

cluster node 2 -> 10.0.0.2

cluster node 3 -> 10.0.0.3

### yum repositories 등록

cat << EOF >  /etc/yum.repos.d/mariadb.repo

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.0/centos6-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

EOF

### 설치전 

selinux = disabled

iptable을 사용한다면 사용되는 기본 port를 열어줘야한다.

### 설치

# 필요한 프로그램

yum install http://dl.fedoraproject.org/pub/epel/6/x86_64/socat-1.7.2.3-1.el6.x86_64.rpm

yum install socat

# Mariadb 설치

yum install MariaDB-Galera-server MariaDB-client rsync galera

# 설치 완료이후

/etc/init.d/mysql start

/usr/bin/mysql_secure_installation

root 비밀번호 입력

# galera config

mysql -u root -p

DELETE FROM mysql.user WHERE user='';

GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'root_pass';

GRANT USAGE ON *.* to sst_user@'%' IDENTIFIED BY 'sst_pass';

GRANT ALL PRIVILEGES on *.* to sst_user@'%';

FLUSH PRIVILEGES;

quit

ㅁ db에서 root 비밀번호 및, sst 관련 유저 비밀번호 생성

service mysql stop

cat >> /etc/my.cnf.d/server.cnf << EOF

binlog_format=ROW

default-storage-engine=innodb

innodb_autoinc_lock_mode=2

innodb_locks_unsafe_for_binlog=1

query_cache_size=0

query_cache_type=0

bind-address=0.0.0.0

datadir=/var/lib/mysql

innodb_log_file_size=100M

innodb_file_per_table

innodb_flush_log_at_trx_commit=2

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

wsrep_cluster_address="gcomm://10.0.0.1,10.0.0.2,10.0.0.3"

wsrep_cluster_name='galera_cluster'

wsrep_node_address='10.0.0.1'

wsrep_node_name='db1'

wsrep_sst_method=rsync

wsrep_sst_auth=sst_user:sst_pass

EOF

ㅁ config db2

wsrep_node_address='10.0.0.2'

wsrep_node_name='db2'

ㅁ config db3

wsrep_node_address='10.0.0.3'

wsrep_node_name='db3'

### 실행 및 확인

/etc/init.d/mysql start --wsrep-new-cluster 

ㄴ db1 에서 실행

mysql -u root -p -e "show status like 'wsrep%'"

.

.

wsrep_local_state_comment | Synced

wsrep_incoming_addresses  | 10.0.0.1:3306

wsrep_cluster_size        | 1

wsrep_ready               | ON

.

.

2번, 3번 서버 mysql 시작한이후 위의 값에서 wsrep_cluster_size 값이 2, 3 으로 올라가는지 확인

1번에 쿼리문 날리셔 나머지 2,3번에서 동일 내용이 select 되는지 확인

### 부하분산

Lvs를 이용하여서 3306 Port 부하분산 진행

ㅁ LVS 설정 (/etc/keepalived/keepalived.conf)

! Configuration File for keepalived

global_defs {

   notification_email {

     root

   }

   notification_email_from admin@admin.com

   smtp_server 127.0.0.1

   smtp_connect_timeout 30

}

vrrp_instance ETH0 {

    state BACKUP

    interface eth0

    virtual_router_id 9

    priority 100

    garp_master_delay 1

    advert_int 1

    lvs_sync_daemon_interface eth0

    authentication {

        auth_type PASS

        auth_pass xxxxxx

    }

    virtual_ipaddress {

        10.0.0.100 (VIP사용할 IP)

    }

}

virtual_server 10.0.0.100 3306 {

    delay_loop 3

    lb_algo rr

    lb_kind DR

    protocol TCP

    real_server 10.0.0.1 3306 {

        weight 100

        TCP_CHECK {

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

        }

    }

    real_server 10.0.0.2 3306 {

        weight 100

        TCP_CHECK {

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

        }

    }

    real_server 10.0.0.3 3306 {

        weight 100

        TCP_CHECK {

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

        }

    }

}

/etc/init.d/keepalived restart 혹은 서비스 중이면 /etc/init.d/keepalived reload

ㅁ 각 DB서버에 10.0.0.100 VIP 등록

cat << EOF >>/etc/sysconfig/network-scripts/ifcfg-lo:0

DEVICE=lo:0

IPADDR=10.0.0.100

NETMASK=255.255.255.255

NETWORK=10.0.0.100

ONBOOT=yes

NAME=loopback

EOF

ifup lo:0

ifconfig | grep 10.0.0.100

ㅁ LVS 서버에서 확인

watch -n 1 "ipvsadm -L"