mysql负载均衡完美解决方案V1.0(2)

7.haproxy安装配置
下载编译安装：
wget http://haproxy.1wt.eu/download/1.4/src/haproxy-1.4.11.tar.gz
tar xvzf haproxy-1.4.11.tar.gz
cd haproxy-1.4.11
make TARGET=linux26 ARCH=x86_64
make install

 

配置
vi /etc/haproxy.cfg

global
        maxconn 40000
        debug
        #quiet
        user haproxy
        group haproxy
        nbproc 1
        log 127.0.0.1 local3
        spread-checks 2
defaults
         timeout server  3s
         timeout connect 3s
         timeout client  60s
         timeout http-request 3s
         timeout queue   3s

frontend db_write
        bind 192.168.5.10:3306
        default_backend cluster_db_write

frontend db_read
        bind 192.168.5.20:3306
        default_backend cluster_db_read

frontend web_haproxy_status
        bind :80
        default_backend web_status

frontend monitor_mdb1
        bind 127.0.0.1:9301
        mode http
        acl no_repl_mdb1 nbsrv(mdb1_replication) eq 0
        acl no_repl_mdb2 nbsrv(mdb2_replication) eq 0
        acl no_mdb1 nbsrv(mdb1_status) eq 0
        acl no_mdb2 nbsrv(mdb2_status) eq 0
        monitor-uri /dbs
        monitor fail unless no_repl_mdb1 no_repl_mdb2 no_mdb2
        monitor fail if no_mdb1 no_mdb2

frontend monitor_mdb2
        bind 127.0.0.1:9302
        mode http
        acl no_repl_mdb1 nbsrv(mdb1_replication) eq 0
        acl no_repl_mdb2 nbsrv(mdb2_replication) eq 0
        acl no_mdb1 nbsrv(mdb1_status) eq 0
        acl no_mdb2 nbsrv(mdb2_status) eq 0
        monitor-uri /dbs
        monitor fail unless no_repl_mdb1 no_repl_mdb2 no_mdb1
        monitor fail if no_mdb1 no_mdb2

frontend monitor_sdb1
        bind 127.0.0.1:9303
        mode http
        acl no_repl_sdb1 nbsrv(sdb1_replication) eq 0
        acl no_repl_mdb1 nbsrv(mdb1_replication) eq 0
        acl no_mdb2 nbsrv(mdb2_status) eq 1
        monitor-uri /dbs
        monitor fail if no_repl_sdb1
        monitor fail if no_repl_mdb1 no_mdb2

frontend monitor_sdb2
        bind 127.0.0.1:9304
        mode http
        acl no_repl_sdb2 nbsrv(sdb2_replication) eq 0
        acl no_repl_mdb1 nbsrv(mdb1_replication) eq 0
        acl no_mdb2 nbsrv(mdb2_status) eq 1
        monitor-uri /dbs
        monitor fail if no_repl_sdb2
        monitor fail if no_repl_mdb1 no_mdb2

frontend monitor_sdb3
        bind 127.0.0.1:9305
        mode http
        acl no_repl_sdb3 nbsrv(sdb3_replication) eq 0
        acl no_repl_mdb2 nbsrv(mdb2_replication) eq 0
        acl no_mdb1 nbsrv(mdb1_status) eq 1
        monitor-uri /dbs
        monitor fail if no_repl_sdb3
        monitor fail if no_repl_mdb2 no_mdb1

frontend monitor_sdb4
        bind 127.0.0.1:9306
        mode http
        acl no_repl_sdb4 nbsrv(sdb4_replication) eq 0
        acl no_repl_mdb2 nbsrv(mdb2_replication) eq 0
        acl no_mdb1 nbsrv(mdb1_status) eq 1
        monitor-uri /dbs
        monitor fail if no_repl_sdb4
        monitor fail if no_repl_mdb2 no_mdb1

frontend monitor_splitbrain
        bind 127.0.0.1:9300
        mode http
        acl no_repl01 nbsrv(mdb1_replication) eq 0
        acl no_repl02 nbsrv(mdb2_replication) eq 0
        acl mdb1 nbsrv(mdb1_status) eq 1
        acl mdb2 nbsrv(mdb2_status) eq 1
        monitor-uri /dbs
        monitor fail unless no_repl01 no_repl02 mdb1 mdb2

backend mdb1_replication
        mode tcp
        balance roundrobin
        option tcpka
        option httpchk
        server mdb1 192.168.5.11:3306 check port 9201 inter 1s rise 1 fall 1
 
backend mdb2_replication
        mode tcp
        balance roundrobin
        option tcpka
        option httpchk
        server mdb2 192.168.5.12:3306 check port 9201 inter 1s rise 1 fall 1

backend sdb1_replication
        mode tcp
        balance roundrobin
        option tcpka
        option httpchk
        server sdb1 192.168.5.21:3306 check port 9201 inter 1s rise 1 fall 1

backend sdb2_replication
        mode tcp
        balance roundrobin
        option tcpka
        option httpchk
        server sdb2 192.168.5.22:3306 check port 9201 inter 1s rise 1 fall 1

backend sdb3_replication
        mode tcp
        balance roundrobin
        option tcpka
        option httpchk
        server sdb3 192.168.5.23:3306 check port 9201 inter 1s rise 1 fall 1

backend sdb4_replication
        mode tcp
        balance roundrobin
        option tcpka
        option httpchk
        server sdb4 192.168.5.24:3306 check port 9201 inter 1s rise 1 fall 1

backend mdb1_status
        mode tcp
        balance roundrobin
        option tcpka
        option httpchk
        server mdb1 192.168.5.11:3306 check port 9200 inter 1s rise 2 fall 2
 
backend mdb2_status
        mode tcp
        balance roundrobin
        option tcpka
        option httpchk
        server mdb2 192.168.5.12:3306 check port 9200 inter 1s rise 2 fall 2

backend cluster_db_write
        mode    tcp
        option  tcpka
        balance roundrobin
        option  httpchk GET /dbs
        server  mdb1 192.168.5.11:3306 weight 1 check port 9201 inter 1s rise 5 fall 1
        server  mdb2 192.168.5.12:3306 weight 1 check port 9201 inter 1s rise 5 fall 1 backup
        server  mdb1_backup 192.168.5.11:3306 weight 1 check port 9301 inter 1s rise 2 fall 2 addr 127.0.0.1 backup
        server  mdb2_backup 192.168.5.12:3306 weight 1 check port 9302 inter 1s rise 2 fall 2 addr 127.0.0.1 backup

backend cluster_db_read
        mode    tcp
        option  tcpka
        balance roundrobin
        option  httpchk GET /dbs
        server  mdb1 192.168.5.11:3306 weight 1 track cluster_db_write/mdb1
        server  mdb2 192.168.5.12:3306 weight 1 track cluster_db_write/mdb2
        server  mdb1_backup 192.168.5.11:3306 weight 1 track cluster_db_write/mdb1_backup
        server  mdb2_backup 192.168.5.12:3306 weight 1 track cluster_db_write/mdb2_backup
        server  mdb1_splitbrain 192.168.5.11:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1
        server  mdb2_splitbrain 192.168.5.12:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1

        server  sdb1_slave 192.168.5.21:3306 weight 1 check port 9303 inter 1s rise 5 fall 1 addr 127.0.0.1
        server  sdb2_slave 192.168.5.22:3306 weight 1 check port 9304 inter 1s rise 5 fall 1 addr 127.0.0.1
        server  sdb3_slave 192.168.5.23:3306 weight 1 check port 9305 inter 1s rise 5 fall 1 addr 127.0.0.1
        server  sdb4_slave 192.168.5.24:3306 weight 1 check port 9306 inter 1s rise 5 fall 1 addr 127.0.0.1

 

backend  web_status
         mode http
         stats enable
       # stats scope
       # stats hide-version
         stats refresh 5s
         stats uri /status
         stats realm Haproxy\ statistics
         stats auth ylmf:gaojinbo

 

8.测试

1）正常情况，backup和splitbrain状态down

 

2）停止mdb2复制，mdb2和sdb3,sdb4状态down，数据库仍可读写

 

3)同时停止mdb1,mdb2复制，mdb1和sdb1,sdb2,sdb3,sdb4状态down，数据库只能读

 

4）关闭mdb1数据库，mdb1,mdb2和sdb1,sdb2状态down，数据库仍可读写

 

5）关闭mdb2数据库，mdb1,mdb2和sdb3,sdb4状态down，数据库仍可读写

 

 

 

完成！