MariaDB+Galera集群+haproxy+keepalived

Galera集群

环境要求

Galera集群至少需要三个节点的服务器硬件，以下操作在三个节点执行。安装后，在任意一个节点执行SQL，都是同步的。

现有三台服务器，ip分别为10.30.1.14、10.30.1.15、10.30.1.16。

安装

3台服务器的主机名修改：

hostnamectl set-hostname node1
hostnamectl set-hostname node2
hostnamectl set-hostname node3

3台服务器的hosts文件修改：

10.30.1.14 node1
10.30.1.15 node2
10.30.1.16 node3

建立3台服务器之间的SSH免密通信（3台服务器执行）：

ssh-keygen -t rsa
ssh-copy-id node1
ssh-copy-id node2
ssh-copy-id node3

3台服务器关闭Selinux:

setenforce 0
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config

3台服务器关闭防火墙或者添加端口允许:

# 3306  MySQL client connections and mysqldump SST
firewall-cmd --zone=public --add-port=3306/tcp --permanent
# 4567  Galera Cluster replication traffic
firewall-cmd --zone=public --add-port=4567/tcp --permanent
# 4568  IST
firewall-cmd --zone=public --add-port=4568/tcp --permanent
# 4444  all SSTs besides mysqldump
firewall-cmd --zone=public --add-port=4444/tcp --permanent
firewall-cmd --reload

3台服务器设置Yum源:

cd /etc/yum.repos.d
cat >> mariadb.repo <<EOF
[mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.4/centos7-amd64
gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF

3台服务器安装软件:

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

3台服务器配置远程访问:

grant all privileges on *.* to root@'%' identified by 'd001!' with grant option;
flush privileges;

3台服务器配置编码为utf-8,编辑配置文件vi /etc/my.cnf:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

galera集群配置

编辑Node1上的/etc/my.cnf.d/server.cnf 添加如下配置：

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://10.30.1.14,10.30.1.15,10.30.1.16" #整个集群的IP地址
binlog_format=row # binlog文件格式：行
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
# Allow server to accept connections on all interfaces.
# bind-address=0.0.0.0
# Optional setting
# wsrep_slave_threads=1
# innodb_flush_log_at_trx_commit=0
wsrep_provider_options="gcache.size=1G"
wsrep_cluster_name=MariaDB-Galera-Cluster
wsrep_node_name=node1 #hostname，对应前面网路配置/etc/hosts
wsrep_node_address=10.30.1.14 #机器IP地址
wsrep_sst_method=rsync #拷贝模式xtrabackup-v2 或者 rsync

编辑Node2上的/etc/my.cnf.d/server.cnf 添加如下配置：

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://10.30.1.14,10.30.1.15,10.30.1.16" #整个集群的IP地址
binlog_format=row # binlog文件格式：行
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
# Allow server to accept connections on all interfaces.
# bind-address=0.0.0.0
# Optional setting
# wsrep_slave_threads=1
# innodb_flush_log_at_trx_commit=0
wsrep_provider_options="gcache.size=1G"
wsrep_cluster_name=MariaDB-Galera-Cluster
wsrep_node_name=node2 #hostname，对应前面网路配置/etc/hosts
wsrep_node_address=10.30.1.15 #机器IP地址
wsrep_sst_method=rsync #拷贝模式xtrabackup-v2 或者 rsync

编辑Node3上的/etc/my.cnf.d/server.cnf 添加如下配置：

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://10.30.1.14,10.30.1.15,10.30.1.16" #整个集群的IP地址
binlog_format=row # binlog文件格式：行
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
# Allow server to accept connections on all interfaces.
# bind-address=0.0.0.0
# Optional setting
# wsrep_slave_threads=1
# innodb_flush_log_at_trx_commit=0
wsrep_provider_options="gcache.size=1G"
wsrep_cluster_name=MariaDB-Galera-Cluster
wsrep_node_name=node3 #hostname，对应前面网路配置/etc/hosts
wsrep_node_address=10.30.1.16 #机器IP地址
wsrep_sst_method=rsync #拷贝模式xtrabackup-v2 或者 rsync

启动集群
- 启动第一个节点，底层命令是：mysqld –wsrep-new-cluster
  
  galera_new_cluster
- 在其他节点上启动服务
  
  systemctl start mariadb.service
- 主节点中添加集群认证用户galera，密码galera（可选）
```
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'galera'@'%' IDENTIFIED BY 'galera' WITH GRANT OPTION;
MariaDB [(none)]> flush privileges;
```
- 故障排查
  
  启动集群时报错[ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates.
  
  打开文件/var/lib/mysql/grastate.dat，修改safe_to_bootstrap的值置为1
集群测试
- 确认集群启动成功（返回当前的集群节点数量）
  
  mysql> show status like 'wsrep_cluster_size';
- 查看galera状态
  
  mysql> show status like 'wsrep%';

HAProxy

安装

yum install -y haproxy

配置

编辑HAProxy配置文件 vi /etc/haproxy/haproxy.cfg,配置如下(两台haproxy服务器配置相同):

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                    http
    log                     global
    option                  tcplog
    option                  dontlognull
    option http-server-close
    #option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000
    
listen galera
    bind 0.0.0.0:13306
    balance roundrobin
    mode tcp
    option tcpka
    option mysql-check user haproxy
    server galera-mariadb-1 192.168.123.36:3306 check weight 1
    server galera-mariadb-2 192.168.123.35:3306 check weight 1
    server galera-mariadb-3 192.168.123.34:3306 check weight 1
    
listen stats
## HAProxy stats web gui running on port 9000 - username and password: haproxy.
    bind 0.0.0.0:9000
    mode http
    stats enable
    stats uri /stats
    stats realm HAProxy\ Statistics
    stats auth haproxy:haproxy
    stats admin if TRUE

启动

systemctl start haproxy

keepalived

安装

创建依赖环境：

yum -y install openssl-devel gcc gcc-c++
mkdir /etc/keepalived
wget https://www.keepalived.org/software/keepalived-2.0.18.tar.gz

安装keepalived:

tar -zxvf keepalived-2.0.18.tar.gz
mv keepalived-2.0.18 /usr/local/keepalived
cd /usr/local/keepalived
./configure && make && make install

设置开机启动

systemctl enable keepalived

配置

编辑haproxy检测脚本，vi /etc/keepalived/chk_haproxy.sh

#!/bin/bash
chkha=`ps -C haproxy --no-header |wc -l`
if [ $chkha -eq 0 ];then
  systemctl stop keepalived
fi

赋予脚本执行权限

chmod +x chk_haproxy.sh

编辑keepalived主配置文件，vi /etc/keepalived/keepalived.conf

global_defs {
    router_id Haproxy1  #服务器标识
}

vrrp_script chk_haproxy {
    script "/etc/keepalived/chk_haproxy.sh"
    interval 1  #（检测脚本执行的间隔，单位是秒)
    weight 2		#权重
}

vrrp_instance VI_1 {
    state MASTER     #指定keepalived的角色，MASTER为主，BACKUP为备
    interface eth0   #绑定的网卡
    virtual_router_id 201   #虚拟路由编号，主从要一直
    priority 100     #优先级，数值越大，获取处理请求的优先级越高
    advert_int 1			#检查间隔，默认为1s(vrrp组播周期秒数)
    authentication {  #设置验证类型和密码，MASTER和BACKUP必须使用相同的密码才能正常通信
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        chk_haproxy   #调用检测脚本）
    }
    virtual_ipaddress {
       10.30.1.26     #定义虚拟ip(VIP)，可多设，每行一个
    }
    track_interface {
        eth0
    }
}

编辑keepalived备配置文件，vi /etc/keepalived/keepalived.conf

global_defs {
    router_id Haproxy2
}

vrrp_script chk_haproxy {
    script "/etc/keepalived/chk_haproxy.sh"
    interval 1
    weight 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 201
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        chk_haproxy
    }
    virtual_ipaddress {
        192.168.123.44
    }
    track_interface {
        eth0
    }
}

如果开启了防火墙，需打开如下配置

firewall-cmd --add-rich-rule='rule protocol value="vrrp" accept' --permanent
firewall-cmd --reload
firewall-cmd --list-all

启动keepalived

systemctl start keepalived