MySQL从库集群方案之HAProxy篇_MySQL
程序员文章站
2024-01-10 15:11:40
...
Mysql集群Proxy
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# 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权限的账号。
作者简介:崔晓辉,网名coralzd,大众网系统管理员,精通网站系统架构、Unix技术。gtalk:coralzd@gmail.com