lvs+keepalived对MySQL Slave 做读负载均衡完整文档20150523

lbdbzj110

　　前言：

电商大促 后端API对MySQL读写要求在毫秒级别，因为瞬时流量和用户数突增，导致数据库挂掉了将近3分钟左右，520大促期间 Haproxy 在MySQL端口转发效率上，
稍稍的让我们逊色了一下，于是决定尝试下lvs 实现对MySQL Slave 的负载均衡，折腾过程中，多多少少踩过不少坑，网络上的多数文档残缺不全或者错误百出，
很大程度上误导了大家，一下是完整文档实战过程，写出来分享给大家。

提纲：
1：系统约定
2：部署前注意事项
3：部署realserver 以及注意事项
4：部署lvs双M 提升服务器有效利用率
5：lvs monitor部署


一：系统约定
group_db_01
old_db_slave_01  192.168.1.121
old_db_slave_02  192.168.1.123
old_db_slave_03  192.168.1.217

group_db_02
mall_db_slave_01 192.168.1.122
mall_db_slave_02 192.168.1.68
mall_db_slave_03 192.168.1.218

group_lvs:
lvs-01:192.168.1.235
lvs-02:192.168.1.236

vip-01:192.168.1.237
vip-02:192.168.1.238

系统版本：
CentOS64_x64

二：部署前注意事项
1：关闭iptable(后期需要再开启调试)

2：关闭selinux 方法如下：
sed -i 's/\=enforcing/\=disabled/' /etc/sysconfig/selinux

3：内核优化 主要是开启内核转发 这里贴出完整的内核配置

group_lvs操作
vi /etc/sysctl.conf
#----------------------------------------------------------------------------------------------------------------------------------
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 1200
net.ipv4.ip_local_port_range = 1024 65000
net.ipv4.tcp_max_syn_backlog = 8192
net.ipv4.tcp_max_tw_buckets = 5000
net.core.somaxconn = 32768
net.ipv4.tcp_keepalive_probes = 5
net.ipv4.tcp_keepalive_intvl = 20
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.core.netdev_max_backlog = 32768
net.ipv4.tcp_timestamps = 0
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syn_retries = 2
net.ipv4.tcp_retries2 = 5
net.ipv4.tcp_mem = 41943040 73400320 94371840
net.ipv4.tcp_max_orphans = 3276800
fs.file-max = 1300000
#LVS虚拟服务器(VIP)忽略ARP广播包
net.ipv4.ip_forward= 1
net.ipv4.conf.lo.arp_ignore= 1
net.ipv4.conf.lo.arp_announce= 2
net.ipv4.conf.all.arp_ignore= 1
net.ipv4.conf.all.arp_announce= 2
#----------------------------------------------------------------------------------------------------------------------------------

三：部署realserver 所有操作相同

vi script/ipvs_v1.1.sh
#----------------------------------------------------------------------------------------------------------------------------------
#!/bin/bash
#created by zhangluya
#date:2015-05-21
#info:ipvsadm
VIP=(
    192.168.1.237
    192.168.1.238
)
. /etc/rc.d/init.d/functions
case "$1" in
start)
    echo "start realserver......"
      for ((i=0; i<`echo ${#VIP

}`; i++));do
              interface="lo:`echo ${VIP[$i]}|awk -F . '{print $4}'`"
              /sbin/ifconfig $interface ${VIP[$i]} broadcast ${VIP[$i]} netmask 255.255.255.255 up
              /sbin/route add -host ${VIP[$i]} dev ${interface}
      done
    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
    ;;
stop)
    /sbin/ifconfig lo:237 down
    /sbin/ifconfig lo:238 down
    /sbin/route del 192.168.1.237 > /dev/null 2>&1
    /sbin/route del 192.168.1.238 > /dev/null 2>&1
    echo "close lvs directorserver"
    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
    sysctl -p /dev/null 2>&1
    ;;
*)
    echo "Usage: $0 {start|stop}"
    exit 1
esac
#----------------------------------------------------------------------------------------------------------------------------------

chmod a+x /opt/sbin/lvs_script/ipvs_v1.1.sh
echo '/opt/sbin/lvs_script/ipvs_v1.1.sh start' >> /etc/rc.local

四：部署lvs双M 提升服务器有效利用率
yum install -y ipvsadm kernel-devel perl  perl-DBI  perl-DBD-MySQL  make gcc openssl-devel libnl*
wget http://www.keepalived.org/software/keepalived-1.2.16.tar.gz
tar zvfx keepalived-1.2.16.tar.gz
cd keepalived-1.2.16
./configure
make
make install
/bin/cp /usr/local/etc/sysconfig/keepalived  /etc/sysconfig/
mkdir -p /etc/keepalived
/bin/cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/
/bin/cp /usr/local/sbin/keepalived /usr/sbin/
/bin/cp /usr/local/etc/rc.d/init.d/keepalived  /etc/rc.d/init.d/
chmod +x /etc/rc.d/init.d/keepalived
cd ..
echo "/etc/rc.d/init.d/keepalived" >> /etc/rc.local

lvs-01 配置：
#----------------------------------------------------------------------------------------------------------------------------------
! Configuration File for keepalived
global_defs {
   notification_email {
     zhangluya@hichao.com
     64125568@qq.com
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL_237
   }
vrrp_instance VI_1 {
    state MASTER
    interface em1
    virtual_router_id 235
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.237/24
    }
    }
virtual_server 192.168.1.237 3306 {
    delay_loop 6
    lb_algo wrr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP
    real_server 192.168.1.121 3306 {
        weight 1
        MISC_CHECK {
        misc_path "/etc/keepalived/check_slave.pl 192.168.1.121"
        misc_dynamic
        }
    }
  
  
    real_server 192.168.1.123 3306 {
        weight 1
        MISC_CHECK {
        misc_path "/etc/keepalived/check_slave.pl 192.168.1.123"
        misc_dynamic
        }
    }
   
    real_server 192.168.1.217 3306 {
        weight 1
        MISC_CHECK {
        misc_path "/etc/keepalived/check_slave.pl 192.168.1.217"
        misc_dynamic
        }
    }  
}

vrrp_instance VI_2 {
    state BACKUP
    interface em1
    virtual_router_id 236
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.238/24
    }
    }
virtual_server 192.168.1.238 3306 {
    delay_loop 6
    lb_algo wrr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP
    real_server 192.168.1.122 3306 {
        weight 1
        MISC_CHECK {
        misc_path "/etc/keepalived/check_slave.pl 192.168.1.122"
        misc_dynamic
        }
    }
  
  
    real_server 192.168.1.68 3306 {
        weight 1
        MISC_CHECK {
        misc_path "/etc/keepalived/check_slave.pl 192.168.1.68"
        misc_dynamic
        }
    }
   
    real_server 192.168.1.218 3306 {
        weight 1
        MISC_CHECK {
        misc_path "/etc/keepalived/check_slave.pl 192.168.1.218"
        misc_dynamic
        }
    }  
}
#----------------------------------------------------------------------------------------------------------------------------------


lvs-02
#----------------------------------------------------------------------------------------------------------------------------------
! Configuration File for keepalived
global_defs {
   notification_email {
     zhangluya@hichao.com
     64125568@qq.com
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL_237
}
vrrp_instance VI_1 {
    state BACKUP
    interface em1
    virtual_router_id 235
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.237/24
    }
}
virtual_server 192.168.1.237 3306 {
    delay_loop 6
    lb_algo wrr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP
    real_server 192.168.1.121 3306 {
        weight 1
        MISC_CHECK {
        misc_path "/etc/keepalived/check_slave.pl 192.168.1.121"
        misc_dynamic
       }
     }
  
    real_server 192.168.1.123 3306 {
        weight 1
        MISC_CHECK {
        misc_path "/etc/keepalived/check_slave.pl 192.168.1.123"
        misc_dynamic
       }
     }
    real_server 192.168.1.217 3306 {
        weight 1
        MISC_CHECK {
        misc_path "/etc/keepalived/check_slave.pl 192.168.1.217"
        misc_dynamic
       }
    }  
}

vrrp_instance VI_2 {
    state MASTER
    interface em1
    virtual_router_id 236
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.238/24
    }
}
virtual_server 192.168.1.238 3306 {
    delay_loop 6
    lb_algo wrr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP
    real_server 192.168.1.122 3306 {
        weight 1
        MISC_CHECK {
        misc_path "/etc/keepalived/check_slave.pl 192.168.1.122"
        misc_dynamic
       }
     }
  
    real_server 192.168.1.68 3306 {
        weight 1
        MISC_CHECK {
        misc_path "/etc/keepalived/check_slave.pl 192.168.1.68"
        misc_dynamic
       }
     }
    real_server 192.168.1.218 3306 {
        weight 1
        MISC_CHECK {
        misc_path "/etc/keepalived/check_slave.pl 192.168.1.218"
        misc_dynamic
       }
    }  
}
#----------------------------------------------------------------------------------------------------------------------------------

echo "/etc/init.d/keepalived start" >> /etc/rc.local

check_slave.pl
#----------------------------------------------------------------------------------------------------------------------------------
#!/usr/bin/perl -w
use DBI;
use DBD::mysql;
#CONFIG VARIABLES
$SBM = 120;
$db = "mysql";
$host = $ARGV[0];
$port = 3306;
$user = "lvs_";
$pw = "123456";
#SQL query
$query = "show slave status";
$dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pw, { RaiseError => 0,PrintError => 0 });
if (!defined($dbh)) {
    exit 1;
}
$sqlQuery = $dbh->prepare($query);
$sqlQuery->execute;
$Slave_IO_Running =  "";
$Slave_SQL_Running = "";
$Seconds_Behind_Master = "";
while (my $ref = $sqlQuery->fetchrow_hashref()) {
    $Slave_IO_Running = $ref->{'Slave_IO_Running'};
    $Slave_SQL_Running = $ref->{'Slave_SQL_Running'};
    $Seconds_Behind_Master = $ref->{'Seconds_Behind_Master'};
}
$sqlQuery->finish;
$dbh->disconnect();
if ( $Slave_IO_Running eq "No" || $Slave_SQL_Running eq "No" ) {
    exit 1;
} else {
    if ( $Seconds_Behind_Master gt $SBM ) {
        exit 1;
    } else {
         exit 0;
    }
}
#----------------------------------------------------------------------------------------------------------------------------------
chmod a+x check_slave.pl


五：lvs monitor部署 监控类
#----------------------------------------------------------------------------------------------------------------------------------
1：在LVS上安装web和php服务
yum install httpd php pango* -y
2：在LVS上安装rrdtool工具
wget  http://oss.oetiker.ch/rrdtool/pub/rrdtool-1.4.7.tar.gz
tar zxvf rrdtool-1.4.7.tar.gz
cd rrdtool-1.4.7
./configure --prefix=/usr/local/rrdtool
make
make install
cd
echo "/usr/local/rrdtool/lib"   >> /etc/ld.so.conf
ldconfig -v
3：下载安装lvs-rrd
wget http://tepedino.org/lvs-rrd/lvs-rrd-v0.7.tar.gz
tar -zxvf lvs-rrd-v0.7.tar.gz
mv lvs-rrd-v0.7 /var/www/html/lvs
cd /var/www/html/lvs/
4：修改默认配置，主要是些命令的路径指向
vim lvs.rrd.update
#---------------------------------------------------------
RRDTOOL="/usr/local/rrdtool/bin/rrdtool"
IPVSADM="/sbin/ipvsadm"
WORKDIR="/var/www/html/lvs/rrd"
#---------------------------------------------------------
vim graph-lvs.sh
#---------------------------------------------------------
WORKDIR="/var/www/html/lvs/rrd"
RRDTOOL="/usr/local/rrdtool/bin/rrdtool"
GRAPHS="/var/www/html/lvs/graphs"
WEBPATH="/lvs/graphs"
#---------------------------------------------------------
vim lvs-rrd.php
#---------------------------------------------------------
<?php
    header("Cache-Control: max-age=300, must-revalidate");
    system("/var/www/html/lvs/graph-lvs.sh -H");
    ?>
#---------------------------------------------------------

5：创建rrd绘图目录，并修改文件权限
mkdir -p /var/www/html/lvs/rrd
chown  -R apache /var/www/html/lvs/
6：配置周期收集数据，并开启服务
*/1 * * * * /var/www/html/lvs/lvs.rrd.update > /dev/null 2>&1
/etc/init.d/httpd start
echo "/etc/init.d/httpd start" >> /etc/rc.local