如何监控innodb status指标
发布时间：2021-12-29 00:31:06 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍如何监控innodb status指标，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！ 输入被监控机器的ip 实时获取innodb相关status指标，包含： ---------|--- MySQL Command Status --|----- Innodb row operation ----|--
|
这篇文章主要介绍如何监控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 %11dn",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指标”这篇文章的所有内容，感谢各位的阅读！ （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
