用sed语法将pt-query-digest分析的mysql慢查询转换成html页面,简单转换但是看着方便多了

  1. 云栖社区>
  2. 博客>
  3. 正文

用sed语法将pt-query-digest分析的mysql慢查询转换成html页面,简单转换但是看着方便多了

扬仔8888 2017-12-05 23:21:52 浏览1328
展开阅读全文

主要练习sed的部分高级语法和正则匹配,不过其实用perl语法更方便。主要sed语法如下

#处理pt-query工具分析后的mysql慢查询

#头部 尾部

#!/bin/bash

echo "used: sh chsqltohtml.sh abc.log,it will create abc.html to the current path"

SLOWLOG=$1

INDEXHTML=`echo $SLOWLOG|cut -d . -f 1`.html

echo $SLOWLOG

echo ${INDEXHTML}

sed  -e '1 s/^$/<html>\n\t<head><meta charset="gbk" \/>\n<title>'$SLOWLOG'慢查询<\/title> \n\t<\/head>\n\t\t<body bgcolor="#87CEEB">\n\t\t<h3 style="color:green;font-size:40px;text-align:cen

ter;"><i>'$SLOWLOG'慢查询<\/i><\/h3>\n<hr color=green>\n<table border="1", bgcolor="#9ACD32">\n<tr><td> /' -e '$a\<hr color=red>\n</body>\n</html> ' -e 's/^#/<br>#/'   ${SLOWLOG} >${INDEXHTML}


sed -i -e    '/^<br># Attribute/i\</td></tr></table>\n<table border="1">'  ${INDEXHTML}

sed -i -e    '/^<br># Query size/a\</table>'   ${INDEXHTML}

sed -i -e    '/^<br># MISC/i\</table>'    ${INDEXHTML}

sed -i -e    '/^<br># Query 1/i\</table>'  ${INDEXHTML}

#sed -i -e    's/^$/<hr color=red>/' ${INDEXHTML}

sed -i -e    '/^<br># Profile/i\<hr color=red>\n' ${INDEXHTML}


#替换

sed -i  -e 's/<br># Exec time/<tr><td>Exec_time/g'  -e 's/<br># Lock time/<tr><td>Lock_time/g'  -e 's/<br># Rows sent/<tr><td>Rows_sent/g' -e 's/<br># Rows examine/<tr><td>Rows_examine/g'  -

e 's/<br># Query size/<tr><td>Query_size/g'  -e 's/<br># Rank Query ID/<table border="1">\n<tr><td>Rank Query ID/g'  ${INDEXHTML} 

#1-20个空格

sed -i -e '/Exec_time/s/[ ]\{1,20\}/<\/td><td>/g'  -e '/Exec_time/s/$/<\/td>/g' ${INDEXHTML}

sed -i -e '/Lock_time/s/[ ]\{1,20\}/<\/td><td>/g'  -e '/Lock_time/s/$/<\/td>/g' ${INDEXHTML}

sed -i -e '/Rows_sent/s/[ ]\{1,20\}/<\/td><td>/g'  -e '/Rows_sent/s/$/<\/td>/g' ${INDEXHTML}

sed -i -e '/Rows_examine/s/[ ]\{1,20\}/<\/td><td>/g'  -e '/Rows_examine/s/$/<\/td>/g' ${INDEXHTML}

sed -i -e '/Query_size/s/[ ]\{1,20\}/<\/td><td>/g'  -e '/Query_size/s/$/<\/td>/g' ${INDEXHTML}

sed -i -r 's/<tr><td>(Rank\s+)(Query ID\s+)(Response\s+)(time\s+)(Calls\s+)(R\/Call\s+)(V\/M\s+)(Item)/<tr><td>等级<\/td><td>Query ID<\/td><td>响应时间<\/td><td>时间占比<\/td><td>执行次数<\/

td><td>平均响应时间<\/td><td>\7<\/td><td>语句<\/td>/g'  ${INDEXHTML}

#删除多余===

sed -i '/=\{3,20\}/d' ${INDEXHTML} 

sed -i 's/<br># Attribute/<tr><td>Attribute/g' ${INDEXHTML}

sed -i -e '/Attribute/s/[ ]\{1,20\}/<\/td><td>/g'  -e '/Attribute/s/$/<\/td>/g' ${INDEXHTML}

#sed -i -e '/MISC 0xMISC/s/$/<\/td>\n<\/table>/g' ${INDEXHTML}

###超链接

sed -i -r 's/^<br>#(\s+[0-9]{1,2}\s+)(0x[A-Z0-9]+)(\s+[0-9]{1,10}\.[0-9]{1,4}\s+)([0-9]{1,3}\.[0-9]%\s+)([0-9]{1,9}\s+)([0-9]{1,10}\.[0-9]{1,4}\s+)([0-9]{1,9}\.+[0-9]*\s+)(.+)/\<tr><td>\1\<\

/td><td><a href="#\2\">\2<\/a>\<\/td><td>\3\<\/td><td>\4\<\/td><td>\5\<\/td><td>\6\<\/td><td>\7\<\/td><td>\8\<\/td>/g' ${INDEXHTML}

sed -i -r 's/<br># Query(\s+[0-9]+:\s+)([0-9]+\.[0-9]{1,4}\s+)QPS,(\s+[0-9]+\.[0-9]+).*(0x[A-Z0-9]+)(\s+at byte\s+[0-9]+)/\<hr color=red>\n<br># Query\1\2\3\x concurrency, ID <a name="\4\">\

4\<\/a>\5/g' ${INDEXHTML}

echo "over"



b78a3c425c085100b10741d89ae761bc02d49aa3



网友评论

登录后评论
0/500
评论
扬仔8888
+ 关注