我对使用 HA 代理作为 MySQL 环境中的故障转移/负载平衡器感兴趣。我们有一个主-主 + 2 个从属设置。
我希望主/主设置（即使它们是为此设置的）只写入一个主服务器。这是为了避免出现裂脑问题。
我确实计划在 HA 代理上设置一个单独的端口用于读取并实现负载平衡。
是否可以仅使用 HA 代理作为故障转移？如果可以，您将如何设置它？
它是否类似于使用循环调度的平衡，但将其中一个服务器的权重设为 1，将另一个服务器的权重设为 0？其想法是，如果第一个服务器离线，则无论其权重如何，都将使用第二个服务器？
答案1
是否可以仅使用 HA 代理作为故障转移？如果可以，您将如何设置它？
是的，可以通过添加backup选项到末尾server行，如下所示：
frontend FE_mysql_writer
bind V.I.P.A:3306
default_backend BE_mysql_writer
backend BE_mysql_writer
mode tcp
balance roundrobin
option tcpka
option httpchk
server mysql1 ip1:3306 weight 1 check port 9199 inter 12000 rise 3 fall 3
server mysql2 ip2:3306 weight 1 check port 9199 inter 12000 rise 3 fall 3 backup
9199端口用于监控MySQL状态：
/etc/services
mysqlchk 9199/tcp # mysqlchk
/etc/xinetd.d/mysqlchk
# /etc/xinetd.d/mysqlchk
# default: on
# description: mysqlchk
service mysqlchk
{
flags = REUSE
socket_type = stream
port = 9199
wait = no
user = nobody
server = /opt/mysqlchk
log_on_failure += USERID
disable = no
only_from = 0.0.0.0/0 # recommended to put the IPs that need
# to connect exclusively (security purposes)
per_source = UNLIMITED # Recently added (May 20, 2010)
# Prevents the system from complaining
# about having too many connections open from
# the same IP. More info:
# http://www.linuxfocus.org/English/November2000/article175.shtml
}
/opt/mysqlchk
#!/bin/bash
# /opt/mysqlchk
# This script checks if a mysql server is healthy running on localhost. It will
# return:
#
# "HTTP/1.x 200 OK\r" (if mysql is running smoothly)
#
# - OR -
#
# "HTTP/1.x 500 Internal Server Error\r" (else)
#
# The purpose of this script is make haproxy capable of monitoring mysql properly
#
# Author: Unai Rodriguez
#
# It is recommended that a low-privileged-mysql user is created to be used by
# this script. Something like this:
#
# mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' \
# -> IDENTIFIED BY '257retfg2uysg218' WITH GRANT OPTION;
# mysql> flush privileges;
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="mysqlchkusr"
MYSQL_PASSWORD="pa$$w0rd"
TMP_FILE="/tmp/mysqlchk.out"
ERR_FILE="/tmp/mysqlchk.err"
#
# We perform a simple query that should return a few results :-p
#
/usr/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME \
--password=$MYSQL_PASSWORD -e"show databases;" > $TMP_FILE 2> $ERR_FILE
#
# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
#
if [ "$(/bin/cat $TMP_FILE)" != "" ]
then
# mysql is fine, return http 200
/bin/echo -e "HTTP/1.1 200 OK\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "MySQL is running.\r\n"
/bin/echo -e "\r\n"
else
# mysql is fine, return http 503
/bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "MySQL is *down*.\r\n"
/bin/echo -e "\r\n"
fi
来源：http://sysbible.org/2008/12/04/having-haproxy-check-mysql-status-through-a-xinetd-script/
但是，当主服务器发生故障时，如何将从服务器指向新的主服务器？最好使用 HAProxy 来平衡读取操作，并将写入操作（包括故障转移）交给Percona Pacemaker 资源代理处理。