MySQL MGR + ShardingProxy + Keepalived + HAProxy + mysqld_exporter 高可用企业级生产部署

sudo mkdir -p /etc/haproxy
sudo mkdir -p /var/lib/haproxy # Using for haproxy unix socks stats
sudo tee /etc/haproxy/haproxy.cfg <<-'EOF'
#---------------------------------------------------------------------
# Example configuration for a possible web application.  See the
# full configuration options online.
#
#   http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#---------------------------------------------------------------------

global
    # to have these messages end up in /var/log/haproxy.log you will
    # need to:
    #
    # 1) configure syslog to accept network log events.  This is done
    #    by adding the '-r' option to the SYSLOGD_OPTIONS in
    #    /etc/sysconfig/syslog
    #
    # 2) configure local2 events to go to the /var/log/haproxy.log
    #   file. A line like the following can be added to
    #   /etc/sysconfig/syslog
    #
    #    local2.*                       /var/log/haproxy.log
    #
    log         127.0.0.1 local2

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     50000
    user        root
    group       root
    daemon

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

defaults
    mode                    http # tcp|http|health
    log                     global
    option                  tcplog # tcplog|httplog
    option                  dontlognull
    option http-server-close
    #option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 50000

# Must setup mode=http to be effective, defaults{mode=http} is also possible.
# see: https://www.haproxy.com/blog/haproxy-exposes-a-prometheus-metrics-endpoint/
frontend stats
   bind *:8404
   #option http-use-htx
   #http-request use-service prometheus-exporter if { path /metrics }
   stats enable
   stats uri /stats
   stats refresh 10s

frontend mysql_cluster1
    bind *:3306
    mode        tcp
    default_backend     shardingproxy_cluster1

backend shardingproxy_cluster1
    balance     roundrobin
    mode        tcp
    server      shardingproxy1 10.0.0.162:3307 check
    server      shardingproxy2 10.0.0.163:3307 check
    server      shardingproxy3 10.0.0.163:3307 check
EOF

sudo tee /etc/systemd/system/haproxy.service <<-'EOF'
[Unit]
Description=HAProxy Load Balancer
After=network-online.target
Wants=network-online.target

[Service]
EnvironmentFile=-/etc/sysconfig/haproxy
Environment="CONFIG=/etc/haproxy/haproxy.cfg" "PIDFILE=/run/haproxy.pid" "EXTRAOPTS=-S /run/haproxy-master.sock"
ExecStartPre=/bin/haproxy -f $CONFIG -c -q $EXTRAOPTS
ExecStart=/bin/haproxy -DWs -f $CONFIG -p $PIDFILE $EXTRAOPTS
ExecReload=/bin/haproxy -f $CONFIG -c -q $EXTRAOPTS
ExecReload=/bin/kill -USR2 $MAINPID
StandardOutput=journal
StandardError=journal
KillMode=mixed
Restart=always
User=root
Group=root
SuccessExitStatus=143
Type=forking

# The following lines leverage SystemD's sandboxing options to provide
# defense in depth protection at the expense of restricting some flexibility
# in your setup (e.g. placement of your configuration files) or possibly
# reduced performance. See systemd.service(5) and systemd.exec(5) for further
# information.

# NoNewPrivileges=true
# ProtectHome=true
# If you want to use 'ProtectSystem=strict' you should whitelist the PIDFILE,
# any state files and any other files written using 'ReadWritePaths' or
# 'RuntimeDirectory'.
# ProtectSystem=true
# ProtectKernelTunables=true
# ProtectKernelModules=true
# ProtectControlGroups=true
# If your SystemD version supports them, you can add: @reboot, @swap, @sync
# SystemCallFilter=~@cpu-emulation @keyring @module @obsolete @raw-io

[Install]
WantedBy=multi-user.target
EOF

# 前置依赖
sudo yum -y install openssl-devel autoconf automake
# 克隆源码
git clone https://github.com/acassen/keepalived.git
cd keepalived
# 从 configure.ac 生成 configure
./autogen.sh
# 生成 Makefile
chmod +x ./configure; ./configure --prefix=$(pwd)
# 编译安装
make && make install
# 查看编译产物
$ ./keepalived/keepalived --version

Keepalived v2.2.2 (03/05,2021)

Copyright(C) 2001-2021 Alexandre Cassen, 

Built with kernel headers for Linux 3.10.0
Running on Linux 3.10.0-957.21.3.el7.x86_64 #1 SMP Tue Jun 18 16:35:19 UTC 2019
Distro: CentOS Linux 7 (Core)

configure options: --prefix=/root/keepalived

Config options:  LVS VRRP VRRP_AUTH VRRP_VMAC OLD_CHKSUM_COMPAT INIT=systemd
System options:  VSYSLOG RTA_ENCAP RTA_EXPIRES RTA_PREF FRA_TUN_ID RTAX_CC_ALGO RTAX_QUICKACK IFA_FLAGS NET_LINUX_IF_H_COLLISION NET_LINUX_IF_ETHER_H_COLLISION LIBIPTC_LINUX_NET_IF_H_COLLISION IFLA_LINK_NETNSID GLOB_BRACE GLOB_ALTDIRFUNC INET6_ADDR_GEN_MODE SO_MARK

sudo mkdir -p /etc/keepalived
sudo cat<<-'EOF' >/etc/keepalived/keepalived.conf
global_defs {
  notification_email {
    983708408@qq.com # 通知接受邮箱 (**可按实际配置**)
  }
  notification_email_from xxx@163.com # 通知发送邮箱 (**可按实际配置**)
  smtp_server smtp.163.com # 邮箱主机 (**可按实际配置**)
  smtp_connect_timeout 10
  router_id  iloqg8n3yb9mje1s
  script_user root
  enable_script_security
  vrrp_garp_interval 0
  vrrp_garp_master_delay 40
  vrrp_garp_master_refresh 60
  vrrp_skip_check_adv_addr
  vrrp_gna_interval 0
  #vrrp_strict
}

vrrp_script haproxy_checkpoint {
  script "/etc/keepalived/haproxy_checker.sh" # 用于检测 real-ip 上服务的自定义脚本 (**可按实际配置**)
  interval 2
  weight 2
}

vrrp_instance keepalived_1 {
  interface eth0 # 使用网卡 (**可按实际配置**)
  virtual_router_id 51 # 虚拟路由的标志,  一组lvs的虚拟路由标识必须相同, 这样才能切换
  priority 150 # 服务启动优先级, 值越大优先级越高, BACKUP 不能大于MASTER
  advert_int 3 # 服务器之间的存活检查时间
  # 通常如果master服务死掉后backup会变成master, 但是当master服务又好了的时候 master 此时会抢占VIP,
  # 这样就会发生两次切换对业务繁忙的网站来说是不好的。所以我们要在配置文件加入 nopreempt 非抢占,
  # 但是这个参数只能用于state 为backup, 故我们在用HA的时候最好 master 和 backup 的 state 都设置成 BACKUP
  # 让其通过 priority 来竞争。
  state BACKUP # MASTER|BACKUP, 可将所有节点都设置为BACKUP时, 第一个启动时会抢占为 MASTER
  nopreempt # 指定非抢占
  authentication {
    auth_type PASS # 认证类型
    auth_pass ufsoft # 认证密码, 一组lvs 服务器的认证密码必须一致
  }

  track_script {
    haproxy_checkpoint # 执行监控 haproxy 进程的脚本
  }

  # 极力推荐显示指定广播 peer, 生产踩过的坑: 当时未指定此配置,
  # 可能会导致VRRPv2包被发到 vrrp.mcast.net 去了, 最终导致闹裂 (可通过 tcpdump -i any vrrp 捕包排查).
  unicast_src_ip 10.111.0.112
  unicast_peer {
    10.111.0.111
    10.111.0.112
  }

  # 虚拟IP地址
  virtual_ipaddress {
    10.111.0.200 # 虚拟vip (**可按实际配置**)
  }
}
EOF

sudo tee /etc/keepalived/haproxy_checker.sh <<-'EOF'
#!/bin/bash

BASE_DIR=$(cd dirname $0; pwd)
APP_NAME=$(echo basename $0)
LOG_DIR="/mnt/disk1/log/keepalived"; mkdir -p $LOG_DIR
LOG_FILE="${LOG_DIR}/$(echo $APP_NAME|sed 's/.sh//g').stdout"

if [ -z "$(ps -ef | grep '/bin/haproxy'| grep -v grep)" ]; then
  echo "$(date '+%Y-%m-%d %H:%M:%S') - HAProxy is dead." | tee -a "$LOG_FILE"
  systemctl stop keepalived
else
  echo "$(date '+%Y-%m-%d %H:%M:%S') - HAPorxy is running." | tee -a "$LOG_FILE"
fi
exit 0
EOF

sudo chmod +x /etc/keepalived/haproxy_checker.sh

sudo tee /etc/systemd/system/keepalived.service <<-'EOF'
[Unit]
Description=LVS and VRRP High Availability Monitor
After=network-online.target syslog.target
Wants=network-online.target

[Service]
Type=forking
PIDFile=/run/keepalived.pid
KillMode=process
EnvironmentFile=-/etc/keepalived/keepalived
ExecStart=/sbin/keepalived $KEEPALIVED_OPTIONS -f /etc/keepalived/keepalived.conf
ExecReload=/bin/kill -HUP $MAINPID
#StandardOutput=/mnt/disk1/log/keepalived.out
StandardError=journal
TimeoutStartSec=5
TimeoutSec=600
Restart=alway

[Install]
WantedBy=multi-user.target
EOF