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

mysql负载均衡完美解决方案V1.01.环境：mysql5ubuntu10.04x86_64mdb1eth0192.168.5.11mdb2eth0192.168.5.12sdb1eth0192.168.5.21sdb2eth0192.168.5.22sdb3eth0192.168.5.23sdb4eth0192.168.5.24haproxyeth0192.168.5.10(mdbvipwrite)eth1192.168.5.20(sdbvipread)说明：mdbvip用于DB的写，sdbvip用于DB读，实现读写分离和负载均衡，带故障检测自动切换2.架构图web1web2web3|||—————————-|haproxy(lbdbwrite/read)|———————————-||mdb1mdb2||————–—————-||||sdb1sdb2sdb3sdb4说明：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）当mdb2offline时，mdb1进入backupmode,停止发送请求到mdb2,sdb3,sdb48）当mdb1offline时，mdb2进入backupmode,停止发送请求到mdb1,sdb1,sdb29)当mdb1mdb2同时offline,整个DB停止工作3.安装mysql-server登录mdb1,mdb2,sdb1,sdb2,sdb3,sdb4，输入以下命令进行安装：apt-getinstallmysql-server-y安装时会提示输入mysqlroot用户密码，输入gaojinbo.com修改mysql配置，监听所有接口vi/etc/mysql/my.cnf修改为：bind-address=0.0.0.0重启mysql/etc/init.d/mysqlrestart4.配置mdb1,mdb2主-主同步1)mdb1:vi/etc/mysql/my.cnfserver-id=1log_bin=mysql-binlog-slave-updates#很重要，从前一台机器上同步过来的数据才能同步到下一台机器expire_logs_days=10max_binlog_size=100Mauto_increment_offset=1auto_increment_increment=22)mdb2:vi/etc/mysql/my.cnfserver-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/mysqlrestart添加复制用户mysql-uroot-pgaojinbo.comGRANTREPLICATIONSLAVEON*.*TO‘repl’@’192.168.5.%’IDENTIFIEDBY‘gaojinbo’;记录日志文件和posmysql-uroot-pgaojinbo.comshowmasterstatus\G4)mdb1:changemastertomaster_host=’192.168.5.12′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000003′,master_log_pos=106;startslave;showslavestatus\G说明：mysql-bin.000003和106是主库配置第3)步记录的信息出现以下内容，说明同步okSlave_IO_Running:YesSlave_SQL_Running:Yes5)mdb2:changemastertomaster_host=’192.168.5.11′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000001′,master_log_pos=249;startslave;showslavestatus\G说明：mysql-bin.000001和249是主库配置第3)步记录的信息出现以下内容，说明同步okSlave_IO_Running:YesSlave_SQL_Running:Yes6）测试主-主同步mdb1:mysql-uroot-pgaojinbo.comshowdatabases;createdatabasegaojinbo;mdb2:mysql-uroot-pgaojinbo.comshowdatabases;即可看到在mdb1上建立的数据库gaojinbo至此mdb1,mdb2主-主配置完成！5.4台从库配置sdb1-4配置(注：server-id不能相同)：vi/etc/mysql/my.cnfserver-id=3log_bin=mysql-bin重启mysql/etc/init.d/mysqlrestartsdb1和sdb2配置成mdb1的从库:mysql-uroot-pgaojinbo.comchangemastertomaster_host=’192.168.5.11′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000001′,master_log_pos=345;startslave;showslavestatus\G说明：mysql-bin.000001和345是主库配置第3)步记录的信息出现以下内容，说明同步okSlave_IO_Running:YesSlave_SQL_Running:Yessdb3和sdb4配置成mdb2的从库:changemastertomaster_host=’192.168.5.12′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000003′,master_log_pos=106;startslave;showslavestatus\G说明：mysql-bin.000003和106是主库配置第3)步记录的信息出现以下内容，说明同步okSlave_IO_Running:YesSlave_SQL_Running:Yes测试：mdb1:mysql-uroot-pgaojinbo.comshowdatabases;createdatabaseeossc;在其他DB上，这时会看到刚建立的数据库eossc至此4台从数据库配置完成！6.编写mysql检测脚本1）mdb1和mdb2:vi/etc/xinetd.d/mysqlchk##/etc/xinetd.d/mysqlchk#servicemysqlchk_write{flags=REUSEsocket_type=streamport=9200wait=nouser=nobodyserver=/opt/mysqlchk_status.shlog_on_failure+=USERIDdisable=noonly_from=192.168.5.0/24#recommendedtoputtheIPsthatneed#toconnectexclusively(securitypurposes)}servicemysqlchk_replication{flags=REUSEsocket_type=streamport=9201wait=nouser=nobodyserver=/opt/mysqlchk_replication.shlog_on_failure+=USERIDdisable=noonly_from=192.168.5.0/24#recommendedtoputtheIPsthatneed#toconnectexclusively(securitypurposes)}添加服务端口vi/etc/servicesmysqlchk_write9200/tcp#mysqlchk_writemysqlchk_replication9201/tcp#mysqlchk_replicationmdb1上操作：vi/opt/mysqlchk_status.sh#!/bin/bashMYSQL_HOST=192.168.5.11MYSQL_PORT=3306MYSQL_USERNAME=rootMYSQL_PASSWORD=gaojinbo.comERROR_MSG=`/usr/bin/mysql–host=$MYSQL_HOST–port=$MYSQL_PORT–user=$MYSQL_USERNAME–password=$MYSQL_PASSWORD-eshowdatabases;2/dev/null`if[$ERROR_MSG!=]then#mysqlisfine,returnhttp200/bin/echo-eHTTP/1.1200OK\r\n/bin/echo-eContent-Type:Content-Type:text/plain\r\n/bin/echo-e\r\n/bin/echo-eMySQLisrunning.\r\n/bin/echo-e\r\nelse#mysqlisdown,returnhttp503/bin/echo-eHTTP/1.1503ServiceUnavailable\r\n/bin/echo-eContent-Type:Content-Type:text/plain\r\n/bin/echo-e\r\n/bin/echo-eMySQLis*down*.\r\n/bin/echo-e\r\nfivi/opt/mysqlchk_replication.sh#!/bin/bashMYSQL_HOST=192.168.5.11MYSQL_PORT=3306MYSQL_USERNAME=rootMYSQL_PASSWORD=gaojinbo.com/usr/bin/mysql–host=$MYSQL_HOST–port=$MYSQL_PORT–user=$MYSQL_USERNAME–password=$MYSQL_PASSWORD-eshowslavestatus\G;/tmp/check_repl.txtiostat=`grepSlave_IO_Running/tmp/check_repl.txt|awk‘{print$2}’`sqlstat=`grepSlave_SQL_Running/tmp/check_repl.txt|awk‘{print$2}’`#echoiostat:$iostatandsqlstat:$sqlstatif[$iostat=No]||[$sqlstat=No];then#mysqlisdown,returnhttp503/bin/echo-eHTTP/1.1503ServiceUnavailable\r\n/bin/e