使用HAProxy对MySQL进行负载均衡和状态监控

By
24
Jun 2011

转载请保留原文内容，并声明转载地址：http://www.toplee.com/blog/1284.html

乐搜（lesoo.com)使用HAProxy已经很久了，但主要用在前端web请求的负载均衡和状态监控上，对于后端的Memcached以及MySQL却一直没有应用，最近对系统架构进行了小规模的整理，把MySQL也收编到了HAProxy下，经过一段时间的使用，体验还是不错的。

详细的HAProxy的安装配置我就不说了，主要针对HAProxy如何监控检测后端MySQL服务器的状态说两句。

我们的环境由两台master和若干台slave数据库组成，在HA的配置中，可以设置灵活的策略和进行slave的分组，如果系统更大了，将还会根据业务系统来拆分若干个master和slave组。

声明：以下内容应用和验证环境是基于 FreeBSD 8.2 环境，MySQL在5.1以上版本，理论上 Linux 的各个版本基本都能通过，如果有不能通过的情况，请别随意张口扔板砖，欢迎添加评论留言向我提问

HA对MySQL状态监控实现的方式很多，不过大多逻辑都一样，使用自己编写的脚本程序（可以是shell、php、perl等你熟悉的语言）监控mysql的运行情况，然后通过HTTP的方式输出运行结果给HA，我使用的是shell程序来实现的监控，结合 FreeBSD的inetd服务来实现监控端口的提供

1. 监控脚本编写

下面是我写的一段监控shell脚本，用于监控其中一台叫mdb1的master数据库replication运行：

  1. #!/usr/local/bin/bash
  2. MYSQL_HOST="mdb1"
  3. MYSQL_PORT="3307"
  4. MYSQL_USERNAME="michael"
  5. MYSQL_PASSWORD="michael@lesoo.com"
  6.  
  7. /usr/local/bin/mysql -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USERNAME -p$MYSQL_PASSWORD -e "show slave status\G;" > /tmp/check_mdb1_rep.txt
  8. iostat=`grep "Slave_IO_Running" /tmp/check_mdb1_rep.txt  |awk '{print $2}'`
  9. sqlstat=`grep "Slave_SQL_Running" /tmp/check_mdb1_rep.txt |awk '{print $2}'`
  10. #echo iostat:$iostat and sqlstat:$sqlstat
  11. if [ "$iostat" = "Yes" ] && [ "$sqlstat" = "Yes" ];
  12. then
  13. # mysql is fine, return http 200
  14. /bin/echo "HTTP/1.1 200 OK\r\n"
  15. /bin/echo "Content-Type: Content-Type: text/plain\r\n"
  16. /bin/echo "\r\n"
  17. /bin/echo "MySQL ($MYSQL_HOST:$MYSQL_PORT) replication is running.\r\n"
  18. /bin/echo "\r\n"
  19. else
  20. # mysql is down, return http 503
  21. /bin/echo "HTTP/1.1 503 Service Unavailable\r\n"
  22. /bin/echo "Content-Type: Content-Type: text/plain\r\n"
  23. /bin/echo "\r\n"
  24. /bin/echo "MySQL ($MYSQL_HOST:$MYSQL_PORT) replication  is *down*.\r\n"
  25. /bin/echo "\r\n"

上面的代码大概有点shell脚本编写经验的都能明白什么意思，就是用mysql 命令连接指定数据库并 -e 方式执行MySQL命令 “show slave status\G”，用以输出当前MySQL服务器的replication运行状况，里面会有两个主要的状态参数Slave_IO_Running和Slave_SQL_Running， 这两个参数用于表示当前数据库的replication运行是否正常，如果有一个是No，则表示数据库同步歇菜了，把状态据结果通过Linux的管道命令输出到 /tmp/check_mdb1_rep.txt文件（你可以自己随便改个文件名），再使用grep和awk命令把文件中想要的两个参数文本行读取出来并拆分，最后判断是否都是“Yes”， 对于正常情况，我们输出一个HTTP协议标准的header头信息，里面包含关键的Code 200或者Code 503，这个其实就是HAProxy监控最终需要的东西。

上面的解释如果有不明白的，可以留言来问我。

把以上这段代码保存为一个文件，比如 /bin/mdb1_replication.sh ，记得 chmod +x

2. 把脚本变成一个系统TCP监听服务

编写/etc/service文件，在末尾添加如下一行

  1. mdb1_replication 8822/tcp

这段代码的意思就是定义一个系统的服务，名称叫mdb1_replication， 占用8822端口，使用TCP协议访问（这里名称和端口可以自己定义，只要不和系统别的服务重名和端口重复即可）

然后编写/etc/inetd.conf，在文件末尾添加如下一行

  1. mdb1_replication stream tcp nowait root /bin/mdb1_replication.sh mdb1_replication

这段代码的意思是添加一个接受系统inetd管理的服务，服务名称是前面提到的mdb1_replication， 执行的脚本是 /bin/mdb1_replication.sh，其他的几个参数意思就自己man inetd.conf看看吧

然后运行修改运行 /etc/rc.d/inetd restart 启动服务

运行 netstat -anl |grep 8822 看看是否服务和端口起来了，没有起来的话，检查检查哪里出错了，看看系统日志。

最后执行 telnet localhost 8822，正常应该看到如下运行结果

  1. %telnet localhost 8822
  2. Trying 127.0.0.1...
  3. Connected to localhost.
  4. Escape character is '^]'.
  5. HTTP/1.1 200 OK\r\n
  6. Content-Type: Content-Type: text/plain\r\n
  7. \r\n
  8. MySQL (mdb2:3307) replication is running.\r\n
  9. \r\n
  10. Connection closed by foreign host.
  11. %

3. 配置Haproxy的监控

在HA配置文件中添加类似下面的部分内容

  1. listen mdb_3307 192.168.0.8:3307
  2. mode tcp
  3. maxconn 100
  4. balance roundrobin
  5. option httpchk OPTIONS * HTTP/1.1\r\nHost:\ www
  6. server www1:3307 www1:3307 weight 1 check port 9922 inter 2s rise 2 fall 2
  7. server mdb2:3307 mdb2:3307 weight 3 check port 9922 inter 2s rise 2 fall 2 backup
  8. option tcpka

上面这段配置我就不过多说明了，这是HAProxy的配置语法，如果对HA有不明白的地方，欢迎讨论。

补充： 目前我们的系统已经实现了 Nginx / Memcached / Sphinx / MySQL 等各个后端应用的负载均衡和状态监控，还有短信报警等环节，有兴趣讨论交流的，欢迎和我联络！

Category: Web架构/负载/性能  Tags: , , , , ,
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
25 Responses
  1. cfanbo says:
    June 24, 2011 at 7:32 pm  (Quote)

    监控类的直接用cacti或者nagios进行不是更方便么?

  2. Michael says:
    June 24, 2011 at 9:21 pm  (Quote)

    监控类的直接用cacti或者nagios进行不是更方便么?

    这不为了要实现状态监控的同时进行实时的负载均衡策略调整么，cacti那样的是用在别处的，我们也假设了类似的监控系统

  3. wwek says:
    June 25, 2011 at 2:25 am  (Quote)

    最近在看ha方面的东西。感谢·

  4. paul says:
    June 25, 2011 at 9:33 am  (Quote)

    如何基于haproxy做七层负载均衡，是否有相关实例可以分享一下？查阅过一些资料，未能明白究竟。还望赐教

  5. Michael says:
    June 25, 2011 at 6:47 pm  (Quote)

    如何基于haproxy做七层负载均衡，是否有相关实例可以分享一下？查阅过一些资料，未能明白究竟。还望赐教

    我一直都是参考这个文档进行配置的： http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
    实话实说，HAProxy对于中型和普通大型网站来说，是很不错的选择，7层交换的配置非常灵活，有很好的正则表达式支持，当然，熟悉Nginx的话，也可以用Nginx做很多工作，再配合Squid和Varnish做反向代理和缓存。

  6. kong says:
    June 29, 2011 at 4:51 pm  (Quote)

    网站改版了？
    有美工介绍么呵呵

  7. Michael says:
    June 30, 2011 at 4:29 pm  (Quote)

    网站改版了？
    有美工介绍么呵呵

    哈哈，说句实在话，我们现在还需要一名中等的美术。。。

  8. 北京装修 says:
    July 4, 2011 at 2:29 pm  (Quote)

    感觉好专业

  9. 猪猪 says:
    July 14, 2011 at 10:05 am  (Quote)

    Michael 编程可以，美工学要加强

  10. jason says:
    August 22, 2011 at 1:36 pm  (Quote)

    mysql多master同步，还是有问题啊，虽然mysql提供了主键offset，但不能解决表中非主键值的更新和多值主键的问题，除非程序逻辑上有大的改动，不知博主是否发现

  11. 奶粉网 says:
    September 3, 2011 at 1:52 pm  (Quote)

    在学校没有雪编程很是后悔！！····

  12. 第五代官网 says:
    September 21, 2011 at 5:33 pm  (Quote)

    确实专业的很 期待你的更多作品

  14. 第五代官网 says:
    September 22, 2011 at 2:43 am  (Quote)

    快点更新哦~~~

  15. 网资 says:
    October 4, 2011 at 12:18 am  (Quote)

    楼主该更新博客咯~~~

  16. the north face says:
    October 8, 2011 at 3:35 pm  (Quote)

    一直认为mssql比mysql性能好。现在想想是自己对mysql了解的太少了！

  17. 罗莱家纺官网 says:
    October 13, 2011 at 4:43 pm  (Quote)

    不是很懂 但看上去很专业 支持你！

  18. JeremyWei says:
    October 18, 2011 at 9:19 pm  (Quote)

    mdb1_replication 监听的是8822端口，HAProxy里边的healthchek怎么检查的是9922端口？

  19. feather extensions says:
    October 18, 2011 at 10:03 pm  (Quote)

    嗯~进来学习一下~

  20. 1 says:
    November 8, 2011 at 5:22 pm  (Quote)

    监听写错了，8822和9922.呵呵
    学习了

  21. liuxuejin says:
    January 20, 2012 at 2:22 pm  (Quote)

    您好：haproxy的4层如何？跟lvs的四层有什么区别啊？ 貌似 haproxy的四层只是 转发而已。跟lvs的NAT 是差不多？期待回答啊！

  22. Michael says:
    January 31, 2012 at 6:37 pm  (Quote)

    您好：haproxy的4层如何？跟lvs的四层有什么区别啊？ 貌似 haproxy的四层只是 转发而已。跟lvs的NAT 是差不多？期待回答啊！

    如果要使用四层交换，还是建议LVS，毕竟是内核级别的，效率会更好，而且LVS支持的四层交换有三种方式，会比较灵活，基本可以达到硬件交换的能力了，而使用haproxy更多的还是使用7层交换的灵活性，在7层负载均衡方面，haproxy可以做到转发也就是类似DR的模式，但是四层也就是常说的TCP模式下，不能实现DR，仅能实现NAT的工作模式。

  23. timberland roll top boots says:
    November 23, 2012 at 6:10 pm  (Quote)

    First, we will tell in our short story about the Timberland 6 Inch boots intended for mountaineering campaigns. These timberland roll top boots consist of two parts. External part of the boot is made of shock-resistant plastic and it is designed with aim to protect the leg against external damages and rigid fixing of a foot (it is essential in case of overcoming slippery surfaces or descent on skis).

  24. rayban sunglasses online says:
    March 18, 2013 at 9:56 pm  (Quote)

    is therefore r under a case than it already was. ppHow with abundant a case I communicateed LG Samarticulate wheadgear trecipient TVs and Paggregationtransonic surveys were for amountd. Wheadgear Heras they Tolde

  25. yang says:
    April 9, 2013 at 6:12 pm  (Quote)

    [root@localhost ssh]# telnet 192.168.100.30 8822
    Trying 192.168.100.30…
    Connected to 192.168.100.30 (192.168.100.30).
    Escape character is ‘^]’.
    Connection closed by foreign host.

    [root@localhost ssh]# netstat -lnp|grep 8822
    tcp 0 0 0.0.0.0:8822 0.0.0.0:* LISTEN 10497/xinetd
    能帮看下哪里出错吗？

  29. minecraft says:
    October 8, 2018 at 11:41 am  (Quote)

    With havin so much written content do you ever run into any issues of plagorism or
    copyright violation? My site has a lot of unique content I’ve either authored myself or outsourced but it appears a lot
    of it is popping it up all over the internet without my permission. Do
    you know any solutions to help stop content from being ripped off?
    I’d truly appreciate it.

  30. minecraft free download 2018 says:
    October 8, 2018 at 5:45 pm  (Quote)

    Hi, just wanted to tell you, I loved this article. It was funny.
    Keep on posting!

  31. minecraft free download 2018 says:
    October 8, 2018 at 5:49 pm  (Quote)

    Thanks a bunch for sharing this with all folks you actually know what you are
    talking approximately! Bookmarked. Please additionally seek advice from my website =).
    We can have a hyperlink trade arrangement among us

  32. minecraft free download 2018 says:
    October 8, 2018 at 8:12 pm  (Quote)

    Hi, i feel that i noticed you visited my blog so i got here to return the desire?.I am trying to to
    find things to improve my site!I guess its adequate to make use of some of your ideas!!

  33. minecraft free download 2018 says:
    October 9, 2018 at 10:56 am  (Quote)

    An intriguing discussion is worth comment. I do believe that
    you should publish more about this topic, it may not
    be a taboo subject but generally people do
    not speak about these subjects. To the next! Kind regards!!

  34. minecraft free download 2018 says:
    October 10, 2018 at 4:09 pm  (Quote)

    Saved as a favorite, I love your website!

  35. minecraft free download 2018 says:
    October 11, 2018 at 10:13 pm  (Quote)

    You’re so interesting! I do not believe I’ve read through a single thing like
    this before. So nice to find someone with unique thoughts on this topic.
    Seriously.. many thanks for starting this up.
    This web site is something that’s needed on the internet, someone with some originality!

  36. tinder dating site says:
    October 16, 2018 at 8:22 pm  (Quote)

    Amazing! This blog looks exactly like my old one! It’s on a
    totally different subject but it has pretty much the same page layout
    and design. Superb choice of colors!

  39. minecraft secret free download says:
    October 18, 2018 at 8:50 pm  (Quote)

    I visited many blogs however the audio quality for
    audio songs present at this site is truly fabulous.

  40. minecraft secret free download says:
    October 19, 2018 at 6:08 am  (Quote)

    I’m impressed, I have to admit. Rarely do I encounter a blog that’s equally educative and entertaining,
    and let me tell you, you’ve hit the nail on the head.
    The issue is something which not enough folks are speaking intelligently about.
    Now i’m very happy I found this in my hunt for something relating to this.

  43. Health Benefits of Coconut Oil says:
    October 25, 2018 at 7:38 am  (Quote)

    Hey! Someone in my Myspace group shared this site with us so I came to check it out.
    I’m definitely loving the information. I’m bookmarking and will be tweeting this to my followers!
    Outstanding blog and brilliant style and design.

  44. Coconut Oil says:
    October 26, 2018 at 3:04 am  (Quote)

    This paragraph will assist the internet visitors for creating new website or even a blog from start to end.

  45. Health Benefits of Coconut Oil says:
    October 29, 2018 at 2:18 pm  (Quote)

    That is really interesting, You’re a very professional blogger.
    I have joined your rss feed and look forward to in the hunt for extra
    of your excellent post. Additionally, I have shared your site in my social networks

  46. Coconut Oil says:
    October 30, 2018 at 6:01 am  (Quote)

    Fantastic post however I was wondering if you could write a litte more on this
    subject? I’d be very thankful if you could elaborate a little bit
    further. Bless you!

  47. descargar facebook says:
    November 3, 2018 at 2:35 am  (Quote)

    Admiring the time and effort you put into your blog and in depth information you present.
    It’s nice to come across a blog every once in a while that isn’t the
    same outdated rehashed material. Excellent read! I’ve saved your site and I’m adding your RSS feeds to my Google account.

  48. quest bars cheap says:
    November 4, 2018 at 2:07 am  (Quote)

    Hi there, for all time i used to check weblog
    posts here in the early hours in the break of day, for the reason that
    i like to find out more and more.

  49. quest bars cheap says:
    November 4, 2018 at 3:53 am  (Quote)

    It’s very effortless to find out any topic on web as compared to textbooks,
    as I found this article at this web site.

  50. quest bars cheap says:
    November 4, 2018 at 4:00 pm  (Quote)

    I have been exploring for a little bit for any high-quality articles or weblog posts in this sort of house .
    Exploring in Yahoo I eventually stumbled upon this website.
    Reading this information So i’m satisfied to exhibit that I’ve an incredibly excellent
    uncanny feeling I came upon just what I needed.
    I such a lot undoubtedly will make certain to do not forget this
    web site and provides it a glance regularly.

  51. Quest Bars says:
    November 9, 2018 at 7:43 pm  (Quote)

    We are a gaggle of volunteers and opening a new scheme in our
    community. Your website offered us with valuable information to work on. You’ve
    performed a formidable process and our whole community
    will be thankful to you.

  52. What Equipment is Needed for Sling TV says:
    November 10, 2018 at 7:44 am  (Quote)

    Does your blog have a contact page? I’m having problems locating it but, I’d like to
    send you an email. I’ve got some recommendations for your
    blog you might be interested in hearing. Either way,
    great blog and I look forward to seeing it expand over time.

  53. What Equipment is Needed for Sling TV says:
    November 13, 2018 at 1:55 am  (Quote)

    Hello there, You’ve done a great job. I’ll definitely digg it and personally recommend to
    my friends. I’m confident they’ll be benefited from
    this web site.

  54. Sling TV says:
    November 15, 2018 at 10:29 pm  (Quote)

    Great website you have here but I was wanting to know if you
    knew of any forums that cover the same topics talked about in this article?
    I’d really like to be a part of community where I can get responses from other
    knowledgeable individuals that share the same interest.
    If you have any suggestions, please let me know.

    Thank you!

  55. What Equipment is Needed for Sling TV says:
    November 19, 2018 at 1:53 am  (Quote)

    Helpful information. Fortunate me I found your website by accident, and I am surprised why this coincidence did not took
    place in advance! I bookmarked it.

  56. What Equipment is Needed for Sling TV says:
    November 20, 2018 at 12:42 am  (Quote)

    Attractive section of content. I just stumbled
    upon your website and in accession capital to assert that I acquire in fact enjoyed
    account your blog posts. Any way I will be subscribing to
    your feeds and even I achievement you access consistently fast.

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image

«
»