MySQL教程：MySQL从库集群方案之HAProxy

来源：学生作业帮助网编辑：作业帮时间：2024/05/02 10:33:39 MySQL

MySQL教程：MySQL从库集群方案之HAProxyMySQL

【51Test.NET-MySQL教程：MySQL从库集群方案之HAProxy】：
HAProxy反向代理服务器支持双机热备支持虚拟主机，其配置简单，拥有非常不错的服务器健康检查功能。当其代理的后端服务器出现故障，HAProxy会自动将该服务器摘除，故障恢复后再自动将该服务器加入。

这里有两台HAProxy机器，分别安装keepalived，组成热备形式。作用：当一台有问题，另一台可以在1秒内接管。

xinetd服务的作用是检测端口，本文中使用8890端口。HAProxy用http协议检测这个端口是否正常。

MySQL同步状态脚本，是放在从库本地，由xinetd服务来激活脚本，正常就会输出200状态码给HAProxy，证明从库正常；否则，就剔除。（这里就可以加上短信报警了）

系统架构图

HAProxy系统架构图

使用软件

HAProxy 1.4.16
Keepalived 1.1.20
Xinetd 2.3.14
MySQL 同步状态脚本 0.2

一、系统约定

系统环境

OS:CentOS 5.6 x86_64
MASTER:192.168.1.65
BACKUP:192.168.1.66
VIP:192.168.1.67
serivce Port:3306

工作流程

准备工作：应用配置好slave的VIP 192.168.1.67 端口3306

(1)应用服务器

(2)连接HAProxy的vip 192.168.1.67:3306，根据算法，分配到一台slave。

(3)检测slave的8890端口是否返回http 200状态码。

(4)返回200 状态码，HAProxy 返回正常，继续服务。

(5)返回503，剔除该slave，并将mysql请求转发到另外一台slave。

(6)有问题的slave，发送短信报警，相关人员检查。

二、Keepalived 1.1.20的安装于配置

#cd /var/tmp/#wget http://www.keepalived.org/software/keepalived-1.1.20.tar.gz#tar zxvf keepalived-1.1.20.tar.gz#cd keepalived-1.1.20#./configure –prefix=/usr#make && make install#cp /usr/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/#cp /usr/etc/sysconfig/keepalived /etc/sysconfig/#mkdir /etc/keepalived

vim /etc/keepalived/keepalived.conf! Configuration File for keepalived

global_defs { notification_email { coralzd@gmail } notification_email_from coralzd@gmail smtp_server 192.168.1.1 smtp_connect_timeout 30 router_id LVS_DEVEL}

vrrp_script chk_HAProxy { script "killall -0 HAProxy" interval 2 weight 2 }vrrp_instance VI_1 { state MASTER interface eth0 virtual_router_id 50 priority 150 advert_int 1 authentication { auth_type PASS auth_pass 1111 } track_interface { eth0 } virtual_ipaddress { 192.168.1.67 } track_script { chk_HAProxy } }

三、HAProxy 1.4.16的安装与配置

#cd /var/tmp/#wget http://HAProxy.1wt.eu/download/1.4/src/HAProxy-1.4.16.tar.gz#tar -zxvf HAProxy-1.4.16.tar.gz#cd HAProxy-1.4.16#make install#mkdir -p /usr/local/HAProxy/etc#mkdir -p /usr/local/HAProxy/sbin#cp examples/HAProxy.cfg /usr/local/HAProxy/etc#ln -s /usr/local/sbin/HAProxy /usr/local/HAProxy/sbin/HAProxy#mkdir /usr/share/HAProxy

/etc/HAProxy/HAProxy.cfg

global log 127.0.0.1 local1 notice maxconn 4096 chroot /usr/share/HAProxy uid 99 gid 99 daemon #debug #quiet

defaults log global mode http #option httplog option dontlognull retries 3 option redispatch maxconn 2000 contimeout 5000 clitimeout 50000 srvtimeout 50000

listen DZW_MYSQL_SLAVE 192.168.1.67:3306 #cookie SERVERID rewrite mode tcp maxconn 200 balance roundrobin option httpchk OPTIONS * HTTP/1.1\r\nHost:\ www server mysql_192_168_1_23 192.168.1.23:3306 check port 8890 inter 5s rise 2 fall 3 server mysql_192_168_1_24 192.168.1.24:3306 check port 8890 inter 5s rise 2 fall 3 srvtimeout 20000 listen admin_status mode http bind 192.168.1.65:8899 option httplog log global stats enable stats refresh 10s stats hide-version stats realm Haproxy\ Statistics stats uri /admin-status stats auth admin:123456 stats admin if TRUE

HAProxy 启动脚本

/etc/init.d/HAProxy
#!/bin/sh## chkconfig: - 85 15# description: HA-Proxy is a TCP/HTTP reverse proxy which is particularly suited \#              for high availability environments.# processname: HAProxy# config: /etc/HAProxy/HAProxy.cfg# pidfile: /var/run/HAProxy.pid
# Script Author: Simon Matter # Version: 2004060600
# Source function library.if [ -f /etc/init.d/functions ]; then  . /etc/init.d/functionselif [ -f /etc/rc.d/init.d/functions ] ; then  . /etc/rc.d/init.d/functionselse  exit 0fi
# Source networking configuration.. /etc/sysconfig/network
# Check that networking is up.[ ${NETWORKING} = "no" ] && exit 0
# This is our service nameBASENAME=HAProxyif [ -L ___FCKpd___2 ]; then  BASENAME=`find ___FCKpd___2 -name $BASENAME -printf %l`  BASENAME=`basename $BASENAME`fi
[ -f /etc/$BASENAME/$BASENAME.cfg ] || exit 1
RETVAL=0
start() {  /usr/sbin/$BASENAME -c -q -f /etc/$BASENAME/$BASENAME.cfg  if [ $? -ne 0 ]; then    echo "Errors found in configuration file, check it with '$BASENAME check'."    return 1  fi
  echo -n "Starting $BASENAME: "  daemon /usr/sbin/$BASENAME -D -f /etc/$BASENAME/$BASENAME.cfg -p /var/run/$BASENAME.pid  RETVAL=$?  echo  [ $RETVAL -eq 0 ] && touch /var/lock/subsys/$BASENAME  return $RETVAL}
stop() {  echo -n "Shutting down $BASENAME: "  killproc $BASENAME -USR1  RETVAL=$?  echo  [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/$BASENAME  [ $RETVAL -eq 0 ] && rm -f /var/run/$BASENAME.pid  return $RETVAL}
restart() {  /usr/sbin/$BASENAME -c -q -f /etc/$BASENAME/$BASENAME.cfg  if [ $? -ne 0 ]; then    echo "Errors found in configuration file, check it with '$BASENAME check'."    return 1  fi  stop  start}
reload() {  /usr/sbin/$BASENAME -c -q -f /etc/$BASENAME/$BASENAME.cfg  if [ $? -ne 0 ]; then    echo "Errors found in configuration file, check it with '$BASENAME check'."    return 1  fi  /usr/sbin/$BASENAME -D -f /etc/$BASENAME/$BASENAME.cfg -p /var/run/$BASENAME.pid -sf $(cat /var/run/$BASENAME.pid)}
check() {  /usr/sbin/$BASENAME -c -q -V -f /etc/$BASENAME/$BASENAME.cfg}
rhstatus() {  status $BASENAME}
condrestart() {  [ -e /var/lock/subsys/$BASENAME ] && restart || :}
# See how we were called.case "$1" in  start)    start    ;;  stop)    stop    ;;  restart)    restart    ;;  reload)    reload    ;;  condrestart)    condrestart    ;;  status)    rhstatus    ;;  check)    check    ;;  *)    echo ___FCKpd___2quot;Usage: $BASENAME {start|stop|restart|reload|condrestart|status|check}"    exit 1esac exit $?
chkconfig –add HAProxy chkconfig HAProxy onservice HAProxy start

四、xinetd安装和配置

yum install -y xinetdvim /etc/xinetd.d/mysql_status.shservice mysqlrep_status{         flags           = REUSE         socket_type     = stream         port            = 8890        wait            = no         user            = nobody         server          = /usr/local/bin/mysqlrep_status.sh        log_on_failure  += USERID         disable         = no         }

重启xinetd

service xinetd restart

MySQL同步检测脚本（脚本检测同步sql和IO进程是否都为真，以及select是否达到20个进程以上）

#!/bin/bash # # /usr/local/bin/mysqlchk_status.sh # # 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 503 Internal Server Error\r" (else) # 
MYSQL_HOST="localhost"MYSQL_PORT="3306"MYSQL_USERNAME="repdb63"MYSQL_PASSWORD="mylqs9eyex7s"# # We perform a simple query that should return a few results #/usr/local/mysql/bin/mysql  -hlocalhost –urepdb63 –pmylqs9eyex7s -e "show slave status\G;"   > /tmp/rep.txtmysql -urepdb63 -pmylqs9eyex7s -e "show full processlist;" >/tmp/processlist.txtmysql -urepdb63 -pmylqs9eyex7s -e "show slave status\G;" >/tmp/rep.txtiostat=`grep "Slave_IO_Running" /tmp/rep.txt  |awk '{print $2}'`            sqlstat=`grep "Slave_SQL_Running" /tmp/rep.txt |awk '{print $2}'`           result=$(cat /tmp/processlist.txt|wc -l)#echo iostat:$iostat and sqlstat:$sqlstat # if slave_IO_Running and Slave_sql_Running ok,then return 200 code if [ "$result" -lt "20" ] && [ "$iostat" = "Yes" ] && [ "$sqlstat" = "Yes" ];
then        # mysql is fine, return http 200         /bin/echo -e "HTTP/1.1 200 OK\r\n"         else        # mysql is down, return http 503         /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"         fi

注意：在mysql slave另行建立一个具有process和slave_client权限的账号。

MySQL