MySQL数据库类似AWR巡检报告信息脚本

【说明】

有时候需要提供巡检报告信息，按照html格式来取一些数据，根据https://www.modb.pro/db/633746(主要参考)来修改了一下格式及采集内容，如果需要个人修改，可以联系说明信息。

【包含内容】

System Statistics
Database Statistics
SQL Statistics
Replication Statistics
Initialization Parameters
Performance Statistics
Innodb Status

System Statistics
Host Disk
Host Memory
Cpu info
Iostat info
System Messages

Database Statistics
QPS
TPS
Top Schema
Top Table
Top Fage
Not Primary Key Table
Database Charset
Table Charset
Table Not Innodb

SQL Statistics
SQL ordered by Elapsed Time
SQL ordered by Lock Time
SQL ordered by Numrows
SQL ordered by Sent Rows
SQL ordered by Sort Rows
SQL ordered by Update Delete
SQL ordered by Disk Temp Table
SQL ordered by Not Ues Index
SQL ordered by Logic Read
SQL ordered by Big Trans

Replication Statistics
Replication Hosts
Replication Status

Initialization Parameters
Parameters Status

Performance Statistics
Parameters Status
Memory Info
Host Info
User Info
File IO Info
Wait Event Info
Statistics INFO
Statement INFO
Current Process Info

Innodb Status

【结果展示】

【使用方法】

创建一个xunjian_awr.sh文件，然后添加脚本内容，赋予执行权限；

执行：./xunjian_awr.sh MYSQL 10.0.0.xxx 3306 root 'xxxxxx' 建议使用数据库服务器运行，因为会采集主机的信息，centos主机采集数据，如果其他主机则修改对应的采集命令测试

报告文件指定了/root/CHECK_MYSQL_*

【脚本】

vim xunjian_awr.sh

#!/bin/bash
# slow_query_log = on
# Deadlock log parameters
# innodb_print_all_deadlocks=ON
# performance_schema=ON
# performance_schema_events_statements_history_long_size=1000000 #保留百万条默认是1万条根据自己内存来设定先在测试库确定内存消耗量类似ORACLE V$SQL
# performance_schema_digests_size=100000 #此参数类似ORACLE SQLAREA汇总的设置10万不同SQL基本覆盖7天的量
# performance_schema_max_sql_text_length=255 #此参数是文本长度,默认值1024 注意它是TEXT_LENGTH X HISTORY_LONG_SIZ的乘积消耗内存,这里不需要看完整的SQL
# performance_schema_max_digest_length=255 #同上
# ./xunjian_awr.sh MYSQL 10.0.0.xxx 3306 root 'xxxxxx'
#================================================Declare Segment===============================================
DB_TYPE=$1

if [ "$DB_TYPE" != 'MYSQL' ]
then
echo "help: invalid database type"
echo "help: $0 <MYSQL> <IP> <PORT> <DB_USER> <DB_PWD>"
exit 1
fi

TARGET_DB_IP=$2
TARGET_DB_PROT=$3
TARGET_DB_USER=$4
TARGET_DB_PASS=$5
MYSQL_ERROR_LOG='/db/mysql/logs/mysqld.log'
CHECK_RESULT_FILE="/root/CHECK_MYSQL_${TARGET_DB_IP}_$(date +%Y%m%d-%H%M%S).html"
START_DATE=`date --date='7 day ago' '+%Y-%m-%d %H:00:00.000000'`
END_DATE=`date '+%Y-%m-%d %H:00:00.000000'`

##下面参数没有起作用不用改
CURN_DATE=$(date "+%Y-%m-%d %H:%M:%S")
SEVEN_DATE=$(date -d"7 day ago" +%Y-%m-%d)

#===============================================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
}

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 OUTPUT_TITLE()
{
echo -e "<h3 class="awr" id="\"$1"\" >$1</h3>" >>${CHECK_RESULT_FILE} 2>/dev/null
}

function OUTPUT_TITLE_02()
{
echo -e "<h4 class="awr" id="\"$1"\" >$1</h4>" >>${CHECK_RESULT_FILE} 2>/dev/null
}

CREATE_HTML_HEAD()
{
echo -e '<html>
<head>
<meta charset="UTF-8">
<style type="text/css">
body {font:12px Courier New,Helvetica,sansserif; color:#336699; background:White;}
table {font:12px Consolas; color:Black; background:#FFFFCC; padding:1px; margin:0px 0px 0px 0px; cellspacing:0px;border-collapse:collapse; border: none;}
th.awrbg {font:bold 12pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#336699;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 10pt Arial,Helvetica,Geneva,sans-serif;color:black;background-color:White;border-top:0px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
table tr:nth-child(even){ background-color: #FFFFCC; }
table tr:nth-child(odd){ background-color: White; }
</style>
</head>
<body>' >>${CHECK_RESULT_FILE} 2>/dev/null

echo -e " <h1 class="awr"> WORKLOAD REPOSITORY report for </h1> ">>${CHECK_RESULT_FILE}
echo -e "<br/>" >>${CHECK_RESULT_FILE}
echo -e " <h2 class="awr"> </h2> <p> ">>${CHECK_RESULT_FILE}
}

CREATE_HTML_END(){
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() ##Field name of the table
{
th_str=`echo $1|awk 'BEGIN {FS=" "}''{i=1; while(i<=NF) {print "<th class='awrbg' scope="col"> "$i"</th>";i++}}'`
}

OUT_PUT_TITEL() ##Field row of table
{
OUT_PUT_FILED "$*"; echo -e "<tr> $th_str </tr>" >> ${CHECK_RESULT_FILE}
}

OUT_PUT_VAULES() ##Output the contents of the line
{
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_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}
}

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

}

disk_info()
{
TITL="Filesystem Size Used Avail Use% Mountedon "
OUT_PUT_TITEL $TITL
mapfile -t lines < <(df -h | tail -n +2)
for line in "${lines[@]}"; do
OUT_PUT_LINES ${line}
done
}

iostat_info()
{
TITL="Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util"
OUT_PUT_TITEL $TITL
mapfile -t lines < <(iostat -x -m -d -t -p all -c 10 -d $(lsblk -d -o NAME | grep -v 'loop') 4)
for line in "${lines[@]}"; do
OUT_PUT_LINES ${line}
done
}

System_Messages_info()
{
TITL="System_Messages"
OUT_PUT_TITEL $TITL
mapfile -t lines < <(dmesg -T | tail -n 100)
for line in "${lines[@]}"; do
OUT_PUT_LINES ${line}
done
}

##物理文件慢日志信息抓取
function SLOW_MysqlDB()
{
v_slow_log=`mysql -h $TARGET_DB_IP -P $TARGET_DB_PROT -u$TARGET_DB_USER -p$TARGET_DB_PASS -e "show variables like 'slow_query_log_file';" 2>/dev/null |grep slow_query_log_file|awk '{print $2}'`
if [ -f "$v_slow_log" ]; then
OUT_PUT_TABLE_HEAD
TITL="Message"
OUT_PUT_TITEL $TITL
tail -100 $v_slow_log| awk '{print "<tr> <th scope= class=\"awrc\" style=\"text-align:left;\" >", $0, "</th> </tr>";print "\n";}' >> ${CHECK_RESULT_FILE}
fi
}

##定义各个指标的子目录信息
Main_report_statistics_items=("System Statistics" "Database Statistics" "SQL Statistics" "Replication Statistics" "Initialization Parameters" "Performance Statistics" "Innodb Status")
function OUT_PUT_Main_report_Statistics() {
echo -e "<ul>" >> "${CHECK_RESULT_FILE}"

# 遍历数组，为每个元素生成一个列表项
for item in "${Main_report_statistics_items[@]}"; do
echo -e " <li><a href=\"#${item}\" style=\"color: brown;\"><b>${item}</b></a></li>" >> "${CHECK_RESULT_FILE}"
done