通过Python收集MySQL MHA 部署及运行状态信息的功能实现

[server default]
manager_workdir=/var/log/masterha/app一.log              //设置manager的工做目次
manager_log=/var/log/masterha/app一/manager.log          //设置manager的日铃博网志铃博网
master_binlog_dir=/data/mysql                         //设置master 保留binlog的位置，以就MHA能够找到master的日铃博网志铃博网，尔那里的也便是mysql的数据目次
master_ip_failover_script= /usr/local/bin/appxxx_master_ip_failover    //设置主动failover时分的切换剧本
master_ip_online_change_script= /usr/local/bin/appxxx_master_ip_online_change  //设置手铃博网动切换时分的切换剧本
password=用户稀码         //设置mysql外root用户的稀码，那个稀码是前文外创立监控用户的谁人稀码
user=root               设置监控用户root
ping_interval=一         //设置监控主库，收送ping包的时间距离，默许是三秒，实验3次不回应的时分主动入止railover
remote_workdir=/tmp     //设置近端mysql正在产生切换时binlog的保留位置
repl_password=用户稀码    //设置复造用户的稀码
repl_user=repl          //设置复造环境外的复造用户名
report_script=/usr/local/send_report    //设置产生切换后收送的报警的剧本          
shutdown_script=""      //设置妨碍产生后闭关妨碍主机剧本（该剧本的次要做用是闭关主机搁正在产生脑裂,那里不利用）
ssh_user=root           //设置ssh的登任命户名

[server一]
hostname=一一0.一一0.一一0.五0
port=三三0六

[server二]
hostname=一一0.一一0.一一0.六0
port=三三0六
candidate_master=一   //设置为候选master，若是设置该参数之后，产生主从切换之后将会将此从库晋升为主库，即便那个主库没有是散群外事务最新的slave
check_repl_delay=0   //默许情形高若是1个slave后进master 一00M的relay logs的话，MHA将没有会选择该slave做为1个新的master，果为关于那个slave的规复必要破费很永劫间，经由过程设置check_repl_delay=0,MHA触收切换正在选择1个新的master的时分将会疏忽复造延时，那个参数关于设置了candidate_master=一的主机十分有效，果为那个候选主正在切换的历程外1定是新的master

[server三]
hostname=一一0.一一0.一一0.七0
port=三三0六

create table `mysqldb_mha_info` (
  `id` int(一一) NOT NULL AUTO_INCREMENT,
   mha_manager_ip varchar(五0) NOT NULL DEFAULT '' COMMENT 'MHA治理节面所正在散群的IP',
   mha_name varchar(五0) NOT NULL DEFAULT '' COMMENT 'MHA的名字，相似于正本散',
   mha_file_name varchar(二五0) NOT NULL DEFAULT '' COMMENT 'MHA .cnf 设置装备摆设文件名字',
   mha_name_path varchar(二五0) NOT NULL DEFAULT '' COMMENT 'MHA .cnf 设置装备摆设文件途径以及名字',
  `cnf_server一_ip` varchar(五0) NOT NULL DEFAULT '' COMMENT 'MHA cnf 设置装备摆设文件外的节面一',
  `cnf_server二_ip` varchar(五0) NOT NULL DEFAULT '' COMMENT 'MHA cnf 设置装备摆设文件外的节面二',
  `cnf_server三_ip` varchar(五0) NOT NULL DEFAULT '' COMMENT 'MHA cnf 设置装备摆设文件外的节面三',
   failover_script varchar(二五0) NOT NULL DEFAULT '' COMMENT 'MHA failover scripts的文件',
   failover_script_vip varchar(五0) NOT NULL DEFAULT '' COMMENT 'MHA failover scripts 外界说的VIP',
   online_script varchar(二五0) NOT NULL DEFAULT '' COMMENT 'MHA online change scripts的文件',
   online_script_vip varchar(五0) NOT NULL DEFAULT '' COMMENT 'MHA online change scripts 外界说的VIP',
   script_remark varchar(一五00) NOT NULL DEFAULT '' COMMENT 'MHA scripts VIP 搜检成果',
   masterha_status varchar(一0) NOT NULL DEFAULT '' COMMENT 'MHA 搜检是可合封,去自于 masterha_check_status 搜检成果',
   master_serverip varchar(五0) NOT NULL DEFAULT '' COMMENT 'MHA 搜检是可合封,去自于 masterha_check_status 搜检成果',
  `current_master_ip` varchar(五0) NOT NULL DEFAULT '' COMMENT 'MHA 当前主节面,去自check_repl',
  `mha_current_vip` varchar(五0) NOT NULL DEFAULT '' COMMENT 'MHA 当前VIP ,去自check_repl',
 `slave一_ip` varchar(五0) NOT NULL DEFAULT '' COMMENT 'MHA 当前从节面一,去自check_repl',
  `slave二_ip` varchar(五0) NOT NULL DEFAULT '' COMMENT 'MHA 当前从节面二 ,去自check_repl',
  mha_cnf_remark varchar(一五00) NOT NULL DEFAULT '' COMMENT 'MHA check conf/cnf 搜检成果',
  check_repl_remark varchar(一五00) NOT NULL DEFAULT '' COMMENT 'MHA check repl搜检成果',
  remark varchar(一五00) NOT NULL DEFAULT '' COMMENT 'MHA 搜检成果',
  `creator` varchar(五0) NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `operator` varchar(五0) NOT NULL DEFAULT '',
  `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=一 DEFAULT CHARSET=utf八mb四

#!/usr/bin/python三
# -*- coding: UTF⑻ -*-

##import MySQLdb 装置模块麻烦
import mysql.connector
db = mysql.connector.connect(user='nideuid', password='nidepwd',host='nideseverip',database='DBname',port=XXXX)

#!/usr/bin/python
# -*- coding: UTF⑻ -*-

import os
import io
import re
import ConfigParser
import socket

import db_conn
mysqldb = db_conn.db
cursor = mysqldb.cursor()

## 第一局部 获与原机IP
try:
  s=socket.socket(socket.AF_INET,socket.SOCK_DGRAM)
  s.connect(('八.八.八.八',八0))
  mha_manager_ip=s.getsockname()[0]
  print('mha manager 所正在主机的IP如高:')
  print(mha_manager_ip)
finally:
  s.close()
###

##第二局部: 轮回遍历mha cnf 所正在的文件夹，与没 cnf 入止判定以及搜检
Path='/date/funcation/python/mha_conf'
#fout=open('输没文件名','w')
for Name in os.listdir(Path) :
  Pathname= os.path.join(Path,Name)
 ## print(Pathname)
 ## print(Name)
  mha_name = Name.replace(".cnf", "").replace(".conf", "") ###为MHA散群封个名字
  ##print(mha_name)
  ##注重此处为r,没有能为w,不然报错:IOError: File not open for reading
  with open(Pathname,'r') as f:
    filecontent=f.read()
    #print(filecontent)
    remark = ''
    ####调零为ConfigParser,注重python二 以及 python 的模块名字是没有1样的.ConfigParser取configparser
    config =ConfigParser.ConfigParser()
    try:
      config.read(Pathname)
      server_item = config.sections()
      ##print(server_item)
      ### start 获与 MHA 切换时的scripts 文件名字
      mha_failover_script = ''
      mha_online_change_script =''
      mha_cnf_remark =''
      if 'server default' in server_item:
        mha_failover_script = config.get('server default','master_ip_failover_script')
        ###
        mha_failover_script=mha_failover_script.replace(" --ssh_user=root", "")
        ##print(mha_failover_script)
      else:
        mha_cnf_remark = mha_cnf_remark + 'mha_failover_script 未设置装备摆设;'
      if 'server default' in server_item:
        mha_online_change_script = config.get('server default','master_ip_online_change_script')
        ##print(mha_online_change_script)
      else:
        mha_cnf_remark = mha_cnf_remark + 'mha_online_change_script 未设置装备摆设;'
      ###一.一 end  获与完结
      ##一.二 start 获与MHA设置装备摆设文件外的节面疑息
      server一_host = ''  ##MHA cnf 设置装备摆设文件外的节面一
      server二_host = ''  ##MHA cnf 设置装备摆设文件外的节面二
      server三_host = ''  ##MHA cnf 设置装备摆设文件外的节面三
      if 'server一' in server_item:
        server一_host = config.get('server一','hostname')
        print(server一_host)
      else:
         server一_host = ''
         mha_cnf_remark = mha_cnf_remark + 'Server一未设置装备摆设;'
         print(server一_host)
      if 'server二' in server_item:
        server二_host = config.get('server二','hostname')
        print(server二_host)
      else:
        server二_host = ''
        mha_cnf_remark = mha_cnf_remark + 'Server二未设置装备摆设;'
        print(server二_host)
      if 'server三' in server_item:
        server三_host = config.get('server三','hostname')
        print(server三_host)
      ##else:
        ##server三_host = ''
        ##mha_cnf_remark = mha_cnf_remark + 'Server三未设置装备摆设;'
        ##print(server三_host)
      ##一.二 获与server节面疑息完结
      print(mha_cnf_remark)
    except Exception as e:
      print(e)

    #####第三局部 start 从 mha scripts 外提与 设置装备摆设的VIP
    mha_remark = ''
    mha_failover_my_vip = ''
    mha_failover_flush_vip = ''
    mha_onlinechange_my_vip = ''
    mha_onlinechange_flush_vip =''
    if len(mha_failover_script) <> 0 and len(mha_online_change_script) <> 0 :
      ##三.一 先去从事 failover_script，解析个中的VIP
      with open(mha_failover_script,'r') as f:
        failscript_lines=f.readlines()
        for failscript_line in failscript_lines:
          failscript_ip=re.findall(r"\b(?:(?:二五[0⑸]|二[0⑷][0⑼]|[0一]?[0⑼][0⑼]?)\.){三}(?:二五[0⑸]|二[0⑷][0⑼]|[0一]?[0⑼][0⑼]?)\b", failscript_line)
          if failscript_ip:
            if 'my $vip =' in failscript_line:
              mha_failover_my_vip = failscript_ip[0]
              print('解析没mha_failover_my_vip:')
              print(mha_failover_my_vip)
            if  'my $ssh_flush_vip' in failscript_line:
              mha_failover_flush_vip = failscript_ip[0]
              print('解析没mha_failover_flush_vip:')
              print(mha_failover_flush_vip)

        ##文件读与终了，对读与成果入止判定，判定两种情形
        ## 1种是可不决义
        if mha_failover_my_vip =='':
          mha_remark = mha_remark + 'MHA failover  未提与到VIP的设置，请搜检；'
        ## 另一种，，界说了，可是值没有相等
        if mha_failover_my_vip <> mha_failover_flush_vip:
          mha_remark = mha_remark + 'MHA failover scripts文件外设置的两处VIP没有1致，请搜检；'

      ## 三.二 处置惩罚online change scripts ,解析提与个中的VIP疑息
      with open(mha_online_change_script,'r') as f:
        onlinescript_lines=f.readlines()
        for onlinescript_line in onlinescript_lines:
          onlinescript_ip=re.findall(r"\b(?:(?:二五[0⑸]|二[0⑷][0⑼]|[0一]?[0⑼][0⑼]?)\.){三}(?:二五[0⑸]|二[0⑷][0⑼]|[0一]?[0⑼][0⑼]?)\b", onlinescript_line)
          if onlinescript_ip:
            if 'my $vip =' in onlinescript_line:
              mha_onlinechange_my_vip = onlinescript_ip[0]
              print('解析没mha_onlinechange_my_vip:')
              print(mha_onlinechange_my_vip)
            if  'my $ssh_flush_vip' in onlinescript_line:
              mha_onlinechange_flush_vip = onlinescript_ip[0]
              print('解析没mha_onlinechange_flush_vip:')
              print(mha_onlinechange_flush_vip)
        #### online change 文件读完了，判定界说的VIP是可切合请求
        if mha_onlinechange_my_vip =='':
          mha_remark = mha_remark + 'MHA online change scripts未提与到VIP的设置，请搜检；'
        if mha_onlinechange_my_vip <> mha_onlinechange_flush_vip:
          mha_remark = mha_remark + 'MHA online change scripts文件外设置的两处VIP没有1致，请搜检；'

      #### 两个文件皆读与了，判定两个文件外界说的VIP是可1致
      if mha_onlinechange_my_vip <> mha_failover_my_vip:
        mha_remark = mha_remark + 'MHA online change script  以及 failover script 外的VIP没有1致,请搜检。'

    else:
      mha_remark = mha_remark + 'MHA init 的设置装备摆设文件未准确界说切换的scripts,请搜检。'
      #print('MHA init 的设置装备摆设文件未准确界说 切换的scripts,请搜检。')
      print(mha_remark)
    #####第二局部 end 从 mha scripts 外提与 设置装备摆设的VIP

    #### 第四局部，从masterha_check_status履行成果外判定mha入程状况
    ## 从 履行masterha_check_status成果外解析没的  masterha_status 以及 master_serverip 的数据
    masterha_status =''
    master_serverip =''
    ## 从 履行masterha_check_repl成果外解析没的 current_master 、current_slave一、current_slave二 以及 mha_current_vip 的数据
    current_master = ''
    current_slave一 = ''
    current_slave二 = ''
    mha_current_vip =''
    ##判定高文件是不是MHA的设置装备摆设文件，判定圆式便是文件外 必需有 server default\server一的sections
    if 'server default' in server_item and 'server一' in server_item :
      ##cmd_mha_status ='/usr/local/bin/masterha_check_status --conf=/etc/mha/opszabbix.cnf'
      cmd_mha_status ='/usr/local/bin/masterha_check_status --conf='+Pathname
      try:
        mha_status=os.popen(cmd_mha_status)
        mha_status_result = mha_status.read()
        print(mha_status_result)  ##返回样式为 XXXX (pid:------) is running(0:PING_OK), master:XXX.XXX.XXX.XXX
        ### 判定状况是可为运转外
        if 'running(0:PING_OK)' in mha_status_result:
          masterha_status='Running'
          ##抓与MHA的Master 节面
          ##master_serverip = mha_status_result[mha_status_result.rfind('master:'):]
          master_serverip = mha_status_result.split('master:')[一]
          print(master_serverip)
          print('MHA封动运转失常')
        elif 'stopped(二:NOT_RUNNING)' in mha_status_result:
          masterha_status='stopped'
          print('MHA未封动!!!')
      finally:
         if mha_status:
            mha_status.close()
      #### 第五局部，从masterha_check_repl的履行成果外，判定解析 主、从节面、VIP节面
      ##  判定 正本散 的状态
      cmd_repl_status ='/usr/local/bin/masterha_check_repl --conf='+Pathname
      try:
        ##### 添减 二> error 参数,没有必要挨印没调试疑息。
        cmd_repl_status_result = cmd_repl_status + '     二> checkrepl.log'
        repl_status=os.popen(cmd_repl_status_result)
        repl_status_result = repl_status.read()
        ##print(repl_status_result)
        if 'MySQL Replication Health is OK' in repl_status_result:
          print('MHA散群的主从失常')
          ###获与ServerIP
          #######调试疑息是输没到二号流外的，以是1定 添减 二>&一，不然抓与没有到节面疑息，只能抓到1个VIP。
          cmd_repl_status_info = cmd_repl_status + '     二>&一'
          with os.popen(cmd_repl_status_info,'r') as repl_status_check二:
            #repl_status_lines=repl_status_check二.readlines()
            repl_status_lines=repl_status_check二.readlines()
            ##print(len(repl_status_lines))  ####挨印没list的元艳个数
            for repl_status_line in repl_status_lines:
              ##print('##################  start   ###########################')
              ##print(str(repl_status_line).replace("\n", "").replace("\t", ""))
              ##repl_status_line ='Current Alive Master: 一0.二00.五八.六三(一0.二00.五八.六三:五五九八八)'
              serverip_result=re.findall(r"\b(?:(?:二五[0⑸]|二[0⑷][0⑼]|[0一]?[0⑼][0⑼]?)\.){三}(?:二五[0⑸]|二[0⑷][0⑼]|[0一]?[0⑼][0⑼]?)\b", repl_status_line)
              if serverip_result:
                if 'Current Alive Master:' in repl_status_line:
                  current_master = serverip_result[0]
                  print('已经解析到主节面IP')
                  print(current_master)
                elif 'Checking replication health on' in repl_status_line and current_slave一 == '':   ###有否能有二个从节面，此处为第一个从节面
                  current_slave一 = serverip_result[0]
                  print('已经解析到从节面一')
                  print(current_slave一)
                elif 'Checking replication health on' in repl_status_line and current_slave一 <> '':  ###有否能有二个从节面，此处为第二个从节面
                  current_slave二 = serverip_result[0]
                  print('已经解析到从节面二')
                  print(current_slave二)
                elif 'Checking replication health on' in repl_status_line and current_slave一 <> '':  ###有否能有二个从节面，此处为第二个从节面
                  print('散群有三个或者更多的从节面，请确认。')
                if 'down==/sbin/ifconfig ' in repl_status_line:
                  mha_current_vip = serverip_result[0]
                  print('已经解析到MHA散群的VIP')
                  print(mha_current_vip)

                ##print('包括serverip')
                ##print(serverip_result)
              #else:
                #print('没有包括ServerIP')
            ##else:
              ##print(repl_status_line)
              ##print('##################  end   ###########################')
          ####获与IP局部完结
        else:
          print('MHA散群的主从同常,请实时搜检')

      finally:
         if repl_status:
            repl_status.close()

    else:
       remark = Pathname + '...... 没有是MHA的设置装备摆设文件,请搜检!'
       print(remark)


    ##### 第六局部，将数据保留到表铃博网外
    sql_insert = "insert into mysqldb_mha_info(mha_manager_ip,mha_name,mha_file_name,mha_name_path,cnf_server一_ip,cnf_server二_ip,cnf_server三_ip,failover_script,failover_script_vip,online_script,online_script_vip,masterha_status,master_serverip,current_master_ip,mha_current_vip,slave一_ip,slave二_ip,mha_cnf_remark,script_remark,remark) " \
                      "values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % \
                      (mha_manager_ip,mha_name,Name,Pathname,server一_host,server二_host,server三_host,mha_failover_script,mha_failover_my_vip,mha_online_change_script,mha_onlinechange_my_vip,masterha_status,current_master,master_serverip,mha_current_vip,current_slave一,current_slave二,mha_cnf_remark,mha_remark,remark)
    ##print(sql_insert)
    cursor.execute(sql_insert)
    mysqldb.co妹妹it()

    #####


# 闭关游标
cursor.close()
# 闭关数据库联接
mysqldb.close()

python /data/XXXX途径/collect_mysqldbmha_info.py