存档

‘Haproxy’ 分类的存档

[转]haproxy+mysql slave多从库负载均衡带故障检测

2012年3月4日 没有评论

haproxy+mysql slave多从库负载均衡带故障检测V1.1

Author:gaojinbo
Time:2010-9-15

使用haproxy对mysql slave进行负载均衡,故障检测

环境:
haproxy 192.168.3.100
vip db_read 192.168.3.140

db1 master 192.168.3.141
db2 slave1 192.168.3.142
db3 slave2 192.168.3.143

一、安装配置主从数据库,同步数据库为gaojinbo_www

master上操作
1)修改配置
vi /etc/mysql/my.cnf
server-id = 1
log_bin = mysql-bin
log-slave-updates
binlog_do_db = gaojinbo_www

2)重启Mysql并查看master状态
/etc/init.d/mysql restart
mysql -uroot -pylmf
grant replication slave on *.* to ‘replication’@’%’ identified by ‘ylmf’;
show master status\G;

说明:添加用户replication用户同步数据库,记录master状态,配置从库的时候需要

slave1上操作
1)修改配置
vi /etc/mysql/my.cnf
server-id = 2
log_bin = mysql-bin
log-slave-updates
replicate_do_db = gaojinbo_www

slave2上操作
1)修改配置
vi /etc/mysql/my.cnf
server-id = 3
log_bin = mysql-bin
log-slave-updates
replicate_do_db = gaojinbo_www

2台slave上操作
mysql -uroot -pylmf
change master to master_host=’192.168.3.141′,master_port=3306,master_user=’replication’,master_password=’ylmf’,master_log_file=’mysql-bin.000003′,master_log_pos=106;

二、mysql同步检测脚本,只需要在2台slave上操作
1.安装xinetd
apt-get install xinetd

2.增加检测服务
vi /etc/xinetd.d/mysqlchk
service mysqlchk_read
{
flags = REUSE
socket_type = stream
port = 9201
wait = no
user = nobody
server = /opt/mysqlchk_replication.sh
log_on_failure += USERID
disable = no
only_from = 192.168.3.0/24 # recommended to put the IPs that need
# to connect exclusively (security purposes)
}

vi /etc/services
mysqlchk_read 9201/tcp #mysql slave check read

3.检测脚本
vi /opt/mysqlchk_status.sh

#!/bin/bash
#
# /opt/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 500 Internal Server Error\r” (else)
#

MYSQL_HOST=”127.0.0.1″
MYSQL_PORT=”3306″
MYSQL_USERNAME=”root”
MYSQL_PASSWORD=”ylmf”
#
# We perform a simple query that should return a few results
ERROR_MSG=/usr/bin/mysql –host=$MYSQL_HOST –port=$MYSQL_PORT –user=$MYSQL_USERNAME –password=$MYSQL_PASSWORD -e "show databases;" 2>/dev/null
#
# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
#
if [ “$ERROR_MSG” != “” ]
then
# mysql is fine, return http 200
/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
# mysql is down, return http 503
/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

三、haproxy配置
1.下载
wget http://haproxy.1wt.eu/download/1.4/src/haproxy-1.4.8.tar.gz

2.编译安装
tar xvzf haproxy-1.4.8.tar.gz
cd haproxy-1.4.8
make TARGET=linux26
make install

3.配置文件
vi /etc/haproxy.cfg

global
maxconn 40000
debug
#quiet
user haproxy
group haproxy
nbproc 1
log 127.0.0.1 local3
spread-checks 2
defaults
timeout server 3s
timeout connect 3s
timeout client 60s
timeout http-request 3s
timeout queue 3s

frontend db_read
bind 192.168.3.140:3306
default_backend cluster_db_read

frontend web_haproxy
bind :80
default_backend web_status

backend cluster_db_read
mode tcp
option tcpka
balance roundrobin
option httpchk
server db03_slave 192.168.3.142:3306 weight 1 check port 9201 inter 1s rise 5 fall 1
server db04_slave 192.168.3.143:3306 weight 1 check port 9201 inter 1s rise 5 fall 1

backend web_status
mode http
stats enable
# stats scope
# stats hide-version
stats refresh 5s
stats uri /status
stats realm Haproxy\ statistics
stats auth gaojinbo:gaojinbo

完成!

分类: Haproxy, 负载均衡 标签: