Centos7 mariadb-galera-cluster-5.5+HAProxy+keepalived

1 服務(wù)器

    mysql00 192.168.20.104

    mysql01 192.168.20.102

    mysql02 192.168.20.103

    haproxy  192.168.20.105

    haproxy1  192.168.20.106

2 yum源

vi /etc/yum.repos.d/galera.repo

[galera]

name=Galera

baseurl=http://releases.galeracluster.com/centos/7/x86_64/

gpgkey=http://releases.galeracluster.com/GPG-KEY-galeracluster.com

gpgcheck=1

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/5.5/centos7-amd64/ 

enabled = 1

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

gpgcheck=1

3 修改hosts hostname

vi /etc/hosts

mysql00 192.168.20.104

mysql01 192.168.20.102

mysql02 192.168.20.103

vi /etc/hostname

mysql00(01,02)

4 yum install MariaDB-client MariaDB-Galera-server galera -y

5 修改數(shù)據(jù)庫配置文件

vi /etc/my.cnf

[mysqld]

datadir=/home/mysql                    #修改mysql數(shù)據(jù)目錄

socket=/var/lib/mysql/mysql.sock

user=mysql

binlog_format=ROW

bind-address=0.0.0.0

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

innodb_flush_log_at_trx_commit=0

innodb_buffer_pool_size=122M

innodb_log_file_size =48M 

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

#wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"

wsrep_cluster_name="cluster"

wsrep_cluster_address="gcomm://192.168.20.104,192.168.20.102,192.168.20.103"              #配置集群中所有節(jié)點(diǎn)IP

wsrep_node_name=mysql00          #本節(jié)點(diǎn)NAME

wsrep_node_address="192.168.20.104"                  #本節(jié)點(diǎn)IP地址

wsrep_sst_method=rsync                                           #全量同步方式

[mysql_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

6 啟動(dòng)第一個(gè)節(jié)點(diǎn)mysql00

必須按順序啟動(dòng)，先啟動(dòng)第一個(gè)節(jié)點(diǎn)（/etc/init.d/mysql start --wsrep-new-cluster），然后啟動(dòng)其他節(jié)點(diǎn)（/etc/init.d/mysql start ）

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

成功顯示success

在數(shù)據(jù)庫中查看：

MariaDB [(none)]> show global status like 'wsrep_cluster%';

+--------------------------+--------------------------------------+

| Variable_name            | Value                                |

+--------------------------+--------------------------------------+

| wsrep_cluster_conf_id    | 20                                   |

| wsrep_cluster_size       | 1                                    |

| wsrep_cluster_state_uuid | f5845fb3-abd6-11e6-82e5-627a888c772f |

| wsrep_cluster_status     | Primary                              |

+--------------------------+--------------------------------------+

 wsrep_cluster_size  1 表示已有一個(gè)節(jié)點(diǎn)

7 設(shè)置數(shù)據(jù)庫root密碼

/usr/bin/mysql_secure_installation

8 配置cluster_check

cd /tmp
wget https://raw.githubusercontent.com/olafz/percona-clustercheck/master/clustercheck

chmod +x clustercheck
mv clustercheck /usr/bin/

vi /etc/xinetd.d/mysqlchk

# default: on
# description: mysqlchk
service mysqlchk
{
        disable = no
        flags = REUSE
        socket_type = stream
        port = 9200             # This port used by xinetd for clustercheck
        wait = no
        user = nobody
        server = /usr/bin/clustercheck
        log_on_failure += USERID
        only_from = 0.0.0.0/0
        per_source = UNLIMITED
}

systemctl start xinetd

 安裝HAproxy

登錄192.168.20.105 192.168.20.106

vi /etc/hosts

mysql00 192.168.20.104

mysql01 192.168.20.102

mysql02 192.168.20.103

haproxy 192.168.20.105

haproxy1 192.168.20.106

yum -y install haproxy

vi /etc/rsyslog.conf(為haproxy配置log)

# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 51

vi /etc/rsyslog.d/haproxy.conf

local2.=info     /var/log/haproxy-access.log
local2.notice    /var/log/haproxy-info.log

systemctl restart rsyslog

Create a new HAProxy configuration file

cd /etc/haproxy/
mv haproxy.cfg haproxy.cfg.orig （將之前的配置文件備份）

vi /etc/haproxy/haproxy.cfg  （創(chuàng)建新的配置文件）

global

        log 127.0.0.1   local2

        maxconn 1024

        user haproxy

        group haproxy

        daemon

        stats socket /var/run/haproxy.sock mode 600 level admin         # Make sock file for haproxy

defaults

        log     global

        mode    http

        option  tcplog

        option  dontlognull

        retries 3

        option  redispatch

        maxconn 1024

        timeout connect 5000ms

        timeout client 50000ms

        timeout server 50000ms

listen mariadb_cluster 0.0.0.0:3030

## MariaDB balance leastconn - the cluster listening on port 3030.

        mode tcp

     #   balance leastconn

        balance roundrobin   #輪詢

        option tcpka

        option mysql-check user haproxy    #健康檢查

        server mysql00 192.168.20.104:3306 check weight 1                              #主用

        server mysql01 192.168.20.102:3306 check weight 1                              #主用

        server mysql02 192.168.20.103:3306 check weight 1  backup                #備用

listen stats 0.0.0.0:9000

## HAProxy stats web gui running on port 9000 - username and password: howtoforge.    WEB界面管理  

        mode http

        stats enable

        stats uri /stats

        stats realm HAProxy\ Statistics

        stats auth howtoforge:howtoforge

        stats admin if TRUE

KEEPALIVED 配置    （虛擬地址為192.168.20.107）

HAproxy

yum install -y keepalived

global_defs {  

    notification_email {  

#    cloud_data@163.com            # e-mail地址  

    }  

#    notification_email_from cloud_data@163.com  

#    smtp_server smtp.163.com   # 郵件服務(wù)器地址  

#    smtp_connect_timeout 30     # 連接超時(shí)時(shí)間  

    router_id LVS_Master  

}  

vrrp_script chk_http_port {  

    script  "/etc/keepalived/check_haproxy.sh"    # haproxy運(yùn)行檢測腳本[haproxy宕掉重啟haproxy服務(wù)]  

    interval        5                     # 腳本執(zhí)行間隔  

    weight         -5                    # 執(zhí)行腳本后優(yōu)先級變更：5表示優(yōu)先級+5;-5則表示優(yōu)先級-5  

}  

vrrp_instance VI_A {  

    state MASTER                    # 主上此值為MASTER,從上為BACKUP  

    interface eth0  

    virtual_router_id 50            # 此值主從必須一致  

    priority 100  

    advert_int 1  

    authentication {                  # authentication兩個(gè)參數(shù)值,主從也必須一致  

        auth_type PASS  

        auth_pass kongzhong  

    }  

track_script {  

    chk_http_port  

    }  

    virtual_ipaddress {  

        192.168.20.107        # haproxy提供的虛擬IP地址  

    }  

}  

HAproxy1

global_defs {  

    notification_email {  

  #  cloud_data@163.com   

    }  

   # notification_email_from cloud_data@163.com  

    #smtp_server smtp.163.com  

   # smtp_connect_timeout 30  

    router_id LVS_Master  

}  

vrrp_script chk_http_port {  

    script  "/etc/keepalived/check_haproxy.sh"  

    interval        5  

    weight         -5  

}  

vrrp_instance VI_A {  

    state BACKUP  

    interface eth0  

    virtual_router_id 50  

    priority 80  

    advert_int 1  

    authentication {  

        auth_type PASS  

        auth_pass kongzhong  

    }  

track_script {  

    chk_http_port  

    }  

    virtual_ipaddress {  

        192.168.20.107

    }  

}  

驗(yàn)證：

在瀏覽器輸入 http://192.168.20.107:9000/stats

用戶名/密碼：howtoforge/howtoforge

狀態(tài)顯示正常

斷開192.168.20.104 192.168.20.102其中一臺 ，登錄數(shù)據(jù)庫虛擬地址  mysql -uroot -p -P 3030 -h 192.168.20.107  ，是否可以正常登錄