使用HAProxy搭建高可用的mysql

本文以版本1.8.14为例，介绍HAProxy的安装配置
首先下载
wget https://www.haproxy.org/download/1.8/src/haproxy-1.8.14.tar.gz
然后解压
tar -zxvf haproxy-1.8.14.tar.gz
cd haproxy-1.8.14
执行命令uname -r，查看系统信息

make TARGET=linux310 ARCH=x86_64
make install SBINDIR=/usr/sbin/ MANDIR=/usr/share/man/ DOCDIR=/usr/share/doc/
创建目录和用户
mkdir /etc/haproxy
mkdir /var/lib/haproxy
useradd -r haproxy

编辑配置文件
vi /etc/haproxy/haproxy.cfg

global
    log         127.0.0.1 local2
    maxconn     4000
    daemon

defaults
        log     global
        log 127.0.0.1 local3
        mode    http
        option  tcplog
        option  dontlognull
        retries 10
        option redispatch
        maxconn         2000
        timeout connect         10s
        timeout client          1m
        timeout server          1m
        timeout http-keep-alive 10s
        timeout check           10s

listen  mycat
        bind 0.0.0.0:3300
        mode tcp
        balance roundrobin
        server mysql1 192.168.6.13:8066 check port 8066 maxconn 300
        server mysql2 192.168.6.14:8066 check port 8066 maxconn 300

listen stats
        bind 0.0.0.0:1080
        mode http
        option httplog
        maxconn 10
        stats refresh 30s
        stats uri /stats
        stats realm XingCloud\ Haproxy
        stats auth admin:admin          #统计监控页面用这个账号登录，可以自己设置
        stats auth Frank:Frank
        stats hide-version
        stats admin if TRUE

配置日志
vi /etc/rsyslog.cfg

#Provides TCP syslog reception #去掉下面两行注释，开启TCP监听
$ModLoad imudp
$UDPServerRun 514
local2.*   /var/log/haproxy.log

vi /etc/sysconfig/rsyslog

将SYSLOGD_OPTIONS=""修改为 SYSLOGD_OPTIONS="-r -m 2 -c 2"

创建日志文件
touch /var/log/haproxy.log
启动日志服务
systemctl restart rsyslog.service
启动haproxy
cd /usr/local/haproxy-1.8.14
./haproxy -f /etc/haproxy/haproxy.cfg

在两台服务器的mysql添加远程访问权限

GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES; 

访问监控页面
http://本机ip:1080/stats