Mycat高可用方案-HAProxy+Keepalived

概述

在实际项目中，Mycat服务也需要考虑高可用性，如果Mycat所在服务器出现宕机，或Mycat服务故障，需要有备机提供服务，需要考虑Mycat集群。

高可用方案

使用HAProxy + Keepalived配合两台Mycat搭起Mycat集群，实现高可用性。HAProxy实现了Mycat多节点的集群高可用和负载均衡，而HAProxy自身的高可用则可以通过Keepalived 来实现。

安装配置 HAProxy

各版本下载地址：

https://src.fedoraproject.org/repo/pkgs/haproxy/

本文使用版本为：1.5.18

下载地址：

https://src.fedoraproject.org/repo/pkgs/haproxy/haproxy-1.5.18.tar.gz/21d35f114583ef731bc96af05b46c75a/haproxy-1.5.18.tar.gz

安装步骤：

• 将下载好的HAProxy安装包传到/usr/local/src目录下进行解压

  cd /usr/local/src
  wget https://src.fedoraproject.org/repo/pkgs/haproxy/haproxy-1.5.18.tar.gz/21d35f114583ef731bc96af05b46c75a/haproxy-1.5.18.tar.gz
  tar -zxvf haproxy-1.5.18.tar.gz
  cd /usr/local/src/haproxy-1.5.18

• 安装依赖插件

  # Ubuntu 依次执行
  sudo apt-get install -y gcc
  sudo apt-get install -y g++
  sudo apt-get install -y libssl-dev
  sudo apt-get install -y daemon
  sudo apt-get install -y make
  # CentOS
  yum install -y gcc openssl-devel popt-devel

• 查看内核版本，进行编译

  # 查看内核版本
  uname -r
  # 查看 haproxy README文件对应的系统内核版本
  cat /usr/local/haproxy/README

  make TARGET=linux2628 PREFIX=/usr/local/haproxy ARCH=x86_64

  • ARGET：内核版本，使用uname -r查看内核，结合查看haproxy README文件对应的系统内核版本
  • ARCH：系统位数
  • PREFIX：为haprpxy安装路径

• 编译完成，进行安装

  make install PREFIX=/usr/local/haproxy

• 安装完成

  • 创建配置目录

  mkdir -p /usr/data/haproxy/

  • 创建HAProxy配置文件

  vim /usr/local/haproxy/haproxy.conf

  配置内容如下：

  global
      log 127.0.0.1 local0
      #log 127.0.0.1 local1 notice
      #log loghost local0 info
      maxconn 4096
      chroot /usr/local/haproxy
      pidfile /usr/data/haproxy/haproxy.pid
      uid 99
      gid 99
      daemon
      #debug
      #quiet
  defaults
      log global
      mode tcp
      option abortonclose
      option redispatch
      retries 3
      maxconn 2000
      timeout connect 5000
      timeout client 50000
      timeout server 50000
  # HAProxy 监听配置
  listen proxy_status
      # 监听代理端口
      bind :48066
          mode tcp
          balance roundrobin
          # 监听Mycat服务地址
          server mycat_1 192.168.110.145:8066 check inter 10s
          server mycat_2 192.168.110.150:8066 check inter 10s
  # HAProxy 控制台配置
  frontend admin_stats
      bind :7777
          mode http
          stats enable
          option httplog
          maxconn 10
          stats refresh 30s
          stats uri /admin
          stats auth admin:123456
          stats hide-version
          stats admin if TRUE

• 启动验证

  • 启动HAProxy

    /usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf

  • 查看HAProxy进程

    ps -ef|grep haproxy

  • 打开浏览器访问

    http://192.168.110.151:7777/admin

    输入配置文件中的账号密码进行访问。admin/123456

    

  • 验证负载均衡，通过HAProxy访问Mycat

    mysql -umycat -p123456 -h 192.168.110.151 -P 48066

HAProxy(backup) 192.168.110.152如上配置

配置Keepalived

各版本下载地址：

https://www.keepalived.org/download.html

本文使用版本为：1.4.2

下载地址：

https://www.keepalived.org/software/keepalived-1.4.2.tar.gz

安装步骤：

• 将下载好的Keepalived安装包传到/usr/local/src目录下进行解压

  cd /usr/local/src
  wget https://www.keepalived.org/software/keepalived-1.4.2.tar.gz
  tar -zxvf keepalived-1.4.2.tar.gz
  cd /usr/local/src/keepalived-1.4.2

• 安装依赖插件

  # Ubuntu 依次执行
  sudo apt-get install -y gcc
  sudo apt-get install -y g++
  sudo apt-get install -y libssl-dev
  sudo apt-get install -y daemon
  sudo apt-get install -y make
  # CentOS
  yum install -y gcc openssl-devel popt-devel

• 进入解压后的目录，进行配置

  cd /usr/local/src/keepalived-1.4.2
  ./configure --prefix=/usr/local/keepalived

• 进行编译，完成后进行安装

  cd /usr/local/src/keepalived-1.4.2
  make && make install

• 运行前配置，将keepalived设置为系统服务

  • Ubuntu配置

    mkdir /etc/keepalived
    mkdir /etc/sysconfig
    cp /usr/local/keepalived/etc/keepalived/keepalived.conf  /etc/keepalived/
    cp /usr/local/src/keepalived-1.4.2/keepalived/etc/init.d/keepalived /etc/init.d/
    cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
    ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/
    ln -s /usr/local/keepalived/sbin/keepalived /sbin/

  • CentOS

    mkdir /etc/keepalived
    cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
    cp /usr/local/src/keepalived-1.4.2/keepalived/etc/init.d/keepalived /etc/init.d/
    cp /usr/local/src/keepalived-1.4.2/keepalived/etc/sysconfig/keepalived /etc/default/keepalived
    cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

• 修改配置文件

  vim /etc/keepalived/keepalived.conf

  配置文件内容如下：

  ! Configuration File for keepalived
  global_defs {
      notification_email {
          xlcocoon@foxmail.com
      }
      notification_email_from keepalived@showjoy.com
      smtp_server 127.0.0.1
      smtp_connect_timeout 30
      router_id LVS_DEVEL
      vrrp_skip_check_adv_addr
      vrrp_garp_interval 0
      vrrp_gna_interval 0
  }
  vrrp_instance VI_1 {
      #主机配MASTER，备机配BACKUP
      state MASTER
      #所在机器网卡
      interface ens33
      virtual_router_id 51
      #数值越大优先级越高
      priority 100
      advert_int 1
      authentication {
          auth_type PASS
          auth_pass 1111
      }
      virtual_ipaddress {
          #虚拟IP，在HAProxy之上设定一个虚拟IP，可以在该网段内任意指定未使用的IP
          192.168.110.200
      }
  }
  virtual_server 192.168.110.200 48066 {
      delay_loop 6
      lb_algo rr
      lb_kind NAT
      persistence_timeout 50
      protocol TCP
      # HAProxy 地址
      real_server 192.168.110.151 48066 {
          weight 1
          TCP_CHECK {
              connect_timeout 3
              retry 3
              delay_before_retry 3
          }
      }
      real_server 192.168.110.152 48066 {
          weight 1
          TCP_CHECK {
              connect_timeout 3
              nb_get_retry 3
              delay_before_retry 3
          }
      }
  }

• 配置重载

  systemctl daemon-reload

• 启动验证

  • 启动Keepalived

    service keepalived start

  • 启动后可以看到在ens33真实IP:192.168.110.151下面挂载了一个虚拟IP:192.168.110.200

    root@ubuntu:~# ip addr
    1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1
        link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
        inet 127.0.0.1/8 scope host lo
           valid_lft forever preferred_lft forever
        inet6 ::1/128 scope host 
           valid_lft forever preferred_lft forever
    2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
        link/ether 00:0c:29:9b:eb:14 brd ff:ff:ff:ff:ff:ff
        inet 192.168.110.151/24 brd 192.168.110.255 scope global ens33
           valid_lft forever preferred_lft forever
        inet 192.168.110.200/32 scope global ens33
           valid_lft forever preferred_lft forever
        inet6 fe80::20c:29ff:fe9b:eb14/64 scope link 
           valid_lft forever preferred_lft forever
    3: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default 
        link/ether 02:42:c3:d3:e0:c7 brd ff:ff:ff:ff:ff:ff
        inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
           valid_lft forever preferred_lft forever

  • 查看Keepalived状态

    service keepalived status

  • 登录验证

    mysql -umycat -p123456 -h 192.168.110.200 -P 48066

• 测试高可用

  • 关闭Mycat1

    

  • 通过虚拟IP查询数据

    # 通过Keepalived登录数据库
    mysql -umycat -p123456 -h 192.168.110.200 -P 48066
    # 切换数据库
    use TESTDB;
    # 执行SQL命令
    show tables;

    root@ubuntu-mysql-master:~# mysql -umycat -p123456 -h 192.168.110.200 -P 48066
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.6.29-mycat-1.6.7.6-release-20220524173810 MyCat Server (OpenCloudDB)
    
    Copyright (c) 2000, 2021, Oracle and/or its affiliates.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> use TESTDB;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +------------------+
    | Tables_in_orders |
    +------------------+
    | customer         |
    | dict_order_type  |
    | login_info       |
    | orders           |
    | orders_detail    |
    | orders_ware_info |
    | payment_info     |
    +------------------+
    7 rows in set (0.00 sec)