mysql负载均衡完美解决方案haproxy

mysql负载均衡完美解决方案V1.01.环境：mysql 5ubuntu10.04 x86_64mdb1 eth0 192.168.5.11mdb2 eth0 192.168.5.12sdb1 eth0 192.168.5.21sdb2 eth0 192.168.5.22sdb3 eth0 192.168.5.23sdb4 eth0 192.168.5.24haproxyeth0 192.168.5.10 (mdb vip write)eth1 192.168.5.20 (sdb vip read)说明：mdb vip用于DB的写，sdb vip用于DB读，实现读写分离和负载均衡，带故障检测自动切换2.架构图web1 web2 web3| | |—————————-|haproxy(lb db write/read)|———————————-| |mdb1 mdb2| |————–—————-| | | |sdb1 sdb2 sdb3 sdb4说明：1）mdb1和mdb1配置成主-主模式，相互同步,通过haproxy提供一个lb的写ip2）sdb1和sdb2配置为mdb1的从，sdb3和sdb4配置为mdb2的从3）sdb1,sdb2,sdb3,sdb4这4台从库，通过haproxy提供一个lb的读ip4) 当mdb2停止复制，mdb1为主库，haproxy停止发送请求到mdb2和sdb3,sdb45) 当mdb1停止复制，mdb2为主库，haproxy停止发送请求到mdb1和sdb1,sdb26) 当mdb1和mdb2同时停止复制，这时2台主库变成readonly模式，数据库不能写入7）当mdb2 offline时，mdb1进入backup mode,停止发送请求到mdb2,sdb3,sdb48）当mdb1 offline时，mdb2进入backup mode,停止发送请求到mdb1,sdb1,sdb29) 当mdb1 mdb2同时offline,整个DB停止工作3.安装mysql-server登录mdb1,mdb2,sdb1,sdb2,sdb3,sdb4，输入以下命令进行安装：apt-get install mysql-server -y安装时会提示输入mysql root用户密码，输入修改mysql配置，监听所有接口vi /etc/mysql/f修改为：bind-address = 0.0.0.0重启mysql/etc/init.d/mysql restart4.配置mdb1,mdb2主-主同步1)mdb1:vi /etc/mysql/fserver-id = 1log_bin = mysql-binlog-slave-updates #很重要，从前一台机器上同步过来的数据才能同步到下一台机器expire_logs_days = 10max_binlog_size = 100Mauto_increment_offset = 1auto_increment_increment = 22)mdb2:vi /etc/mysql/fserver-id = 2log_bin = mysql-binlog-slave-updates #很重要，从前一台机器上同步过来的数据才能同步到下一台机器expire_logs_days = 10max_binlog_size = 100Mauto_increment_offset = 2auto_increment_increment = 23)mdb1和mdb2:重启mysql/etc/init.d/mysql restart添加复制用户mysql -uroot GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.5.%’ IDENTIFIED BY ‘gaojinbo’;记录日志文件和posmysql -uroot show master status\G4)mdb1:change master tomaster_host=’192.168.5.12′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000003′,master_log _pos=106;start slave;show slave status\G说明：mysql-bin.000003和106是主库配置第3)步记录的信息出现以下内容，说明同步okSlave_IO_Running: YesSlave_SQL_Running: Yes5)mdb2:change master tomaster_host=’192.168.5.11′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000001′,master_log _pos=249;start slave;show slave status\G说明：mysql-bin.000001和249是主库配置第3)步记录的信息出现以下内容，说明同步okSlave_IO_Running: YesSlave_SQL_Running: Yes6）测试主-主同步mdb1:mysql -uroot show databases;create database gaojinbo;mdb2:mysql -uroot show databases;即可看到在mdb1上建立的数据库gaojinbo至此mdb1,mdb2主-主配置完成！5.4台从库配置sdb1-4配置(注：server-id不能相同)：vi /etc/mysql/fserver-id = 3log_bin = mysql-bin重启mysql/etc/init.d/mysql restartsdb1和sdb2配置成mdb1的从库:mysql -uroot change master tomaster_host=’192.168.5.11′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000001′,master_log _pos=345;start slave;show slave status\G说明：mysql-bin.000001和345是主库配置第3)步记录的信息出现以下内容，说明同步okSlave_IO_Running: YesSlave_SQL_Running: Yessdb3和sdb4配置成mdb2的从库:change master tomaster_host=’192.168.5.12′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000003′,master_log _pos=106;start slave;show slave status\G说明：mysql-bin.000003和106是主库配置第3)步记录的信息出现以下内容，说明同步okSlave_IO_Running: YesSlave_SQL_Running: Yes测试：mdb1:mysql -uroot show databases;create database eossc;在其他DB上，这时会看到刚建立的数据库eossc至此4台从数据库配置完成！6.编写mysql检测脚本1）mdb1和mdb2:vi /etc/xinetd.d/mysqlchk## /etc/xinetd.d/mysqlchk#service mysqlchk_write{flags = REUSEsocket_type = streamport = 9200wait = nouser = nobodyserver = /opt/mysqlchk_status.shlog_on_failure += USERIDdisable = noonly_from = 192.168.5.0/24 # recommended to put the IPs that need# to connect exclusively (security purposes)}service mysqlchk_replication{flags = REUSEsocket_type = streamport = 9201wait = nouser = nobodyserver = /opt/mysqlchk_replication.shlog_on_failure += USERIDdisable = noonly_from = 192.168.5.0/24 # recommended to put the IPs that need# to connect exclusively (security purposes)}添加服务端口vi /etc/servicesmysqlchk_write 9200/tcp #mysqlchk_writemysqlchk_replication 9201/tcp #mysqlchk_replicationmdb1上操作：vi /opt/mysqlchk_status.sh#!/bin/bashMYSQL_HOST="192.168.5.11"MYSQL_PORT="3306"MYSQL_USERNAME="root"MYSQL_PASSWORD=""ERROR_MSG=`/usr/bin/mysql –host=$MYSQL_HOST –port=$MYSQL_PORT –user=$MYSQL_USERNAME –password=$MYSQL_PASSWORD -e "show databases;" 2>/dev/null`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"fivi /opt/mysqlchk_replication.sh#!/bin/bashMYSQL_HOST="192.168.5.11"MYSQL_PORT="3306"MYSQL_USERNAME="root"MYSQL_PASSWORD=""/usr/bin/mysql –host=$MYSQL_HOST –port=$MYSQL_PORT –user=$MYSQL_USERNAME –password=$MYSQL_PASSWORD -e "show slave status\G;" >/tmp/check_repl.txtiostat=`grep "Slave_IO_Running" /tmp/check_repl.txt |awk ‘{print $2}’ `sqlstat=`grep "Slave_SQL_Running" /tmp/check_repl.t xt |awk ‘{print $2}’ `#echo iostat:$iostat and sqlstat:$sqlstatif [ "$iostat" = "No" ] || [ "$sqlstat" = "No" ];then# 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 replication is *down*.\r\n"/bin/echo -e "\r\n"else# 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 replication is running.\r\n"/bin/echo -e "\r\n"fi测试同步检测脚本：mysql -uroot stop slave sql_thread; #或者stop slave io_thread;/opt/mysqlchk_replication.shmdb2上操作：添加和mdb1一样的脚本，把/opt/mysqlchk_status.sh 里面的192.168.5.11修改为192.168.5.12/opt/mysqlchk_replication.sh 里面的192.168.5.11修改为192.168.5.122）sdb1,sdb2,sdb3,sdb4上操作：vi /etc/xinetd.d/mysqlchk## /etc/xinetd.d/mysqlchk#service mysqlchk_replication{flags = REUSEsocket_type = streamport = 9201wait = nouser = nobodyserver = /opt/mysqlchk_replication.shlog_on_failure += USERIDdisable = noonly_from = 192.168.5.0/24 # recommended to put the IPs that need# to connect exclusively (security purposes)}vi /opt/mysqlchk_replication.sh#!/bin/bashMYSQL_HOST="192.168.5.21"MYSQL_PORT="3306"MYSQL_USERNAME="root"MYSQL_PASSWORD=""/usr/bin/mysql –host=$MYSQL_HOST –port=$MYSQL_PORT –user=$MYSQL_USERNAME –password=$MYSQL_PASSWORD -e "show slave status\G;" >/tmp/check_repl.txtiostat=`grep "Slave_IO_Running" /tmp/check_repl.txt |awk ‘{print $2}’ `sqlstat=`grep "Slave_SQL_Running" /tmp/check_repl.txt |awk ‘{print $2}’ `#echo iostat:$iostat and sqlstat:$sqlstatif [ "$iostat" = "No" ] || [ "$sqlstat" = "No" ];then# 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 replication is *down*.\r\n"/bin/echo -e "\r\n"else# 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 replication is running.\r\n"/bin/echo -e "\r\n"fi注：脚本/opt/mysqlchk_replication.sh里面的ipsdb1 MYSQL_HOST="192.168.5.21"sdb2 MYSQL_HOST="192.168.5.22"sdb3 MYSQL_HOST="192.168.5.23"sdb4 MYSQL_HOST="192.168.5.24"添加服务端口vi /etc/servicesmysqlchk_replication 9201/tcp #mysqlchk_replication3)所有DB上操作：增加检测脚本执行权限chmod +x /opt/mysql*.sh重启系统reboot查看监听端口netstat -antup|grep xinetdtcp 0 0 0.0.0.0:9200 0.0.0.0:* LISTEN 903/xinetd tcp 0 0 0.0.0.0:9201 0.0.0.0:* LISTEN 903/xinetd 注：sdb只有9201监听7.haproxy安装配置下载编译安装：tar xvzf haproxy-1.4.11.tar.gzcd haproxy-1.4.11make TARGET=linux26 ARCH=x86_64make install配置vi /etc/haproxy.cfgglobalmaxconn 40000debug#quietuser haproxygroup haproxynbproc 1log 127.0.0.1 local3spread-checks 2defaultstimeout server 3stimeout connect 3stimeout client 60stimeout http-request 3stimeout queue 3sfrontend db_writebind 192.168.5.10:3306default_backend cluster_db_writefrontend db_readbind 192.168.5.20:3306default_backend cluster_db_readfrontend web_haproxy_statusbind :80default_backend web_statusfrontend monitor_mdb1bind 127.0.0.1:9301mode httpacl no_repl_mdb1 nbsrv(mdb1_replication) eq 0acl no_repl_mdb2 nbsrv(mdb2_replication) eq 0acl no_mdb1 nbsrv(mdb1_status) eq 0acl no_mdb2 nbsrv(mdb2_status) eq 0monitor-uri /dbsmonitor fail unless no_repl_mdb1 no_repl_mdb2 no_mdb2 monitor fail if no_mdb1 no_mdb2frontend monitor_mdb2bind 127.0.0.1:9302mode httpacl no_repl_mdb1 nbsrv(mdb1_replication) eq 0acl no_repl_mdb2 nbsrv(mdb2_replication) eq 0acl no_mdb1 nbsrv(mdb1_status) eq 0acl no_mdb2 nbsrv(mdb2_status) eq 0monitor-uri /dbsmonitor fail unless no_repl_mdb1 no_repl_mdb2 no_mdb1 monitor fail if no_mdb1 no_mdb2frontend monitor_sdb1bind 127.0.0.1:9303mode httpacl no_repl_sdb1 nbsrv(sdb1_replication) eq 0acl no_repl_mdb1 nbsrv(mdb1_replication) eq 0acl no_mdb2 nbsrv(mdb2_status) eq 1monitor-uri /dbsmonitor fail if no_repl_sdb1monitor fail if no_repl_mdb1 no_mdb2frontend monitor_sdb2bind 127.0.0.1:9304mode httpacl no_repl_sdb2 nbsrv(sdb2_replication) eq 0acl no_repl_mdb1 nbsrv(mdb1_replication) eq 0acl no_mdb2 nbsrv(mdb2_status) eq 1monitor-uri /dbsmonitor fail if no_repl_sdb2monitor fail if no_repl_mdb1 no_mdb2frontend monitor_sdb3bind 127.0.0.1:9305mode httpacl no_repl_sdb3 nbsrv(sdb3_replication) eq 0acl no_repl_mdb2 nbsrv(mdb2_replication) eq 0acl no_mdb1 nbsrv(mdb1_status) eq 1monitor-uri /dbsmonitor fail if no_repl_sdb3monitor fail if no_repl_mdb2 no_mdb1frontend monitor_sdb4bind 127.0.0.1:9306mode httpacl no_repl_sdb4 nbsrv(sdb4_replication) eq 0acl no_repl_mdb2 nbsrv(mdb2_replication) eq 0acl no_mdb1 nbsrv(mdb1_status) eq 1monitor-uri /dbsmonitor fail if no_repl_sdb4monitor fail if no_repl_mdb2 no_mdb1frontend monitor_splitbrainbind 127.0.0.1:9300mode httpacl no_repl01 nbsrv(mdb1_replication) eq 0acl no_repl02 nbsrv(mdb2_replication) eq 0acl mdb1 nbsrv(mdb1_status) eq 1acl mdb2 nbsrv(mdb2_status) eq 1monitor-uri /dbsmonitor fail unless no_repl01 no_repl02 mdb1 mdb2backend mdb1_replicationmode tcpbalance roundrobinoption tcpkaoption httpchkserver mdb1 192.168.5.11:3306 check port 9201 inter 1s rise 1 fall 1backend mdb2_replicationmode tcpbalance roundrobinoption tcpkaoption httpchkserver mdb2 192.168.5.12:3306 check port 9201 inter 1s rise 1 fall 1 backend sdb1_replicationmode tcpbalance roundrobinoption tcpkaoption httpchkserver sdb1 192.168.5.21:3306 check port 9201 inter 1s rise 1 fall 1 backend sdb2_replicationmode tcpbalance roundrobinoption tcpkaoption httpchkserver sdb2 192.168.5.22:3306 check port 9201 inter 1s rise 1 fall 1 backend sdb3_replicationmode tcpbalance roundrobinoption tcpkaoption httpchkserver sdb3 192.168.5.23:3306 check port 9201 inter 1s rise 1 fall 1 backend sdb4_replicationmode tcpbalance roundrobinoption tcpkaoption httpchkserver sdb4 192.168.5.24:3306 check port 9201 inter 1s rise 1 fall 1 backend mdb1_statusmode tcpbalance roundrobinoption tcpkaoption httpchkserver mdb1 192.168.5.11:3306 check port 9200 inter 1s rise 2 fall 2backend mdb2_statusmode tcpbalance roundrobinoption tcpkaoption httpchkserver mdb2 192.168.5.12:3306 check port 9200 inter 1s rise 2 fall 2backend cluster_db_writemode tcpoption tcpkabalance roundrobinoption httpchk GET /dbsserver mdb1 192.168.5.11:3306 weight 1 check port 9201 inter 1s rise 5 fall 1server mdb2 192.168.5.12:3306 weight 1 check port 9201 inter 1s rise 5 fall 1 backupserver mdb1_backup 192.168.5.11:3306 weight 1 check port 9301 inter 1s rise 2 fall 2 addr 127.0.0.1 backup server mdb2_backup 192.168.5.12:3306 weight 1 check port 9302 inter 1s rise 2 fall 2 addr 127.0.0.1 backup backend cluster_db_readmode tcpoption tcpkabalance roundrobinoption httpchk GET /dbsserver mdb1 192.168.5.11:3306 weight 1 track cluster_db_write/mdb1server mdb2 192.168.5.12:3306 weight 1 track cluster_db_write/mdb2server mdb1_backup 192.168.5.11:3306 weight 1 track cluster_db_write/mdb1_backupserver mdb2_backup 192.168.5.12:3306 weight 1 track cluster_db_write/mdb2_backupserver mdb1_splitbrain 192.168.5.11:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1 server mdb2_splitbrain 192.168.5.12:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1server sdb1_slave 192.168.5.21:3306 weight 1 check port 9303 inter 1s rise 5 fall 1 addr 127.0.0.1server sdb2_slave 192.168.5.22:3306 weight 1 check port 9304 inter 1s rise 5 fall 1 addr 127.0.0.1server sdb3_slave 192.168.5.23:3306 weight 1 check port 9305 inter 1s rise 5 fall 1 addr 127.0.0.1server sdb4_slave 192.168.5.24:3306 weight 1 check port 9306 inter 1s rise 5 fall 1 addr 127.0.0.1backend web_statusmode httpstats enable# stats scope# stats hide-versionstats refresh 5sstats uri /statusstats realm Haproxy\ statisticsstats auth ylmf:gaojinbo8.测试1）正常情况，backup和splitbrain状态down2）停止mdb2复制，mdb2和sdb3,sdb4状态down，数据库仍可读写3)同时停止mdb1,mdb2复制，mdb1和sdb1,sdb2,sdb3,sdb4状态down，数据库只能读4）关闭mdb1数据库，mdb1,mdb2和sdb1,sdb2状态down，数据库仍可读写5）关闭mdb2数据库，mdb1,mdb2和sdb3,sdb4状态down，数据库仍可读写完成！。