lvs+keepalived对MySQL Slave 做读负载均衡完整文档20150523
前言：电商大促 后端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_01192.168.1.121
old_db_slave_02192.168.1.123
old_db_slave_03192.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 perlperl-DBIperl-DBD-MySQLmake 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;
$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工具
wgethttp://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
页:
[1]