Mysql高可用搭建

安装haproxy负载均衡器

yum install haproxy -y

配置haproxy配置文件，目录：/etc/haproxy/haproxy.cfg

global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    stats socket /var/lib/haproxy/stats

defaults
    mode                   tcp
    log                     global
    option                  tcplog
    option                  dontlognull
    option http-server-close
    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

frontend    mysql
    bind        0.0.0.0:3307
    mode       tcp
    log             global
    default_backend         mysql_server

backend     mysql_server
    balance     roundrobin
    server  mysql1 192.168.56.163:3306 check inter 5s rise 2 fall 3
    server  mysql2 192.168.56.162:3306 check inter 5s rise 2 fall 3

listen stats
    mode http
    bind 0.0.0.0:1080
    stats enable
    stats hide-version
    stats uri /haproxyadmin?stats
    stats realm Haproxy\ Statistics
    stats auth admin:admin
    stats admin if TRUE

启动haproxy负载均衡

/usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg

Keepalived安装

yum install gcc gcc-c++ openssl openssl-devel

wget -q https://www.keepalived.org/software/keepalived-1.2.18.tar.gz

[root@localhost home]# tar -zxvf keepalived-1.2.18.tar.gz
[root@localhost home]# cd keepalived-1.2.18
[root@localhost keepalived-1.2.18]# ./configure --prefix=/usr/local/keepalived
[root@localhost keepalived-1.2.18]# make && make install

将 keepalived 安装成 Linux 系统服务

[root@localhost keepalived-1.2.18]# mkdir /etc/keepalived
[root@localhost keepalived-1.2.18]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@localhost keepalived-1.2.18]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
[root@localhost keepalived-1.2.18]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@localhost keepalived-1.2.18]# ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@localhost keepalived-1.2.18]# chkconfig keepalived on

keepalived主机配置

! Configuration File for keepalived
global_defs {
    router_id LVS_MASTER
}

vrrp_script chk_haproxy {
    script "/etc/keepalived/haproxy_check.sh" ## 检测 haproxy 状态的脚本路径
    interval 2 ## 检测时间间隔
    weight 2 ## 如果条件成立，权重+2
}

vrrp_instance VI_1 {
    state MASTER
    interface eth1
    virtual_router_id 79
    priority 100
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1234
    }
    virtual_ipaddress {
        192.168.56.170
    }
    track_script {
        chk_haproxy
    }
}
# 写VIP virtual_server，只配置本地机器
virtual_server 192.168.56.170 3307 {# 定义虚拟服务器，地址与上面的virtual_ipaddress相同
    delay_loop 3 # 健康检查时间间隔，3秒
    lb_algo rr # 负载均衡调度算法：rr|wrr|lc|wlc|sh|dh|lblc
    lb_kind DR # 负载均衡转发规则：NAT|DR|TUN
    # persistence_timeout 5 # 会话保持时间5秒，动态服务建议开启
    protocol TCP # 转发协议protocol，一般有tcp和udp两种
    real_server 192.168.56.163 3307 {
        weight 1 # 权重越大负载分越大，0表示失效
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

Keepalived从机配置

! Configuration File for keepalived
global_defs {
    router_id LVS_MASTER
}

vrrp_script chk_haproxy {
    script "/etc/keepalived/haproxy_check.sh" ## 检测 haproxy 状态的脚本路径
    interval 2 ## 检测时间间隔
    weight 2 ## 如果条件成立，权重+2
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth1
    virtual_router_id 79
    priority 90
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1234
    }
    virtual_ipaddress {
        192.168.56.170
    }
    track_script {
        chk_haproxy
    }
}
# 写VIP virtual_server，只配置本地机器
virtual_server 192.168.56.170 3307 {# 定义虚拟服务器，地址与上面的virtual_ipaddress相同
    delay_loop 3 # 健康检查时间间隔，3秒
    lb_algo rr # 负载均衡调度算法：rr|wrr|lc|wlc|sh|dh|lblc
    lb_kind DR # 负载均衡转发规则：NAT|DR|TUN
    # persistence_timeout 5 # 会话保持时间5秒，动态服务建议开启
    protocol TCP # 转发协议protocol，一般有tcp和udp两种
    real_server 192.168.56.162 3307 {
        weight 1 # 权重越大负载分越大，0表示失效
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

haproxy状态检测脚本

#!/bin/bash
START_HAPROXY="/usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg" #haproxy启动命令
LOG_FILE="/usr/local/keepalived/log/haproxy-check.log" # 日志文件
HAPS=`ps -C haproxy --no-header |wc -l` # 检测haproxy的状态，0代表未启动,1已经启动
date "+%Y-%m-%d %H:%M:%S" >> $LOG_FILE #在日志文件当中记录检测时间
echo "check haproxy status" >> $LOG_FILE # 记录haproxy的状态
if [ $HAPS -eq 0 ];then #执行haproxy判断
  echo $START_HAPROXY >> $LOG_FILE #记录启动命令
  /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg #启动haproxy
  sleep 3
  if [ `ps -C haproxy --no-header |wc -l` -eq 0 ];then
    echo "start haproxy failed, killall keepalived" >> $LOG_FILE
    killall keepalived
    systemctl stop keepalived
  fi
fi

[root@centos8 keepalived]# mkdir /usr/local/keepalived/log
[root@centos8 keepalived]# touch /usr/local/keepalived/log/haproxy-check.log

chmod +x haproxy_check.sh