haproxymysqlslave多从库负载均衡带故障检测V1.1.doc

haproxy+mysql slave多从库负载均衡带故障检测V1.1Author:gaojinbo Time:2010-9-15 使用haproxy对mysql slave进行负载均衡，故障检测 环境： haproxy 00 vip db_read 40 db1 master 41 db2 slave1 42 db3 slave2 43 一、安装配置主从数据库，同步数据库为gaojinbo_www master上操作 1）修改配置 vi /etc/mysql/f 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 statusG; 说明：添加用户replication用户同步数据库，记录master状态，配置从库的时候需要 slave1上操作 1）修改配置 vi /etc/mysql/f server-id = 2 log_bin = mysql-bin log-slave-updates replicate_do_db = gaojinbo_www slave2上操作 1）修改配置 vi /etc/mysql/f 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=41,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 = /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 OKr (if mysql is running smoothly) # # OR # # HTTP/1.x 500 Internal Server Errorr (else) # MYSQL_HOST= 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 OKrn /bin/echo -e Content-Type: Content-Type: text/plainrn /bin/echo -e rn /bin/echo -e MySQL is running.rn /bin/echo -e rn else # mysql is down, return http 503 /bin/echo -e HTTP/1.1 503 Service Unavailablern /bin/echo -e Content-Type: Content-Type: text/plainrn /bin/echo -e rn /bin/echo -e MySQL is *down*.rn /bin/echo -e rn fi 三、haproxy配置 1.下载 wget http:/haproxy.1wt.eu/download/1.4/src/haproxy-1.4.8.tar.gz2.编译安装 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 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 40: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 42:3306 weight 1 check port 9201 inter 1s rise 5 fall 1 server db04_slave 43:3306 weight 1 check port 9201 inter 1s rise 5 fall 1