Use HAProxy to perform load balancing and status monitoring for the MySQLslave Group

1. Install haproxyhaproxymachine haproxy.1wt.deu tarzxvfhaproxy-1.4.25.tar.gz cdhaproxy-1.4.25makeTARGETlinux26makeinstallmkdir-pusrlocalhaproxychownnobody: nobodyusrlocalhaproxymkdiretchaproxycpexampleshaprox

I. the installation of haproxy machine http://haproxy.1wt.deu needs to flip the wall tar zxvf haproxy-1.4.25.tar.gzcd haproxy-1.4.25make TARGET = linux26make installmkdir-p/usr/local/haproxy/chown nobody: nobody/usr/local/haproxy/mkdir/etc/haproxy/cp examples/haprox

1. Install haproxy

Haproxy Machine

Http://haproxy.1wt.deu

Wall flip required

tar zxvf haproxy-1.4.25.tar.gzcd haproxy-1.4.25make TARGET=linux26make installmkdir -p /usr/local/haproxy/chown nobody:nobody /usr/local/haproxy/mkdir /etc/haproxy/cp examples/haproxy.cfg /etc/haproxy/cp examples/haproxy.init /etc/init.d/haproxychown root:root /etc/init.d/haproxy chmod 700 /etc/init.d/haproxy

Modify the haproxy Startup Script

/Usr/sbin/$ BASENAME
Change
/Usr/local/sbin/$ BASENAME

sed -i -r 's|/usr/sbin|/usr/local/sbin|' /etc/init.d/haproxy

Edit configuration file
Vi/etc/haproxy. cfg

global#log 127.0.0.1local0log 127.0.0.1local3 info#log loghostlocal0 infomaxconn 4096chroot /usr/local/haproxyuid nobodygid nobodydaemondebug#quietdefaultslogglobalmodetcp#optionhttplogoptiondontlognullretries3option redispatchmaxconn2000contimeout5000clitimeout50000srvtimeout50000frontend mysqlbind 192.168.0.107:3306maxconn 3000default_backend mysql_slavebackendmysql_slave  #cookieSERVERID rewritemode tcpbalanceroundrobin #balancesource #balanceleastconn contimeout 10stimeout check 2soption httpchk OPTIONS * HTTP/1.1\r\nHost:\ wwwservermysql_192_168_0_104_3306 192.168.0.104:3306 weight 1 check port 9300 inter 5s rise 2 fall 3servermysql_192_168_0_104_3307 192.168.0.104:3307 weight 1 check port 9301 inter 5s rise 2 fall 3#servermysql_192_168_0_106_3306 192.168.0.106:3306 weight 1 check port 9300 inter 5s rise 2 fall 3listen  admin_statusmode  httpbind 192.168.0.107:8000option httploglog globalstats enablestats refresh 30sstats hide-versionstats realm Haproxy\ Statisticsstats uri  /admin-status stats auth  admin:123456 stats admin if TRUE

Open the monitored iptables

iptables -A INPUT -p tcp -m tcp -s 192.168.0.0/24 --dport 8000 -j ACCEPT

Add auto-start and start haproxy Service

chkconfig –add haproxy chkconfig haproxy onservice haproxy start

Monitored Machine

I am a single-host dual-instance, so I have two scripts. Only one script and one service port are required for a Single-host instance.
Edit mysql detection 3306 script
Vi/opt/shell/mysqlchk_status_3306.sh

#!/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="mysqlcheck"MYSQL_PASSWORD="paSSword"MYSQL_PATH="/opt/mysql/bin/"# # We perform a simple query that should return a few results #${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txt${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show full processlist;" >/tmp/processlist${MYSQL_PORT}.txt${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txtiostat=`grep "Slave_IO_Running" /tmp/rep${MYSQL_PORT}.txt  |awk '{print $2}'`            sqlstat=`grep "Slave_SQL_Running" /tmp/rep${MYSQL_PORT}.txt |awk '{print $2}'`           result=$(cat /tmp/processlist${MYSQL_PORT}.txt|wc -l)echo iostat:$iostat and sqlstat:$sqlstat # if slave_IO_Running and Slave_sql_Running ok,then return 200 code if [ "$result" -gt "3" ] && [ "$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

Vi/opt/shell/mysqlchk_status_3307.sh

#!/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="3307"MYSQL_USERNAME="mysqlcheck"MYSQL_PASSWORD="paSSword"MYSQL_PATH="/opt/mysql/bin/"# # We perform a simple query that should return a few results #${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txt${MYSQL_PATH}mysql -S/data/mysql/mysql.sock -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show full processlist;" >/tmp/processlist${MYSQL_PORT}.txt${MYSQL_PATH}mysql -S/data/mysql/mysql.sock -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txtiostat=`grep "Slave_IO_Running" /tmp/rep${MYSQL_PORT}.txt  |awk '{print $2}'`            sqlstat=`grep "Slave_SQL_Running" /tmp/rep${MYSQL_PORT}.txt |awk '{print $2}'`           result=$(cat /tmp/processlist${MYSQL_PORT}.txt|wc -l)#echo iostat:$iostat and sqlstat:$sqlstat echo $result# if slave_IO_Running and Slave_sql_Running ok,then return 200 code if [ "$result" -gt "3" ] && [ "$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

Chmod 775/opt/shell/mysqlchk_status_3306.sh
Chmod 775/opt/shell/mysqlchk_status_3307.sh

Create an account with the process and slave_client permissions in mysql slave.

CREATE USER 'mysqlcheck'@'localhost' IDENTIFIED BY 'PaSSword';GRANT PROCESS , REPLICATION CLIENT ON * . * TO 'mysqlcheck'@'localhost' IDENTIFIED BY 'PaSSword' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;flush privileges;

Test script
./Mysqlchk_status_3306.sh

Add Service
Bind an intranet ip address, run on port 930, and only open to 192.168.0 Intranet
Yum install-y xinetd
Vim/etc/xinetd. d/mysql_status

service mysqlchk_status3306{         flags           = REUSE         socket_type     = stream         bind            = 192.168.0.104        port            = 9300        wait            = no         user            = nobody         server          = /opt/shell/mysqlchk_status_3306.sh        log_type        = FILE /dev/null        log_on_failure  += USERID         disable         = no         only_from       = 192.168.0.0/24 }service mysqlchk_status3307{         flags           = REUSE         socket_type     = stream         bind            = 192.168.0.104        port            = 9301        wait            = no         user            = nobody         server          = /opt/shell/mysqlchk_status_3307.sh        log_type        = FILE /dev/null        log_on_failure  += USERID         disable         = no         only_from       = 192.168.0.0/24 }

The IP addresses of bind and only_from must have the permissions that haproxy can request. Use 0.0.0.0 for drbd.
The user must use the execution permission of the server script
Port must be declared in/etc/service

Chattr-I/etc/services
Vi/etc/services

mysqlchk_status3306    9300/tcp#haproxy mysql checkmysqlchk_status3307    9301/tcp#haproxy mysql check

In services, mysqlchk_status3306 must correspond to the service name in xinetd. d.

Open iptables

iptables -A INPUT -p tcp -m tcp -s 192.168.0.0/24 --dport 9300 -j ACCEPTiptables -A INPUT -p tcp -m tcp -s 192.168.0.0/24 --dport 9301 -j ACCEPT

/Etc/init. d/iptables save

Add auto-start and startup services
Chkconfig xencetd-level 345 on
/Etc/init. d/xinetd start

Check whether running
Netstat-lntp

Active Internet connections (only servers)Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   tcp        0      0 0.0.0.0:9300                0.0.0.0:*                   LISTEN      4863/xinetd         tcp        0      0 0.0.0.0:9301                0.0.0.0:*                   LISTEN      4863/xinetd

If not, check the bind Address and service port.

Run the test on the invigilator
Telnet 192.168.0.104 9300

Trying 192.168.0.104...Connected to 192.168.0.104 (192.168.0.104).Escape character is '^]'./opt/shell/mysqlchk_status_3306.sh: line 24: /tmp/processlist3306.txt: Permission denied/opt/shell/mysqlchk_status_3306.sh: line 25: /tmp/rep3306.txt: Permission deniedHTTP/1.1 200 OKConnection closed by foreign host.

An error has been reported because it has been run with root. The temporary file is deleted on the monitored host.

rm -f /tmp/processlist3306.txt /tmp/processlist3307.txtrm -f /tmp/rep3306.txt /tmp/rep3307.txt

If no output exists, check the execution permission of the mysqlchk_status_3306.sh script.

After startup, there will be a lot of logs in/var/log/messages.

Oct 23 14:37:00 lova xinetd[11057]: START: mysqlchk_status3306 pid=11464 from=192.168.0.22Oct 23 14:37:00 lova xinetd[11057]: EXIT: mysqlchk_status3306 status=0 pid=11464 duration=0(sec)Oct 23 14:37:05 lova xinetd[11057]: START: mysqlchk_status3306 pid=11494 from=192.168.0.22Oct 23 14:37:05 lova xinetd[11057]: EXIT: mysqlchk_status3306 status=0 pid=11494 duration=0(sec)

Output logs to the black hole in haproxy Configuration
Log_type = FILE/dev/null

View monitoring

The direct access to localhost is 503

Http: // localhost/

503 Service Unavailable

No server is available to handle this request.

Add admin-status

Http: // localhost/admin-status

You need to add the user permission through haproxy to mysql on slave mysql during application.

Haproxy command
/Etc/init. d/haproxy
Usage: haproxy {start | stop | restart | reload | condrestart | status | check}

Appendix
Optimize time_wait to prevent port depletion
Vi/etc/sysctl. conf

net.ipv4.ip_local_port_range = 1025 65000net.ipv4.tcp_tw_reuse = 1net.ipv4.tcp_tw_recycle = 1net.ipv4.tcp_fin_timeout = 15net.ipv4.tcp_max_tw_buckets = 35000

Sysctl-p

Use nginx reverse proxy haprox backend

# Omit listen admin_status mode http bind 192.168.0.107: 8000 option httplog log global stats enable stats refresh 30 s stats hide-version stats realm Haproxy \ Statistics # stats uri/admin-status stats uri/haproxy/# stats auth admin: 123456 # stats admin if TRUE

Nginx. conf

# Omitting location ~ * ^/Haproxy/{proxy_passhttp: // 192.168.0.107: 8000; proxy_set_headerHost $ host; proxy_set_headerX-Real-IP $ remote_addr; proxy_set_headerX-Forwarded-For $ scheme; # proxy_set_headerX-Forwarded-For $ remote_addr; proxy_redirectoff;} # omitted

Refer:

Http://linux.die.net/man/5/xinetd.conf

Http://adslroot.blogspot.com/2013/12/haproxy-mysql.html

Http://sssslide.com/www.slideshare.net/Severalnines/haproxy-mysql-slides