MYSQL AWR 报表

海鲨数据库架构师 2022-10-22

499

终于完成MYSQL AWR HTML报告

第一版的AWR 分享给好友们,好友提了些需求,最近这几周空闲也实现了

第二版增加功能

1 系统版本
2 数据库版本
4 MYSQL系统内存
3 TOP PRC SWAP
4 TOP PRC PHYMEM
5 TOP PRC PHY MEM SHARED
6 TOP PRC PRIV PHY MEM
7 排序
8 内存细节
9 OOM得分
10 增加文件头部
11 多SCHEMA
12 错误日志 Error Warning
13 增加日志函数
14 死锁BUG
15 当前实例状态
16 自动作业支持 CRONTAB 获取自动日期前7天

支持多SCHEMA. 比第一版本更加完善,采集更多信息.

主要是包含了操作系统内存信息

脚本有三,分别是头脚本,主体脚本和测试脚本.
头脚本是考虑多次更新主体脚本后,部署多台MYSQL,要修改用户密码等必要环境,觉得有些麻烦不方便.

测试脚本:主要是感觉某个SQL或者某个SHELL函数达不到预期时候,调试主体脚本产生的信息高达5MB. 所以单独拿出来调试.

第二版本
awr_head_mysql.sh
awr_mysql_get.sh
awr_test_mysql.sh

觉得好,麻烦请点击文后的广告,给予友情支持!

头脚本 awr_head_mysql.sh

#!/bin/bash
# by sharkz@fankun.com
# 2022-07-8

#================================================DataSegment===============================================
export TARGET_DB_IP='10.203.251.2'
export TARGET_DB_PROT='3306'
export TARGET_DB_USER='root'
export TARGET_DB_PASS='shark@666'

export SYS_DIR='/dev/mapper/centos-root'
export DB_DIR='/var/lib/mysql'
export BAK_DIR='/home/databak/'

export MYSQL_ERROR_LOG='/home/deploy/logs/aesygo_test_mysqld_error.log'

export SCHEMA='sqle'            #默认检查的SCHEMA
export IS_CHECK_MULTI_SCHEMA=0  #是否检查多个SCHEMA ? 0 否 1 是

export IS_AUTO_CHECK=0          #是否自动检查 放入到CRONTAB中? 自动检查 检查当前日期7天前
export BEFORE_DAYS=7            #多少天前

export CHARTSET='utf8mb4'           #检查字符集 这个是符合标准 UTF8MB4 支持4个字节汉字 UTF8支持不够 各个表字符集也要统一
export CHART_COLLATION='utf8mb4_general_ci' #检查字符排序方式  排序方式要统一

export CHECK_RESULT_FILE="/home/deploy/AWR_${SCHEMA}_MYSQL_${TARGET_DB_IP}_$(date +%Y%m%d-%H%M%S).html"

AWR 主体脚本:awr_mysql_get.sh

#!/bin/bash
# by Sharkz@fankun.com
# 2022-07-8
# Be sure to output table
# long_query_time = 5
# slow_query_log = on
# log_output = 'TABLE' 
# Deadlock log parameters
# innodb_print_all_deadlocks=ON
# performance_schema=ON  #此参数必须ON
# 可能需要sudo sh check_mysql.sh 方式执行

#================================================DataSegment===============================================
#--------------------- 把下面数据端设置放在  awr_head_mysql.sh  且设置chmod +x----------------------------------------------------------------
#export TARGET_DB_IP=192.168.0.221
#export TARGET_DB_PROT=3306
#export TARGET_DB_USER=root
#export TARGET_DB_PASS='Shark@438'
#export MYSQL_ERROR_LOG='/home/mysqld.log'
#export SYS_DIR='/dev/mapper/centos-root'
#export DB_DIR='/var/lib/mysql'
#export BAK_DIR='/home/databak/'
#export SCHEMA='sqle'
#export CHARTSET='utf8mb4'
#export CHART_COLLATION='utf8mb4_general_ci'
#export CHECK_RESULT_FILE="/root/CHECK_${SCHEMA}_MYSQL_${TARGET_DB_IP}_$(date +%Y%m%d-%H%M%S).html"
#export IS_CHECK_MULTI_SCHEMA=0 
#export IS_AUTO_CHECK=0 
#export BEFORE_DAYS=7
#------------------------------------------------------------------------------------------------------------------------------------------

#TARGET_DB_IP=192.168.0.221
#TARGET_DB_PROT=3306
#TARGET_DB_USER=root
#TARGET_DB_PASS='Shark@438'
#SYS_DIR='/dev/mapper/centos-root'
#DB_DIR='/var/lib/mysql'
#BAK_DIR='/home/databak/'
#SCHEMA='sqle'
#CHECK_RESULT_FILE="/root/CHECK_${SCHEMA}_MYSQL_${TARGET_DB_IP}_$(date +%Y%m%d-%H%M%S).html"
#CHARTSET='utf8mb4'
#CHART_COLLATION='utf8mb4_general_ci'
#IS_CHECK_MULTI_SCHEMA=0 
#MYSQL_ERROR_LOG='/home/deploy/mysql-error.log'
#BEFORE_DAYS=7
#IS_AUTO_CHECK=0


#---------------------------------目的在于部署多台MYSQL服务器上,每台服务器的参数不一样,每次更新的时候都要同步下,觉得没有必要------------------------------
START_TIME=`date +%s`
. ./awr_head_mysql.sh 

BUSSINE_SCHEMA='aesygo_test'

if [[ ${IS_AUTO_CHECK} -eq 0 ]] ; then 
   START_DATE_TIME='2022-10-10 00:00:00.000000'
   END_DATE_TIME='2022-10-18 00:00:00.000000'

   START_DATE=${START_DATE_TIME: 0: 10}
   END_DATE=${END_DATE_TIME: 0: 10}
else 
   START_DATE=$(date -d"${BEFORE_DAYS} day ago" +%Y-%m-%d)
   END_DATE=$(date "+%Y-%m-%d")       
   START_DATE_TIME=${START_DATE}"00:00:00.000000"  
   END_DATE_TIME=${END_DATE}"00:00:00.000000"  
fi 

#以下不需要改动脚本运行日志输出参数,主要是DEBUG时候使用
IS_INFOMATION_OUTPUT_CONSOLE=0
LOGFILE_NAME=check_mysql_`date +%F_%H`.log
LOGFILE_DIR='./logs/'
LOGFILE_PATH=${LOGFILE_DIR}${LOGFILE_NAME}
V_DATE=`date "+%Y-%m-%d %H:%M:%S"`

#===============================================Fuction segments======================================================================

#--------------------------------以下 MYSQL 函数-------------------------------------------------------------------------
function Target_MysqlDB()
{
 mysql -h $TARGET_DB_IP -P $TARGET_DB_PROT -u$TARGET_DB_USER -p$TARGET_DB_PASS --html -t  -e "$*" >> ${CHECK_RESULT_FILE} 2>/dev/null
}

function Target_MysqlDB_NOT_HTML()
{
 mysql -h $TARGET_DB_IP -P $TARGET_DB_PROT -u$TARGET_DB_USER -p$TARGET_DB_PASS  -N -e "$*"  2>/dev/null

}


function SALVE_MysqlDB()
{
 mysql -h $TARGET_DB_IP -P $TARGET_DB_PROT -u$TARGET_DB_USER -p$TARGET_DB_PASS  -e "$*" 1> /tmp/tmp_slave_status.txt   2>/dev/null
}

function Target_MysqlDB_Innodb_status()
{
 mysql -h $TARGET_DB_IP -P $TARGET_DB_PROT -u$TARGET_DB_USER -p$TARGET_DB_PASS  -e "$*" 1> /tmp/awr_innodb_status.txt   2>/dev/null
}
#--------------------------------以下 HTML 函数-------------------------------------------------------------------------
CREATE_HTML_HEAD()
{
echo -e '<html>
<head>
<meta charset="UTF-8">
<style type="text/css">
    body        {font:12px Courier New,Helvetica,sansserif; color:black; background:White;}
    table       {font:12px Courier New,Helvetica,sansserif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} 
    th.awrbg    {font:bold 12pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#000000;padding-left:4px; padding-right:4px;padding-bottom:2px}
    td.awrc     {font:12pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;}
    td.awrnc    {font:12pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;}
    h1.awr   {font:bold 24pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-bottom:0pt;padding:0px 0px 0px 0px;}
    h2.awr   {font:bold 20pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
    h3.awr   {font:bold 18pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;}
    h4.awr   {font:bold 16pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
    LINES_2.awr   {font:bold 20pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
    table tr:nth-child(even){            background-color: #fafad2;        }
    table tr:nth-child(odd){          background-color: #b2e1b2;        }
</style>
</head>
<body>' >>${CHECK_RESULT_FILE} 2>/dev/null

 echo -e " <h1 align="center" class="awr"> ${SCHEMA} AWR Report </h1> ">>${CHECK_RESULT_FILE}
 echo -e "<br/>" >>${CHECK_RESULT_FILE}
 echo -e " <h2 align="center" class="awr"> Report:$(date +%Y-%m-%d)  </h2> ">>${CHECK_RESULT_FILE}
 echo -e " <h3 align="center" class="awr"> Check Date $START_DATE  To $END_DATE </h3> ">>${CHECK_RESULT_FILE}
 echo -e '<hr style="FILTER:alpha(opacity=100,finishopacity=0,style=2)" width="100%"color=#cccc99 SIZE=1>' >>${CHECK_RESULT_FILE} 2>/dev/null
}


function OUTPUT_H1_TITLE() #输出HTML 头标题
{
  echo -e "<h1 class="awr" >$1</h1>" >>${CHECK_RESULT_FILE} 2>/dev/null
}

function OUTPUT_H2_TITLE() #输出HTML 大标题
{
  echo -e "<h2 class="awr" >$1</h2>" >>${CHECK_RESULT_FILE} 2>/dev/null
}

function OUTPUT_H3_TITLE() #输出HTML 中标题
{
  echo -e "<h3 class="awr" >$1</h3>" >>${CHECK_RESULT_FILE} 2>/dev/null
}

function OUTPUT_H4_TITLE() #输出HTML  小标题
{
  echo -e "<h4 class="awr" >$1</h4>" >>${CHECK_RESULT_FILE} 2>/dev/null
}


CREATE_HTML_END() #输出HTML 结束
{
  echo -e "</body></html>" >>${CHECK_RESULT_FILE} 2>/dev/null
}

OUT_PUT_TABLE_HEAD() #输出表格头
{
  echo -e '<table width="" border="1" >' >>${CHECK_RESULT_FILE}
}


OUT_PUT_FILED() ##输出字段名函数
{
    th_str=`echo $1|awk 'BEGIN {FS=" "}''{i=1; while(i<=NF) {print "<th class='awrbg' scope="col"> "$i"</th>";i++}}'`
}

OUT_PUT_TITEL() ##字段接口函数
{
  OUT_PUT_FILED "$*"; echo -e "<tr>    $th_str  </tr>" >> ${CHECK_RESULT_FILE}
}


OUT_PUT_VAULES() ##输出字段内容函数
{
    th_str=`echo $1|awk 'BEGIN{FS=" "}''{i=1; while(i<=NF) {print "<td scope="row" class='awrc'> "$i"</td>";i++}}'`
}

OUT_PUT_LINES() #输出内容接口函数
{
  OUT_PUT_VAULES "$*";  echo -e "<tr>    $th_str  </tr>" >>${CHECK_RESULT_FILE}
}

OUT_PUT_SINGLE_LINES() #输出单行内容接口函数
{
 echo -e "<tr><td scope="" class='awrc'>  $* </td></tr>" >>${CHECK_RESULT_FILE}
}

OUT_PUT_ROW_HEAD() #输出行头
{
   echo -e "<tr>" >>${CHECK_RESULT_FILE}
}

OUT_PUT_ROW_TAIL() #输出列VALUE
{
   echo -e "</tr>" >>${CHECK_RESULT_FILE}
}
OUT_PUT_SINGLE_COL()#输出行尾
{
  echo -e " <td scope=""row"" class='awrc'>  $* </td>" >>${CHECK_RESULT_FILE}
}

OUT_PUT_NEW_LINE() #
{
      echo -e " <br />">>${CHECK_RESULT_FILE}
}

OUT_PUT_TABLE_TAIL() #输出表尾
{
  echo -e "</table>" >>${CHECK_RESULT_FILE}
  OUT_PUT_NEW_LINE
}

OUT_PUT_LINES_LOG() ## 
{
     echo -e "<tr> <td scope="row" class='awrc'> $1</td></tr>" >>${CHECK_RESULT_FILE}
}

#--------------------------------以下 特色 函数功能-------------------------------------------------------------------------
function WriteLogs()
{
  if [ ! -d $LOGFILE_DIR ] ; then
    mkdir -p logs
    echo "" > ${LOGFILE_PATH}
  fi
  V_DATE=`date "+%Y-%m-%d %H:%M:%S"`
  echo "$V_DATE==>$*" >> ${LOGFILE_PATH};
  if [ $IS_INFOMATION_OUTPUT_CONSOLE -eq 1 ] ; then #屏幕输出DEBUG信息
   echo -e "$*"
  fi
}

deadlock()
{
    ERROR_LOG=${MYSQL_ERROR_LOG}
    DEADLOCK_KEY='InnoDB: Transactions deadlock detected,'
    DEADLOCK_END_KEY='InnoDB: *** WE ROLL BACK TRANSACTION'
    IS_EXIST_DEADLOCK=$(cat ${ERROR_LOG} | grep -m 1 "InnoDB: Transactions deadlock detected,")
    FIND_DATE1=${START_DATE}
    FIND_DATE=$(date -d "${FIND_DATE1}" +%s)

    #Match all line numbers and times of key
if [[ -n ${IS_EXIST_DEADLOCK} ]]; then 
    START_LINE_VAR=$(cat ${ERROR_LOG} | grep -n  "InnoDB: Transactions deadlock detected," |awk -F ":"  '{printf($1);printf ";"}')
    START_TIME_VAR=$(cat ${ERROR_LOG} | grep -n  "InnoDB: Transactions deadlock detected," |awk -F ":"  '{printf($2);printf ";"}')
    START_KEY_LINE_LIST=(${START_LINE_VAR//;/ })
    START_KEY_TIME_LIST=(${START_TIME_VAR//;/ })

    #Last compliant_ Key all line numbers and times
    CLOSE_LINE_VAR=$(cat ${ERROR_LOG} | grep -n  "WE ROLL BACK TRANSACTION" |grep -i "InnoDB:"|awk -F ":"  '{printf($1);printf ";"}')
    CLOSE_TIME_VAR=$(cat ${ERROR_LOG} | grep -n  "WE ROLL BACK TRANSACTION" |grep -i "InnoDB:"| awk -F ":"  '{printf($2);printf ";"}')
    CLOSE_KEY_LINE_LIST=(${CLOSE_LINE_VAR//;/ })
    CLOSE_KEY_TIME_LIST=(${CLOSE_TIME_VAR//;/ })

    START_POS=0
    for (( i=0; i<${#START_KEY_TIME_LIST[*]}; i=i+1 ))
    do
        temp_time=${START_KEY_TIME_LIST[$i]}
        START_TIME=${temp_time:0:10}
        DIG_START_TIME=$(date -d "${START_TIME}" +%s)

        if [[ ${DIG_START_TIME} -ge ${FIND_DATE}  ]]; then
            START_POS=${i}
            break
        fi
    done

    END_POS=0
    for (( k=0; k<${#CLOSE_KEY_TIME_LIST[*]}; k=k+1 ))
    do
        temp_time=${CLOSE_KEY_TIME_LIST[$i]}
        CLOSE_TIME=${temp_time:0:10}
        DIG_CLOSE_TIME=$(date -d "${CLOSE_TIME}" +%s)    

        if [[ ${DIG_CLOSE_TIME} -ge ${FIND_DATE}  ]]; then
            if [[  ${k} -ge ${START_POS} ]] ; then 
              END_POS=${k}
              break
            fi 
        fi
    done

#echo -e "start :${START_POS};  close:${END_POS}"

for (( j=0; j<${#CLOSE_KEY_TIME_LIST[*]}; j=j+1 ))
do
  if [[ ${j} -ge ${START_POS} ]] ; then 

     CLOSE=${CLOSE_KEY_LINE_LIST[$j]}
     START=${START_KEY_LINE_LIST[$j]}
     DEADLOCK_STRINGS=$(sed -n "${START},${CLOSE}p" ${ERROR_LOG} |sed ":a;N;s/\n/<br>/g;ta")  #把LINUX换行符替换成HTML换行符<br>
     OUT_PUT_LINES_LOG "${DEADLOCK_STRINGS}"
     else
        OUT_PUT_LINES_LOG " 未发现死锁日志信息"        
     fi              
done
else
   OUT_PUT_LINES_LOG " 未发现死锁日志信息"
fi 

}

LATEST_deadlock() ##最近死锁日志 通过SHOW ENGING INNODB STATUS\G; 获取 存放到/tmp/mysql_check_innodb_status.txt
{  
    ERROR_LOG=$1 
    DEADLOCK_KEY='LATEST DETECTED DEADLOCK'
    DEADLOCK_END_KEY='WE ROLL BACK TRANSACTION'
    IS_EXIST_DEADLOCK=$(cat ${ERROR_LOG} | grep -m 1 "LATEST DETECTED DEADLOCK")
    FIND_DATE1=${START_DATE}
    FIND_DATE=$(date -d "${FIND_DATE1}" +%s)

    if [[ -n ${IS_EXIST_DEADLOCK} ]] ; then 
      START_LINE_VAR=$(cat ${ERROR_LOG} | grep -n  "${DEADLOCK_KEY}" |awk -F ":"  '{printf($1);printf ";"}')
      START_TIME_VAR=$(cat ${ERROR_LOG} | grep -A 2  "${DEADLOCK_KEY}" |grep -i `date +%Y` |awk -F " "  '{printf($1);printf ";"}')
      CLOSE_LINE_VAR=$(cat ${ERROR_LOG} | grep -n  "${DEADLOCK_END_KEY}" |awk -F ":"  '{printf($1);printf ";"}')

      START_KEY_LINE_LIST=(${START_LINE_VAR//;/ })
      START_KEY_TIME_LIST=(${START_TIME_VAR//;/ })
      CLOSE_KEY_LINE_LIST=(${CLOSE_LINE_VAR//;/ })

      START_POS=0
      for (( i=0; i<${#START_KEY_TIME_LIST[*]}; i=i+1 ))
      do
        temp_time=${START_KEY_TIME_LIST[$i]}
        START_TIME=${temp_time:0:10}
        DIG_START_TIME=$(date -d "${START_TIME}" +%s)

        if [[ ${DIG_START_TIME} -ge ${FIND_DATE}  ]]; then
            START_POS=${i}
            break
        fi
      done

    for (( j=0; j<${#CLOSE_KEY_LINE_LIST[*]}; j=j+1 ))
    do
      if [[ ${j} -ge ${START_POS} ]] ; then 
        CLOSE=${CLOSE_KEY_LINE_LIST[$j]}
        START=${START_KEY_LINE_LIST[$j]}
        DEADLOCK_STRINGS=$(sed -n "${START},${CLOSE}p" ${ERROR_LOG}) 
        OUT_PUT_LINES_LOG "${DEADLOCK_STRINGS}"     
      else
        OUT_PUT_LINES_LOG " 未发现死锁日志信息"        
      fi      
    done
else
   OUT_PUT_LINES_LOG " 未发现死锁日志信息"
fi   
 }


sar_cpu()
{
   TITL="DATE:         CPU     %user     %nice   %system   %iowait    %steal     %idle "
   OUT_PUT_TITEL $TITL 
   for file in `ls -tr /var/log/sa/sa* | grep -v sar`
   do    
    dat=`sar -f $file | head -n 1 | awk '{print $4}'`    
    INFO=$(echo -n $dat ;   sar -f $file  | grep -i Average | sed "s/Average://")
    OUT_PUT_LINES ${INFO}
  done

}


Parse_salve_txt() #解析SALVE信息
{
    MASTER_UUID=$(cat /tmp/tmp_slave_status.txt           | grep -i "Master_UUID" |awk '{print $2}')
    SLAVE_IO_STATE=$(cat /tmp/tmp_slave_status.txt        | grep -i "Slave_IO_State" |awk -F " " '{for (i=2;i<=NF;i++)printf("%s ", $i);print ""}')
    READ_MASTER_POST=$(cat /tmp/tmp_slave_status.txt      | grep -i "Read_Master_Log_Pos"  |awk '{print $2}')
    Relay_Master_Log_File=$(cat /tmp/tmp_slave_status.txt | grep -i "Relay_Master_Log_File"|awk '{print $2}')
    EXEC_MASTER_POST=$(cat /tmp/tmp_slave_status.txt      | grep -i "Exec_Master_Log_Pos"  |awk '{print $2}')
    BEHIND_SECONDS=$(cat /tmp/tmp_slave_status.txt        | grep -i "Seconds_Behind_Master"|awk '{print $2}')
    SLAVE_IO_RUNNING=$(cat /tmp/tmp_slave_status.txt      | grep -i "Slave_IO_Running" |awk '{print $2}')
    SLAVE_SQL_RUNNING=$(cat /tmp/tmp_slave_status.txt     | grep -i "Slave_SQL_Running:" |awk '{print $2}')
    REPLICATE_DO_DB=$(cat /tmp/tmp_slave_status.txt       | grep -i "Replicate_Do_DB" |awk '{print $2}')
    SQLDELAY=$(cat /tmp/tmp_slave_status.txt              | grep -i "SQL_Delay"  |awk '{print $2}')
    SALVE_SQL_RUN_STATE=$(cat /tmp/tmp_slave_status.txt   | grep -i "Slave_SQL_Running_State"  |awk -F " " '{for (i=2;i<=NF;i++)printf("%s ", $i);print ""}')
    LAST_SQL_ERROR=$(cat /tmp/tmp_slave_status.txt        | grep -i "Last_IO_Errno"|awk '{print $2}')
    LAST_IO_ERROR=$(cat /tmp/tmp_slave_status.txt         | grep -i "Last_SQL_Errno"|awk '{print $2}')
    RETRIEVED_GTID=$(cat /tmp/tmp_slave_status.txt        | grep -i "retrieved_gtid_set" |awk -F " " '{for (i=2;i<=NF;i++)printf("%s ", $i);print ""}')
    EXECUTED_GTID=$(cat /tmp/tmp_slave_status.txt         | grep -i "${MASTER_UUID}:" |grep -vi "retrieved_gtid_set")
    SALVE_SQL_RUN_STATE=${SALVE_SQL_RUN_STATE// /_}
    SLAVE_IO_STATE=${SLAVE_IO_STATE// /_}
}

Get_Mysqld_OOM_SCORE()
{

     OOM_ATCION=$(cat /proc/sys/vm/panic_on_oom)
     OOM_KILL=$(cat /proc/sys/vm/oom_kill_allocating_task)
     OOM_CORE_SCORE=$(cat  /proc/$(pidof mysqld)/oom_score)
     OOM_USER_SCORE=$(cat  /proc/$(pidof mysqld)/oom_score_adj)
     case $1 in
     1) 
        case  ${OOM_ATCION} in  
          0)   echo "OMM_ACTION start_omm_killer" ;;
          1)   echo "OMM_ACTION start_omm_killer_or_restart_os" ;;
          2)   echo "OMM_ACTION force_restart_os" ;;
          *)   ;;
        esac
        ;;
    2)
         case  ${OOM_KILL} in  
            0)   echo "OOM_KILL_WHO kill_top_omm_score" ;;
            1)   echo "OMM_ACTION kill_apply_memory_proc" ;;
            *)   echo "OMM_ACTION kill_apply_memory_proc" ;;
          esac
         ;; 
    3) 
      echo -e "OMM_SCORE_OF_CORE   ${OOM_CORE_SCORE}" ;;
    4)  
      echo -e "OMM_SCORE_OF_USER   ${OOM_USER_SCORE}" ;;

    *) ;;
    esac 
}



 GET_SWAP () 
 {
  SUM=0
  OVERALL=0
  echo "" > /tmp/mysql_awr_sort.txt
  #获取进程目录
  for DIR in `find /proc/ -maxdepth 1 -type d | egrep "^/proc/[0-9]"` ; do
    #获取进程PID
    PID=`echo $DIR | cut -d / -f 3`

    #获取进程名称
    PROGNAME=`ps -p $PID -o comm --no-headers`

    #获取进程的所有分区中SWAP值

    for SWAP in `grep Swap $DIR/smaps 2>/dev/null| awk '{ print $2 }'`
    do
          #进程swap值求和
      let SUM=$SUM+$SWAP
    done

    if [ $SUM -gt 0 ] ; then 

     echo -e  "$PID   $SUM  $PROGNAME" >> /tmp/mysql_awr_sort.txt
    fi

    #总共swap分区值求和
    let OVERALL=$OVERALL+$SUM
    SUM=0 

  done

  sort -k 2 -nr /tmp/mysql_awr_sort.txt -o /tmp/mysql_awr_sort.txt
  echo  "$OVERALL"

 }

 GET_PHYSICS_MEMORY () 
 {
  SUM=0
  OVERALL=0
    echo "" > /tmp/mysql_awr_sort.txt

  #获取进程目录
  for DIR in `find /proc/ -maxdepth 1 -type d | egrep "^/proc/[0-9]"` ; do
    #获取进程PID
    PID=`echo $DIR | cut -d / -f 3`

    #获取进程名称
    PROGNAME=`ps -p $PID -o comm --no-headers`

    #获取进程的所有分区中RSS值

    for RSS in `grep Rss $DIR/smaps 2>/dev/null| awk '{ print $2 }'`
    do
          #进程RSS值求和
      let SUM=$SUM+$RSS
    done

    if [ $SUM -gt 0 ] ; then 
    echo -e  "$PID   $SUM  $PROGNAME" >> /tmp/mysql_awr_sort.txt
    fi
       #总共RSS分区值求和
    let OVERALL=$OVERALL+$SUM
    SUM=0 
    done

  sort -k 2 -nr /tmp/mysql_awr_sort.txt -o /tmp/mysql_awr_sort.txt
  echo  "$OVERALL"
 }


 GET_PHYSICS_MEMORY_AVG_SHARD () 
 {
  SUM=0
  OVERALL=0
      echo "" > /tmp/mysql_awr_sort.txt

  #获取进程目录
  for DIR in `find /proc/ -maxdepth 1 -type d | egrep "^/proc/[0-9]"` ; do
    #获取进程PID
    PID=`echo $DIR | cut -d / -f 3`

    #获取进程名称
    PROGNAME=`ps -p $PID -o comm --no-headers`

    #获取进程的所有分区中PSS值

    for PSS in `grep Pss $DIR/smaps 2>/dev/null| awk '{ print $2 }'`
    do
          #进程PSS值求和
      let SUM=$SUM+$PSS
    done

    if [ $SUM -gt 0 ] ; then 
      echo -e  "$PID   $SUM  $PROGNAME" >> /tmp/mysql_awr_sort.txt
    fi

    #总共swap分区值求和
    let OVERALL=$OVERALL+$SUM
    SUM=0 

  done
  sort -k 2 -nr /tmp/mysql_awr_sort.txt -o /tmp/mysql_awr_sort.txt
  echo  "$OVERALL"
 }



 GET_PHYSICS_MEMORY_PROC_USS () 
 {
  SUM=0
  OVERALL=0
  echo "" > /tmp/mysql_awr_sort.txt

  #获取进程目录
  for DIR in `find /proc/ -maxdepth 1 -type d | egrep "^/proc/[0-9]"` ; do
    #获取进程PID
    PID=`echo $DIR | cut -d / -f 3`

    #获取进程名称
    PROGNAME=`ps -p $PID -o comm --no-headers`

    #获取进程的所有分区中PSS值

    for USSC in `grep Private_Clean $DIR/smaps 2>/dev/null| awk '{ print $2 }'`
    do
      let SUM=$SUM+$USSC
    done

    for USSD in `grep Private_Dirty $DIR/smaps 2>/dev/null| awk '{ print $2 }'`
    do
      let SUM=$SUM+$USSD
    done

    if [ $SUM -gt 0 ] ; then 
       echo -e  "$PID   $SUM  $PROGNAME" >> /tmp/mysql_awr_sort.txt
    fi

    let OVERALL=$OVERALL+$SUM
    SUM=0 

  done

   sort -k 2 -nr /tmp/mysql_awr_sort.txt -o /tmp/mysql_awr_sort.txt
  echo  "$OVERALL"
 }

#获得MYSQLD 系统层面内存使用情况
GET_MYSQL_PHY_MEM()
{
  MYSQL_PID=$(pidof mysqld)
   #进程私有内存值求和
  for USSC in `grep Private_Clean /proc/${MYSQL_PID}/smaps 2>/dev/null| awk '{ print $2 }'`
    do        
      let SUM=$SUM+$USSC
    done

    for USSD in `grep Private_Dirty /proc/${MYSQL_PID}/smaps 2>/dev/null| awk '{ print $2 }'`
    do
      let SUM=$SUM+$USSD
    done

    PRIVATE_MEM=$SUM
    SUM=0 

   #进程共享内存值求和
   for PSS in `grep Pss /proc/${MYSQL_PID}/smaps 2>/dev/null| awk '{ print $2 }'`
    do

      let SUM=$SUM+$PSS
    done

    INCLUDE_SHARED_MEM=$SUM
    SUM=0 

      #进程全内存值求和

   for RSS in `grep Rss /proc/${MYSQL_PID}/smaps 2>/dev/null| awk '{ print $2 }'`
    do

      let SUM=$SUM+$RSS
    done

    RSS_MEM=$SUM
    SUM=0 

 #进程swap值求和
   for SWAP in `grep Swap /proc/${MYSQL_PID}/smaps 2>/dev/null| awk '{ print $2 }'`
    do         
      let SUM=$SUM+$SWAP
    done
    SWAP_MEM=$SUM
    SUM=0      

    echo "${MYSQL_PID}  $RSS_MEM    $INCLUDE_SHARED_MEM  $PRIVATE_MEM $SWAP_MEM "
}

 GET_OS_VERSION()
 {
   if [ -f /etc/redhat-release ] ; then 
    VERSION_A=$(cat /etc/redhat-release)
    echo "${VERSION_A}""<br>"
   fi

   if [ -f /proc/version ] ; then 
    VERSION_B=$(cat /proc/version)
    echo "${VERSION_B}""<br>"
    fi

 }

 GET_MYSQL_ERROR()
 {
    FIND_POST=''
    START_DATE_L=$(date  +%s -d "${START_DATE}")      #转换成UTC日期类型来比较
    END_DATE_L=$(date +%s -d "+7 day ${START_DATE}")  #求7天后UTC日期
    i=0

   if [[  -f ${MYSQL_ERROR_LOG} ]] ; then 
    cat ${MYSQL_ERROR_LOG} | grep -E 'Warning|ERROR' > /tmp/mysql_awr_error.txt

    while ( [ -z ${FIND_POST} ] && [ ${START_DATE_L} -lt ${END_DATE_L} ] ) do #如果没有找到,日期递加
      FIND_POST=$(cat -n /tmp/mysql_awr_error.txt |grep -m 1 "${START_DATE}T"|awk '{printf($1);printf ""}')

      if [[ -z ${FIND_POST} ]]; then 
        let i=${i}+1
        START_DATE_L=$(date +%s -d "+${i} day ${START_DATE}") #转成UTC才能对比
        START_DATE=$(date +'%Y-%m-%d' -d "@${START_DATE_L}")   #UTC日期转成字符才能过滤
      fi 
    done

    FILE_END=$(cat -n /tmp/mysql_awr_error.txt  |tail -1 |awk '{printf($1);print ""}')

    if [[ -n ${FIND_POST} ]]; then
     SIZE=$[${FILE_END}-${FIND_POST}]
     #WriteLogs "GET MYSQL ERROR POST AND SIZE : ${FIND_POST}  ${SIZE}"
     grep -A ${SIZE} "${START_DATE}T" /tmp/mysql_awr_error.txt > /tmp/mysql_awr_current_error.txt
    fi 
   else 
     WriteLogs "Not Find ${MYSQL_ERROR_LOG}"     
   fi 
 }

#================================================SQL Segments========================================================================
GET_DB_SCHEMA_LIST_SQL="select  distinct table_schema from information_schema.tables where table_schema not in ('mysql','sys','information_schema','performance_schema');"
GET_MYSQL_VERSION_SQL="SELECT version();"

TOP_SCHEMA_SQL="select  table_schema,
ROUND(SUM(TABLE_ROWS),2) as ALLSIZE_ROWS,
ROUND(SUM(DATA_LENGTH+INDEX_LENGTH+DATA_FREE)/1024/1024,2) as ALLSIZE_MB,
ROUND(SUM(DATA_LENGTH)/1024/1024,2) AS DATASIZE_MB,
ROUND(SUM(INDEX_LENGTH)/1024/1024,2) AS INDEXSIZE_MB
from information_schema.TABLES
GROUP BY table_schema
order by ALLSIZE_ROWS desc
limit 10;
"

TOP_TABLE_SQL="select TABLE_NAME,ROUND(ALL_LENGTH/1024/1024,2) as ALLSIZE_MB,TABLE_ROWS,ROUND(DATA_LENGTH/1024/1024,2)  AS DATASIZE_MB,ROUND(INDEX_LENGTH/1024/1024,2)  AS INDEXSIZE_MB,frag_rate,avg_row_length
from
(
select  TABLE_NAME,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,DATA_FREE, DATA_LENGTH+INDEX_LENGTH+DATA_FREE as ALL_LENGTH, RoUND(DATA_FREE/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE)*100,2) AS  frag_rate,avg_row_length
from information_schema.TABLES
where table_schema="\''${SCHEMA}'\'"
order by ALL_LENGTH desc
limit 20
) tmp;"

TO_FRAG_SQL="
select TABLE_NAME,ROUND(ALL_LENGTH/1024/1024,2) as ALLSIZE_MB,ROUND(DATA_LENGTH/1024/1024,2)  AS DATASIZE_MB,ROUND(INDEX_LENGTH/1024/1024,2)  AS INDEXSIZE_MB,frag_rate,TABLE_ROWS,avg_row_length
from
(
select  TABLE_NAME,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,DATA_FREE, DATA_LENGTH+INDEX_LENGTH+DATA_FREE as ALL_LENGTH, RoUND(DATA_FREE/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE)*100,2) AS  frag_rate,avg_row_length
from information_schema.TABLES
where table_schema="\''${SCHEMA}'\'"
order by frag_rate desc
limit 10
) tmp
where frag_rate >10;"

##events_statements_history =10000 没法保留1周
GET_TPS_SQL=" 
 SELECT DATE_FORMAT(Per_Second,'%Y-%m-%d') as Per_DAY,SUM(TPS) AS TOTAL_TPS,AVG(TPS) AS AVG_TPS,MAX(TPS) AS MAX_TPS
 FROM
 (
    select DATE_FORMAT(START_TIME,'%Y-%m-%d %H:%i:%S') as Per_Second,count(DIGEST) AS TPS
    from
    (
    SELECT 
    FROM_UNIXTIME( (unix_timestamp(sysdate()) - (select variable_value  from performance_schema.global_status  where variable_name = 'Uptime')) + TIMER_START/1000000000000 ) AS START_TIME,
    FROM_UNIXTIME( (unix_timestamp(sysdate()) - (select variable_value  from performance_schema.global_status  where variable_name = 'Uptime')) + TIMER_END/1000000000000 ) AS END_TIME,
    DIGEST,DIGEST_TEXT,
    TIMER_WAIT/1000000000000  AS RUN_SECONDS,
    LOCK_TIME/1000000000000   AS LOCK_SECONDS,
    SQL_TEXT,EVENT_NAME
    FROM performance_schema.events_statements_history
    WHERE EVENT_NAME in ('statement/sql/update','statement/sql/insert','statement/sql/delete')
) base1 
 where START_TIME between '${START_DATE_TIME}' and '${END_DATE_TIME}'
 group by Per_Second,EVENT_NAME
) DAY1
GROUP BY  Per_DAY
order by Per_DAY asc;"

##events_statements_history =10000 没法保留1周
GET_QPS_SQL=" 
 SELECT DATE_FORMAT(Per_Second,'%Y-%m-%d') as Per_DAY,SUM(QPS) AS TOTAL_QPS,AVG(QPS) AS AVG_QPS,MAX(QPS) AS MAX_QPS
 FROM
 (
    select DATE_FORMAT(START_TIME,'%Y-%m-%d %H:%i:%S') as Per_Second,count(DIGEST) AS QPS
    from
    (
    SELECT 
    FROM_UNIXTIME( (unix_timestamp(sysdate()) - (select variable_value  from performance_schema.global_status  where variable_name = 'Uptime')) + TIMER_START/1000000000000 ) AS START_TIME,
    FROM_UNIXTIME( (unix_timestamp(sysdate()) - (select variable_value  from performance_schema.global_status  where variable_name = 'Uptime')) + TIMER_END/1000000000000 ) AS END_TIME,
    DIGEST,DIGEST_TEXT,
    TIMER_WAIT/1000000000000  AS RUN_SECONDS,
    LOCK_TIME/1000000000000   AS LOCK_SECONDS,
    SQL_TEXT,EVENT_NAME
    FROM performance_schema.events_statements_history
    WHERE EVENT_NAME in ('statement/sql/select','statement/sql/update','statement/sql/insert','statement/sql/delete')
) base1 
 where START_TIME between '${START_DATE_TIME}' and  '${END_DATE_TIME}'
 group by Per_Second,EVENT_NAME
) DAY1
GROUP BY  Per_DAY
order by Per_DAY asc;"



#查询到运行时间 最长的5%的语句。
GET_SYS_TOP95_LONG_SQL=" select
 query,
db,
full_scan,
exec_count,
err_count,
warn_count,
total_latency,
max_latency,
avg_latency,
rows_sent,
rows_sent_avg,
rows_examined,
rows_examined_avg,
first_seen,
last_seen
from sys.statements_with_runtimes_in_95th_percentile 
limit 10;"

#总计执行时间最长的SQL语句
GET_SYS_TOP_LONG_TIME_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,
COUNT_STAR, 
sys.format_time(SUM_TIMER_WAIT) AS SUM_TIME,  
sys.format_time(MIN_TIMER_WAIT) AS MIN_TIME,  
sys.format_time(AVG_TIMER_WAIT) AS AVG_TIME,
sys.format_time(MAX_TIMER_WAIT) AS MAX_TIME,
sys.format_time(SUM_LOCK_TIME) AS SUM_LOCK_TIME,
SUM_ROWS_AFFECTED,SUM_ROWS_SENT,SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL 
ORDER BY SUM_TIME DESC 
LIMIT 10;"

GET_SYS_TOP_LOCK_TIME_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,
COUNT_STAR, 
sys.format_time(SUM_TIMER_WAIT) AS SUM_TIME,  
sys.format_time(MIN_TIMER_WAIT) AS MIN_TIME,  
sys.format_time(AVG_TIMER_WAIT) AS AVG_TIME,
sys.format_time(MAX_TIMER_WAIT) AS MAX_TIME,
sys.format_time(SUM_LOCK_TIME) AS SUM_LOCK_TIME,
SUM_ROWS_AFFECTED,SUM_ROWS_SENT,SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL 
ORDER BY SUM_LOCK_TIME DESC 
LIMIT 10; "


GET_SYS_TOP_EXECUTE_COUNT_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,
COUNT_STAR, 
sys.format_time(SUM_TIMER_WAIT) AS SUM_TIME,  
sys.format_time(MIN_TIMER_WAIT) AS MIN_TIME,  
sys.format_time(AVG_TIMER_WAIT) AS AVG_TIME,
sys.format_time(MAX_TIMER_WAIT) AS MAX_TIME,
sys.format_time(SUM_LOCK_TIME) AS SUM_LOCK_TIME,
SUM_ROWS_AFFECTED,SUM_ROWS_SENT,SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL 
ORDER BY COUNT_STAR DESC 
LIMIT 10;" 


GET_SYS_TOP_WAIT_EVENT_SQL="
SELECT EVENT_NAME,COUNT_STAR,sys.format_time(sum_timer_wait),sys.format_time(avg_timer_wait),sys.format_time(max_timer_wait) 
FROM performance_schema.events_waits_summary_global_by_event_name
order by sum_timer_wait desc
LIMIT 25;"

#慢查询
GET_WEEK_SLOW_SQL="SELECT db,CAST(sql_text AS CHAR  ) as SQLTEXT,
count(thread_id)            as total_exec_times,
round(avg(query_time),3) as avg_exec_seconds,
round(max(query_time),3) as max_exec_seconds,
sum(rows_sent)           as total_sent_rows,
round(avg(rows_sent),0)  as avg_sent_rows,
max(rows_sent)           as max_sent_rows,
sum(rows_examined)       as total_examined,
round(avg(rows_examined),0) as avg_examined,
max(rows_examined)          as max_examined,
DATE_FORMAT(min(start_time),'%Y-%m-%d %H:%i:%S')            as first_exec_time,
DATE_FORMAT(max(start_time),'%Y-%m-%d %H:%i:%S')            as last_exec_time
FROM mysql.slow_log
where 1=1
and start_time >= '${START_DATE_TIME}'
and start_time < '${END_DATE_TIME}'
AND DB="\''${SCHEMA}'\'"
group by db,sql_text
order by total_exec_times desc,avg_exec_seconds desc"



##events_waits_history_long =10000 没法保留1周 GET_WEEK_TOP_WAITEVENTS_SQL
GET_WEEK_TOP_WAIT_EVENTS_SQL="
SELECT V.EVENT_NAME,sys.format_time(V.WAIT_TIMES) AS WAIT_TIME,sys.format_bytes(V.SIZE) as SIZE,V.SPING_COUNT
FROM
(
    SELECT EVENT_NAME,
    SUM(TIMER_WAIT) AS WAIT_TIMES,
    SUM(NUMBER_OF_BYTES) AS SIZE,
    SUM(SPINS) AS SPING_COUNT
FROM
(
    SELECT EVENT_NAME, 
    FROM_UNIXTIME( (unix_timestamp(sysdate()) - (select variable_value  from performance_schema.global_status  where variable_name = 'Uptime')) + TIMER_START/1000000000000 )  AS START_TIME,
    TIMER_WAIT,
    SPINS,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,OBJECT_TYPE,OPERATION,NUMBER_OF_BYTES
    from performance_schema.events_waits_history_long 
) W
    WHERE    START_TIME  >= '${START_DATE_TIME}' AND START_TIME < '${END_DATE_TIME}'
    GROUP BY EVENT_NAME
    ORDER BY   WAIT_TIMES DESC
) V
LIMIT 10;"

##events_waits_history_long =10000 没法保留1周
GET_WEEK_TOP_WAITEVENTS_DETAIL_SQL="
SELECT  EVENT_NAME,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,OBJECT_TYPE,OPERATION,SPING_COUNT,
sys.format_time(V.WAIT_TIMES) AS WAIT_TIME,sys.format_bytes(V.SIZE) as SIZE
FROM 
(
 SELECT 
 EVENT_NAME,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,OBJECT_TYPE,OPERATION,
 SUM(TIMER_WAIT) AS WAIT_TIMES,SUM(NUMBER_OF_BYTES) AS SIZE,SUM(SPINS) AS SPING_COUNT
FROM
(
 SELECT EVENT_NAME, 
 FROM_UNIXTIME( (unix_timestamp(sysdate()) - (select variable_value  from performance_schema.global_status  where variable_name = 'Uptime')) + TIMER_START/1000000000000 )  AS START_TIME,
 TIMER_WAIT,
 SPINS,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,OBJECT_TYPE,OPERATION,NUMBER_OF_BYTES
 from performance_schema.events_waits_history_long   
) W
 WHERE    START_TIME >= '${START_DATE_TIME}' AND START_TIME < '${END_DATE_TIME}'
 GROUP BY EVENT_NAME,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,OBJECT_TYPE,OPERATION
 ORDER BY   WAIT_TIMES DESC
) V
LIMIT 100;"


##events_statements_summary_by_digest =10000 有可能没法保留1周
GET_WEEK_TOP_RUNTIME_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,
sys.format_time(SUM_TIMER_WAIT) AS TOTAL_RUN_SECONDS,
sys.format_time(MIN_TIMER_WAIT) AS MIN_RUN_SECONDS,
sys.format_time(AVG_TIMER_WAIT) AS AVG_RUN_SECONDS,
sys.format_time(MAX_TIMER_WAIT) AS MAX_RUN_SECONDS,
sys.format_time(SUM_LOCK_TIME)  AS LOCK_RUN_SECONDS
from performance_schema.events_statements_summary_by_digest
WHERE    1=1
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
AND SCHEMA_NAME="\''${SCHEMA}'\'"
ORDER BY TOTAL_RUN_SECONDS DESC
LIMIT 10;"

##events_statements_summary_by_digest =10000 有可能没法保留1周
GET_WEEK_TOP_EXECUTE_COUNT_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,
sys.format_time(SUM_TIMER_WAIT) AS TOTAL_RUN_SECONDS,
sys.format_time(MIN_TIMER_WAIT) AS MIN_RUN_SECONDS,
sys.format_time(AVG_TIMER_WAIT) AS AVG_RUN_SECONDS,
sys.format_time(MAX_TIMER_WAIT) AS MAX_RUN_SECONDS,
sys.format_time(SUM_LOCK_TIME)  AS LOCK_RUN_SECONDS
from performance_schema.events_statements_summary_by_digest
WHERE    1=1
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
AND SCHEMA_NAME="\''${SCHEMA}'\'"
ORDER BY COUNT_STAR DESC
LIMIT 10;"


#平均执行时间最长的语句
GET_WEEK_TOP_AVGTIME_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR ,
sys.format_time(SUM_TIMER_WAIT) AS TOTAL_RUN_SECONDS,
sys.format_time(MIN_TIMER_WAIT) AS MIN_RUN_SECONDS,
sys.format_time(AVG_TIMER_WAIT) AS AVG_RUN_SECONDS,
sys.format_time(MAX_TIMER_WAIT) AS MAX_RUN_SECONDS,
sys.format_time(SUM_LOCK_TIME)  AS LOCK_RUN_SECONDS
from performance_schema.events_statements_summary_by_digest
WHERE    1=1
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
AND SCHEMA_NAME="\''${SCHEMA}'\'"
ORDER BY AVG_RUN_SECONDS DESC
LIMIT 10;"  


#TOP 锁时间时间最长的语句
GET_WEEK_TOP_LOCKTIME_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,
sys.format_time(MIN_TIMER_WAIT) AS MIN_RUN_SECONDS,
sys.format_time(AVG_TIMER_WAIT) AS AVG_RUN_SECONDS,
sys.format_time(MAX_TIMER_WAIT) AS MAX_RUN_SECONDS,
sys.format_time(SUM_LOCK_TIME)  AS LOCK_RUN_SECONDS
from performance_schema.events_statements_summary_by_digest
WHERE    1=1
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
AND SCHEMA_NAME="\''${SCHEMA}'\'"
ORDER BY LOCK_RUN_SECONDS DESC
LIMIT 10;"  


#top 5 出检查行数最多的SQL语句
GET_WEEK_TOP_EXAMINEDROW_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_EXAMINED,
sys.format_time(MIN_TIMER_WAIT) AS MIN_RUN_SECONDS,
sys.format_time(AVG_TIMER_WAIT) AS AVG_RUN_SECONDS,
sys.format_time(MAX_TIMER_WAIT) AS MAX_RUN_SECONDS,
sys.format_time(SUM_LOCK_TIME)  AS LOCK_RUN_SECONDS
from performance_schema.events_statements_summary_by_digest
WHERE    1=1
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
AND SCHEMA_NAME="\''${SCHEMA}'\'"
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 10;"  

#--top 5 返回行数最多的SQL语句
GET_WEEK_TOP_SENTROW_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,
sys.format_time(MIN_TIMER_WAIT) AS MIN_RUN_SECONDS,
sys.format_time(AVG_TIMER_WAIT) AS AVG_RUN_SECONDS,
sys.format_time(MAX_TIMER_WAIT) AS MAX_RUN_SECONDS,
sys.format_time(SUM_LOCK_TIME)  AS LOCK_RUN_SECONDS
from performance_schema.events_statements_summary_by_digest
WHERE    1=1
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
AND SCHEMA_NAME="\''${SCHEMA}'\'"
ORDER BY SUM_ROWS_SENT DESC
LIMIT 10;" 

#--top 10排序行数最多的SQL语句
GET_WEEK_TOP_SORTROW_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_SORT_ROWS,
sys.format_time(MIN_TIMER_WAIT) AS MIN_RUN_SECONDS,
sys.format_time(AVG_TIMER_WAIT) AS AVG_RUN_SECONDS,
sys.format_time(MAX_TIMER_WAIT) AS MAX_RUN_SECONDS,
sys.format_time(SUM_LOCK_TIME)  AS LOCK_RUN_SECONDS
from performance_schema.events_statements_summary_by_digest
WHERE    1=1
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
AND SCHEMA_NAME="\''${SCHEMA}'\'"
ORDER BY SUM_SORT_ROWS DESC
LIMIT 10;" 


#--top 10 更新行数最多的SQL语句
GET_WEEK_TOP_AFFECTED_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_AFFECTED,
sys.format_time(MIN_TIMER_WAIT) AS MIN_RUN_SECONDS,
sys.format_time(AVG_TIMER_WAIT) AS AVG_RUN_SECONDS,
sys.format_time(MAX_TIMER_WAIT) AS MAX_RUN_SECONDS,
sys.format_time(SUM_LOCK_TIME)  AS LOCK_RUN_SECONDS
from performance_schema.events_statements_summary_by_digest
WHERE    1=1
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
AND SCHEMA_NAME="\''${SCHEMA}'\'"
ORDER BY SUM_ROWS_AFFECTED DESC
LIMIT 10;" 


#--top 5 磁盘临时表数最多的SQL语句
GET_WEEK_TOP_DISKTMP_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_CREATED_TMP_DISK_TABLES,
sys.format_time(MIN_TIMER_WAIT) AS MIN_RUN_SECONDS,
sys.format_time(AVG_TIMER_WAIT) AS AVG_RUN_SECONDS,
sys.format_time(MAX_TIMER_WAIT) AS MAX_RUN_SECONDS,
sys.format_time(SUM_LOCK_TIME)  AS LOCK_RUN_SECONDS
from performance_schema.events_statements_summary_by_digest
WHERE    1=1
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
AND SCHEMA_NAME="\''${SCHEMA}'\'"
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 10;" 


#--top 5 未使用索引最多的SQL语句
GET_WEEK_TOP_NO_INDEX_SQL="
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_NO_INDEX_USED,
sys.format_time(MIN_TIMER_WAIT) AS MIN_RUN_SECONDS,
sys.format_time(AVG_TIMER_WAIT) AS AVG_RUN_SECONDS,
sys.format_time(MAX_TIMER_WAIT) AS MAX_RUN_SECONDS,
sys.format_time(SUM_LOCK_TIME)  AS LOCK_RUN_SECONDS
from performance_schema.events_statements_summary_by_digest
WHERE    1=1
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
AND SCHEMA_NAME="\''${SCHEMA}'\'"
ORDER BY SUM_NO_INDEX_USED DESC
LIMIT 10;" 



#--全局内存设置
GET_GOBAL_MEM_OPTION_SQL="
SELECT
ROUND(@@innodb_buffer_pool_size/1024/1024,2) as BUF_POOL ,
ROUND(@@innodb_log_buffer_size/1024/1024,2) as LOG_BUF,
(SELECT COUNT(host) FROM information_schema.processlist ) as ALL_connect,
ROUND(@@tmp_table_size/1024/1024,2) as TMP_TABLE,
ROUND(@@read_buffer_size/1024/1024,2) as READ_BUF,
ROUND(@@sort_buffer_size/1024/1024,2) as SORT_BUF,
ROUND(@@join_buffer_size/1024/1024,2) as JOIN_BUF,
ROUND(@@read_rnd_buffer_size/1024/1024,2) as READ_RND_BUF,
ROUND(@@binlog_cache_size/1024/1024,2) as BINLOG_CACHE,
ROUND(@@thread_stack/1024/1024,2) as THREAD_STACK,
(SELECT COUNT(host) FROM information_schema.processlist where command<>'Sleep') as Active_connect;
"

#TOP 事件 内存使用
GET_EVENT_MEMORY_SQL="
SELECT event_name,
sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED) AS MEMORY
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;"

#对象等待统计
GET_OBJECT_TOTAL_SQL="
SELECT  
OBJECT_SCHEMA,OBJECT_TYPE,OBJECT_NAME,COUNT_STAR,
sys.format_time(SUM_TIMER_WAIT) AS SUM_TIMER_WAIT,
sys.format_time(MIN_TIMER_WAIT) AS MIN_TIMER_WAIT,
sys.format_time(AVG_TIMER_WAIT) AS AVG_TIMER_WAIT,
sys.format_time(MAX_TIMER_WAIT) AS MAX_TIMER_WAIT
FROM performance_schema.objects_summary_global_by_type 
WHERE OBJECT_SCHEMA='"\''${SCHEMA}'\'"
order by sum_timer_wait desc
limit 250; "

#HOST INFO
GET_HOST_SUMMARY_SQL="select * from sys.host_summary ;"
GET_HOST_IO_SQL='SELECT host as "客户端IP",IOS AS "文件I/O事件总次数", IO_LATENCY AS "文件I/O事件总执行时间" FROM sys.host_summary_by_file_io;'
GET_HOST_IO_TYPE_SQL="select * from sys.host_summary_by_file_io_type;"
GET_HOST_STAGES_SQL="select * from sys.host_summary_by_stages;"
GET_HOST_STATEMENT_SQL="select * from sys.host_summary_by_statement_latency ;"
GET_HOST_STATEMENT_TYPE_SQL="select * from sys.host_summary_by_statement_type;"

#USER INFO
GET_USER_SUMMARY_SQL="select * from sys.user_summary;"
GET_USER_IO_SQL="select * from sys.user_summary_by_file_io;"
GET_USER_IO_TYPE="select * from sys.user_summary_by_file_io_type;"
GET_USER_STAGES_SQL="select * from sys.user_summary_by_stages;"
GET_USER_STATEMENT_SQL="select * from sys.user_summary_by_statement_latency;"
GET_USE_STATEMENT_TYPE_SQL="select * from sys.user_summary_by_statement_type;"
GET_THREAD_IO_SQL="select * from sys.io_by_thread_by_latency;"

GET_FILE_IO_BYTES_SQL="select * from sys.io_global_by_file_by_bytes LIMIT 250;"
GET_FILE_IO_TIME_SQL="select * from sys.io_global_by_file_by_latency LIMIT 250;"
GET_FILE_IO_SUMMARY_BYTES_SQL="select * from sys.io_global_by_wait_by_bytes LIMIT 250;"
GET_FILE_IO_SUMMARY_TIME_SQL="select * from sys.io_global_by_wait_by_latency;"
GET_NEW_IO_SQL="select * from sys.latest_file_io ORDER BY LATENCY DESC LIMIT 250;"

GET_GOBAL_MEMORY_SQL="select * from sys.memory_global_total;"
GET_INNODB_BUF_SCHEMA_SQL="select * from sys.innodb_buffer_stats_by_schema;"
GET_INNODB_BUF_TABLE_SQL="select * from sys.innodb_buffer_stats_by_table WHERE pages > 100 limit 200;"
GET_HOST_MEMORY_BYTES_SQL="select * from sys.memory_by_host_by_current_bytes;"
GET_THREAD_MEMORY_BYTES_SQL="select * from sys.memory_by_thread_by_current_bytes;"
GET_USER_CURRENT_MEMORY_BYTES_SQL="select * from sys.memory_by_user_by_current_bytes;"
GET_EVENT_CURRENT_MEMEORY_BYTES_SQL="select * from sys.memory_global_by_current_bytes LIMIT 250;"
#Wait Event Info

GET_WAIT_EVENT_SQL="select * from sys.wait_classes_global_by_avg_latency;"
GET_HOST_WAIT_SQL="select * from sys.waits_by_host_by_latency;"
GET_USER_WAIT_SQL="select * from sys.waits_by_user_by_latency;"
GET_GLOBAL_WAIT_SQL="select * from sys.waits_global_by_latency;"
GET_LOCK_WAIT_SQL="select * from sys.innodb_lock_waits;"

GET_PROCESSLIST_SQL="select * from sys.processlist;"
GET_SESSION_SQL="select * from sys.session where 1=1 and conn_id!=connection_id();"
GET_CURRENT_TABLE_LOCK_WAIT_SQL="select * from sys.schema_table_lock_waits;"

#----schema---------------------------
GET_AUTO_INCREMENT_SQL="select * from sys.schema_auto_increment_columns where table_schema="\''${SCHEMA}'\'";"
GET_INDEXS_STATIS_SQL="select * from sys.schema_index_statistics  where table_schema="\''${SCHEMA}'\'";"
GET_REDUN_INDEX_SQL="select * from sys.schema_redundant_indexes  where table_schema="\''${SCHEMA}'\'";"
GET_TABLE_STATIS_SQL="select * from sys.schema_table_statistics  where table_schema="\''${SCHEMA}'\'";"
GET_BUF_HOT_TABLE_SQL="select * from sys.schema_table_statistics_with_buffer  where table_schema="\''${SCHEMA}'\'";"
GET_UNUSED_INDEX_SQL="SELECT * FROM sys.schema_unused_indexes  where object_schema="\''${SCHEMA}'\'";"

GET_FULL_TABLE_SCAN_SQL="select * from sys.schema_tables_with_full_table_scans  where object_schema="\''${SCHEMA}'\'";"
GET_STATEMENT_ANLAYZ_SQL="select * from sys.statement_analysis  where db="\''${SCHEMA}'\'" limit 500;"
GET_FULL_SCAN_STATEMENT_SQL="select * from sys.statements_with_full_table_scans where db="\''${SCHEMA}'\'";"
GET_FILESORT_STATEMENT_SQL="select * from sys.statements_with_sorting where db="\''${SCHEMA}'\'";"
GET_TEMP_STATEMENT_SQL="select * from sys.statements_with_temp_tables where db="\''${SCHEMA}'\'";"


#check sql
#----------------------------------------------------------------------------------------------
#1.太多逻辑读的SQL 平均每次大于2万行
GET_WEEK_TOP_LOGIC_READ_SQL="SELECT SCHEMA_NAME,
DIGEST AS digest,
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT,
ROUND(SUM_ROWS_AFFECTED/COUNT_STAR, 0) AS rows_affected_avg,
ROUND(SUM_ROWS_SENT/COUNT_STAR, 0) AS rows_sent_avg,
ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) AS rows_examined_avg,
FIRST_SEEN,
LAST_SEEN 
FROM performance_schema.events_statements_summary_by_digest 
where  DIGEST_TEXT not like '%SHOW%' 
and DIGEST_TEXT not like 'desc%'
and SCHEMA_NAME="\''${SCHEMA}'\'"
and ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) >20000
and COUNT_STAR >200
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
order by  ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0)  desc;"

#大事务影响行超过10万行
GET_WEEK_BIG_TRANS_SQL="SELECT SCHEMA_NAME,
DIGEST AS digest,
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT,
ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) AS rows_affected_avg,
ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS rows_sent_avg,
ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS rows_examined_avg,
FIRST_SEEN,
LAST_SEEN 
FROM performance_schema.events_statements_summary_by_digest 
where  DIGEST_TEXT not like '%SHOW%' and  DIGEST_TEXT not like 'desc%'
and SCHEMA_NAME="\''${SCHEMA}'\'"
and ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) >100000
and COUNT_STAR >200
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
order by ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) desc;"

#查询语句返回太多行以及分页返回超过千行
GET_WEEK_RETRUN_ROWS_SQL="
SELECT SCHEMA_NAME,
DIGEST AS digest,
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT,
ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) AS rows_affected_avg,
ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS rows_sent_avg,
ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS rows_examined_avg,
FIRST_SEEN,
LAST_SEEN 
FROM performance_schema.events_statements_summary_by_digest 
where  DIGEST_TEXT not like '%SHOW%' and  DIGEST_TEXT not like 'desc%'
and SCHEMA_NAME="\''${SCHEMA}'\'"
and ROUND(SUM_ROWS_SENT / COUNT_STAR, 0)>1000
and COUNT_STAR >200
and ((last_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}') or (first_seen >= '${START_DATE_TIME}'  and last_seen < '${END_DATE_TIME}'))
order by ROUND(SUM_ROWS_SENT / COUNT_STAR, 0);"

#拥有不被推荐的数据类型
GET_NOT_RECOMMEND_TYPE_SQL="select TABLE_SCHEMA, TABLE_NAME,COLUMN_NAME,DATA_TYPE 
from information_schema.COLUMNS 
where DATA_TYPE in ('enum','set','bit','binary') 
and table_schema="\''${SCHEMA}'\'"
order by table_name;"


#拥有超过5个索引的表
GET_FIVE_INDEX_TABLE_SQL="select table_schema,table_name,count(*) AS num_idx
from 
  (select distinct table_schema,table_name, INDEX_NAME 
   from information_schema.STATISTICS
   where  table_schema="\''${SCHEMA}'\'"
   ) a 
group by table_schema,table_name 
having   num_idx>5 
order by table_schema,num_idx desc,table_name ;"

#有主键的表
GET_NO_PRIMARY_TABLE_SQL="select  t.table_name 
from information_schema.tables t
left join
 (select table_name from information_schema.STATISTICS
  where INDEX_NAME='PRIMARY'
  and  table_schema ="\''${SCHEMA}'\'"
  group by   table_name 
 ) a
on  t.table_name=a.table_name
where t.table_schema ="\''${SCHEMA}'\'"
and a.table_name is null
order by table_name;"

#组合索引超过5个字段的
GET_FIVE_INDEX_COL_SQL="select table_schema, table_name,index_name,count(index_name) num_col
from information_schema.STATISTICS 
where 
table_schema="\''${SCHEMA}'\'"
and NON_UNIQUE=1
group by table_schema,table_name,index_name 
having   num_col>5  
order by  table_schema, num_col,table_name,index_name;"

#表注解为空的
GET_TAB_COMM_NULL_SQL="select TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT 
from information_schema.TABLES 
where
table_schema="\''${SCHEMA}'\'"
and TABLE_COMMENT=''
order by table_name;"

#列注解为空的
GET_COL_COM_NULL_SQL="select distinct TABLE_SCHEMA,TABLE_NAME,column_name,COLUMN_COMMENT 
from information_schema.COLUMNS 
where COLUMN_COMMENT='' 
and table_schema="\''${SCHEMA}'\'"
and column_name not in ('id')
order by table_name;"

#列注解包含值域的
GET_COL_COMM_VAULE_SQL="select distinct TABLE_SCHEMA,TABLE_NAME,column_name ,COLUMN_COMMENT
from information_schema.COLUMNS
where COLUMN_COMMENT regexp '[0-9]'
and table_schema="\''${SCHEMA}'\'"
order by table_name;"

#数据库字符集
GET_SCHEMA_CHARSET_SQL="select schema_name,default_character_set_name,default_collation_name 
from information_schema.schemata 
where default_character_set_name != '${CHARTSET}'
and schema_name="\''${SCHEMA}'\'";"

#表字符集
GET_TABLE_CHAR_SORT_SQL="select TABLE_SCHEMA, TABLE_NAME,TABLE_COLLATION
  from information_schema.tables
where TABLE_COLLATION !='${CHART_COLLATION}'
and table_schema="\''${SCHEMA}'\'"
order by table_name;"

#列字符集
GET_COL_CHAR_SET_SQL="select TABLE_SCHEMA, TABLE_NAME,column_name, CHARACTER_SET_NAME
  from information_schema.columns
where 
   table_schema="\''${SCHEMA}'\'"
   and CHARACTER_SET_NAME != '${CHARTSET}'
order by table_name;"


#拥有超过30个列的表
GET_TABLE_COL_OVER_SQL="select TABLE_SCHEMA, TABLE_NAME,count(COLUMN_NAME) num_col
from information_schema.COLUMNS 
where 
table_schema="\''${SCHEMA}'\'"
group by TABLE_SCHEMA, TABLE_NAME 
having num_col>30
order by table_name;"

#主键拥有3个列以上的
GET_PRIMARY_THREE_COL_SQL="select table_schema, table_name,index_name,count(COLUMN_NAME) num_col
from information_schema.STATISTICS 
where INDEX_NAME='PRIMARY' 
and table_schema="\''${SCHEMA}'\'"
group by table_schema,table_name
having   num_col>3  
order by  table_schema, num_col,table_name,index_name;"

#索引第一列不够多的选择值
GET_INDEX_FIRST_COL_SELECT_SQL="SELECT 
    first.TABLE_SCHEMA,
    first.TABLE_NAME,
    first.INDEX_NAME,
    first.COLUMN_NAME col1,
    first.CARDINALITY CARDINALITY1,
    second.COLUMN_NAME col2,
    second.CARDINALITY CARDINALITY2
FROM
    ((SELECT 
        TABLE_SCHEMA,
            TABLE_NAME,
            INDEX_SCHEMA,
            INDEX_NAME,
            COLUMN_NAME,
            SEQ_IN_INDEX,
            CARDINALITY
    FROM
        information_schema.STATISTICS
    WHERE
        table_schema = "\''${SCHEMA}'\'"
            AND SEQ_IN_INDEX = 1) first, (SELECT 
        TABLE_SCHEMA,
            TABLE_NAME,
            INDEX_SCHEMA,
            INDEX_NAME,
            COLUMN_NAME,
            SEQ_IN_INDEX,
            CARDINALITY
    FROM
        information_schema.STATISTICS
    WHERE
        table_schema = "\''${SCHEMA}'\'"
            AND SEQ_IN_INDEX = 2) second)
WHERE
    first.TABLE_SCHEMA = second.TABLE_SCHEMA
        AND first.TABLE_NAME = second.TABLE_NAME
        AND first.INDEX_NAME = second.INDEX_NAME
        AND second.CARDINALITY > first.CARDINALITY
ORDER BY first.TABLE_NAME;"



#拥有外键的表
GET_Foreign_KEY_TAB_SQL="select table_name,column_name,constraint_name,referenced_table_name,referenced_column_name 
from  information_schema.key_column_usage 
where referenced_table_name is not null 
and constraint_schema="\''${SCHEMA}'\'"
order by TABLE_NAME;"

#使用rand函数来排序的
GET_RAND_SORT_SQL="select SCHEMA_NAME,DIGEST,DIGEST_TEXT 
from performance_schema.events_statements_summary_by_digest 
where DIGEST_TEXT like '%ORDER BY \`rand\`%' 
and SCHEMA_NAME="\''${SCHEMA}'\'";"

#使用 select *
GET_SELECT_STAR_SQL="select count_star,SCHEMA_NAME, DIGEST,DIGEST_TEXT 
from performance_schema.events_statements_summary_by_digest 
where DIGEST_TEXT like '%select \*%' 
and SCHEMA_NAME="\''${SCHEMA}'\'"
order by count_star desc;"

#使用 DISTINCT * 
GET_DISTINCT_STAR_SQL="select SCHEMA_NAME,DIGEST,DIGEST_TEXT 
from performance_schema.events_statements_summary_by_digest 
where DIGEST_TEXT like '%DISTINCTROW \*%'
and SCHEMA_NAME="\''${SCHEMA}'\'";"

#表列名相同,大小类型不同
GET_TAB_COL_SIZE_TYPE_SQL="
select distinct A.*
from
(
select  table_name,column_name,data_type,CHARACTER_MAXIMUM_LENGTH
from information_schema.columns IC
where IC.table_schema="\''${SCHEMA}'\'"
AND COLUMN_NAME NOT IN ('status','create_time','update_time','id')
)A
INNER JOIN 
(
select  table_name,column_name,data_type,CHARACTER_MAXIMUM_LENGTH
from information_schema.columns IC
where IC.table_schema="\''${SCHEMA}'\'"
AND COLUMN_NAME NOT IN ('status','create_time','update_time','id')
)B  ON A.COLUMN_NAME=B.COLUMN_NAME AND A.TABLE_NAME<>B.TABLE_NAME 
AND ( A.data_type<>B.data_type OR  A.CHARACTER_MAXIMUM_LENGTH<>B.CHARACTER_MAXIMUM_LENGTH)
ORDER BY A.COLUMN_NAME;"

#BUSINESS
#------------------------------------------------------------------------------------------------




GET_SLAVE_INFO_SQL="show slave status \G;"

GET_SERVIE_STATUS_SQL="SELECT  * FROM  performance_schema.global_status"
GET_INNODB_STATUS_SQL="show engine innodb status;"

#Change Buffer占据缓冲池总页的百分比
GET_CHANGE_BUF_RATE_SQL="
SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
            WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
            (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
            (SELECT ((change_buffer_pages/total_pages)*100))
            AS change_buffer_page_percentage;
"


#============================================================CODE SEGMETN===================================================================




WriteLogs "START_DATE_TIME:${START_DATE_TIME}"
WriteLogs "CLOSE_DATE:${END_DATE_TIME}"

if [[ ! -f  ${CHECK_RESULT_FILE} ]] ; then 
  touch ${CHECK_RESULT_FILE} 
fi

CREATE_HTML_HEAD
OUTPUT_H2_TITLE  "操作系统信息"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "系统版本信息" 
OUT_PUT_TABLE_HEAD    
OUT_PUT_TITEL "版本信息"
OUT_PUT_ROW_HEAD
OUT_PUT_SINGLE_LINES "$(GET_OS_VERSION)"
OUT_PUT_ROW_TAIL
OUT_PUT_TABLE_TAIL 


OUTPUT_H4_TITLE "SYS SPACE"                    ##Print the title of the table
OUT_PUT_TABLE_HEAD                          ##Print table header
OUT_PUT_TITEL "$(df -h |head -1|awk '{ for (i=1; i<7; i++ ) printf("%s ",$i);print ""}')"           ##打印表格字段
OUT_PUT_LINES "$(df -h |grep ${SYS_DIR})"   ##Print each row of the table
OUT_PUT_TABLE_TAIL                          ##Print the tail of the table


OUTPUT_H4_TITLE "MYSQL SPACE"
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL  "Size    Dir"                 ##Table fields (space delimited)
OUT_PUT_LINES "$(du -h ${DB_DIR} |tail -1 )"
OUT_PUT_TABLE_TAIL 

OUTPUT_H4_TITLE "数据库备份空间"
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL  "Size    Dir"                 ##
if [[ -d ${BAK_DIR} ]]; then 
  OUT_PUT_LINES "$(du -h ${BAK_DIR} |tail -1 )"
else 
 WriteLogs "ERROR:BAKUP DIR NOT FOUND ! ${BAK_DIR}" 
fi  
OUT_PUT_TABLE_TAIL


OUTPUT_H4_TITLE "内存信息"
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL  "TYPE TOTAL USED FREE SHARED BUFF AVAILABLE"
OUT_PUT_LINES "$(free -m |tail -2|grep Mem )"
OUT_PUT_LINES "$(free -m |tail -2|grep Swap)"
OUT_PUT_TABLE_TAIL

OUTPUT_H4_TITLE "内存细节信息"
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL  "TYPE  num  unit"
OUT_PUT_LINES "$(cat /proc/meminfo |grep  'MemAvailable' )"
OUT_PUT_LINES "$(cat /proc/meminfo |grep  'AnonHugePages')"
OUT_PUT_LINES "$(cat /proc/meminfo |grep  'PageTables')"
OUT_PUT_LINES "$(cat /proc/meminfo |grep  'SwapCached')"
OUT_PUT_LINES "$(cat /proc/meminfo |grep  'Slab' )"
OUT_PUT_LINES "$(cat /proc/meminfo |grep  'SReclaimable' )"
OUT_PUT_LINES "$(cat /proc/meminfo |grep  'Shmem' )"
OUT_PUT_LINES "$(cat /proc/meminfo |grep  'Mapped' )"
OUT_PUT_LINES "$(cat /proc/meminfo |grep  'AnonPages' )" 
OUT_PUT_LINES "$(cat /proc/meminfo |grep  'Mlocked' )" 
OUT_PUT_TABLE_TAIL


OUTPUT_H4_TITLE "TOP SWAP PROC "
TOTAL=$(GET_SWAP)
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL "PID  SWAP_USED_KB PROGNAME"
if [[ -f /tmp/mysql_awr_sort.txt ]] ; then 
while read line; 
do
OUT_PUT_LINES "$line";
done < /tmp/mysql_awr_sort.txt
fi
OUT_PUT_LINES "TOTAL ${TOTAL} KB"
OUT_PUT_TABLE_TAIL



OUTPUT_H4_TITLE "TOP PROC PHYSICS MEMORY  "
TOTAL=$(GET_PHYSICS_MEMORY)
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL "PID  PHYSICS_MEMORY_USED_KB PROGNAME"
if [[ -f /tmp/mysql_awr_sort.txt ]] ; then 
    while read line; 
    do
    OUT_PUT_LINES "$line";
    done < /tmp/mysql_awr_sort.txt
fi
OUT_PUT_LINES "TOTAL ${TOTAL} KB"
OUT_PUT_TABLE_TAIL


OUTPUT_H4_TITLE "TOP PROC PHYSICS MEMORY AVG SHARD"
TOTAL=$(GET_PHYSICS_MEMORY_AVG_SHARD)
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL "PID  PHYSICS_MEMORY_USED_KB PROGNAME"
if [[ -f /tmp/mysql_awr_sort.txt ]] ; then 
    while read line; 
    do
    OUT_PUT_LINES "$line";
    done < /tmp/mysql_awr_sort.txt
fi
OUT_PUT_LINES "TOTAL ${TOTAL} KB"
OUT_PUT_TABLE_TAIL


OUTPUT_H4_TITLE "TOP PROC PRIVATE PHYSICS MEMORY "
TOTAL=$(GET_PHYSICS_MEMORY_PROC_USS)
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL "PID  PHYSICS_MEMORY_USED_KB PROGNAME"
if [[ -f /tmp/mysql_awr_sort.txt ]] ; then 
    while read line; 
    do
    OUT_PUT_LINES "$line";
    done < /tmp/mysql_awr_sort.txt
fi
OUT_PUT_LINES "TOTAL ${TOTAL} KB"
OUT_PUT_TABLE_TAIL

OUTPUT_H4_TITLE "MYSQL PHYSICS MEMORY "
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL  "PID  PHY_MEM AVG_SHARED_MEM PRIVATE_MEM  SWAP_MEM"
OUT_PUT_LINES "$(GET_MYSQL_PHY_MEM)"
OUT_PUT_TABLE_TAIL


OUTPUT_H4_TITLE "MYSQL OOM INFO"
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL "OOM_TYPE   OOM"
OUT_PUT_LINES $(Get_Mysqld_OOM_SCORE 1)
OUT_PUT_LINES $(Get_Mysqld_OOM_SCORE 2)
OUT_PUT_LINES $(Get_Mysqld_OOM_SCORE 3)
OUT_PUT_LINES $(Get_Mysqld_OOM_SCORE 4)
OUT_PUT_TABLE_TAIL


OUTPUT_H4_TITLE "CPU INFO"
OUT_PUT_TABLE_HEAD
sar_cpu
OUT_PUT_TABLE_TAIL



OUTPUT_H2_TITLE  "MYSQL 实例信息"

OUTPUT_H4_TITLE "MYSQL 版本"
Target_MysqlDB $GET_MYSQL_VERSION_SQL
OUT_PUT_NEW_LINE


OUTPUT_H4_TITLE "MYSQL ERROR AND WARNING INFO:"
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL "数据库告警和错误信息"
GET_MYSQL_ERROR
if  [[ -f /tmp/mysql_awr_current_error.txt  ]] ; then 
    maxline=$(wc -l < /tmp/mysql_awr_current_error.txt)
    if [[ ${maxline} -gt 0 ]] ; then 
    while read line; 
    do
        OUT_PUT_SINGLE_LINES "$line";
    done < /tmp/mysql_awr_current_error.txt
    else
        OUT_PUT_SINGLE_LINES "没有找到错误告警信息"
    fi 
else 
    OUT_PUT_SINGLE_LINES "没有找到错误告警信息"
fi 
OUT_PUT_TABLE_TAIL




OUTPUT_H4_TITLE "SLAVE INFO:"
SALVE_MysqlDB $GET_SLAVE_INFO_SQL
Parse_salve_txt
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL "KEY   VAULE"
OUT_PUT_LINES "MASTER_UUID  ${MASTER_UUID}" 
OUT_PUT_LINES "SLAVE_IO_STATE  ${SLAVE_IO_STATE}" 
OUT_PUT_LINES "READ_MASTER_POST  ${READ_MASTER_POST}" 
OUT_PUT_LINES "Relay_Master_Log_File  ${Relay_Master_Log_File}" 
OUT_PUT_LINES "EXEC_MASTER_POST  ${EXEC_MASTER_POST}" 
OUT_PUT_LINES "BEHIND_SECONDS  ${BEHIND_SECONDS}" 
OUT_PUT_LINES "SLAVE_IO_RUNNING  ${SLAVE_IO_RUNNING}"
OUT_PUT_LINES "SLAVE_SQL_RUNNING  ${SLAVE_SQL_RUNNING}"
OUT_PUT_LINES "REPLICATE_DO_DB  ${REPLICATE_DO_DB}" 
OUT_PUT_LINES "SQLDELAY  ${SQLDELAY}" 
OUT_PUT_LINES "SALVE_SQL_RUN_STATE  ${SALVE_SQL_RUN_STATE}" 
OUT_PUT_LINES "LAST_SQL_ERROR  ${LAST_SQL_ERROR}" 
OUT_PUT_LINES "LAST_IO_ERROR  ${LAST_IO_ERROR}" 
OUT_PUT_LINES "RETRIEVED_GTID  ${RETRIEVED_GTID}" 
OUT_PUT_LINES "EXECUTED_GTID  ${EXECUTED_GTID}" 
OUT_PUT_TABLE_TAIL 

OUTPUT_H4_TITLE "死锁信息:"                 
OUT_PUT_TABLE_HEAD                           
OUT_PUT_TITEL "DEAD_LOCK"                    
deadlock 
OUT_PUT_TABLE_TAIL 

##The following MySQL HTML output SQL contents (including field names):

OUTPUT_H4_TITLE "TPS"
Target_MysqlDB $GET_TPS_SQL
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "QPS"
Target_MysqlDB $GET_QPS_SQL
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "查询到运行时间 最长的5%的语句"
Target_MysqlDB $GET_SYS_TOP95_LONG_SQL
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "执行时间最长的SQL语句"
Target_MysqlDB $GET_SYS_TOP_LONG_TIME_SQL
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "持有锁最长的SQL"
Target_MysqlDB $GET_SYS_TOP_LOCK_TIME_SQL
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "执行次数最多的SQL语句"
Target_MysqlDB $GET_SYS_TOP_EXECUTE_COUNT_SQL
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "等待时间TOP25的SQL"
Target_MysqlDB $GET_SYS_TOP_WAIT_EVENT_SQL
OUT_PUT_NEW_LINE


OUTPUT_H4_TITLE "全局内存设置"
Target_MysqlDB $GET_GOBAL_MEM_OPTION_SQL
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "目前内存"
Target_MysqlDB "$GET_GOBAL_MEMORY_SQL"
OUT_PUT_NEW_LINE


OUTPUT_H4_TITLE "SCHEMA IN BUFFER INFO"
Target_MysqlDB "$GET_INNODB_BUF_SCHEMA_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "TOP 200 TABLES IN BUFFER INFO"
Target_MysqlDB "$GET_INNODB_BUF_TABLE_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "CONNECT MEMORY"
Target_MysqlDB "$GET_HOST_MEMORY_BYTES_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "THREAD MEMORY"
Target_MysqlDB "$GET_THREAD_MEMORY_BYTES_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "USER MEMORY"
Target_MysqlDB "$GET_USER_CURRENT_MEMORY_BYTES_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "事件角度的内存"
Target_MysqlDB $GET_EVENT_MEMORY_SQL
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "EVENT MEMORY"
Target_MysqlDB "$GET_EVENT_CURRENT_MEMEORY_BYTES_SQL"
OUT_PUT_NEW_LINE


OUTPUT_H3_TITLE "客户端访问IO 信息:"
OUT_PUT_NEW_LINE

Target_MysqlDB "$GET_HOST_SUMMARY_SQL"
OUT_PUT_NEW_LINE
Target_MysqlDB "$GET_HOST_IO_SQL"
OUT_PUT_NEW_LINE
Target_MysqlDB "$GET_HOST_IO_TYPE_SQL"
OUT_PUT_NEW_LINE
Target_MysqlDB "$GET_HOST_STAGES_SQL"
OUT_PUT_NEW_LINE
Target_MysqlDB "$GET_HOST_STATEMENT_SQL"
OUT_PUT_NEW_LINE
Target_MysqlDB "$GET_HOST_STATEMENT_TYPE_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H3_TITLE "用户访问IO 信息"
OUT_PUT_NEW_LINE

Target_MysqlDB "$GET_USER_SUMMARY_SQL"
OUT_PUT_NEW_LINE
Target_MysqlDB "$GET_USER_IO_SQL"
OUT_PUT_NEW_LINE
Target_MysqlDB "$GET_USER_IO_TYPE"
OUT_PUT_NEW_LINE
Target_MysqlDB "$GET_USER_STAGES_SQL"
OUT_PUT_NEW_LINE
Target_MysqlDB "$GET_USER_STATEMENT_SQL"
OUT_PUT_NEW_LINE
Target_MysqlDB "$GET_USE_STATEMENT_TYPE_SQL"
OUT_PUT_NEW_LINE
Target_MysqlDB "$GET_THREAD_IO_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H3_TITLE "文件 IO INFO"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE  "字节数"
Target_MysqlDB "$GET_FILE_IO_BYTES_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE  "执行时间"
Target_MysqlDB "$GET_FILE_IO_TIME_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE  "读写字节总结"
Target_MysqlDB "$GET_FILE_IO_SUMMARY_BYTES_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE  "读写时间总结"
Target_MysqlDB "$GET_FILE_IO_SUMMARY_TIME_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE  "最新线程IO总结"
Target_MysqlDB "$GET_NEW_IO_SQL"
OUT_PUT_NEW_LINE




#Wait Event Info
OUTPUT_H3_TITLE "等待事件"
OUT_PUT_NEW_LINE

Target_MysqlDB "$GET_WAIT_EVENT_SQL"
OUT_PUT_NEW_LINE

Target_MysqlDB "$GET_HOST_WAIT_SQL"
OUT_PUT_NEW_LINE

Target_MysqlDB "$GET_USER_WAIT_SQL"
OUT_PUT_NEW_LINE

Target_MysqlDB "$GET_GLOBAL_WAIT_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "当前锁等待"
Target_MysqlDB "$GET_LOCK_WAIT_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "当前进程连接"
Target_MysqlDB "$GET_PROCESSLIST_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "当前会话"
Target_MysqlDB "$GET_SESSION_SQL"

OUTPUT_H4_TITLE "当前表锁等待"
OUT_PUT_NEW_LINE
Target_MysqlDB "$GET_CURRENT_TABLE_LOCK_WAIT_SQL"
OUT_PUT_NEW_LINE


OUTPUT_H2_TITLE  "MYSQL 状态信息"

OUTPUT_H4_TITLE "全局状态"
Target_MysqlDB "$GET_SERVIE_STATUS_SQL"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "Master 状态"
Target_MysqlDB "show master status;"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "Master 信息"
Target_MysqlDB "select * from mysql.master_info;"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "中继日志 信息"
Target_MysqlDB "select * from mysql.slave_relay_log_info;"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "Salve工作线程 信息"
Target_MysqlDB "select * from mysql.slave_worker_info;"
OUT_PUT_NEW_LINE

OUTPUT_H4_TITLE "INNODB 引擎状态"
OUT_PUT_TABLE_HEAD
OUT_PUT_TITEL "INNNODB引擎状态"
Target_MysqlDB_Innodb_status "$GET_INNODB_STATUS_SQL"
if [[ -f /tmp/awr_innodb_status.txt ]] ; then 
    sed -i 's/\\n/<br>/g' /tmp/awr_innodb_status.txt ##MYSQL输出文本把换行符\N 变成实际符号\n
    maxline=$(wc -l < /tmp/awr_innodb_status.txt)
    if [[ ${maxline} -gt 0 ]] ; then 
        while read line; 
        do
            OUT_PUT_SINGLE_LINES "$line"
        done < /tmp/awr_innodb_status.txt
    else
        WriteLogs "Error:/tmp/awr_innodb_status.txt have not content"
    fi 
else
   WriteLogs "Error:Not found /tmp/awr_innodb_status.txt "
fi    
OUT_PUT_TABLE_TAIL
OUT_PUT_NEW_LINE



OUTPUT_H4_TITLE "InnoDB mutex and rw-lock statistics"
Target_MysqlDB "SHOW ENGINE INNODB MUTEX;"
OUT_PUT_NEW_LINE

#----循环输出多SCHEMA -----------------------------------
OUTPUT_H2_TITLE "SCHEMA 信息"

OUTPUT_H4_TITLE "TOP SCHEMA"
Target_MysqlDB $TOP_SCHEMA_SQL
OUT_PUT_NEW_LINE

if [ ${IS_CHECK_MULTI_SCHEMA} -eq 1 ] ; then            #检查参数是否要多SCHEMA?  如果要就把SCHEMA查出来并放进数组里保存
     DB_SCHEMA_LIST=(`Target_MysqlDB_NOT_HTML ${GET_DB_SCHEMA_LIST_SQL}`)
  if [ $? -ne 0 ];then
    WriteLogs  "fail:GET_DB_SCHEMAS!"
    WriteLogs "${GET_DB_SCHEMA_LIST_SQL}"
    exit 1
  fi

  if [ ${#DB_SCHEMA_LIST[*]} -eq 0 ] ; then
    WriteLogs "Error:No Find SCHEMAS In List!"
    exit 1
  fi  
fi

if [ ${IS_CHECK_MULTI_SCHEMA} -eq 0 ] ; then 
  DB_SCHEMA_LIST=${SCHEMA}
fi

for (( i=0; i < ${#DB_SCHEMA_LIST[*]}; i=i+1 ))
do
    SCHEMA=${DB_SCHEMA_LIST[$i]}

    OUTPUT_H3_TITLE "About  ${SCHEMA} info :"
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "TOP TABLES "
    ACTION_SQL=$(eval echo "\" $TOP_TABLE_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "TOP TABLE FRAG RATE >10%"
    Target_MysqlDB $TO_FRAG_SQL
    ACTION_SQL=$(eval echo "\" $TO_FRAG_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "表的访问效率排行统计情况"
    OUTPUT_H4_TITLE "等待事件调用次数，总计、最小、平均、最大延迟"
    Target_MysqlDB $GET_OBJECT_TOTAL_SQL
    OUT_PUT_NEW_LINE


    OUTPUT_H4_TITLE "GET_WEEK_SLOW_SQL"
    Target_MysqlDB $GET_WEEK_SLOW_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_SLOW_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE


    OUTPUT_H4_TITLE "GET_WEEK_TOP_WAIT_EVENTS_SQL"
    Target_MysqlDB $GET_WEEK_TOP_WAIT_EVENTS_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_WAIT_EVENTS_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "GET_WEEK_TOP_WAITEVENTS_DETAIL_SQL"
    Target_MysqlDB $GET_WEEK_TOP_WAITEVENTS_DETAIL_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_WAITEVENTS_DETAIL_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "GET_WEEK_TOP_RUNTIME_SQL"
    Target_MysqlDB $GET_WEEK_TOP_RUNTIME_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_RUNTIME_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "GET_WEEK_TOP_EXECUTE_COUNT_SQL"
    Target_MysqlDB $GET_WEEK_TOP_EXECUTE_COUNT_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_EXECUTE_COUNT_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "GET_WEEK_TOP_AVGTIME_SQL"
    Target_MysqlDB $GET_WEEK_TOP_AVGTIME_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_AVGTIME_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "GET_WEEK_TOP_LOCKTIME_SQL"
    Target_MysqlDB $GET_WEEK_TOP_LOCKTIME_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_LOCKTIME_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "GET_WEEK_TOP_EXAMINEDROW_SQL"
    Target_MysqlDB $GET_WEEK_TOP_EXAMINEDROW_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_EXAMINEDROW_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "GET_WEEK_TOP_SENTROW_SQL"
    Target_MysqlDB $GET_WEEK_TOP_SENTROW_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_SENTROW_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "GET_WEEK_TOP_SORTROW_SQL"
    Target_MysqlDB $GET_WEEK_TOP_SORTROW_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_SORTROW_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "GET_WEEK_TOP_AFFECTED_SQL"
    Target_MysqlDB $GET_WEEK_TOP_AFFECTED_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_AFFECTED_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "GET_WEEK_TOP_DISKTMP_SQL"
    Target_MysqlDB $GET_WEEK_TOP_DISKTMP_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_DISKTMP_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "GET_WEEK_TOP_NO_INDEX_SQL"
    Target_MysqlDB $GET_WEEK_TOP_NO_INDEX_SQL
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_NO_INDEX_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE


    OUTPUT_H3_TITLE "统计"
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "AUTO INCREMENT"
    Target_MysqlDB "$GET_AUTO_INCREMENT_SQL"
    ACTION_SQL=$(eval echo "\" $GET_AUTO_INCREMENT_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE " INDEXS STATISTICS:"
    Target_MysqlDB "$GET_INDEXS_STATIS_SQL"
    ACTION_SQL=$(eval echo "\" $GET_INDEXS_STATIS_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    Target_MysqlDB "$GET_REDUN_INDEX_SQL"
    ACTION_SQL=$(eval echo "\" $GET_REDUN_INDEX_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE " TABLE STATISTICS:"
    Target_MysqlDB "$GET_TABLE_STATIS_SQL"
    ACTION_SQL=$(eval echo "\" $GET_TABLE_STATIS_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE   "BUF HOT INFO:"
    Target_MysqlDB "$GET_BUF_HOT_TABLE_SQL"
    ACTION_SQL=$(eval echo "\" $GET_BUF_HOT_TABLE_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "UNUSED INDEX:"
    Target_MysqlDB "$GET_UNUSED_INDEX_SQL"
    ACTION_SQL=$(eval echo "\" $GET_UNUSED_INDEX_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H3_TITLE "STATEMENT INFO"
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "FULL SCAN TABLE:"
    Target_MysqlDB "$GET_FULL_TABLE_SCAN_SQL"
    ACTION_SQL=$(eval echo "\" $GET_FULL_TABLE_SCAN_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "ALL SQL:"
    Target_MysqlDB "$GET_STATEMENT_ANLAYZ_SQL"
    ACTION_SQL=$(eval echo "\" $GET_STATEMENT_ANLAYZ_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "FULL SCAN SQL:"
    Target_MysqlDB "$GET_FULL_SCAN_STATEMENT_SQL"
    ACTION_SQL=$(eval echo "\" $GET_FULL_SCAN_STATEMENT_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "FILE SORT SQL:"
    Target_MysqlDB "$GET_FILESORT_STATEMENT_SQL"
    ACTION_SQL=$(eval echo "\" $GET_FILESORT_STATEMENT_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE " TEMP SQL:"
    Target_MysqlDB "$GET_TEMP_STATEMENT_SQL"
    ACTION_SQL=$(eval echo "\" $GET_TEMP_STATEMENT_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "使用 DISTINCT 星 :"
    Target_MysqlDB "$GET_DISTINCT_STAR_SQL"
    ACTION_SQL=$(eval echo "\" $GET_DISTINCT_STAR_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "使用 SELECT 星 :"
    Target_MysqlDB "$GET_SELECT_STAR_SQL"
    ACTION_SQL=$(eval echo "\" $GET_SELECT_STAR_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "拥有外键的表 :"
    Target_MysqlDB "$GET_Foreign_KEY_TAB_SQL"
    ACTION_SQL=$(eval echo "\" $GET_Foreign_KEY_TAB_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE


    OUTPUT_H4_TITLE "索引第一列不够多的选择值 :"
    Target_MysqlDB "$GET_INDEX_FIRST_COL_SELECT_SQL"
    ACTION_SQL=$(eval echo "\" $GET_INDEX_FIRST_COL_SELECT_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE


    OUTPUT_H4_TITLE "拥有超过30个列的表 :"
    Target_MysqlDB "$GET_TABLE_COL_OVER_SQL"
    ACTION_SQL=$(eval echo "\" $GET_TABLE_COL_OVER_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "列字符集 :"
    Target_MysqlDB "$GET_COL_CHAR_SET_SQL"
    ACTION_SQL=$(eval echo "\" $GET_COL_CHAR_SET_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "表字符集 :"
    Target_MysqlDB "$GET_TABLE_CHAR_SORT_SQL"
    ACTION_SQL=$(eval echo "\" $GET_TABLE_CHAR_SORT_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "数据库字符集 :"
    Target_MysqlDB "$GET_SCHEMA_CHARSET_SQL"
    ACTION_SQL=$(eval echo "\" $GET_SCHEMA_CHARSET_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "列注解包含值域的 :"
    Target_MysqlDB "$GET_COL_COMM_VAULE_SQL"
    ACTION_SQL=$(eval echo "\" $GET_COL_COMM_VAULE_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "列注解为空的 :"
    Target_MysqlDB "$GET_COL_COM_NULL_SQL"
    ACTION_SQL=$(eval echo "\" $GET_COL_COM_NULL_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE


    OUTPUT_H4_TITLE "表注解为空的 :"
    Target_MysqlDB "$GET_TAB_COMM_NULL_SQL"
    ACTION_SQL=$(eval echo "\" $GET_TAB_COMM_NULL_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE


    OUTPUT_H4_TITLE "组合索引超过5个字段的 :"
    Target_MysqlDB "$GET_FIVE_INDEX_COL_SQL"
    ACTION_SQL=$(eval echo "\" $GET_FIVE_INDEX_COL_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE


    OUTPUT_H4_TITLE "没有主键的表 :"
    Target_MysqlDB "$GET_NO_PRIMARY_TABLE_SQL"
    ACTION_SQL=$(eval echo "\" $GET_NO_PRIMARY_TABLE_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "拥有超过5个索引的表 :"
    Target_MysqlDB "$GET_FIVE_INDEX_TABLE_SQL"
    ACTION_SQL=$(eval echo "\" $GET_FIVE_INDEX_TABLE_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "拥有不被推荐的数据类型 :"
    Target_MysqlDB "$GET_NOT_RECOMMEND_TYPE_SQL"
    ACTION_SQL=$(eval echo "\" $GET_NOT_RECOMMEND_TYPE_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "查询语句返回太多行:"
    Target_MysqlDB "$GET_WEEK_RETRUN_ROWS_SQL"
    ACTION_SQL=$(eval echo "\" $GET_WEEK_RETRUN_ROWS_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "大事务影响行超过10万行:"
    Target_MysqlDB "$GET_WEEK_BIG_TRANS_SQL"
    ACTION_SQL=$(eval echo "\" $GET_WEEK_BIG_TRANS_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "太多逻辑读的SQL 平均每次大于2万行:"
    Target_MysqlDB "$GET_WEEK_TOP_LOGIC_READ_SQL"
    ACTION_SQL=$(eval echo "\" $GET_WEEK_TOP_LOGIC_READ_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

    OUTPUT_H4_TITLE "表列名相同,大小类型不同:"
    Target_MysqlDB "$GET_TAB_COL_SIZE_TYPE_SQL"
    ACTION_SQL=$(eval echo "\" $GET_TAB_COL_SIZE_TYPE_SQL\"")
    Target_MysqlDB $ACTION_SQL
    OUT_PUT_NEW_LINE

 done


#============================Bussines Info==============================
OUTPUT_H2_TITLE "业务信息"




echo -e '<hr style="FILTER:alpha(opacity=100,finishopacity=0,style=2)" width="100%"color=#cccc99 SIZE=1>' >>${CHECK_RESULT_FILE} 2>/dev/null
echo " <h4 align="center" class="awr">  COPY 2022-07-29 Author:InnerCodeDBA  fankun@sharkz.com.cn.earth  </h4> " >>${CHECK_RESULT_FILE}                       
CREATE_HTML_END


sed -i 's/<TH>/<TH class=awrbg scope="col">/g'  ${CHECK_RESULT_FILE}  ## Add background to SQL fields output by MySQL

CUR_TIMES=`date +%s`
MID_TIMES=$[$CUR_TIMES - $START_TIME]
echo -e "\e[1;33m Thank you for use this awr ! The Program Spend $MID_TIMES Mseconds  \e[0m"

最后是测试脚本 awr_test_mysql.sh

#================================================DataSegment===============================================
. ./awr_head_mysql.sh 


BUSSINE_SCHEMA='aesygo_test'
BEFORE_DAYS=7
START_DATE_TIME='2022-09-12 00:00:00.000000'
END_DATE_TIME='2022-09-20 00:00:00.000000'

START_DATE='2022-09-12'


#以下不需要改动脚本运行日志输出参数,主要是DEBUG时候使用
IS_INFOMATION_OUTPUT_CONSOLE=0
LOGFILE_NAME=test_awr_mysql_`date +%F_%H`.log
LOGFILE_DIR='./logs/'
LOGFILE_PATH=${LOGFILE_DIR}${LOGFILE_NAME}
V_DATE=`date "+%Y-%m-%d %H:%M:%S"`

#===============================================Fuction segments======================================================================
function Target_MysqlDB()
{
 mysql -h $TARGET_DB_IP -P $TARGET_DB_PROT -u$TARGET_DB_USER -p$TARGET_DB_PASS --html -t  -e "$*" >> ${CHECK_RESULT_FILE} 2>/dev/null
}
##不支持HTML输出的
function SALVE_MysqlDB()
{
 mysql -h $TARGET_DB_IP -P $TARGET_DB_PROT -u$TARGET_DB_USER -p$TARGET_DB_PASS  -e "$*" 1>tmp_slave_status.txt   2>/dev/null
}


function Target_MysqlDB_NOT_HTML()
{
 mysql -h $TARGET_DB_IP -P $TARGET_DB_PROT -u$TARGET_DB_USER -p$TARGET_DB_PASS  -N -e "$*"    2>/dev/null
}

function WriteLogs()
{
  if [ ! -d $LOGFILE_DIR ] ; then
    mkdir -p logs
    echo "" > ${LOGFILE_PATH}
  fi
  V_DATE=`date "+%Y-%m-%d %H:%M:%S"`
  echo "$V_DATE==>$*" >> ${LOGFILE_PATH};
  if [ $IS_INFOMATION_OUTPUT_CONSOLE -eq 1 ] ; then #屏幕输出DEBUG信息
   echo -e "$*"
  fi
}
#===============================================HTML Fuction segments======================================================================
CREATE_HTML_HEAD()
{
echo -e '<html>
<head>
<meta charset="UTF-8">
<style type="text/css">
    body        {font:12px Courier New,Helvetica,sansserif; color:black; background:White;}
    table       {font:12px Courier New,Helvetica,sansserif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} 
    th.awrbg    {font:bold 12pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#000000;padding-left:4px; padding-right:4px;padding-bottom:2px}
    td.awrc     {font:12pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;}
    td.awrnc    {font:12pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;}
    h1.awr   {font:bold 24pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White; margin-bottom:0pt;padding:0px 0px 0px 0px;}
    h2.awr   {font:bold 20pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
    h3.awr   {font:bold 18pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;}
    h4.awr   {font:bold 20pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border-top:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
    table tr:nth-child(even){            background-color: #fafad2;        }
    table tr:nth-child(odd){          background-color: #b2e1b2;        }
</style>
</head>
<body>' >>${CHECK_RESULT_FILE} 2>/dev/null

 echo -e " <h1 align="center" class="awr"> ${SCHEMA} Daily patrol inspection report </h1> ">>${CHECK_RESULT_FILE}
 echo -e "<br/>" >>${CHECK_RESULT_FILE}
 echo -e " <h2 align="center" class="awr"> Report:$(date +%Y-%m-%d)  </h2> ">>${CHECK_RESULT_FILE}
}

function OUTPUT_TITLE() #输出HTML 标题
{
  echo -e "<h3 class="awr" >$1</h3>" >>${CHECK_RESULT_FILE} 2>/dev/null
}


CREATE_HTML_END() #输出HTML 结束
{
  echo -e "</body></html>" >>${CHECK_RESULT_FILE} 2>/dev/null
}

OUT_PUT_TABLE_HEAD() #输出表格头
{
  echo -e '<table width="" border="1" >' >>${CHECK_RESULT_FILE}
}


OUT_PUT_FILED() ##输出字段名函数
{
    th_str=`echo $1|awk 'BEGIN {FS=" "}''{i=1; while(i<=NF) {print "<th class='awrbg' scope="col"> "$i"</th>";i++}}'`
}

OUT_PUT_TITEL() ##字段接口函数
{
  OUT_PUT_FILED "$*"; echo -e "<tr>    $th_str  </tr>" >> ${CHECK_RESULT_FILE}
}


OUT_PUT_VAULES() ##输出字段内容函数
{
    th_str=`echo $1|awk 'BEGIN{FS=" "}''{i=1; while(i<=NF) {print "<td scope="row" class='awrc'> "$i"</td>";i++}}'`
}

OUT_PUT_LINES() #输出内容接口函数
{
  OUT_PUT_VAULES "$*";  echo -e "<tr>    $th_str  </tr>" >>${CHECK_RESULT_FILE}
}

OUT_PUT_SINGLE_LINES() #输出单行内容接口函数
{
 echo -e "<tr><td scope=""row"" class='awrc'>  $* </td></tr>" >>${CHECK_RESULT_FILE}
}

OUT_PUT_ROW_HEAD() #输出行头
{
   echo -e "<tr>" >>${CHECK_RESULT_FILE}
}

OUT_PUT_ROW_TAIL() #输出列VALUE
{
   echo -e "</tr>" >>${CHECK_RESULT_FILE}
}
OUT_PUT_SINGLE_COL()#输出行尾
{
  echo -e " <td scope=""row"" class='awrc'>  $* </td>" >>${CHECK_RESULT_FILE}
}

OUT_PUT_NEW_LINE() #
{
      echo -e " <br />">>${CHECK_RESULT_FILE}
}

OUT_PUT_TABLE_TAIL() #输出表尾
{
  echo -e "</table>" >>${CHECK_RESULT_FILE}
  OUT_PUT_NEW_LINE
}

OUT_PUT_LINES_LOG() ## 
{
     echo -e "<tr> <td scope="row" class='awrc'> $1</td></tr>" >>${CHECK_RESULT_FILE}
}




#===============================================================测试函数段============================================================
deadlock()
{
    ERROR_LOG=$1
    DEADLOCK_KEY='InnoDB: Transactions deadlock detected,'
    DEADLOCK_END_KEY='InnoDB: *** WE ROLL BACK TRANSACTION'
    IS_EXIST_DEADLOCK=$(cat ${ERROR_LOG} | grep -m 1 "InnoDB: Transactions deadlock detected,")
    FIND_DATE1=${START_DATE}
    FIND_DATE=$(date -d "${FIND_DATE1}" +%s)

    #Match all line numbers and times of key
if [[ -n ${IS_EXIST_DEADLOCK} ]]; then 
    START_LINE_VAR=$(cat ${ERROR_LOG} | grep -n  "InnoDB: Transactions deadlock detected," |awk -F ":"  '{printf($1);printf ";"}')
    START_TIME_VAR=$(cat ${ERROR_LOG} | grep -n  "InnoDB: Transactions deadlock detected," |awk -F ":"  '{printf($2);printf ";"}')
    START_KEY_LINE_LIST=(${START_LINE_VAR//;/ })
    START_KEY_TIME_LIST=(${START_TIME_VAR//;/ })

    #Last compliant_ Key all line numbers and times
    CLOSE_LINE_VAR=$(cat ${ERROR_LOG} | grep -n  "WE ROLL BACK TRANSACTION" |grep -i "InnoDB:"|awk -F ":"  '{printf($1);printf ";"}')
    CLOSE_TIME_VAR=$(cat ${ERROR_LOG} | grep -n  "WE ROLL BACK TRANSACTION" |grep -i "InnoDB:"| awk -F ":"  '{printf($2);printf ";"}')
    CLOSE_KEY_LINE_LIST=(${CLOSE_LINE_VAR//;/ })
    CLOSE_KEY_TIME_LIST=(${CLOSE_TIME_VAR//;/ })

    START_POS=0
    for (( i=0; i<${#START_KEY_TIME_LIST[*]}; i=i+1 ))
    do
        temp_time=${START_KEY_TIME_LIST[$i]}
        START_TIME=${temp_time:0:10}
        DIG_START_TIME=$(date -d "${START_TIME}" +%s)

        if [[ ${DIG_START_TIME} -ge ${FIND_DATE}  ]]; then
            START_POS=${i}
            break
        fi
    done

    END_POS=0
    for (( k=0; k<${#CLOSE_KEY_TIME_LIST[*]}; k=k+1 ))
    do
        temp_time=${CLOSE_KEY_TIME_LIST[$i]}
        CLOSE_TIME=${temp_time:0:10}
        DIG_CLOSE_TIME=$(date -d "${CLOSE_TIME}" +%s)    

        if [[ ${DIG_CLOSE_TIME} -ge ${FIND_DATE}  ]]; then
            if [[  ${k} -ge ${START_POS} ]] ; then 
              END_POS=${k}
              break
            fi 
        fi
    done

echo -e "start :${START_POS};  close:${END_POS}"

for (( j=0; j<${#CLOSE_KEY_TIME_LIST[*]}; j=j+1 ))
do
  if [[ ${j} -ge ${START_POS} ]] ; then 

     CLOSE=${CLOSE_KEY_LINE_LIST[$j]}
     START=${START_KEY_LINE_LIST[$j]}
     DEADLOCK_STRINGS=$(sed -n "${START},${CLOSE}p" ${ERROR_LOG} |sed ":a;N;s/\n/<br>/g;ta")  #把LINUX换行符替换成HTML换行符<br>
     OUT_PUT_LINES_LOG "${DEADLOCK_STRINGS}"
     else
        OUT_PUT_LINES_LOG " 未发现死锁日志信息"        
     fi              
done
else
   OUT_PUT_LINES_LOG " 未发现死锁日志信息"
fi 

}




#============================================================测试SQL SEGMETN===================================================================
GET_DB_SCHEMA_LIST_SQL="select  distinct table_schema from information_schema.tables where table_schema not in ('mysql','sys','information_schema','performance_schema');"

#表列名相同,大小类型不同
GET_TAB_COL_SIZE_TYPE_SQL="
select A.*
from
(
select  table_name,column_name,data_type,CHARACTER_MAXIMUM_LENGTH
from information_schema.columns IC
where IC.table_schema="\''${SCHEMA}'\'"
AND COLUMN_NAME NOT IN ('status','create_time','update_time','id')
)A
INNER JOIN 
(
select  table_name,column_name,data_type,CHARACTER_MAXIMUM_LENGTH
from information_schema.columns IC
where IC.table_schema="\''${SCHEMA}'\'"
AND COLUMN_NAME NOT IN ('status','create_time','update_time','id')
)B  ON A.COLUMN_NAME=B.COLUMN_NAME AND A.TABLE_NAME<>B.TABLE_NAME 
AND ( A.data_type<>B.data_type OR  A.CHARACTER_MAXIMUM_LENGTH<>B.CHARACTER_MAXIMUM_LENGTH)
ORDER BY A.COLUMN_NAME;"

#============================================================CODE SEGMETN===================================================================
clear
if [[ -z ${START_DATE_TIME} ]] ; then 
  START_DATE_TIME=$(date -d"${BEFORE_DAYS} day ago" +'%Y-%m-%d %H:%M:%S' )
  END_DATE_TIME=$(date "+%Y-%m-%d")
  START_DATE=$(date -d"${BEFORE_DAYS} day ago" +'%Y-%m-%d')
fi

WriteLogs "START_DATE_TIME:${START_DATE_TIME}"
WriteLogs "CLOSE_DATE:${END_DATE_TIME}"


CREATE_HTML_HEAD

#---------------------------------------------------------测试SHELL 命令 结果集到HTML---------------------------------------------------------

#OUTPUT_TITLE "MEM INFO"
#OUT_PUT_TABLE_HEAD
#OUT_PUT_TITEL  "TYPE TOTAL USED FREE SHARED BUFF AVAILABLE"
#OUT_PUT_LINES "$(free -m |tail -2|grep Mem )"
#OUT_PUT_LINES "$(free -m |tail -2|grep Swap)"
#OUT_PUT_TABLE_TAIL




#---------------------------------------------------------测试 SQL 命令 输出到 HTML------------------------ ------------------------------------
#OUTPUT_TITLE "about  ${SCHEMA} info :"
#OUT_PUT_NEW_LINE
#OUTPUT_TITLE "表列名相同,大小类型不同"
#Target_MysqlDB $GET_TAB_COL_SIZE_TYPE_SQL
#OUT_PUT_NEW_LINE


#----------------MYSQL 实例信息--------------------------------
OUTPUT_TITLE "Deadlock Info:"                 
OUT_PUT_TABLE_HEAD                           
OUT_PUT_TITEL "DEAD_LOCK"                    
deadlock ${MYSQL_ERROR_LOG}
OUT_PUT_TABLE_TAIL 


#--------------------------------------------------------------测试 尾-------------------------------------------------------------------------

echo " <h4 align="center" class="awr">  COPY 2022-07-29 Author:InnerCodeDBA  fankun@sharkz.com.cn.earth  </h4> " >>${CHECK_RESULT_FILE}                       
CREATE_HTML_END


sed -i 's/<TH>/<TH class=awrbg scope="col">/g'  ${CHECK_RESULT_FILE}  ## Add background to SQL fields output by MySQL