查看Innodb状态监视器的技巧有哪些

这篇文章主要介绍如何监控innodb status指标，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

输入被监控机器的ip 实时获取innodb相关status指标，包含：

---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --

---QPS---|select insert update delete| read inserted updated deleted| logical  physical

#需要编译脚本 写入账号密码 

#可以给自己维护的所有DB创建一个监控专用账号 密码统一 这样写死在脚本中 直接用即可

]$ cat innodb_status.sh 

#!/bin/bash

#参数变量

host=$1

port=$2

username=用户

passwd=密码

#echo "sh $0 $host $username $passwd $port"

#fountion 脚本说明、帮助

help_info(){ 

  echo "NAME" 

  echo "   $0" 

  echo "SYNOPSIS" 

  echo "   $0 is a shell script and monitor innodb status,eg:qps,tps......" 

  echo "DESCRIPTION" 

  echo "   option like mysql client -h host -u username -p password -P port" 

  echo "Usage:"

  echo "   sh $0 ipaddr username password port"

  echo "   E.g sh $0 10.111.7.31 3306 yujx yujxpw"

  echo

} 

#function 打印innodb相关指标

server_id(){

    mysql -h $host -P$port -u$username -p$passwd -e "show variables like 'server_id'"|grep -iv value

}

innodb_status(){

mysqladmin -h $host -P$port -u$username -p$passwd -r -i 1 ext |\

awk -F"|" \

"BEGIN{ count=0; }"\

'{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\

  print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\

  print "---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical  physical";\

}\

else if ($2 ~ /Queries/){queries=$3;}\

else if ($2 ~ /Com_select /){com_select=$3;}\

else if ($2 ~ /Com_insert /){com_insert=$3;}\

else if ($2 ~ /Com_update /){com_update=$3;}\

else if ($2 ~ /Com_delete /){com_delete=$3;}\

else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\

else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\

else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\

else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\

else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\

else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\

else if ($2 ~ /Uptime / && count >= 2){\

 printf(" %s |%9d",strftime("%H:%M:%S"),queries);\

 printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\

 printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\

 printf("|%10d %11d\n",innodb_lor,innodb_phr);\

}}'

}

# 默认端口3306

if [ ! $port ]; then 

    port=3306

fi 

#echo $#,$1

# 执行脚本

if [ $# -le 0 ]||[ "$1"x = "-h"x ]||[ "$1"x = "--help"x ]

then 

  help_info 

else

  echo "................"

  server_id

  echo "..................."

  innodb_status

fi 

#运行

]$ ./innodb_status.sh ip地址

................

Warning: Using a password on the command line interface can be insecure.

server_id   111111

...................

Warning: Using a password on the command line interface can be insecure.

----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --

---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical  physical

 09:37:24 |  10526| 318  66 5021   0| 18124   66 10061   0|  537009     87

 09:37:25 |  10596| 298  96 5048   0| 21170   96 10118   0|  535858     79

 09:37:26 |  10300| 238  65 4947   0| 21968   65  9914   0|  525109     91

以上是“如何监控innodb status指标”这篇文章的所有内容，感谢各位的阅读！

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!