lvs+keepalived实现mysql的权重读操作

192.128.232.133/24    #读vip地址

slave01：192.128.232.129

slave02：192.128.232.130

slave03：192.128.232.131

master_keepalived:192.128.232.127

slave_keepalived:192.128.232.128

一：三台slave都要执行下面脚本

[root@mysql-129 ~]# cat /etc/init.d/realserver.sh
#!/bin/bash
# description: Config realserver lo and apply noarp

SNS_VIP=192.128.232.133   #读vip

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
 

[root@mysql-129 ~]# chmod +x /etc/init.d/realserver.sh

[root@mysql-129 ~]# /etc/init.d/realserver.sh start

二：keepalived的主配置文件内容。

[root@keepalived-127 ~]# yum -y install keepalived

[root@keepalived-127 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
   router_id LVS_MASTER
}
vrrp_instance Msyql_Lb {
    state BACKUP            #全部节点设置BACKUP,以优先级高暂时为master。
    interface eth0          #对应主机的网卡名称
    virtual_router_id 251   #id号要一致
    priority 100
    advert_int 1
    nopreempt               #当优先级高的主机宕机后，再次恢复时，不会抢夺vip，防止业务频繁切换。

    authentication {
        auth_type PASS
        auth_pass 11111111
    }

    virtual_ipaddress {
        192.128.232.133/24  #vip地址
    }
}

virtual_server 192.128.232.133 3306 {      # 定义虚拟服务器，地址与上面的virtual_ipaddress相同
    delay_loop 6                           # 健康检查时间间隔，3秒
    lb_algo wrr                            # 负载均衡调度算法：rr|wrr|lc|wlc|sh|dh|lblc
    lb_kind DR                             # 负载均衡转发规则：NAT|DR|TUN
    nat_mask 255.255.255.0
   # persistence_timeout 50                # 会话保持时间5秒，动态服务建议开启
    protocol TCP                           # 转发协议protocol，一般有tcp和udp两种

    real_server 192.128.232.129 3306 {
        weight 1                           # 权重越大负载分越大，0表示失效
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }

    real_server 192.128.232.130 3306 {
        weight 1                           # 权重越大负载分越大，0表示失效
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }

    real_server 192.128.232.131 3306 {
        weight 2                           # 权重越大负载分越大，0表示失效
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

三：keepalived的备配置内容

[root@keepalived-128 ~]# yum -y install keepalived
[root@keepalived-128 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
   router_id LVS_MASTER
}
vrrp_instance Msyql_Lb {
    state BACKUP             #全部节点设置BACKUP,以优先级高暂时为master。
    interface eth0              #对应主机的网卡名称
    virtual_router_id 251   #id号要一致
    priority 90                    #优先级低于master
    advert_int 1
    nopreempt     #当优先级高的主机宕机后，再次恢复时，不会抢夺vip，防止业务频繁切换。

    authentication {
        auth_type PASS
        auth_pass 11111111
    }

    virtual_ipaddress {
        192.128.232.133/24    #vip地址
    }
}

virtual_server 192.128.232.133 3306 {# 定义虚拟服务器，地址与上面的virtual_ipaddress相同
    delay_loop 6                  # 健康检查时间间隔，3秒
    lb_algo wrr                    # 负载均衡调度算法：rr|wrr|lc|wlc|sh|dh|lblc
    lb_kind DR                    # 负载均衡转发规则：NAT|DR|TUN
    nat_mask 255.255.255.0
   # persistence_timeout 50        # 会话保持时间5秒，动态服务建议开启
    protocol TCP                  # 转发协议protocol，一般有tcp和udp两种

    real_server 192.128.232.129 3306 {
        weight 1                  # 权重越大负载分越大，0表示失效
        TCP_CHECK {       #tcp检测
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }

    real_server 192.128.232.130 3306 {
        weight 1                  # 权重越大负载分越大，0表示失效
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }

    real_server 192.128.232.131 3306 {
        weight 2                  # 权重越大负载分越大，0表示失效
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

四：ipvsadm转发

[root@keepalived-127 ~]#yum -y install  ipvsadm 
[root@keepalived-127 ~]# watch ipvsadm -Ln --stats                                                                           
Every 2.0s: ipvsadm -ln --stats                                                                                                                                                         Tue Jun  8 11:50:22 2021

IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port               Conns   InPkts  OutPkts  InBytes OutBytes
  -> RemoteAddress:Port
TCP  192.128.232.133:3306                5       45        0     3740        0
  -> 192.128.232.129:3306                1        9        0      748        0
  -> 192.128.232.130:3306                1        9        0      748        0
  -> 192.128.232.131:3306                3       27        0     2244        0

五：测试，在一个客户端机器上，通过读vip实现负载均衡。
[root@keepalived-128 ~]# while true;do mysql -h 192.128.232.133 -u repl -prepl123 -e 'show variables like "hostname"';sleep 2;done
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| hostname      | mysql-131 |
+---------------+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| hostname      | mysql-130 |
+---------------+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| hostname      | mysql-129 |
+---------------+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| hostname      | mysql-131 |
+---------------+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| hostname      | mysql-131 |
+---------------+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| hostname      | mysql-130 |
+---------------+-----------+