haproxy负载均衡MySQL读

一、环境

二、准备

安装好两台MySQL, 对外监听在3306端口，并在iptables上对haproxy放行3306端口。并在MySQL上创建账号:

MySQL> grant all on *.* to 'haproxy'@'192.168.100.100' identified by '123456';
MySQL> flush privileges;

三、配置haproxy

• 安装haproxy

  $ yum install -y haproxy
  

• 修改haproxy配置文件/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                  dontlognull
        timeout queue           1m
        timeout connect         10s
        timeout client          1m
        timeout server          1m
        timeout check           10s
        maxconn                 3000
    
    listen MySQL
        bind 0.0.0.0:3306
        mode tcp
        option tcplog
        timeout connect  300ms
        server slave01 192.168.100.101:3306 check port 3306 inter 200 rise 3 fall 2
        server slave02 192.168.100.102:3306 check port 3306 inter 200 rise 3 fall 2
  
  

• 日志配置

  • 更改rsyslog配置文件/etc/rsyslog.conf

    $ModLoad imudp
    $UDPServerAddress 127.0.0.1
    $UDPServerRun 514
    ...
    local2.*                                                /data/haproxy/haproxy.log
    ...
    

  • 更改/etc/sysconfig/rsyslog

    SYSLOGD_OPTIONS="-r"
    

  • 重启rsyslog

    $ systemctl restart rsyslog
    

  • 启动Haproxy

    $ systemctl start haproxy
    

  • 结果