mysql负载均衡搭建(haproxy)

从库192.168.110（安装haproxy）
从库192.168.111（安装haproxy）

关闭防火墙：systemctl stop firewalld

Haproxy安装

yum install haproxy –y

配置文件：/etc/haproxy/haproxy.cfg

global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    stats socket /var/lib/haproxy/stats

defaults
    mode                   tcp
    log                     global
    option                  tcplog
    option                  dontlognull
    option http-server-close
    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                 3000

frontend    mysql
    bind        0.0.0.0:3307
    mode       tcp
    log             global
    default_backend         mysql_server

backend     mysql_server
    balance     roundrobin
    server  mysql1 192.168.8.110:3306 check inter 5s rise 2 fall 3
    server  mysql2 192.168.8.111:3306 check inter 5s rise 2 fall 3

listen stats
    mode http
    bind 0.0.0.0:1080
    stats enable
    stats hide-version
    stats uri /haproxyadmin?stats
    stats realm Haproxy\ Statistics
    stats auth admin:admin
    stats admin if TRUE

启动命令：

/usr/sbin/haproxy –f /etc/haproxy/haproxy.cfg

查看haproxy是否正常启动

ps –ef | grep haproxy

地址栏访问

192.168.8.111:1080/haproxyadmin?sta...

本地mysql测试(轮询)

>mysql -h192.168.8.111 -uqiao -proot -P3307  -e "show variables like 'server_id'"

>mysql -h192.168.8.111 -uqiao -proot -P3307  -e "show variables like 'server_id'"

D:\phpstudy_pro\Extensions\MySQL5.7.26\bin>mysql -h192.168.8.111 -uqiao -proot -P3307  -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+

D:\phpstudy_pro\Extensions\MySQL5.7.26\bin>mysql -h192.168.8.111 -uqiao -proot -P3307  -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3     |
+---------------+-------+

D:\phpstudy_pro\Extensions\MySQL5.7.26\bin>mysql -h192.168.8.110 -uqiao -proot -P3307  -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3     |
+---------------+-------+

D:\phpstudy_pro\Extensions\MySQL5.7.26\bin>mysql -h192.168.8.110 -uqiao -proot -P3307  -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+

D:\phpstudy_pro\Extensions\MySQL5.7.26\bin>

本作品采用《CC 协议》，转载必须注明作者和本文链接