您的位置：首页 > 博客中心 > 数据库 >

lvs+keepalived+mysql主主复制集群搭建

时间：2022-03-14 13:42

MySQL服务器：192.168.2.134，192.168.2.135

MySQL主从同步的数据库为：chaosdb

实现目的：

增加两台服务器（主备），通过LVS+Keepalived实现MySQL从库读操作负载均衡

架构规划：

操作系统：CentOS 6.X 64位

LVS主服务器：192.168.2.133

LVS备服务器：192.168.2.132

LVS虚拟服务器（VIP）：192.168.2.137

部署完成之后，通过VIP：192.168.21.254，根据LVS调度算法来访问后端真实的MySQL从服务器，实现负载均衡。

第一部分：分别在两台MySQL服务器上操作，即在192.168.2.134，192.168.2.135，执行的操作都是分别执行

一、关闭SELINUX

vi /etc/selinux/config

#SELINUX=enforcing #注释掉

#SELINUXTYPE=targeted #注释掉

SELINUX=disabled #增加

:wq! #保存退出

setenforce 0 #使配置立即生效

二、配置防火墙，开启3306端口

vi /etc/sysconfig/iptables #编辑

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT #允许3306端口通过防火墙

/etc/init.d/iptables restart #重启防火墙使配置生效

或者直接关闭防火墙

#/etc/init.d/iptables status ## 查看防火墙状态

#/etc/init.d/iptable stop ## 本次关闭防火墙

#/etc/init.d/iptable restart ## 重启防火墙

永久性关闭防火墙：

#chkconfig --level 35 iptables off ## 注意中间的是两个英式小短线；重启

三、绑定LVS虚拟服务器（VIP）：192.168.2.137 到lo:0

vim /etc/init.d/ivsrs #编辑，添加以下代码

#!/bin/bash
# description: Config realserver lo and apply noarp
#Written by :chaosju
        
SNS_VIP=192.168.2.137
         
. /etc/rc.d/init.d/functions
         
case "$1" in
   start)
    /sbin/ifconfig lo:0 $SNS_VIP broadcast $SNS_VIP  netmask 255.255.255.255 up               
     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
     echo "RealServer Start OK"
     ;;
   stop)
    /sbin/ifconfig lo:0 down
    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

启动： /etc/init.d/ivsrs start

关闭： /etc/init.d/ivsrs stop

四、设置mysql主主复制和远程访问

第二部分：分别在两台LVS主备服务器上操作

一、关闭SElinux、配置防火墙

1、vi /etc/selinux/config

#SELINUX=enforcing #注释掉

#SELINUXTYPE=targeted #注释掉

SELINUX=disabled #增加

:wq! #保存退出

setenforce 0 #使配置立即生效

2、vi /etc/sysconfig/iptables #编辑

-A RH-Firewall-1-INPUT -d 224.0.0.18 -j ACCEPT #允许VRRP（虚拟路由器冗余协议）组播地址通信

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT #允许3306端口通过防火墙

:wq! #保存退出

/etc/init.d/iptables restart #重启防火墙使配置生效

二、安装lvs软件

yum install ipvsadm #Linux 2.6内核已经集成了lvs软件，此处ipvsadm为lvs管理工具

modprobe ip_vs #加载ip_vs

三、安装keepalived

yum install gcc gcc-c++ make openssl-devel kernel-devel ncurses-devel #安装编译工具包

下载keeplived：http://www.keepalived.org/software/keepalived-1.2.12.tar.gz

上传keepalived-1.2.12.tar.gz到/usr/local/src目录

cd /usr/local/src

tar zxvf keepalived-1.2.12.tar.gz

cd keepalived-1.2.12

./configure #配置，必须看到以下提示，说明配置正确，才能继续安装

Use IPVS Framework : Yes

IPVS sync daemon support : Yes

Use VRRP Framework : Yes

make #编辑

make install #安装

cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/

mkdir /etc/keepalived

cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/

cp /usr/local/sbin/keepalived /usr/sbin/

cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/

chmod +x /etc/rc.d/init.d/keepalived #添加执行权限

chkconfig keepalived on #设置开机启动

service keepalived start #启动

service keepalived stop #关闭

service keepalived restart #重启

四、安装perl、perl-DBI、DBD-mysql模块以及MySQL客户端 #perl脚本连接MySQL数据库需要

yum install perl perl-DBI perl-DBD-MySQL mysql #执行此命令安装

vi /tmp/test_perl.pl

#!/usr/bin/perl

print "Hello, world!\n";

:wq! #保存退出

perl /tmp/test_perl.pl #运行测试脚本，如果出现：Hello,world! 说明perl安装成功

perldoc DBI #查看DBI模块是否安装

perldoc DBD::mysql #查看DBD::mysql模块是否安装

五、配置keepalived

编辑keepalive的master（主）192.168.2.133配置文件 vim /etc/keepalived/keepalived.conf

global_defs {
   notification_email {
    chaosju@163.com
   }
   notification_email_from keepalived@chaos
   smtp_server 127.0.01
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state MASTER            
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.2.137
    }
}

virtual_server 192.168.2.137 3306 {
    delay_loop 6       # 每隔6秒查看realserver状态
    lb_algo lc        # 调度算法为加权最小连接数
    lb_kind DR         # lvs工作模式为DR(直接路由)模式
    nat_mask 255.255.255.0
    persistence_timeout 10  # 同一IP 的连接50秒内被分配到同一台realserver(测试时建议改为0)
    protocol TCP            # 用TCP监测realserver的状态

    real_server 192.168.2.134 3306 {       # 定义realserver
        weight 3                       # 定义权重
        MISC_CHECK {
           misc_path "/etc/keepalived/check_slave.pl 192.168.2.134"
           misc_dynamic
        }
    }
    real_server 192.168.2.135 3306 {
        weight 3
        MISC_CHECK {

           misc_path "/etc/keepalived/check_slave.pl 192.168.2.135"

            misc_dynamic

        }
    }
}

编辑keepalive的slave（从）192.168.2.132配置文件 vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   notification_email {
    chaosju@163.com
   }
   notification_email_from keepalived@chaos
   smtp_server 127.0.01
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 80
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.2.137
    }
}

virtual_server 192.168.2.137 3306 {
    delay_loop 6       # 每隔6秒查看realserver状态
    lb_algo lc        # 调度算法为加权最小连接数
    lb_kind DR         # lvs工作模式为DR(直接路由)模式
    nat_mask 255.255.255.0
    persistence_timeout 10  # 同一IP 的连接50秒内被分配到同一台realserver(测试时建议改为0)
    protocol TCP            # 用TCP监测realserver的状态

    real_server 192.168.2.134 3306 {       # 定义realserver
        weight 3                       # 定义权重
        MISC_CHECK {
           misc_path "/etc/keepalived/check_slave.pl 192.168.2.134"
           misc_dynamic
        }
    }
    real_server 192.168.2.135 3306 {
        weight 3
        MISC_CHECK {

           misc_path "/etc/keepalived/check_slave.pl 192.168.2.135"

            misc_dynamic

        }
    }
}

六、设置MySQL主从（192.168.2.133、192.168.2.132）监控脚本check_slave.pl

vi /etc/keepalived/check_slave.pl #编辑，添加以下代码

#!/usr/bin/perl -w
use DBI;
use DBD::mysql;

$SBM = 120;
$db = "chaosdb";
$host = $ARGV[0];
$port = 3306;
$user = "root";
$pw = "admin";

$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 > $SBM ) {

exit 1;

} else {

exit 0;

}

}