haproxy系列—定制mysql健康检查程序.docx

随着生产环境用户访问的与日俱增，数据库访问压力也随之增大，近期准备对生产环境中的单一数据库进行扩容，增加多台slave数据库,进行读写分离操作，经过若干测试，最终选择使用Haproxy方案，在haproxy版本选型时，选择了1.4的版本，因为在1.4.9开始，haproxy增加了option mysql-check 健康检查功能，其工作原理是建立对应的Mysql连接，然后断开来判断数据库当前的健康状况。而实际生产环境中对数据一致性要求较高，不仅需要对mysql进行健康检查，也需要的slave节点的repliation状态(Slave_IO_Running,Slave_SQL_Running,Seconds_Behind_Master)进行检查，故现阶段的haproxy健康检查功能无法满足需求，需自己进行定制.其检查流程为:(1) 检查时指定需要检查的主机，端口，用户名，密码（可为空），同时需要指定检查的数据库类型（对于master类型数据库，则只需连接进去然后通过”select version()”来检查健康状态，对于slave类型的数据库，则需要增加”show slave status” replication状态检查）(2)结果采用HTTP方式返回，指定不同的状态码对应不同的检查状态，如下所示： 200 为服务器端健康检查OK(健康)-401 为检查段MySQLdb python模块没有安装（此程序采用python开发）405 为当前检查程序不支持该版本的mysqld (其主要原因是因为在不同的mysqld版本中，其”show slave status”的输出格式不同)400 为其他检查端未知异常-501 为服务端（被检查端）连接失败 (其原理是捕捉错误码为2003的mysql报错)502 为服务端（被检查端）认证失败（其原理是捕捉错误码为1045的mysql报错）503 为服务端（被检查端）连接成功，但是执行查询失败 （如 无权限等情况）504 为服务端（被检查端）连接成功，且该服务端为slave类型，此时replication状态无法查询（如没有启动slave）505 为服务端（被检查端）连接成功，且该服务端为slave类型，此时检查replication状态，显示”Slave_IO_Running”不等于”Yes”506 为服务端（被检查端）连接成功，且该服务端为slave类型，此时检查replication状态，显示”Slave_SQL_Running”不等于”Yes”507 为服务端（被检查端）连接成功，且该服务端为slave类型，此时检查replication状态，显示”Seconds_Behind_Master”超过了生产环境中可以承受的replication最大延迟时间具体代码如下:001#!/usr/bin/env python 002#coding = utf8 003#Author: pengyao 004005import sys,getopt 006007def _opts(args): 008get option009mysql_opts= 010opts,args = getopt.getopt(args,P:b:h:u:p:t:) 011012for opt, value in opts: 013#mysql server host 014if opt = -h: 015mysql_optshost = value 016#mysql server port 017elif opt = -P: 018mysql_optsport = value 019#mysql server user 020elif opt = -u: 021mysql_optsuser = value 022#mysql server password 023elif opt = -p: 024mysql_optspassword = value 025#mysql server type 026elif opt = -t: 027mysql_optstype = value 028#mysql slave alter behind_master 029elif opt = -b: 030mysql_optsalter_behind = value 031032return mysql_opts 033034def _response(resp_code): 035response header036state_lists = 037038state_lists200 = OK,Check OK 039040state_lists400 = Client failed, Client Unknow Failed 041state_lists401 = MySQLdb module failed, MySQLdb module is not install 042state_lists405 = Mysql version failed, Mysql Server version is not Allow 043044state_lists500 = Server failed, Server Unknow Failed 045state_lists501 = Connect failed, Mysql server connect failed 046state_lists502 = Auth failed, Mysql server auth failed 047state_lists503 = Query failed, Mysql server query falied 048state_lists504 = Slave failed, Mysql slave is not running 049state_lists505 = Slave IO failed, Mysql slave server IO thread is not running 050state_lists506 = Slave SQL failded, Mysql slave server SQL thread is not running 051state_lists507 = Slave Behind_master failed, Mysql slave server is behind master very long time 052053print HTTP/1.1 %s %sr %(resp_code,state_listsresp_code0) 054print Content-Type: text/plainr055print r056print %s!r %(state_listsresp_code1) 057print r058059sys.exit(0) 060061def _mysql_check(host, port, user, password, type, alter_behind): 062check mysql063try: 064import MySQLdb 065except ImportError: 066_response(401) 067except: 068_response(400) 069else: 070try: 071mysql_conn = MySQLdb.connect(host = host, port = int(port), user = user, passwd = password, db = information_schema) 072except MySQLdb.Error,e: 073if e.args0 = 2003: 074_response(501) 075elif e.args0 = 1045: 076_response(502) 077else: 078_response(500) 079else: 080#check mysql health 081try: 082mysql_cur = mysql_conn.cursor(MySQLdb.cursors.DictCursor) 083mysql_cur.execute(select version() 084mysql_ver=mysql_cur.fetchall()0version() 085except: 086_response(503) 087else: 088if type = master: 089mysql_conn.close() 090_response(200) 091else: 092#slave check 093try: 094mysql_cur.execute(show slave status) 095slave_result = mysql_cur.fetchall()0 096except: 097mysql_conn.close() 098_response(503) 099else: 100mysql_conn.close() 101if slave_result = (): 102_response(504) 103else: 104IO_state = slave_resultSlave_IO_Running 105SQL_state = slave_resultSlave_SQL_Running 106Behind_state = slave_resultSeconds_Behind_Master 107108if IO_state != Yes: 109_response(505) 110else: 111if SQL_state != Yes: 112_response(506) 113else: 114if Behind_state = int(alter_behind): 115_response(507) 116else: 117_response(200) 118119def _main(): 120main121args = sys.argv1: 122123#Init Mysql Server 124mysql_server = 125mysql_serverhost = 126mysql_serverport = 3306127mysql_serveruser = db_check128mysql_serverpassword = 129mysql_servertype = master130mysql_serveralter_behind = 1131132#mysql args 133mysql_opts = _opts(args) 134135for each_opt in mysql_opts.keys(): 136mysql_servereach_opt = mysql_optseach_opt 137138_mysql_check(mysql_serverhost,mysql_serverport,mysql_serveruser,mysql_serverpassword,mysql_servertype,mysql_se