keepalived+mycat+mysql实现高可用主从架构（集群版）

╰+攻爆jí腚メ 2022-03-16 13:46 248阅读 0赞

简单说明拓扑关系:

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODU5ODQ0_size_16_color_FFFFFF_t_70]

## 一.安装mysql ##

1.下载mysql
    	 wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
    	 rpm -ivh mysql-community-release-el7-5.noarch.rpm
    	 yum update
    	 yum install mysql-server
     2.权限设置
    	 chown mysql:mysql -R /var/lib/mysql  
     3.  初始化mysql
    	 mysqld --initialize
     4.  启动mysql
    	 systemctl start mysqld
     5.  查看mysql运行状态
    	 systemctl status mysqld
     6.开启root用户允许远程连接mysql数据库
         grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
         flush privileges;

## 二.安装mycat ##

1.下载地址：
         wget https://raw.githubusercontent.com/MyCATApache/Mycat-download/master/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODU5ODQ0_size_16_color_FFFFFF_t_70 1]  
vi server.xml 修改mycat用户权限配置文件，新增两个用户(默认为root)，限制用户的读写权限

<system>
    <property name="idleTimeout">2880000</property> 
    <property name="maxPacketSize">134217728</property> 
    <property name="charset">utf8</property> 
    <property name="txIsolation">2</property> 
    <property name="sqlExecuteTimeout">600</property> 
    </system>
    
    <user name="root" defaultAccount="true">
                    <property name="password">root</property>
                    <property name="schemas">china,workd</property>
    </user>
    <user name="guandezhi">
                 <property name="password">root</property>
                 <property name="schemas">china</property>
                 <property name="readOnly">true</property>
    </user>
    <user name="zhangyuejie">
             <property name="password">root</property>
             <property name="schemas">world</property>
             <property name="readOnly">true</property>
    </user>

vi schema.xml 修改逻辑库配置文件  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODU5ODQ0_size_16_color_FFFFFF_t_70 2]  
启动mycat  
sh mycat start \#切换到mycat bin目录  
![在这里插入图片描述][20190308011435594.png]

## 三.安装keepalived ##

1.下载keepalived
       yum install -y keepalived ipvsadm nc rsync telnet tcpdump wget
    2.修改keepalived配置文件，默认位置/etc/keepalived/
       vi /etc/keepalived/keepalived.confvi /etc/keepalived/keepalived.conf

! Configuration File for keepalived
    #全局配置
    global_defs {
       notification_email {
            root@localhost    #定义收件人邮件地址
       }
       notification_email_from root@localhost    #定义发件人
       smtp_server 127.0.0.1    #如果要使用第三方smtp服务器，在现实中几乎没有意义（需要验证的原因）,设为本地就可以了
       smtp_connect_timeout 30    #smtp超时时间
       router_id LVS_HA_MYCAT1    #此服务器keepalived的ID,随便改,注意不同服务器不一样就行
    }
    #vrrp配置(HA配置)
    vrrp_instance VI_1 {    #定义虚拟路由，VI_1 为虚拟路由的标示符，自己定义名称
        state MASTER    #指定当前节点为主节点 备用节点上设置为BACKUP即可
        interface ens160    #绑定虚拟IP的网络接口,注意内外网
        virtual_router_id 19    #VRRP组名，两个节点的设置必须一样，以指明各个节点属于同一VRRP组,0-255随便你用,这个ID也是虚拟MAC最后一段的来源
        priority 100    #初始优先级,取值1-254之间,主节点一定要最大,其他从节点则看情况减少
        advert_int 1    #组播信息发送间隔，两个节点设置必须一样
        authentication {    #设置验证信息，两个节点必须一致
            auth_type PASS
            auth_pass 199200
        }
        virtual_ipaddress {
            172.25.62.112    #指定VIP,两个节点设置必须一样,虚拟ip最好和真实ip在同一网段。
        }
    }
    #负载均衡配置(LVS配置)
    virtual_server 172.25.62.112 8066  {    #指定VIP和端口,vip就是上面设置那个
        delay_loop 6    #延迟多少个周期再启动服务，做服务检测
        lb_algo rr    #负载均衡调度算法
        lb_kind DR    #负载均衡类型选择,可选DR|NAT|TUN,DR性能比较高
        nat_mask 255.255.255.0    #vip的掩码
        persistence_timeout 0    #会话保持时间,一定时间之内用户无响应则下一次用户请求时需重新路由,一般设为0,不需要.
        protocol TCP    #使用的协议,一般就TCP
    
        real_server 172.25.62.101 8066  {    #定义后端realserver的真实服务器属性,ip和端口
            weight 1    #负载均衡权重,数值越大,就负担更多连接
            MISC_CHECK {    #定义心跳检测的方法,因为不是web,而且用tcp_check健康检测后面的mycat会报错,所以需要misc_check的方式做心跳检测
            misc_path"/etc/keepalived/shell/check_mycat_status.sh 172.25.62.101 8066"    #自定义心跳检测shell脚本的路径、检测的服务器ip、检测的端口。(引号必须要)
            misc_timeout 3    #脚本执行超时时间
            }
        }
        real_server 172.25.62.102 8066 {    #同上
            weight 2
            MISC_CHECK {
            misc_path"/etc/keepalived/shell/check_mycat_status.sh 172.25.62.102 8066"
            misc_timeout 3
            }
        }
     }

这里的虚拟ip为172.25.62.112，里面涉及的心跳脚本check\_mycat\_status.sh，放在目录 /etc/keepalived/shell ，并授权  
chmod 755 check\_mycat\_status.sh

#!/bin/bash
    result=`nc -v -z $1 $2`
    flag="succeeded"
    if [[ $result =~ $flag ]]
    then
             exit 0
    else;
             exit 1
    fi

在真实服务器上执行下面脚本，绑定vip创建ipvs规则

#!/bin/bash
    SNS_VIP=172.25.62.112
    #/etc/rc.d/init.d/functions
    case "$1" in
    start)
            ifconfig ens160:0:0 $SNS_VIP broadcast $SNS_VIP netmask 255.255.255.255 up
            sleep 5
            /sbin/route add ${SNS_VIP}/32 dev ens160:0:0
            echo "1">/proc/sys/net/ipv4/conf/ens160:0/arp_ignore
            echo "2">/proc/sys/net/ipv4/conf/ens160:0/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
            echo "RealServer Start OK"
    ;;
    stop)
            ifconfig ens160:0:0 down
            /sbin/route del -net $SNS_VIP netmask 255.255.255.255 dev ens160:0:0
            echo "0" >/proc/sys/net/ipv4/conf/ens160:0/arp_ignore
            echo "0">/proc/sys/net/ipv4/conf/ens160:0/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"
    ;;
    restart)
    /sbin/route del -net $SNS_VIP netmask 255.255.255.255 dev ens160:0:0
    /sbin/route add ${SNS_VIP}/32 dev ens160:0:0
    ;;
    *)
            echo "Usage: $0 {start|stop|restart}"
            exit 1
    esac
    exit 0

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODU5ODQ0_size_16_color_FFFFFF_t_70 3]  
启动keepalived

systemctl start keepalived  #启动keepalived
    systemctl status keepalived  #查看keepalived运行状态
    systemctl stop keepalived  #停止keepalived
    systemctl reload keepalived #重启keepalived

查看lvs状态，ipvsadm -L

IP Virtual Server version 1.2.1 (size=4096)
    Prot LocalAddress:Port Scheduler Flags
      -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
    TCP  172.25.62.112:8066 rr
      -> 172.25.62.101:8066           Route   2      341        340       
      -> 172.25.62.102:8066           Route   1      341        340

这样，我们就可以通过 172.25.62.112:8066 访问mycat集群，进而访问到mysql服务器。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODU5ODQ0_size_16_color_FFFFFF_t_70]: /images/20220316/8ef7b6687d684abe934ed8c2cd3cb7e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODU5ODQ0_size_16_color_FFFFFF_t_70 1]: /images/20220316/7faa7dec91c44b4097a7076b8b6c1c9d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODU5ODQ0_size_16_color_FFFFFF_t_70 2]: /images/20220316/aade63283275468c9562aba1447460cf.png
[20190308011435594.png]: /images/20220316/747d409fbe4e4d91980d2703596f8877.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODU5ODQ0_size_16_color_FFFFFF_t_70 3]: /images/20220316/759c34047e4f448496ee1840ba368c6b.png