LVS/DR + KeepAlived实现MySQL的负载均衡

Posted on 2013 年 3 月 16 日 by Jose

LVS负载均衡搭建说明

环境说明

MySQL Galera多主复制已经安装在G221,G222,G223机器上.
G221 : 192.168.1.221
G222: 192.168.1.222
G222: 192.168.1.222
虚拟IP : 192.168.1.230

LVS负载均衡说明

在G221,G222机器上部署LVS/DR + KeepAlived,对G221和G222的MySQL做读的负载均衡.
G223提供写的服务(暂不考虑加入均衡).

Internet--
         |
    =============
    | ISP Router|
    =============
         |
         |
         |            |eth0 -> 192.168.1.221 (connected to lan)
         |-MySQL_LB1==|
         |            |eth0:1 -> 192.168.1.230 (vip master)
         |
         |            |eth0 -> 192.168.1.222 (connected to lan)
         |-MySQL_LB2==|
                      |eth0:1 -> 192.168.1.230 (vip backup)

负载均衡软件安装

在两台机器G221和G222上分别安装下列软件.

安装ipvsadm

# yum -y install kernel-devel make gcc openssl-devel libnl*
# wget http://www./software/kernel-2.6/ipvsadm-1.26.tar.gz
# ln -s /usr/src/kernels/2.6.32-358.2.1.el6.x86_64/ /usr/src/linux
# tar zxvf ipvsadm-1.26.tar.gz
# cd ipvsadm-1.26
# make
# make install

安装KeepAlived

安装popt:

# wget -c http:///files/popt/popt-1.14.tar.gz
# tar zxvf popt-1.14.tar.gz
# cd popt-1.14
# ./configure && make && make install

安装KeepAlived:

# wget -c http://www./software/keepalived-1.2.7.tar.gz
# tar zxvf keepalived-1.2.7.tar.gz
# cd keepalived-1.2.7
# ./configure --prefix=/usr/local/keepalived
# make && make install
# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
# mkdir /etc/keepalived
# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

KeepAlived配置与LVS脚本

KeepAlived配置

G221(LB-Primary)的KeepAlived配置:

[root@G221 ~]# vi /etc/keepalived/keepalived.conf 
[root@G221 ~]# cat /etc/keepalived/keepalived.conf

[root@G221 ~]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id MySQL_LB1
}

vrrp_sync_group VSG {
    group {
        MySQL_Loadblancing
    }
}

vrrp_instance MySQL_Loadblancing {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 101
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.230
    }
}

virtual_server 192.168.1.230 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    # nat_mask 255.255.255.0
    #persistence_timeout 50
    protocol TCP

    real_server 192.168.1.221 3306 {
        weight 3
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
    real_server 192.168.1.222 3306 {
        weight 3
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

G222(LB-Secondary)的KeepAlived配置:

[root@G222 ~]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id MySQL_LB2
}

vrrp_sync_group VSG {
    group {
        MySQL_Loadblancing
    }
}

vrrp_instance MySQL_Loadblancing {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.230
    }
}

virtual_server 192.168.1.230 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    # nat_mask 255.255.255.0
    #persistence_timeout 50
    protocol TCP

    real_server 192.168.1.221 3306 {
        weight 3
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
    real_server 192.168.1.222 3306 {
        weight 3
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

LVS脚本

两台均衡机器都部署如下LVS启动脚本.

# vi /etc/init.d/lvsdr.sh
# cat /etc/init.d/lvsdr.sh

#!/bin/bash
VIP=192.168.1.230                     
. /etc/rc.d/init.d/functions                     
case "$1" in
start)
    /sbin/ifconfig lo down  
    /sbin/ifconfig lo up        
    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    
    /sbin/sysctl -p >/dev/null 2>&1
    /sbin/ifconfig lo:0 $VIP netmask 255.255.255.255 up
    /sbin/route add -host $VIP dev lo:0  
    echo "LVS-DR real server starts successfully.\n"                    
    ;;    
stop)    
    /sbin/ifconfig lo:0 down    
    /sbin/route del $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 "LVS-DR real server stopped." 
    ;;
status)
    isLoOn=`/sbin/ifconfig lo:0 | grep "$VIP"`
    isRoOn=`/bin/netstat -rn | grep "$VIP"`
    if [ "$isLoOn" == "" -a "$isRoOn" == "" ]; then
       echo "LVS-DR real server has to run yet."
    else
       echo "LVS-DR real server is running."
    fi
    exit 3
    ;;
*)    
    echo "Usage: $0 {start|stop|status}" 
    exit 1    
esac                     
exit 0

负载均衡器启动

启动LVS与KeepAlived

两台机器分别启动LVS:
```
# /etc/init.d/lvsdr.sh start
```
两台机器分别启动KeepAlived:
```
# /etc/init.d/keepalived start
```
加入开机自启动

# echo “/etc/init.d/lvsdr.sh start” >> /etc/rc.d/rc.local
# chkconfig keepalived on

负载均衡测试

VIP使用测试

[root@G223 data]# mysql -ugalera -p -h192.168.1.230
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 116
Server version: 5.5.29-log Source distribution, wsrep_23.7.3.r3853

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ctest              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql>

KeepAlived故障测试

G221模拟宕机:

[root@G221 ~]# killall keepalived

G222查看KeepAlived日志:

[root@G222 ~]# tail -f /var/log/messages
Mar 16 00:04:59 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) removing protocol VIPs.
Mar 16 00:04:59 localhost Keepalived_healthcheckers[13137]: Netlink reflector reports IP 192.168.1.230 removed
Mar 16 00:04:59 localhost Keepalived_vrrp[13138]: VRRP_Group(VSG) Syncing instances to BACKUP state
Mar 16 00:05:24 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) Transition to MASTER STATE
Mar 16 00:05:24 localhost Keepalived_vrrp[13138]: VRRP_Group(VSG) Syncing instances to MASTER state
Mar 16 00:05:25 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) Entering MASTER STATE
Mar 16 00:05:25 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) setting protocol VIPs.
Mar 16 00:05:25 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) Sending gratuitous ARPs on eth0 for 192.168.1.230
Mar 16 00:05:25 localhost Keepalived_healthcheckers[13137]: Netlink reflector reports IP 192.168.1.230 added
Mar 16 00:05:30 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) Sending gratuitous ARPs on eth0 for 192.168.1.230

MySQL宕机测试

G221:

[root@G221 ~]# service mysql stop
Shutting down MySQL.... SUCCESS!

查看KeepAlived日志

[root@G222 ~]# tail -f /var/log/messages
Mar 16 00:04:59 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) removing protocol VIPs.
Mar 16 00:04:59 localhost Keepalived_healthcheckers[13137]: Netlink reflector reports IP 192.168.1.230 removed
Mar 16 00:04:59 localhost Keepalived_vrrp[13138]: VRRP_Group(VSG) Syncing instances to BACKUP state
Mar 16 00:05:24 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) Transition to MASTER STATE
Mar 16 00:05:24 localhost Keepalived_vrrp[13138]: VRRP_Group(VSG) Syncing instances to MASTER state
Mar 16 00:05:25 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) Entering MASTER STATE
Mar 16 00:05:25 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) setting protocol VIPs.
Mar 16 00:05:25 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) Sending gratuitous ARPs on eth0 for 192.168.1.230
Mar 16 00:05:25 localhost Keepalived_healthcheckers[13137]: Netlink reflector reports IP 192.168.1.230 added
Mar 16 00:05:30 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) Sending gratuitous ARPs on eth0 for 192.168.1.230
Mar 16 02:36:05 localhost Keepalived_healthcheckers[13137]: TCP connection to [192.168.1.221]:3306 failed !!!
Mar 16 02:36:05 localhost Keepalived_healthcheckers[13137]: Removing service [192.168.1.221]:3306 from VS [192.168.1.230]:3306

可以看到G221的MySQL宕机之后,服务即被移除.

MySQL恢复服务测试
G221恢复服务:

[root@G221 ~]# service mysql start --wsrep-cluster-address=gcomm://192.168.1.223:4567,192.168.1.222:4567
Starting MySQL...... SUCCESS!

查看KeepAlived日志:

[root@G222 ~]# tail -f /var/log/messages
Mar 16 00:04:59 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) removing protocol VIPs.
Mar 16 00:04:59 localhost Keepalived_healthcheckers[13137]: Netlink reflector reports IP 192.168.1.230 removed
Mar 16 00:04:59 localhost Keepalived_vrrp[13138]: VRRP_Group(VSG) Syncing instances to BACKUP state
Mar 16 00:05:24 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) Transition to MASTER STATE
Mar 16 00:05:24 localhost Keepalived_vrrp[13138]: VRRP_Group(VSG) Syncing instances to MASTER state
Mar 16 00:05:25 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) Entering MASTER STATE
Mar 16 00:05:25 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) setting protocol VIPs.
Mar 16 00:05:25 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) Sending gratuitous ARPs on eth0 for 192.168.1.230
Mar 16 00:05:25 localhost Keepalived_healthcheckers[13137]: Netlink reflector reports IP 192.168.1.230 added
Mar 16 00:05:30 localhost Keepalived_vrrp[13138]: VRRP_Instance(MySQL_Loadblancing) Sending gratuitous ARPs on eth0 for 192.168.1.230
Mar 16 02:36:05 localhost Keepalived_healthcheckers[13137]: TCP connection to [192.168.1.221]:3306 failed !!!
Mar 16 02:36:05 localhost Keepalived_healthcheckers[13137]: Removing service [192.168.1.221]:3306 from VS [192.168.1.230]:3306
Mar 16 02:40:35 localhost Keepalived_healthcheckers[13137]: TCP connection to [192.168.1.221]:3306 success.
Mar 16 02:40:35 localhost Keepalived_healthcheckers[13137]: Adding service [192.168.1.221]:3306 to VS [192.168.1.230]:3306