mysql 慢查询脚本_生成每日Mysql慢查询简报脚本

最近慢查询较多，想法子自动做数据统计：

生成的简报的内容如下 :

---------------Daily Slow Query Report Create at 110812 18:16:09-----------------

FROM 8:25:17    To 8:25:57

COUNT   SQL_TEXT

----------------

356 select * from (select a.colum1 ,c.column2 ,c.column3,c.column5

45 select a.*,c.column3, c.column4 from table_namea a l

36 select count(*) from table_namea a left join table_namec

FROM 8:25:57    To 8:26:00

COUNT   SQL_TEXT

----------------

18 select * from (select a.colum1 ,c.column2 ,c.column3,c.column5

5 select a.*,c.column3, c.column4 from table_namea a l

FROM 8:26:00    To 8:26:07

COUNT   SQL_TEXT

----------------

68 select * from (select a.colum1 ,c.column2 ,c.column3,c.column5

15 select a.*,c.column3, c.column4 from table_namea a l

10 select count(*) from table_namea a left join table_namec

-------------------------------------------------------------------------------------------

实现脚本

#!/bin/bash

date_mark=`date +%y%m%d`

date_now=`date "+%y%m%d %H:%M:%S"`

name=`hostname |cut -d'.' -f1`

dirpath="/var/mysqldatadir/"

file_name="$name-slow.log"

#分割时间段-汇总1分钟之内的

sed -n "/Time: $date_mark/,$ p" $dirpath/$file_name > /tmp/$file_name-$date_mark

grep "\# Time: $date_mark" /tmp/$file_name-$date_mark |cut  -c1-21 |awk '{print $4}' |uniq > /tmp/$file_name-$date_mark-split.tmp1

echo "" > /tmp/$file_name-$date_mark-split

for tmp in $(cat /tmp/$file_name-$date_mark-split.tmp1)

do

grep '\# Time: ' /tmp/$file_name-$date_mark|grep "$tmp" |awk '{print $4}'|xargs |awk '{print $1"\n"$NF}' >> /tmp/$file_name-$date_mark-split

done

sed -i -e  '/^$/d' -e '$d' /tmp/$file_name-$date_mark-split

echo -ne "\n---------------Daily Slow Query Report Create at $date_now-----------------\n"

steps_cnt=`wc -l /tmp/$file_name-$date_mark-split|awk '{print $1}'`

#分时间段汇总

LIMIT="$steps_cnt"

for ((cnt=1; cnt <= LIMIT ; cnt++))

do

start_time=`sed -n "$cnt p" /tmp/$file_name-$date_mark-split`

indirect_cnt=`expr $cnt + 1`

end_time=`sed -n "$indirect_cnt p" /tmp/$file_name-$date_mark-split`

last_time=`grep "\# Time: $date_mark" /tmp/$file_name-$date_mark|tail -1`

if [ "$indirect_cnt" -gt "$steps_cnt" ]

then

strings=`echo "FROM $start_time \tTo $last_time"|sed -e "s%# Time: %%g"`

echo -ne "\n$strings\n"

sed -n "/$start_time/,$ p"  /tmp/$file_name-$date_mark|grep -v "^#" |sort -nr |cut -c1-60 |uniq -c|sort -nr|awk 'BEGIN {print "COUNT   SQL_TEXT\n----------------"}{print $0}'

rm -f /tmp/$file_name-$date_mark-split/tmp/$file_name-$date_mark-split.tmp1

sleep 5

e_address="monitor@mydomain.com"

mail -s "$name slow query report at $date_now" $e_address < /tmp/email_text

exit 0

fi

strings=`echo "FROM $start_time \tTo $end_time"|sed -e "s%# Time: %%g"`

echo -ne "\n$strings\n"

sed -n "/$start_time/,/$end_time/ p"  /tmp/$file_name-$date_mark|grep -v "^#" |sort -nr |cut -c1-60 |uniq -c |sort -nr|awk 'BEGIN {print "COUNT   SQL_TEXT\n----------------"}{print $0}'

done

每日调用脚本

report_daily_slowlog.sh > /tmp/email_text