MariaDB Galera集群 haproxy keepalived

title: MariaDB+Galera集群+haproxy+keepalived date: 2020-11-23 22:15:21 cover: https://cdn.jsdelivr.net/gh/haominglfs/images/20201123230920.png tags: - mariadb - galera - haproxy - keepalived categories: mariadb

Galera集群

环境要求

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

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

安装

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

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

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

shell 10.30.1.14 node1 10.30.1.15 node2 10.30.1.16 node3

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

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

3台服务器关闭Selinux:

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

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

shell # 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源:

shell 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台服务器安装软件:

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

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

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

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

```shell [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 添加如下配置：

shell [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 添加如下配置：

shell [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 添加如下配置：

shell [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（可选）

sql 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

安装

创建依赖环境：

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

安装keepalived:

shell 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

shell #!/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

```shell 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

```shell 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 } } ```

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

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

启动keepalived

systemctl start keepalived