HAProxy of MySQL Slave Database Cluster Solution _ MySQL

The HAProxy reverse proxy server supports dual-host hot standby and supports virtual hosts. the configuration is simple and has a very good server health check function. When the back-end server of the proxy fails, HAProxy will automatically remove the server, and then automatically add the server after the fault is restored. Mysql Cluster Proxy

The HAProxy reverse proxy server supports dual-host hot standby and supports virtual hosts. the configuration is simple and has a very good server health check function. When the back-end server of the proxy fails, HAProxy will automatically remove the server, and then automatically add the server after the fault is restored.

Two HAProxy machines are installed with keepalived to form a hot standby mode. Purpose: when one server has a problem, the other server can take over within 1 second.

The xinetd service is used to detect ports. Port 8890 is used in this article. HAProxy uses the http protocol to check whether the port is normal.

The MySQL synchronization status script is stored locally in the slave database and activated by the xinetd service. Normally, the 200 status code is output to HAProxy, proving that the slave database is normal. Otherwise, the script is removed. (You can add an SMS alert here)

System architecture

Use Software

• HAProxy 1.4.16
• Keepalived 1.1.20
• Xinetd 2.3.14
• MySQL synchronization status script 0.2

I. system conventions

System environment

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

Workflow

Preparations: The application configures slave's VIP 192.168.1.67 Port 3306.

(1) Application Server

(2) the vip 192.168.1.67: 3306 connected to HAProxy is allocated to a server load balancer instance based on the algorithm.

(3) Check whether the http 8890 status code is returned for port 200 of slave.

(4) The 200 status code is returned. HAProxy returns normal and continues the service.

(5) return 503, remove the slave, and forward the mysql request to another slave.

(6) If there is a problem with slave, send an SMS alarm and relevant personnel check it.

II. install Keepalived 1.1.20 on configuration

#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/keepalivedvim /etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_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       }  }

3. installation and configuration of 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.cfgglobal        log 127.0.0.1   local1 notice        maxconn 4096        chroot /usr/share/HAProxy        uid 99        gid 99        daemon        #debug        #quietdefaults        log     global        mode    http        #option httplog        option  dontlognull        retries 3        option  redispatch        maxconn 2000        contimeout      5000        clitimeout      50000        srvtimeout      50000listen  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 startup script

/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 1RETVAL=0start() {  /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
  

 

IV. xinetd installation and configuration

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         }

Restart xinetd

service xinetd restart

MySQL synchronization detection script (the script checks whether the synchronization SQL and IO processes are both true, and whether the select statements have more than 20 processes)

#!/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

Note: create an account with the process and slave_client permissions in mysql slave.

Author Profile: Cui Xiaohui, network name corzd, public network system administrator, proficient in website system architecture, Unix technology. Gtalk: coralzd@gmail.com