配置 MySQL 负载均衡 高可用

配置 MySQL 负载均衡 高可用

架构

MySQL:
    master      192.168.131.9
    slave-1     192.168.131.10
    slave-2     192.168.131.11
LB:
    MASTER      192.168.131.161
    BACKUP      192.168.131.159
VIP:
    192.168.131.16

LVS

yum install ipvsadm -y
利用 arp 协议, 搭建路由网桥连接
从而得到流量分发的目的

LVS 配置

    配置 keepalived 时, LVS 会自动得到配置, 无需手动

keepalived

[root@lvs ~]# yum install ipvsadm -y
[root@lvs ~]# ifconfig lo:0 192.168.131.16/32
[root@lvs ~]# yum install keepalived -y
[root@lvs ~]# vim /etc/keepalived/keepalived.conf
​
    ! Configuration File for keepalived
​
    global_defs {
       router_id lvs-keepalived-master    #辅助改为lvs-backup
    }
​
    vrrp_instance VI_1 {
        state MASTER
        interface ens33                #VIP绑定接口
        virtual_router_id 51         #VRID 同一组集群，主备一致
        priority 100            #本节点优先级，辅助改为50
        advert_int 1            #检查间隔，默认为1s
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        virtual_ipaddress {
            192.168.131.16
        }
    }
​
    virtual_server 192.168.131.16 3306 {    #LVS配置
            delay_loop 3
            lb_algo rr     #LVS调度算法
            lb_kind DR     #LVS集群模式（路由模式）
            protocol TCP      #健康检查使用的协议
            real_server 192.168.131.9 3306 {
                    weight 1
                    inhibit_on_failure   #当该节点失败时，把权重设置为0，而不是从IPVS中删除
                    TCP_CHECK {          #健康检查
                            connect_port 3306   #检查的端口
                            connect_timeout 3  #连接超时的时间
                            }
                    }
            real_server 192.168.131.10 3306 {
                    weight 1
                    inhibit_on_failure
                    TCP_CHECK {
                            connect_timeout 3
                            connect_port 3306
                            }
                    }
​
            real_server 192.168.131.11 3306 {
                    weight 1
                    inhibit_on_failure
                    TCP_CHECK {
                            connect_timeout 3
                            connect_port 3306
                            }
                    }
    }
[root@lvs ~]# scp /etc/keepalived/keepalived.conf 192.168.131.159:/etc/keepalivce/
​
    # 注意:
        keepalived文件拷贝到 BACKUP 节点后, 要修改配置文件中的
            state 参数
            priority 参数
        不然会有极大的可能从而产生脑裂

启动,并观测结果

[root@lvs ~]# systemctl start keepalived
[root@lvs ~]# systemctl enable keepalived
[root@backup ~]# systemctl start keepalived
[root@backup ~]# systemctl enable keepalived
​
[root@lvs ~]# ipvsadm
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.131.16:mysql rr
  -> 192.168.131.9:mysql          Route   1      0          0         
  -> 192.168.131.10:mysql         Route   1      0          0         
  -> 192.168.131.11:mysql         Route   1      0          0   
  
[root@backup ~]# ipvsadm
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  backup:mysql rr
  -> 192.168.131.9:mysql          Route   1      0          0         
  -> 192.168.131.10:mysql         Route   1      0          0         
  -> 192.168.131.11:mysql         Route   1      0          0  

数据库状态检测脚本

版本一：简单使用：
#!/bin/bash
/usr/bin/mysql -h ip -uroot -p123 -e "show status;" &>/dev/null
if [ $? -ne 0 ] ;then
        systemctl stop keepalived
fi
# 此处的ip是本地ip
​
版本二：检查多次
#  vim  /root/keepalived_check_mysql.sh
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=1111
CHECK_TIME=3
​
#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
​
check_mysql_helth (){
    $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" &>/dev/null
    if [ $? -eq 0 ] ;then
        MYSQL_OK=1
    else
        MYSQL_OK=0
    fi
    return $MYSQL_OK
}
​
while [ $CHECK_TIME -ne 0 ]
do
        check_mysql_helth
        if [ $MYSQL_OK -eq 1 ] ; then
                exit 0
        fi
​
        if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 1 ];then
                /etc/init.d/keepalived stop
                exit 1
        fi
        let CHECK_TIME--
        sleep 1
done
​
版本三：检查多次
#  vim  /root/keepalived_check_mysql.sh
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=1111
CHECK_TIME=3
​
#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
​
check_mysql_helth (){
    $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" &>/dev/null
    if [ $? -eq 0 ] ;then
        MYSQL_OK=1
    else
        MYSQL_OK=0
    fi
    return $MYSQL_OK
}
​
while [ $CHECK_TIME -ne 0 ]
do
        check_mysql_helth
        if [ $MYSQL_OK -eq 1 ] ; then
                exit 0
        fi
​
        let CHECK_TIME--
        sleep 1
done
/etc/init.d/keepalived stop
exit 1
​
# chmod 755 /root/keepalived_check_mysql.sh
​
两边均启动keepalived
​
日志查看脚本是否被执行
# tail -f /var/log/messages
Jun 19 15:20:19 xen1 Keepalived_vrrp[6341]: Using LinkWatch kernel netlink reflector...
Jun 19 15:20:19 xen1 Keepalived_vrrp[6341]: VRRP sockpool: [ifindex(2), proto(112), fd(11,12)]
Jun 19 15:20:19 xen1 Keepalived_vrrp[6341]: VRRP_Script(check_run) succeeded