![MySQL 的 HA 代理 - 仅故障转移](https://linux22.com/image/592611/MySQL%20%E7%9A%84%20HA%20%E4%BB%A3%E7%90%86%20-%20%E4%BB%85%E6%95%85%E9%9A%9C%E8%BD%AC%E7%A7%BB.png)
我对使用 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 资源代理处理。