標籤：haproxy 負載平衡 mysql 雙主
HAproxy負載平衡MYSQL雙主
650) this.width=650;" height="356" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/QVe5NZzvbuqFFQO9g6PETI58q4ijTp*TC3tbzfsckE0!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgJlAQAAAAADAGk!&su=0194203249&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgJlAQAAA" />
OS |
IP |
子網路遮罩 |
路由網關 |
Centos6.6 （DB:MYSQL） |
Eth0:192.168.26.211 |
255.255.252.0 |
192.168.25.3 |
Centos6.6 （DB:MYSQL） |
Eth0:192.168.26.212 |
255.255.252.0 |
192.168.25.3 |
Centos6.6 （HAproxy） |
Eth0:192.168.26.210 |
255.255.252.0 |
192.168.25.3 |
安裝mysql:192.168.26.211
Yum install –y mysql-server
650) this.width=650;" height="279" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/njOPt6Q*eOQsVDfEnEFGjzGMZPtiR.SQXxfSqPUE9no!/b/dAYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIYAQAAAAADABQ!&su=0119462769&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dAYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIYAQAAA" />
Service mysqld start
650) this.width=650;" height="370" src="http://a4.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/9jS8zoDbCUVToFqneMGl28Ll3NUaDcKtjPrr.GvSEQE!/b/dGcBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgJzAQAAAAADAH8!&su=0154621905&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGcBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgJzAQAAA" />
安裝mysql:192.168.26.212
Yum install –y mysql-server
650) this.width=650;" height="314" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/CQY4iZMPBkD9RWEBgDZPDzA.4NHtoHVWMLbch5sh.zM!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgI7AQAAAAADADc!&su=0175416305&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgI7AQAAA" />
Service mysqld start
650) this.width=650;" height="331" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/upY5OYijBc31YcMlM5iFX0a2bhyM5XKoac.UytgNXko!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KQJMAQAAAAADAEM!&su=073170881&tm=1481871600&sce=0-12-12&rf=2-9" width="552" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KQJMAQAAA" />
配置MYSQL雙主：192.168.26.211
Vim /etc/my.cnf
設定檔如下：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=211
log-bin=mysq-bin
relay-log=relay-bin
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld
650) this.width=650;" height="223" src="http://a4.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/C3vhovvsRE.RS5gKs0bYKeyOapa*gV6shtdlptoo3xs!/b/dGcBAAAAAAAA&ek=1&kp=1&pt=0&bo=aAHgAAAAAAADAKw!&su=0112144289&tm=1481871600&sce=0-12-12&rf=2-9" width="359" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGcBAAAAAAAA&ek=1&kp=1&pt=0&bo=aAHgAAAAA" />
建立遠程所有權帳號：
grant replication slave,replication client on *.* to ‘jerry‘@‘192.168.%.%‘ identified by ‘jerrypass‘;
650) this.width=650;" height="280" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/r3SU9Lj9FgjtfS33tox6soReZwe7DZSvQl6.ZC4lrzI!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIZAQAAAAADABU!&su=0256170209&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIZAQAAA" />
change master to master_host=‘192.168.26.212‘,master_user=‘jerrys‘,master_password=‘jerrypass‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=282;
650) this.width=650;" height="281" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/sL.IpsWei8DveUpdra8ZgZlGYVBPi0UYbGXXq7vqyeQ!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIaAQAAAAADABY!&su=0150802337&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIaAQAAA" />
650) this.width=650;" height="285" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/SlUAKcRDOoW4x*rQcYtFW4iY2U4IZw0WcjvM4Qz15I8!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIeAQAAAAADABI!&su=0242284209&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIeAQAAA" />
Start slave;
650) this.width=650;" height="283" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/h42ejPxXj**uO3PKIsuef3nzHbFgOl*TLUJG2aAreQY!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIcAQAAAAADABA!&su=0150935921&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIcAQAAA" />
650) this.width=650;" height="248" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/buzbHC9Wi3Cfth7A6rY0TU5oFObu8R5ZzB*19*3xvW8!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgL5AAAAAAADAPQ!&su=0155682369&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgL5AAAAA" />
650) this.width=650;" height="428" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/DsNKMek6nsyzJRMzJyAIWVUPNQRo4aoU1kVB04f7M6A!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=GQKtAQAAAAADAJI!&su=0187317265&tm=1481871600&sce=0-12-12&rf=2-9" width="536" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=GQKtAQAAA" />
雙主配置：192.168.26.212
Vim /etc/my.cnf
設定檔如下：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=212
log-bin=mysql-bin
relay-log=relay-bin
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
650) this.width=650;" height="235" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/z95oWasqx4v.ApGvidTD8u31mKdpXPrV*tD6Q.8pPMo!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgLsAAAAAAADAOE!&su=08599297&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgLsAAAAA" />
授權遠端存取帳號：
grant replication slave,replication client on *.* to ‘jerry‘@‘192.168.%.%‘ identified by ‘jerrypass‘;
650) this.width=650;" height="227" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/5bOc.UXFjCV9rYmtXc*6niaTNqmA.Hy47A3r3cNyJK8!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgLkAAAAAAADAOk!&su=0125551057&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgLkAAAAA" />
change master to master_host=‘192.168.26.211‘,master_user=‘jerry‘,master_password=‘jerrypass‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=281;
650) this.width=650;" height="276" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/Vwr7uaSck3gvyBynK6ABDFrvbWEg0Xcdy.5416LBd2Y!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIVAQAAAAADABk!&su=0170821489&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIVAQAAA" />
Start slave;
650) this.width=650;" height="289" src="http://a4.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/*eLUBzMCEFPGzz8I5d3M5Pzc6Tn5K5amh9meC9CRZ68!/b/dGcBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIiAQAAAAADAC4!&su=079112593&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGcBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgIiAQAAA" />
650) this.width=650;" height="419" src="http://a4.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/RxImvJbOJrl2Dsc5rH.LdEf2LiEul2EKsOn4tofYpj0!/b/dGcBAAAAAAAA&ek=1&kp=1&pt=0&bo=KAKkAQAAAAADAKo!&su=022299857&tm=1481871600&sce=0-12-12&rf=2-9" width="551" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGcBAAAAAAAA&ek=1&kp=1&pt=0&bo=KAKkAQAAA" />
配置HAproxy:192.168.26.210
安裝：yum install –y haproxy
編輯設定檔：vim /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 httplog
option dontlognull
option http-server-close
option forwardfor except 127.0.0.0/8
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
#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------
#frontend main *:5000
# acl url_static path_beg -i /static /images /javascript /stylesheets
# acl url_static path_end -i .jpg .gif .png .css .js
#
# use_backend static if url_static
# default_backend app
#
##---------------------------------------------------------------------
## static backend for serving up images, stylesheets and such
##---------------------------------------------------------------------
#backend static
# balance roundrobin
# server static 127.0.0.1:4331 check
frontend websrv *:3306
default_backend mysqlservers
backend mysqlservers
balance leastconn
server m1 192.168.26.211:3306 check port 3306 maxconn 300
server m2 192.168.26.212:3306 check port 3306 maxconn 300
listen static
mode http
bind *:8008
stats enable
stats uri /haadmin?stats
stats auth admin:admin
stats hide-version
stats admin if TRUE
650) this.width=650;" height="307" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/eXgeDmzRcoiVmqNORTgeF47wfd3C9GPN47Dq8*q5WUI!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=9AE0AQAAAAADAOU!&su=082440289&tm=1481871600&sce=0-12-12&rf=2-9" width="499" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=9AE0AQAAA" />
650) this.width=650;" height="282" src="http://a4.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/wJWWzPx69yxg1RMavLNTXzNtB5MU68kyAuaLg1mA*bI!/b/dGcBAAAAAAAA&ek=1&kp=1&pt=0&bo=hAEbAQAAAAADALo!&su=078094737&tm=1481871600&sce=0-12-12&rf=2-9" width="387" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGcBAAAAAAAA&ek=1&kp=1&pt=0&bo=hAEbAQAAA" />
650) this.width=650;" height="294" src="http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/JnEGUDF1qLHjP3OjaOikQ.qPwiDD63C2CZ2qoLGo.Fs!/b/dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgInAQAAAAADACs!&su=095378065&tm=1481871600&sce=0-12-12&rf=2-9" width="553" style="margin:0px;padding:0px;border-width:0px;border-style:none;vertical-align:top;" alt="dGYBAAAAAAAA&ek=1&kp=1&pt=0&bo=KgInAQAAA" />
本文出自 “在路上” 部落格，請務必保留此出處http://jdonghong.blog.51cto.com/3473478/1883376
HAproxy負載平衡MYSQL雙主
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
