LVS+keepalived实现mysql负载均衡和开机自启

àì夳堔傛蜴生んèń 2022-12-11 07:28 241阅读 0赞

### 一、架构 ###

Mysql-cluster本身实现了高可用，但是其sql节点并没有实现负载均衡。怎样让程序能均衡地访问sql节点呢，程序里写哪个数据库服务器IP呢？

LVS是实现负载均衡作用的，即将客户端的需求采用特定的负载均衡算法分发到后端的Web应用服务器上。

Keepalived是用来实现高可用的，提供健康检查，故障转移，即保证主LVS宕机后，从LVS可以在很短时间顶上，从而保证了系统或网站的稳定性。Keepalived在后端的realserver接收不到主节点的信息之后，keepalived能够自己调用ipvsadm命令生成规则，能够自动实现，将主节点的VIP以及ipvs规则“拿过来”，应用在从节点上，继续为用户服务。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMTUzNjE5_size_16_color_FFFFFF_t_70][]

java的高版本的Jdbc驱动已经实现负载均衡

jdbc:mysql:loadbalance://172.8.10.106:3306,172.8.10.107:3306/test?roundRobinLoadBalance=true

使用roundRobin算法，若是其中一台机器down掉以后，将会自动将不能访问的数据库服务器 踢除，如果还原后将继续进行压力分载。其余的数据库操作就和普通的一样。

但是.net没有封装，所以只能手动实现，用LVS+keepalived实现负载均衡，对外连一个VIP就可以了。

Mysql-cluster集群搭建见：[https://blog.csdn.net/qq\_21153619/article/details/108861691][https_blog.csdn.net_qq_21153619_article_details_108861691]

### 二、搭建 ###

1环境准备

<table> 
 <tbody> 
 <tr> 
 <td style="width:77.75pt;"> IP </td> 
 <td style="width:4cm;"> 主机名 </td> 
 <td style="width:219.7pt;"> 备注 </td> 
 </tr> 
 <tr> 
 <td style="width:77.75pt;"> 172.8.10.105 </td> 
 <td style="width:4cm;"> siger-data1.test.com </td> 
 <td style="width:219.7pt;"> 管理节点 </td> 
 </tr> 
 <tr> 
 <td style="width:77.75pt;"> 172.8.10.106 </td> 
 <td style="width:4cm;"> siger-data2.test.com </td> 
 <td style="width:219.7pt;"> 数据节点、sql节点、Lvs-master </td> 
 </tr> 
 <tr> 
 <td style="width:77.75pt;"> 172.8.10.107 </td> 
 <td style="width:4cm;"> siger-data3.test.com </td> 
 <td style="width:219.7pt;"> 数据节点、sql节点、Lvs-backup </td> 
 </tr> 
 <tr> 
 <td style="width:77.75pt;"> 172.8.10.109 </td> 
 <td style="width:4cm;"> / </td> 
 <td style="width:219.7pt;"> VIP </td> 
 </tr> 
 </tbody> 
</table>

2、安装keepalived

\#安装ssl

yum -y install openssl-devel libnfnetlink-devel net-tools

\#解压

mkdir /usr/local/keepalived

mv /opt/keepalived-1.4.2.tar.gz /usr/local/keepalived/

cd /usr/local/keepalived

tar -zxvf keepalived-1.4.2.tar.gz

cd keepalived-1.4.2

./configure --prefix=/usr/local/keepalived/

\#安装和编译

make && make install

\#移动配置文件

cd /usr/local/keepalived/etc/keepalived

mv keepalived.conf keepalived.conf.bak

\# 新建配置文件

vim keepalived.conf

global_defs {
        router_id HaMySQL_1		#设置lvs的id，在一个网络内应该是唯一的
    }
    vrrp_sync_group VGM {
        group {
            VI_MYSQL
        }
    }
    vrrp_instance VI_MYSQL {
        state MASTER   #指定Keepalived的角色，MASTER为主，BACKUP为备  
        interface ens192	#网卡号码
        virtual_router_id 100	#虚拟路由编号，主备要一致
        priority 100  #定义优先级，数字越大，优先级越高，主DR必须大于备用DR    
        advert_int 1  #检查间隔，默认为1s
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        virtual_ipaddress {
            172.8.10.109
        }
    }
    
    virtual_server 172.8.10.109 3306 {
        delay_loop 6 	#健康检查时间间隔，3秒
        lb_algo rr		#负载均衡调度算法：rr|wrr|lc|wlc|sh|dh|lblc
        lb_kind DR		#负载均衡转发规则：NAT|DR|TUN
        #nat_mask 255.255.0.0
    	#persistence_timeout 50		#会话保持时间5秒，动态服务建议开启
        protocol TCP	#转发协议protocol，一般有tcp和udp两种
    
        real_server 172.8.10.106 3306 {
            weight 3
            TCP_CHECK {
    		connect_timeout 3
    		nb_get_retry 3
    		delay_before_retry 3
    		connect_port 3306
    		misc_path "/etc/keepalived/mysql_check.sh 172.8.10.106"
    		misc_timeout 60
    		misc_dynamic
            }
        }
    
        real_server 172.8.10.107 3306 {
            weight 3
            TCP_CHECK {
    		connect_timeout 3
    		nb_get_retry 3
    		delay_before_retry 3
    		connect_port 3306
    		misc_path "/etc/keepalived/mysql_check.sh 172.8.10.107"
    		misc_timeout 60
    		misc_dynamic
            }
        }
    }

\#系统执行目录配置

mkdir /etc/keepalived

cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

cp /usr/local/keepalived/keepalived-1.4.2/keepalived/etc/init.d/keepalived /etc/init.d/

cp /usr/local/keepalived/keepalived-1.4.2/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

\#设置开机自启

systemctl enable keepalived.service

3、校验mysql脚本

\#编辑

vim /etc/keepalived/mysql\_check.sh

#!/bin/bash
    MYSQL_PING=`/usr/local/mysql/bin/mysqladmin -usiger -p'Siger_123' -h $1 ping 2>/dev/null | grep -c alive`
    MYSQL_OK="1"
    
    if [[ ${MYSQL_PING} == ${MYSQL_OK} ]]; then
            echo "yes"
            exit 0
        else
            echo "no"
            exit 1
    fi

\#添加加可执行的权限

chmod +x mysql\_check.sh

\#测试脚本

/etc/keepalived/mysql\_check.sh 172.8.10.106

![20200929101820689.png][]

4、安装ipvsadm**（sql节点）**

yum install -y ipvsadm

\#修改配置

vim /etc/rc.d/init.d/realserver.sh

#!/bin/bash
    # description: Config realserver lo and apply noarp
    SNS_VIP=172.8.10.109
    /etc/rc.d/init.d/functions
    case "$1" in
    start)
           ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP
           /sbin/route add -host $SNS_VIP dev lo:0
           echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
           echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
           echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
           echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
           sysctl -p >/dev/null 2>&1
           echo "RealServer Start OK"
           ;;
    stop)
           ifconfig lo:0 down
           route del $SNS_VIP >/dev/null 2>&1
           echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
           echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
           echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
           echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
           echo "RealServer Stoped"
           ;;
    *)
           echo "Usage: $0 {start|stop}"
           exit 1
    esac
    exit 0

\#添加加可执行的权限  
chmod +x /etc/rc.d/init.d/realserver.sh

chmod 755 /etc/rc.d/init.d/functions

5、启动

\#启动LVS节点

/etc/rc.d/init.d/realserver.sh start

\#看下是否生效

ip addr

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMTUzNjE5_size_16_color_FFFFFF_t_70 1][]

\#启动调度器

service keepalived start

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMTUzNjE5_size_16_color_FFFFFF_t_70 2][]

\#进行查看

tail -f /var/log/messages

若看到如下提示，则表示配置成功

![20200929101820729.png][]

\#在调度器上查看

ipvsadm

![20200929101820710.png][]

6、测试

\#关闭siger-data3.test.com的数据节点和sql节点

/etc/init.d/mysql.server stop

![20200929101820718.png][]

\#查看节点状态

ndb\_mgm

show

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMTUzNjE5_size_16_color_FFFFFF_t_70 3][]

\#查看负载状态

ipvsadm

![20200929101820708.png][]

发现siger-data3.test.com节点已被剔除负载

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMTUzNjE5_size_16_color_FFFFFF_t_70]: /images/20221123/95365d25ed5841519453970229c54607.png
[https_blog.csdn.net_qq_21153619_article_details_108861691]: https://blog.csdn.net/qq_21153619/article/details/108861691
[20200929101820689.png]: /images/20221123/fc495342a7204d85b103778481328305.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMTUzNjE5_size_16_color_FFFFFF_t_70 1]: /images/20221123/37b34f09466b4c3496aeccc57d769645.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMTUzNjE5_size_16_color_FFFFFF_t_70 2]: /images/20221123/12770f6232c84e36b97e1ac0faedb69f.png
[20200929101820729.png]: /images/20221123/b10166a3160248b88808dd73fe25b15c.png
[20200929101820710.png]: /images/20221123/aad9d22666304b9584ca550042f6999c.png
[20200929101820718.png]: /images/20221123/b6e59caaa6554c43b12a6c8612f7e980.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMTUzNjE5_size_16_color_FFFFFF_t_70 3]: /images/20221123/b7eef4e58aa74452982ccf848afd1a82.png
[20200929101820708.png]: /images/20221123/95b54772323940f8abc529052e461701.png