HAProxyでmysql負荷分散

master, slave構成のMySQLをHAProxyで負荷分散します。 WEBサーバにHAProxyをインストールし、別に運用しているmasterDBサーバ１（node1）、slaveDBサーバ２（node2）を負荷分散します。

インストール

$ yum install haproxy

設定ファイルを編集（/etc/haproxy/haproxy.cfg）

#---------------------------------------------------------------------
# Example configuration for a possible web application.  See the
# full configuration options online.
#
#   http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#
#---------------------------------------------------------------------

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
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     4000
    user        haproxy
    group       haproxy
    daemon

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

#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode                    tcp
    log                     global
    option                  tcplog
    retries                 3
    option redispatch
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout check           10s
    maxconn                 3000

listen mysql
    bind 127.0.0.1:3306
    mode tcp
    option mysql-check user haproxy
    balance roundrobin
    server  node1 172.30.2.191:3306 weight 2 check <- master DBサーバ
    server  node2 172.30.2.83:3306 weight 1 check <- slave DBサーバ

起動

$ service haproxy start