mysql官方文档中有这么一句话：
MySQL Proxy is currently an Alpha release and should not be used withinproduction environments.
So。。。
使用haproxy来做这些事，以下仅供参考：
环境配置
master 192.168.1.106 master1
slave1192.168.1.107 master2master1(与master1主-主复制)
slave2192.168.1.110 slave2---->master1(master1的从库)
slave3192.168.1.111 slave3---->master1(master1的从库)
slave4 192.168.1.112 slave4---->master2(master2的从库)
monitor 192.168.1.200
192.168.1.105 eth1 写ip
192.168.1.113 eth2 读ip
说明：
当 master停止复制， slave1成为主库，haproxy停止发送请求到master和slave2,slave3, slave1与slave2,slave3依然可以从master接收日志。
当slave1停止复制，master成为主库，haproxy停止发送请求到slave1和slave4,master与slave4依然可以从slave1接收日志。
当 master和slave1同时停止复制，这时2台主库变成readonly模式，数据库不能写入 ,haproxy停止发送请求到slave2,slave3,slave4(脑裂)。
当slave1 offline时，master进入backup mode,haproxy停止发送请求到slave1,slave4。
当master offline时，slave1进入backup mode,haproxy停止发送请求到master,slave2,slave3。
当master和slave1同时offline,整个DB停止工作。
1、主从配置(略)
2、安装 xinetd ，配置mysqlchk服务
vi /etc/xinetd.d/mysqlchk
--两个master配置
servicemysqlchk-write
{
flags=REUSE
socket_type=stream
port=9201
wait=no
user=root
server=/opt/script/mysqlchk_status.sh
log_on_failure+=USERID
disable=no
only_from=192.168.1.0/24#recommendedtoputtheIPsthatneed
#toconnectexclusively(securitypurposes)
per_source=UNLIMITED
}
servicemysqlchk-read
{
flags=REUSE
socket_type=stream
port=9202
wait=no
user=root
server=/opt/script/mysqlchk_replication.sh
log_on_failure+=USERID
disable=no
only_from=192.168.1.0/24#recommendedtoputtheIPsthatneed
#toconnectexclusively(securitypurposes)
per_source=UNLIMITED
}
--所有slaves只需配置复制状态检查脚本
servicemysqlchk-read
{
flags=REUSE
socket_type=stream
port=9202
wait=no
user=root
server=/opt/script/mysqlchk_replication.sh
log_on_failure+=USERID
disable=no
only_from=192.168.1.0/24#recommendedtoputtheIPsthatneed
#toconnectexclusively(securitypurposes)
per_source=UNLIMITED
}
vi /etc/services
--两个master添加：
mysqlchk-write9201/tcp#MySQLstatuscheck
mysqlchk-read9202/tcp#MySQLreplicationcheck
--所有slaves添加：
mysqlchk-read9202/tcp#MySQLreplicationcheck
重启xinetd
#/etc/init.d/xinetdstop
#/etc/init.d/xinetdstart
查看端口号确认
[root@masterxinetd.d]#netstat-antup|grepxinetd
tcp000.0.0.0:9.0.0.0:*LISTEN3077/xinetd
tcp000.0.0.0:9.0.0.0:*LISTEN3077/xinetd
3、monitor主机安装haproxy
tarzxvfhaproxy-1.4.23.tar.gz
cdhaproxy-1.4.23
makeTARGET=linux26ARCH=x86_64
makeinstall
4、配置haproxy配置文件
vi /usr/local/haproxy-1.4.23/conf/haproxy-db.cfg
#HAProxyconfiguration-haproxy-db.cfg
global
maxconn4096
daemon
pidfile/usr/local/haproxy-1.4.23/haproxy.pid
#debug
#quiet
#chroot/usr/share/haproxy
defaults
logglobal
modehttp
#optionhttplog
optiondontlognull
log127.0.0.1local0
retries3
optionredispatch
maxconn4096
timeoutconnect1000ms
timeoutclient50000ms
timeoutserver50000ms
listenstats:8011
balance
modehttp
statsenable
statsauthroot:monitor
##
##FRONTEND##
##
#Load-balancedIPsforDBwritesandreads
#
frontenddb_write
modetcp
bind192.168.1.105:3306
default_backendcluster_db_write
frontenddb_read
modetcp
bind192.168.1.113:3306
default_backendcluster_db_read
#MonitorDBserveravailability
#
frontendmonitor_master
#
#setmaster_backupto'up'or'down'
#
bind127.0.0.1:9301
modehttp
#optionnolinger
aclno_repl_masternbsrv(master_replication)eq0
aclno_repl_slave1nbsrv(slave1_replication)eq0
aclno_masternbsrv(master_status)eq0
aclno_slave1nbsrv(slave1_status)eq0
monitor-uri/monitor
monitorfailunlessno_repl_masterno_repl_slave1no_slave1
monitorfailifno_masterno_slave1
frontendmonitor_slave1
#
#setslave1_backupto'up'or'down'
#
bind127.0.0.1:9302
modehttp
#optionnolinger
aclno_repl_masternbsrv(master_replication)eq0
aclno_repl_slave1nbsrv(slave1_replication)eq0
aclno_masternbsrv(master_status)eq0
aclno_slave1nbsrv(slave1_status)eq0
monitor-uri/monitor
monitorfailunlessno_repl_masterno_repl_slave1no_master
monitorfailifno_masterno_slave1
frontendmonitor_slave2
#
#setslave2read-onlyslaveto'down'
#
bind127.0.0.1:9303
modehttp
#optionnolinger
aclno_repl_slave2nbsrv(slave2_replication)eq0
aclno_repl_masternbsrv(master_replication)eq0
aclslave1nbsrv(slave1_status)eq1
monitor-uri/monitor
monitorfailifno_repl_slave2
monitorfailifno_repl_masterslave1
frontendmonitor_slave3
#
#setslave3read-onlyslaveto'down'
#
bind127.0.0.1:9304
modehttp
#optionnolinger
aclno_repl_slave3nbsrv(slave3_replication)eq0
aclno_repl_masternbsrv(master_replication)eq0
aclslave1nbsrv(slave1_status)eq1
monitor-uri/monitor
monitorfailifno_repl_slave3
monitorfailifno_repl_masterslave1
frontendmonitor_slave4
#
#setslave4read-onlyslaveto'down'
#
bind127.0.0.1:9305
modehttp
#optionnolinger
aclno_repl_slave4nbsrv(slave4_replication)eq0
aclno_repl_slave1nbsrv(slave1_replication)eq0
aclmasternbsrv(master_status)eq1
monitor-uri/monitor
monitorfailifno_repl_slave4
monitorfailifno_repl_slave1master
#Monitorforsplit-brainsyndrome
#
frontendmonitor_splitbrain
#
#setmaster_splitbrainandslave1_splitbrainto'up'
#
bind127.0.0.1:9300
modehttp
#optionnolinger
aclno_repl01nbsrv(master_replication)eq0
aclno_repl02nbsrv(slave1_replication)eq0
aclmasternbsrv(master_status)eq1
aclslave1nbsrv(slave1_status)eq1
monitor-uri/monitor
monitorfailunlessno_repl01no_repl02masterslave1
##
##BACKEND##
##
#CheckeveryDBserverreplicationstatus
#-performanhttpcheckonport9201(replicationstatus)
#-setto'down'ifresponseis'503ServiceUnavailable'
#-setto'up'ifresponseis'200OK'
#
backendmaster_replication
modetcp
balanceroundrobin
optiontcpka
optionhttpchk
servermaster192.168.1.106:3306checkport9202inter5srise1fall1
backendslave1_replication
modetcp
balanceroundrobin
optiontcpka
optionhttpchk
serverslave1192.168.1.107:3306checkport9202inter5srise1fall1
backendslave2_replication
modetcp
balanceroundrobin
optiontcpka
optionhttpchk
serverslave2192.168.1.110:3306checkport9202inter5srise1fall1
backendslave3_replication
modetcp
balanceroundrobin
optiontcpka
optionhttpchk
serverslave3192.168.1.111:3306checkport9202inter5srise1fall1
backendslave4_replication
modetcp
balanceroundrobin
optiontcpka
optionhttpchk
serverslave4192.168.1.112:3306checkport9202inter5srise1fall1
#CheckMasterDBservermysqlstatus
#-performanhttpcheckonport9201(mysqlstatus)
#-setto'down'ifresponseis'503ServiceUnavailable'
#-setto'up'ifresponseis'200OK'
#
backendmaster_status
modetcp
balanceroundrobin
optiontcpka
optionhttpchk
servermaster192.168.1.106:3306checkport9201inter5srise2fall2
backendslave1_status
modetcp
balanceroundrobin
optiontcpka
optionhttpchk
serverslave1192.168.1.107:3306checkport9201inter5srise2fall2
#DBwritecluster
#Failurescenarios:
#-replication'up'onmaster&slave1=writestomaster
#-replication'down'onslave1=writestomaster
#-replication'down'onmaster=writestoslave1
#-replication'down'onmaster&slave1=gonowhere,split-brain,clusterFAIL!
#-mysql'down'onslave1=writestomaster_backup
#-mysql'down'onmaster=writestoslave1_backup
#-mysql'down'onmaster&slave1=gonowhere,clusterFAIL!
#
backendcluster_db_write
#
#-max1dbserveravailableatalltimes
#-masterispreferred(topoflist)
#-db_backupssettheir'up'or'down'basedonresultsfrommonitor_monitor
#
modetcp
optiontcpka
balanceroundrobin
optionhttpchkGET/monitor
servermaster192.168.1.106:3306weight1checkport9202inter5srise2fall1
serverslave1192.168.1.107:3306weight1checkport9202inter5srise2fall1backup
servermaster_backup192.168.1.106:3306weight1checkport9301inter5srise2fall2addr127.0.0.1backup
serverslave1_backup192.168.1.107:3306weight1checkport9302inter5srise2fall2addr127.0.0.1backup
#DBreadcluster
#Failurescenarios
#-replication'up'onmaster&slave1=readsonmaster,slave1,alldb_slaves
#-replication'down'onslave1=readsonmaster,slavesofmaster
#-replication'down'onmaster=readsonslave1,slavesofslave1
#-replication'down'onmaster&slave1=readsonmaster_splitbrainandmaster_splitbrainonly
#-mysql'down'onslave1=readsonmaster_backup,slavesofmaster
#-mysql'down'onmaster=readsonslave1_backup,slavesofslave1
#-mysql'down'onmaster&slave1=gonowhere,clusterFAIL!
#
backendcluster_db_read
#
#-max2masterdbserversavailableatalltimes
#-maxNslavedbserversavailableatalltimesexceptduringsplit-brain
#-monitortrack'up'and'down'ofmonitorinthecluster_db_write
#-db_backupstrack'up'and'down'ofdb_backupsinthecluster_db_write
#-db_splitbrainssettheir'up'or'down'basedonresultsfrommonitor_splitbrain
#
modetcp
optiontcpka
balanceroundrobin
optionhttpchkGET/monitor
servermaster192.168.1.106:3306weight1trackcluster_db_write/master
serverslave1192.168.1.107:3306weight1trackcluster_db_write/slave1
servermaster_backup192.168.1.106:3306weight1trackcluster_db_write/master_backup
serverslave1_backup192.168.1.107:3306weight1trackcluster_db_write/slave1_backup
servermaster_splitbrain192.168.1.106:3306weight1checkport9300inter5srise1fall2addr127.0.0.1
serverslave1_splitbrain192.168.1.107:3306weight1checkport9300inter5srise1fall2addr127.0.0.1
#
#Scaling&redundancyoptions
#-db_slavessettheir'up'or'down'basedonresultsfrommonitor_monitor
#-db_slavesshouldtakelongertorise
#
serverslave2_slave192.168.1.110:3306weight1checkport9303inter5srise5fall1addr127.0.0.1
serverslave3_slave192.168.1.111:3306weight1checkport9304inter5srise5fall1addr127.0.0.1
serverslave4_slave192.168.1.112:3306weight1checkport9305inter5srise5fall1addr127.0.0.1
5、启动haproxy
haproxy-f/usr/local/haproxy-1.4.23/conf/haproxy-db.cfg
监控地址：http://192.168.1.200:8011/haproxy?stats
user：root password：monitor
一些参数说明 ：
maxconn
Sets the maximum per-process number of concurrent connections to . It
is equivalent to the command-line argument "-n". Proxies will stop accepting
connections when this limit is reached.
daemon
Makes the process fork into background. This is the recommended mode of
operation. It is equivalent to the command line "-D" argument. It can be
disabled by the command line "-db" argument.
pidfile
Writes pids of all daemons into file . This option is equivalent to
the "-p" command line argument. The file must be accessible to the user
starting the process.
retries
Set the number of retries to perform on a server after a connection failure
May be used in sections: defaults | frontend | listen | backend
yes | no | yes | yes
Arguments :
is the number of times a connection attempt should be retried on
a server when a connection either is refused or times out. The
default value is 3.
It is important to understand that this value applies to the number of
connection attempts, not full requests. When a connection has effectively
been established to a server, there will be no more retry.
In order to avoid immediate reconnections to a server which is restarting,
a turn-around timer of 1 second is applied before a retry occurs.
When "option redispatch" is set, the last retry may be performed on another
server even if a cookie references a different server.
See also : "option redispatch"
option redispatch
no option redispatch
Enable or disable session redistribution in case of connection failure
May be used in sections: defaults | frontend | listen | backend
yes | no | yes | yes
Arguments : none
In HTTP mode, if a server designated by a cookie is down, clients may
definitely stick to it because they cannot flush the cookie, so they will not
be able to access the service anymore.
Specifying "option redispatch" will allow the proxy to break their
persistence and redistribute them to a working server.
It also allows to retry last connection to another server in case of multiple
connection failures. Of course, it requires having "retries" set to a nonzero
value.
This form is the preferred form, which replaces both the "redispatch" and
"redisp" keywords.
If this option has been enabled in a "defaults" section, it can be disabled
in a specific instance by prepending the "no" keyword before it.
option dontlognull
no option dontlognull
Enable or disable logging of null connections
May be used in sections : defaults | frontend | listen | backend
yes | yes | yes | no
Arguments : none
In certain environments, there are components which will regularly connect to
various systems to ensure that they are still alive. It can be the case from
another load balancer as well as from monitoring systems. By default, even a
simple port probe or scan will produce a log. If those connections pollute
the logs too much, it is possible to enable option "dontlognull" to indicate
that a connection on which no data has been transferred will not be logged,
which typically corresponds to those probes.
It is generally recommended not to use this option in uncontrolled
environments (eg: internet), otherwise scans and other malicious activities
would not be logged.
If this option has been enabled in a "defaults" section, it can be disabled
in a specific instance by prepending the "no" keyword before it.
另外，使用keepalived实现代理层的HA。
