#发送告警邮件
cat  check_backup.sh
#!/bin/bash

. ~/.bash_profile
BASEDIR=`dirname $0`
cd $BASEDIR

parse_line(){
    EMAIL="xxxxxx@126.com;xxxxxx@126.com"
}
 parse_line
 python tidb_backup_check.py >check_backup_status.html  2>/dev/null
  mutt  -e "set content_type="text/html"" -e "set from="tidb@admin.com"" -e "set realname="TiDB_Admin"" -s "TiDB Backup Status Check" "xxxx@126.com";${EMAIL}"  
      
      
     

    

        
TiDB Backup Status Check
    

           

           

            
说明:
                  
同一个实例通过tidb节点进行检查，包括相应节点的日志信息
                  
以下备份信息主要是检查最近一天的备份，如果备份策略是周末全备需要单独查验
          
如果同一个实体中所有tidb组件没有备份日期与大小，请检查备份job是否正常,zabbix agent 是否有安装
              
如果备份数据库binglog 没有大小，请检查是否有开启归档，归档是否正常
           

           

      
        {table}
      
    
    """
    style="""
    h1.tbsc {
    font: bold 20pt Arial, Helvetica, Geneva, sans-serif;
    color: #336699;
    background-color: White;
    border-bottom: 1px solid #2e5c89;
    margin-top: 0pt;
    margin-bottom: 0pt;
    padding: 0px 0px 0px 0px;
    text-align: center;
}

h3.tbsc {
    font: bold 14pt Arial, Helvetica, Geneva, sans-serif;
    color: #336699;
    background-color: White;
    margin-top: 0pt;
    margin-bottom: 0pt;
    padding: 0px 0px 0px 0px;
    text-align: left;
}

li.tbsc {font: 12pt Arial,Helvetica,Geneva,sans-serif; color:E32636; background:White;}



Table {
  border: 1px solid #1C6EA4;
  background-color: #EEEEEE;
  width: 80%;
  text-align: center;
  border-collapse: collapse;
}
Table td, Table th {
  border: 1px solid #AAAAAA;
  padding: 3px 2px;
}
Table tbody td {
  font-size: 13px;
}
Table tr:nth-child(even) {
  background: #D0E4F5;
}
Table thead {
  background: #1C6EA4;
  background: -moz-linear-gradient(top, #5592bb 0%, #327cad 66%, #1C6EA4 100%);
  background: -webkit-linear-gradient(top, #5592bb 0%, #327cad 66%, #1C6EA4 100%);
  background: linear-gradient(to bottom, #5592bb 0%, #327cad 66%, #1C6EA4 100%);
  border-bottom: 2px solid #444444;
}
Table thead th {
  font-size: 15px;
  font-weight: bold;
  color: #FFFFFF;
  text-align: center;
  border-left: 2px solid #D0E4F5;
}
Table thead th:first-child {
  border-left: none;
}

Table tfoot {
  font-size: 14px;
  font-weight: bold;
  color: #FFFFFF;
  background: #D0E4F5;
  background: -moz-linear-gradient(top, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%);
  background: -webkit-linear-gradient(top, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%);
  background: linear-gradient(to bottom, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%);
  border-top: 2px solid #444444;
}
Table tfoot td {
  font-size: 14px;
}
tr:hover            { background-color: #ffff99;}
    """



    sql_getitem = """ select i.itemid --,i."name",i.key_,h.host,h.hostid,h."name" 
               from items i inner join public.hosts h on  i.hostid=h.hostid  
    where i."name"="tidb check backup status" and i.itemid!="3877749" """

    sql_getInstance = """
    select * from (
    SELECT  lower ("entityName") as instancename, "chineseDesc", "oprDba", case  environment
    when "DBE:PRD" then "生产"
    when "DBE:DRC" then "同城"
    else "远城"
     end env,  "instanceName", "hostIp1", "hostName1",
     substr("instanceName",length("entityName")+2,length("instanceName")) dbtype
    FROM tidb_info a where lower(a."databaseType")="tidb" and a.status="DBS:ON")  b
    where b.dbtype  like "D%"  order by b."instancename" ;"""
    """select i.itemid,i."name",i.key_,to_timestamp(hl.clock) ck,h.host,h.hostid,h."name" ,hl.value from items i 
         inner join hosts h on  i.hostid=h.hostid  
         inner join history_log hl on i.itemid=hl.itemid  where i."name"="tidb check backup status" 
          --and hl.value="/usr/bin/env: python3: No such file or directory"
         and  hl.clock >=extract(epoch from current_date)"""

    rows_item=getinfo(sql_getitem)
    rows_instance=getinfo(sql_getInstance)
    tidbInstance = []
    backrest = []
    if not (rows_instance == 0):
        # print(type(rows_instance))
        dt = pd.DataFrame(rows_instance)
        dt = dt.rename(
            columns={0: "instance", 1: "Chinese_name", 2: "DA", 3: "ENV", 4: "sub_instance", 5: "IP", 6: "hostname",
                     7: "组件"})
    if not (rows_item == 0):
        item = [x for i in rows_item for x in i]
        for i in item:
            sql_getbackup_status = """select value FROM history_log ht  where  itemid={}""".format(i) + 
                                   """and ht.value!="/usr/bin/env: python3: No such file or directory" group by value ,clock order by clock desc limit 1"""
            res = getinfo(sql_getbackup_status)
            if (len(res) > 0 and res != 0):
                h = [y for x in res for y in x]
                for x in h:
                    t = json.loads(x)
                    for m in t:
                        if not (m["instance"] == """[]""" or len(m["size"]) == 0):
                            backrest.append(m)
    # print(backrest)
    dt_backup = pd.DataFrame(backrest)
    #dt_backup=dt_backup.rename(columns={0:"检查日期",1:"备份开始时间",2:"备份结束时间",3:"大小",4:"binlog 大小"})
    # print(dt_backup)

    backinfo = pd.merge(dt, dt_backup, how="left", on=["hostname", "instance"])
    backinfo.rename(
        columns={"instance": "实体名", "Chinese_name": "实体中文名", "DA": "主管DA", "ENV": "环境", "sub_instance": "实例名",
                 "hostname": "主机名", "check day": "检查日期", "backup begin time": "备份开始时间", "backup end time": "备份结束时间",
                 "size": "备份大小", "binglog size": "日志大小"}, inplace=True)
    backinfo_html=backinfo.to_html()
    # print(backinfo)

    html_string = html_string.format(table=backinfo_html,styles=style)
    #html_string=html_string.format(styles=style)
    print(html_string)
    # with app.app_context():
    #     render_template("view.html", tables=[backinfo.to_html()]