一、需求
因为需要对数据库的性能进行持续监控,尽管云上有一些数据,但粒度太大,不适合自己用,所以在网上找了个awk脚本进行修改,并分享给大家。
有两个shell脚本:
1、mysql_status.sh:每秒钟收集一次Mysql信息并存以临时文件里。
生成文件:source_2017-03-31.log按日期生成,status_tmp.log每秒将新数据覆盖原数据
2、mysql_analisys.sh:对收集到的信息进行处理并生成所需报表。
生成文件:report_2017-03-31.log
二、具体代码
[root@lion mysqladmin] cat mysql_status.sh
#!/bin/bash #Path=$(pwd) Path=/data/mysql_slow_log/mysqladmin tmp_file=${Path}/status_tmp.log source_file=${Path}/source_$(date '+%F').log while true do Datetime='| Date_time | '$(date '+%F_%H:%M:%S') mysqladmin -uroot -p'123456' extended-status -r > ${tmp_file} 2>/dev/null echo ${Datetime} >> ${source_file} cat ${tmp_file} | egrep 'Queries |Com_commit |Com_rollback |Threads_connected |Threads_running |Com_select |Com_insert |Com_update |Com_delete |Innodb_rows_read |Innodb_rows_deleted |Innodb_rows_inserted |Innodb_rows_updated |Innodb_buffer_pool_read_requests |Innodb_buffer_pool_reads ' >> ${source_file} sleep 1 done |
[root@lion mysqladmin] cat mysql_analisys.sh (awk代码来源网络)
#/bin/mysql #Path=$(pwd) Path=/data/mysql_slow_log/mysqladmin cat ${Path}/source_$1.log |\ awk 'BEGIN{local_switch=0;print " Date Time QPS Com Roll TPS Th_con run Com_select insert update delete Innodb_read deleted inserted upda read_req pool_reads\n----------------------------------------------------------------------------------------------------------------------------------------------"} $2 ~ /Date_time$/ {d=$4} $2 ~ /Queries$/ {q=$4-lq;lq=$4;} $2 ~ /Com_commit$/ {c=$4-lc;lc=$4;} $2 ~ /Com_rollback$/ {r=$4-lr;lr=$4;} $2 ~ /Threads_connected$/ {tc=$4;} $2 ~ /Threads_running$/ {tr=$4;} $2 ~ /Com_select$/ {cs=$4;} $2 ~ /Com_insert$/ {ci=$4;} $2 ~ /Com_update$/ {cu=$4;} $2 ~ /Com_delete$/ {cd=$4;} $2 ~ /Innodb_rows_read$/ {irr=$4;} $2 ~ /Innodb_rows_deleted$/ {ird=$4;} $2 ~ /Innodb_rows_inserted$/ {iri=$4;} $2 ~ /Innodb_rows_updated$/ {iru=$4;} $2 ~ /Innodb_buffer_pool_read_requests$/ {ibprr=$4;} $2 ~ /Innodb_buffer_pool_reads$/ {ibpr=$4; if(local_switch==0) {local_switch=1; count=0} else { if(count>20) {count=0;print "--------------------------------------------------------------------------------------------------------------------------------------------- \n Date Time QPS Com Roll TPS Th_con run Com_select insert update delete Innodb_read deleted inserted upda read_req pool_reads\n--------------------------------------------------------------------------------------------------------------------------------------------- ";} else{ count+=1; printf "%s %-4d %-4d %-4d %-4d %-4d %-4d %-8d %-8d %-8d %-8d %-8d %-8d %-8d %-4d %-2d %-8d \n", d,q,c,r,c+r,tc,tr,cs,ci,cu,cd,irr,ird,iri,ir,ibprr,ibpr; } } }' > ${Path}/report_$1.log |
三、运行
这里是在计划任务里加入了定时任务,执行后关闭计划任务(脚本中也可以加入个计数器,刚好执行一天就结束)。
手动执行: sh mysql_status.sh
让脚本一直执行。
四、生成汇总报表
./mysql_analisys.sh 2017-03-31
五、查看报表
cat report_2017-03-31.log
--------------------------------------------------------------------------------------------------------------------------------------------- Date Time QPS Com Roll TPS Th_con run Com_select insert update delete Innodb_read deleted inserted upda read_req pool_reads --------------------------------------------------------------------------------------------------------------------------------------------- 2017-03-31_12:39:41 2268 7 0 7 4 1 1215521793 25058254 39746645 3171964 2147483647 1873070 28332293 0 2147483647 2421865 2017-03-31_12:39:42 2331 7 0 7 9 2 1215523384 25058270 39746695 3171965 2147483647 1873071 28332311 0 2147483647 2421866 2017-03-31_12:39:43 2500 10 0 10 5 2 1215524427 25058297 39746752 3171966 2147483647 1873072 28332327 0 2147483647 2421869 2017-03-31_12:39:44 2090 7 0 7 7 1 1215526000 25058331 39746797 3171976 2147483647 1873075 28332355 0 2147483647 2421870 |
六、总结
利用awk强大的编程功能实现了一个简单的小功能。
欢迎朋友们加入我创建的QQ交流群:161490064
转载于:https://blog.51cto.com/ruilan/1912051