自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

MySQL從庫集群方案之HAProxy篇

原創(chuàng)
作者：崔曉輝
系統(tǒng) Linux
HAProxy 反向代理服務(wù)器支持雙機熱備支持虛擬主機，其配置簡單，擁有非常不錯的服務(wù)器健康檢查功能。當其代理的后端服務(wù)器出現(xiàn)故障，HAProxy會自動將該服務(wù)器摘除，故障恢復后再自動將該服務(wù)器加入。本文介紹如何通過兩臺HAProxy服務(wù)器實現(xiàn)MySQL熱備的集群方案。

【51CTO獨家特稿】HAProxy反向代理服務(wù)器支持雙機熱備支持虛擬主機，其配置簡單，擁有非常不錯的服務(wù)器健康檢查功能。當其代理的后端服務(wù)器出現(xiàn)故障，HAProxy會自動將該服務(wù)器摘除，故障恢復后再自動將該服務(wù)器加入。

這里有兩臺HAProxy機器，分別安裝keepalived，組成熱備形式。作用：當一臺有問題，另一臺可以在1秒內(nèi)接管。

xinetd服務(wù)的作用是檢測端口，本文中使用8890端口。HAProxy用http協(xié)議檢測這個端口是否正常。

MySQL同步狀態(tài)腳本，是放在從庫本地，由xinetd服務(wù)來激活腳本，正常就會輸出200狀態(tài)碼給HAProxy，證明從庫正常；否則，就剔除。（這里就可以加上短信報警了）

系統(tǒng)架構(gòu)圖

HAProxy系統(tǒng)架構(gòu)圖

使用軟件

  • HAProxy 1.4.16
  • Keepalived 1.1.20
  • Xinetd 2.3.14
  • MySQL 同步狀態(tài)腳本 0.2

一、系統(tǒng)約定

系統(tǒng)環(huán)境

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

工作流程

準備工作：應(yīng)用配置好slave的VIP 192.168.1.67 端口3306

(1)應(yīng)用服務(wù)器

(2)連接HAProxy的vip 192.168.1.67:3306，根據(jù)算法，分配到一臺slave。

(3)檢測slave的8890端口是否返回http 200狀態(tài)碼。

(4)返回200 狀態(tài)碼，HAProxy 返回正常，繼續(xù)服務(wù)。

(5)返回503，剔除該slave，并將mysql請求轉(zhuǎn)發(fā)到另外一臺slave。

(6)有問題的slave，發(fā)送短信報警，相關(guān)人員檢查。

二、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 $0 ]; then
  BASENAME=`find $0 -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 $"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權(quán)限的賬號。

作者簡介：崔曉輝，網(wǎng)名coralzd，大眾網(wǎng)系統(tǒng)管理員，精通網(wǎng)站系統(tǒng)架構(gòu)、Unix技術(shù)。gtalk：coralzd@gmail.com

【51CTO.com獨家特稿，轉(zhuǎn)載請注明原文作者和出處?！?/p>

【編輯推薦】

  1. 集群監(jiān)控工具簡介：Ganglia和Nagios
  2. 服務(wù)器集群高可用性注意事項：DNS與故障轉(zhuǎn)移
  3. 專題：MySQL集群手冊:數(shù)據(jù)庫高性能之路
責任編輯：yangsai 來源： 51CTO.com
MySQL集群HAProxy
相關(guān)推薦

2011-10-10 09:47:32

HAProxy負載均衡Keepalived

2009-09-03 11:42:32

2024-03-19 08:02:28

集群GaussDB指標

2022-11-26 00:34:57

數(shù)組Java程序

2019-10-12 15:06:02

MySQL數(shù)據(jù)庫命令

2018-02-02 16:15:02

Hadoop數(shù)據(jù)遷移集群

2011-01-14 15:32:17

2011-03-23 15:38:29

2022-03-29 10:52:08

MySQL鎖數(shù)據(jù)庫

2019-07-29 09:34:49

數(shù)據(jù)庫集群Oracle RAC

2021-06-05 21:29:53

數(shù)據(jù)庫MySQL函數(shù)

2024-11-11 08:31:32

2021-02-19 06:56:33

架構(gòu)協(xié)程應(yīng)用

2020-10-28 11:20:18

RabbitMQHAProxy運維

2018-06-19 09:54:22

MySQLHBase存儲

2018-05-02 08:48:58

Raid存儲MySQL

2011-03-09 08:53:02

MySQL優(yōu)化集群

2021-03-06 08:02:39

MySQL集群服務(wù)器

2024-09-26 10:19:15

2019-09-27 08:18:13

負載均衡核心Key
點贊
收藏

51CTO技術(shù)棧公眾號