MySQL会话连接信息汇总报表脚本

mysql -uroot -pvincent -t -e "show full processlist" |\
grep -Ev '.*Id.*User.*Host.*db.*Command|^\+'|\
awk 'BEGIN{FS="|"}
          {{Sessions++}
           {Users[$3]++}
           {split($4,Host,":");Hosts[Host[1]]++}
           {Dbs[$5]++}
           {Commands[$6]++}
           {if($6!~/Sleep/ && $7>=10)Times++}
           {if($8~/^ +$/)States[" NULL"]++;else States[$8]++}
          }
       END{{for(i=1;i<104;i++) Line=Line"-"}{print Line}
           {printf("| Total Sessions: %82s  |\n",Sessions)}{print Line}
           {printf("| Active Sessions Exec >10s:   %69s  |\n",Times)}{print Line}
           {for(h in Hosts)    printf("| Host:     %-72s Connects: %5s  |\n",h,Hosts[h])}  {print Line}
           {for(u in Users)    printf("| User:     %-72s Connects: %5s  |\n",u,Users[u])}  {print Line}
           {for(d in Dbs)      printf("| DB:       %-72s Connects: %5s  |\n",d,Dbs[d])}{print Line}
           {for(c in Commands) printf("| Command:  %-72s Count:    %5s  |\n",c,Commands[c])}  {print Line}
           {for(s in States)   printf("| State:    %-72s Count:    %5s  |\n",s,States[s])}   {print Line}
          }'
# 简单解释：
# 该脚本是对 show full processlist 输出信息的awk汇总报表展示
# 调整 $7>=n 搂取执行时间大于n秒的会话数量
# 因awk数组排序的复杂性，暂时不对每一栏进行排序
# 本脚本能够整体上对MySQL会话状态做一个简单汇总，为定为问题提供一个参考维度