04/4/14

haproxy+keepalived+mysql+xinetd 实现 mysql 双机机制

1、功能描述

a、192.168.0.103 (以下称103,vip:192.168.0.123),192.168.0.104(以下称104,vip:192.168.0.124)分别部署haproxy,并通过keepalived绑定vip(103->123,104->124)。

b、程序代码通过vip连接数据库,103、104各部署一套slave,104从103slave同步。

c、当103宕机,123ip迁移到104。此时104同时有123、124两个vip,并响应123和124数据库请求。同理,104宕机时,103同时为vip123,124提供服务。

d、haproxy通过xinetd服务,启动mysqlchk服务。数据库正常返回http 200,异常则返回503.

e、优点:一、程序迁移数据库改动比较少,运维可完全控制。二、运维可根据情况,安排调节slave使用权重,合理配置资源。三、高可用行,大大减少了单点故障影响。

2、部署过程

a、haproxy.cfg

global
log 127.0.0.1 local3 notice
maxconn 20480
uid mysql
gid mysql
daemon
nbproc 1
pidfile /opt/phpdba/haproxy/logs/haproxy.pid

defaults
option  dontlognull
retries 3
option  redispatch
#maxconn 20480
contimeout      5000
clitimeout      50000
srvtimeout      50000

frontend db_baobao_read
bind :33080
default_backend cluster_db_baobao_read

frontend db_baobao_write
bind :33081
default_backend cluster_db_baobao_write

frontend db_user_write
bind :8066
default_backend cluster_db_user_write

frontend web_haproxy
bind :80
default_backend web_status

backend cluster_db_baobao_read
mode tcp
option tcpka
balance roundrobin
option httpchk
server db103_slave 192.168.0.103:3316 weight 1 check port 8890 inter 1s rise 5 fall 3
server db104_slave 192.168.0.104:3316 weight 1 check port 8890 inter 1s rise 5 fall 3

backend cluster_db_baobao_write
mode tcp
option tcpka
balance roundrobin
option httpchk
server db110_master 192.168.0.110:3308 weight 1 check port 8890 inter 1s rise 5 fall 3

backend cluster_db_user_write
mode tcp
option tcpka
balance roundrobin
option httpchk
server db46_master 192.168.0.46:3306 weight 1 check port 8890 inter 1s rise 5 fall 3

backend web_status
mode http
stats enable
#stats scope
stats hide-version
stats refresh 5s
stats uri /status
stats realm Haproxy\ statistic
stats auth chen-123:phpdba

 

b、keepalvied_db.conf

! Configuration File for keepalived
global_defs {
router_id LVS_db_ci104
}

vrrp_script chk_http_port {
script “/opt/phpdba/keepalived/etc/keepalived/check_haproxy.sh”
interval 2
weight -10
}

vrrp_instance VI_CiUser {
state BACKUP
interface em2
virtual_router_id 58
priority 99
advert_int 1

authentication {
auth_type PASS
auth_pass phpdba
}

virtual_ipaddress {
192.168.0.123 dev em2 label em2:0
}
}

vrrp_instance VI_CiBlog_QQ {
state MASTER
interface em2
virtual_router_id 68
priority 110
advert_int 1
authentication {
auth_type PASS
auth_pass phpdba
}

track_script {
chk_http_port
}

virtual_ipaddress {
192.168.0.124 dev em2 label em2:1
}
}

c、check_haproxy.sh

#!/bin/bash
A=`ps -C haproxy –no-header |wc -l`
if [ $A -eq 0 ];then
/opt/phpdba/haproxy/sbin/haproxy -f /opt/phpdba/haproxy/conf/haproxy.cfg
sleep 3
if [ `ps -C haproxy --no-header |wc -l` -eq 0 ];then
/etc/init.d/keepalived stop
fi
fi

d、/etc/xinetd.d/mysqlchk

service mysqlcheck
{
flags           = REUSE
socket_type     = stream
port            = 8890
wait            = no
user            = root
server          = /opt/phpdba/keepalived/etc/keepalived/mysqlchk.sh
log_on_failure  += USERID
disable         = no
only_from       = 192.168.0.0/24
}

e、/opt/phpdba/keepalived/etc/keepalived/mysqlchk.sh

#!/bin/bash

MYSQL_HOST=”192.168.0.104″
MYSQL_PORT=”3316″
MYSQL_USERNAME=”admin”
MYSQL_PASSWORD=”123456″
MYSQL_MAXTHREADNUM=”200″

ERROR_MSG=`/opt/phpdba/mysql/bin/mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USERNAME -p$MYSQL_PASSWORD -e “show databases;” 2>/dev/null`

if [ "$ERROR_MSG" != "" ]
then
/bin/echo -e “HTTP/1.1 200 OK\r\n”
/bin/echo -e “Content-Type: Content-Type: text/plain\r\n”
/bin/echo -e “\r\n”
/bin/echo -e “MySQL is running. \r\n”
/bin/echo -e “\r\n”
else
/bin/echo -e “HTTP/1.1 503 Service Unavailable\r\n”
/bin/echo -e “Content-Type: Content-Type: text/plain\r\n”
/bin/echo -e “\r\n”
/bin/echo -e “MySQL is down. \r\n”
/bin/echo -e “\r\n”
fi

f、/etc/services

mysqlcheck      8890/tcp
mysqlcheck      8890/udp

g、xinetd

yum install xinetd -f   &&  /etc/init.d/xinetd start

h、keepalvied

/etc/init.d/keepalived start

3、遇到的问题

a、haproxy 配置中check port 同一ip同一端口,只能监控一次。同一ip多个mysql实例监控,采用不同端口监控。

b、防火墙禁止了8890端口访问,导致check port失败。(iptables -I INPUT -p tcp –dport 8890 -j ACCEPT)

4、haproxy 状态页面

haproxy