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

1、haproxy+mysql slave多从库负载均衡带故障检测V1.1Author:gaojinbo Time:2010-9-15 使用haproxy对mysql slave进行负载均衡，故障检测 环境： haproxy            00 vip    db_read    40 db1    master   

2、41 db2    slave1    42 db3    slave2    43  一、安装配置主从数据库，同步数据库为gaojinbo_www master上操作 1）修改配置 vi /etc/mysql/f server-id              

3、= 1 log_bin                 = mysql-bin log-slave-updates binlog_do_db            = gaojinbo_www  2）重启Mysql并查看master状态 /etc/init.d/mysql restart mysql -uroot -p

4、ylmf 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 

5、;                = mysql-bin log-slave-updates replicate_do_db         = gaojinbo_www  slave2上操作 1）修改配置 vi /etc/mysql/f server-id        

6、60;      = 3 log_bin                 = mysql-bin log-slave-updates replicate_do_db         = gaojinbo_www  2台slave上操作 mysql -uroot -pylmf change maste

7、r 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   

8、0;     flags           = REUSE         socket_type     = stream         port          

9、0; = 9201         wait            = no         user            = nobody         serve

10、r          = /opt/mysqlchk_replication.sh         log_on_failure  += USERID         disable         = no       

11、;  only_from       = /24 # recommended to put the IPs that need                                

12、0;    # to connect exclusively (security purposes) vi /etc/services mysqlchk_read   9201/tcp                        #mysql slave check read  3.检测脚本 vi /opt

13、/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="

14、;" 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

15、;" 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    

16、0;    /bin/echo -e "HTTP/1.1 200 OKrn"         /bin/echo -e "Content-Type: Content-Type: text/plainrn"         /bin/echo -e "rn"         /bin/echo -e

17、"MySQL is running.rn"         /bin/echo -e "rn" else         # mysql is down, return http 503         /bin/echo -e "HTTP/1.1 503 Service Unavailablern"   &

18、#160;     /bin/echo -e "Content-Type: Content-Type: text/plainrn"         /bin/echo -e "rn"         /bin/echo -e "MySQL is *down*.rn"         /bi

19、n/echo -e "rn" 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         maxc

20、onn 40000         debug         #quiet         user haproxy         group haproxy         nbproc 1     &#

21、160;   log local3         spread-checks 2 defaults          timeout server  3s          timeout connect 3s          time

22、out client  60s          timeout http-request 3s          timeout queue   3s frontend db_read         bind 40:3306         d

23、efault_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