1. LVS为MySQL读提供负载均衡
最后更新于：2022-04-02 07:38:24
#### 部署约定
|序号|目录名称|目录位置|
| :------------: | :------------: |:------------: |
|1|keepalived配置文件目录|/etc/keepalived|
|2|从库状态检查脚本目录|/etc/keepalived|
#### YUM安装LVS和Keepalived
##### 配置阿里云的YUM源
```shell
#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
```
安装
```shell
yum install ipvsadm keepalived
```
#### 配置Keepalived
```shell
! 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
```shell
#!/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状态
```shell
watch -n1 ipvsadmin -Ln
```
#### 上传 /etc/keepalived/check_slave.pl 内容
```shell
#!/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;
}
}
```
';