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