LVS+Keepalived实现Mysql负载均衡、故障剔除(DR模式)

lpvsadm
IPVS(IP Virtual Server)是整个负载均衡的基础，如果没有这个基础，故障隔离与失败切换就毫无意义了。
IPVS 具体实现是由ipvsadm这个程序来完成。ipvsadm只要安装好就可以，所有配置由keepalived.conf这个文件来实现。
keepalived
在这里主要作用是Realserver的健康状态检查及故障隔离；负载均衡Master机和Backup主机之间失败切换的实现(failover)。
mysql
这里的两个mysql没有做主从，仅是单纯的实现负载均衡；适用于多个从数据库的轮询，就是将这些从mysql承担的查询压力
分散开来，并且实现这些节点的故障隔离及节点控制端的高可用。

一、准备工作
1.server准备
virtual ip              192.168.1.130    由master提供，master出现故障时由backup提供
server01 (master)       192.168.1.120    提供vip,并实时监测mysql01、mysql02的健康状态
server02 (backup)       192.168.1.121    备用机，master出现故障时接替其工作
mysql01 (realserver01)  192.168.1.122    真实地提供mysql服务，与mysql02做轮询
mysql02 (realserver02)  192.168.1.123    真实地提供mysql服务，与mysql01做轮询
2.所需软件
system:      Redhat 2.6.32-279.el6.x86_64
ipvsadm:    
keepalived:  
二、安装配置
1.安装ipvsadm （master、backup都要安装）

# ln -s /usr/src/kernels/2.6.32-279.el6.x86_64/ /usr/src/linux/
# tar zxvf ipvsadm-1.26.tar.gz
# cd ipvsadm-1.26
# make && make install
# ipvsadm -ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port Forward Weight ActiveConn InActConn

2.安装keepalived （master、backup都要安装配置,注意配置文件的区别，其它都相同）

# tar zxvf keepalived-1.2.0.tar.gz
# cd keepalived-1.2.0
# ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.32-279.el6.x86_64/
# make && make install
# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/keepalived
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived
# mkdir /etc/keepalived
# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
master配置文件：
# vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
   notification_email {
        root@localhost
   }
   notification_email_from root@localhost
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_MASTER
}

vrrp_instance VI_1 {
    state MASTER --backup此处需要修改为BACKUP
    interface eth0
    virtual_router_id 51
    priority 100 --backup此处值应略小一些，如90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.130
    }
}

virtual_server 192.168.1.130 3306 {
    delay_loop 6
    lb_algo wrr
    lb_kind DR
#    persistence_timeout 50 --表示同一ip50秒内的连接都发送到同一realserver,为了测试轮询，先注释
    protocol TCP

    real_server 192.168.1.122 3306 {
        weight 3
        TCP_CHECK {
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
    real_server 192.168.1.123 3306 {
        weight 3
        TCP_CHECK {
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

backup配置文件：
与master相同，仅两处需要修改为：
state MASTER --> state BACKUP
priority 100 --> priority 90

# chkconfig keepalived on
# service keepalived start
# service keepalived status
keepalived (pid 1344) 正在运行...

3.realserver.sh脚本（mysql01 mysql02 都要部署）

vi /root/bin/realserver.sh
--------------------------------------------------------------
#!/bin/bash
vip=192.168.1.130
. /etc/rc.d/init.d/functions
case "$1" in
    start)
        /sbin/ifconfig lo:0 $vip broadcast $vip netmask 255.255.255.255 up
        /sbin/route add -host $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)
        /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 "RealServer Stoped";;
     *) echo "Usage: $0 {start|stop}"
        exit 1;;
esac
exit 0
--------------------------------------------------------------
# chmod +x /root/bin/realserver.sh
# sh /root/bin/realserver.sh start
# echo "sh /root/bin/realserver.sh start" >> /etc/rc.local

4.启动mysql01、mysql02上的mysql服务,为了更好区分，分别执行：

mysql> grant all on *.* to 'mstsc'@'192.168.1.%' identified by 'mstsc';
mysql> flush privileges;
mysql> create database mysql_122;

mysql> grant all on *.* to 'mstsc'@'192.168.1.%' identified by 'mstsc';
mysql> flush privileges;
mysql> create database mysql_123

三、测试
1.查看状态，并测试页面是否正常轮询 （此时master、backup上都启动keepalived，mysql01、mysql02都启动mysql)

# ip a|grep 192.168
    inet 192.168.1.120/24 brd 192.168.1.255 scope global eth0
    inet 192.168.1.130/32 scope global eth0
# ipvsadm -ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.1.130:3306 rr
  -> 192.168.1.122:3306           Route   3      1          0
  -> 192.168.1.123:3306           Route   3      0          1
# mysql -h192.168.1.130 -umstsc -p
Enter password:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysql_123 |
| test |
+--------------------+
4 rows in set (0.01 sec)
mysql> quit

[root@server02 keepalived]# mysql -h192.168.1.130 -umstsc -p
Enter password:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysql_122 |
| test |
+--------------------+
4 rows in set (0.00 sec)
状态及轮询测试正常

2.停止master上的keepalived,查看backup是否正常接管vip,再测试页面轮询是否正常

master
# service keepalived stop
停止 keepalived： [确定]
# ip a|grep 192.168
    inet 192.168.1.120/24 brd 192.168.1.255 scope global eth0
backup
# ip a |grep 192.168
    inet 192.168.1.121/24 brd 192.168.1.255 scope global eth0
    inet 192.168.1.130/32 scope global eth0
[root@server02 keepalived]# ipvsadm -ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.1.130:3306 rr
  -> 192.168.1.122:3306           Route   3      0          0
  -> 192.168.1.123:3306           Route   3      0          0
backup正常接管
master
# mysql -h192.168.1.130 -umstsc -p
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysql_123 |
| test |
+--------------------+
4 rows in set (0.01 sec)

# mysql -h192.168.1.130 -umstsc -p
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysql_122 |
| test |
+--------------------+
4 rows in set (0.00 sec)
轮询正常

3.停止mysql01上的mysql，查看keepalived能否正常检测出并隔离故障机器

mysql01
# service mysqld stop
backup
# ipvsadm -ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.1.130:3306 rr
  -> 192.168.1.123:3306           Route   3      0          0
master
# mysql -h192.168.1.130 -umstsc -p
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysql_123 |
| test |
+--------------------+
4 rows in set (0.00 sec)

# mysql -h192.168.1.130 -umstsc -p
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysql_123 |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql01节点故障被检测到并被隔离，master连续访问mysql,此时只能访问到mysql02这个real server提供的mysql服务。