ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Linux操作系统下Oracle11g R2 RAC 安装配置教程
Oracle数据库培训-备份恢复-性能优化-集群容灾
Oracle数据库DBA高级工程师培训视频

MySQL从库集群方案之HAProxy篇

内容发布:luashin| 发布时间:2017-6-8 19:55:36
MySQL从库集群方案之HAProxy篇
HAProxy反向代理服务器支持双机热备支持虚拟主机,其配置简单,拥有非常不错的服务器健康检查功能。当其代理的后端服务器出现故障,HAProxy会自动将该服务器摘除,故障恢复后再自动将该服务器加入。这里有两台HAProxy机器,分别安装keepalived,组成热备形式。作用:当一台有问题,另一台可以在1秒内接管。
xinetd服务的作用是检测端口,本文中使用8890端口。HAProxy用http协议检测这个端口是否正常。
MySQL同步状态脚本,是放在从库本地,由xinetd服务来激活脚本,正常就会输出200状态码给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.com
   }
   notification_email_from coralzd@gmail.com
   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 <simon.matter@invoca.ch>
# Version: 2004060600
# Source function library.
if [ -f /etc/init.d/functions ]; then
  . /etc/init.d/functions
elif [ -f /etc/rc.d/init.d/functions ] ; then
  . /etc/rc.d/init.d/functions
else
  exit 0
fi
# Source networking configuration.
. /etc/sysconfig/network
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0
# This is our service name
BASENAME=HAProxy
if [ -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 1
esac
exit $?
chkconfig --add HAProxy
chkconfig HAProxy on
service HAProxy start

四、xinetd安装和配置
yum install -y xinetd
vim /etc/xinetd.d/mysql_status.sh
service 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.txt
mysql -urepdb63 -pmylqs9eyex7s -e "show full processlist;" >/tmp/processlist.txt
mysql -urepdb63 -pmylqs9eyex7s -e "show slave status\G;" >/tmp/rep.txt
iostat=`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压力测试工具简单教程--mysqlslap
下一篇:OneProxy实现MySQL分库分表
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表