LVS为MySQL读提供负载均衡

1. LVS为MySQL读提供负载均衡

部署约定

序号	目录名称	目录位置
1	keepalived配置文件目录	/etc/keepalived
2	从库状态检查脚本目录	/etc/keepalived

YUM安装LVS和Keepalived

配置阿里云的YUM源

#clean OS default repo
mkdir /etc/yum.repos.d/old && mv /etc/yum.repos.d/C* /etc/yum.repos.d/old/
#add local repo
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo

安装

yum install ipvsadm keepalived

配置Keepalived

! Configuration File for keepalived

global_defs {
   router_id MHA-A
}

# db Read
vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 150
    advert_int 6
    authentication {
        auth_type PASS
        auth_pass 123qwe
    }
    virtual_ipaddress {
        192.168.0.21/24
    }
}


# VIP 192.168.0.21
virtual_server 192.168.0.21 3306 {
    delay_loop 10
    lb_algo rr
    lb_kind DR
    nat_mask 255.255.255.0
    protocol TCP

    #sorry_server 192.168.0.235 3306

    real_server 192.168.0.235 3306 {
        weight 1
        TCP_CHECK {
          connect_port 3306
          connect_timeout 10
          nb_get_retry 3
          delay_before_retry 5
        }
        MISC_CHECK {
          misc_path "/etc/keepalived/check_slave.pl 192.168.0.235 3306"
          misc_dynamic
        }
    }

        real_server 192.168.0.236 3306 {
        weight 1
        TCP_CHECK {
          connect_port 3306
          connect_timeout 10
          nb_get_retry 3
          delay_before_retry 5
        }
        MISC_CHECK {
          misc_path "/etc/keepalived/check_slave.pl 192.168.0.236 3306"
          misc_dynamic
        }
    }
}

所有从库绑定读VIP

/app/scripts/lvs-start-client-read.sh

#!/bin/bash
#real_server.sh
vip=192.168.0.21
open() {
        sudo ifconfig lo:Rvip ${vip}/32 up
        sudo sysctl -w net.ipv4.conf.lo.arp_announce=2
        sudo sysctl -w net.ipv4.conf.lo.arp_ignore=1
        sudo sysctl -w net.ipv4.conf.all.arp_announce=2
        sudo sysctl -w net.ipv4.conf.all.arp_ignore=1
}
close() {
        sudo ifconfig lo:Rvip down
        sudo sysctl -w net.ipv4.conf.lo.arp_announce=0
        sudo sysctl -w net.ipv4.conf.lo.arp_ignore=0
        sudo sysctl -w net.ipv4.conf.all.arp_announce=0
        sudo sysctl -w net.ipv4.conf.all.arp_ignore=0
}
case $1 in
start)
        open
;;
stop)
        close
;;
*)
        echo "Usage: $0 need argument  [start|stop]"
;;
esac

启动Keepalived，观察连接情况

查看LVS状态

watch -n1 ipvsadmin -Ln

上传 /etc/keepalived/check_slave.pl 内容

#!/usr/bin/perl -w

use DBI;
use DBD::mysql;


# CONFIG VARIABLES
$SBM = 200;
$db = "information_schema";
$host = $ARGV[0];
$port = $ARGV[1];
$user = "repl";
$pw = "replpassword";

# SQL query
$query = "show slave status";

$dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pw, { RaiseError => 0,PrintError => 0 });

#print "$db, $host , $port , $user, $pw";

if (!defined($dbh)) {
    #print "connect fail.";
    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'};

}

#print "Slave_IO_Running = $Slave_IO_Running\n";
#print "Slave_SQL_Running = $Slave_SQL_Running\n";
#print "Seconds_Behind_Master = $Seconds_Behind_Master\n";

$sqlQuery->finish;
$dbh->disconnect();


if ( $Slave_IO_Running eq "No" || $Slave_SQL_Running eq "No" || $Slave_IO_Running eq "" || $Slave_SQL_Running eq "" ||  $Seconds_Behind_Master eq "NULL" || $Seconds_Behind_Master eq "" ) {


#print "Slave_IO_Running = $Slave_IO_Running\n";
#print "Slave_SQL_Running = $Slave_SQL_Running\n";
#print "Seconds_Behind_Master = $Seconds_Behind_Master\n";

    exit 1;

} else {

    if ( $Seconds_Behind_Master > $SBM ) {
        #print "Seconds_Behind_Master > SBM";
    exit 1;
    } else {
        #print "Seconds_Behind_Master < SBM";
        exit 0;
    }

}