mysql巡检_MySQL巡检

最新推荐文章于 2024-07-15 10:06:00 发布

带虾条酱

最新推荐文章于 2024-07-15 10:06:00 发布

阅读量156

点赞数

文章标签： mysql巡检

本文链接：https://blog.csdn.net/weixin_29323977/article/details/113122610

版权

本文提供了检查MySQL数据库性能的方法，包括查询非InnoDB引擎的表，实时监测QPS、TPS、事务操作以及查看不同实例的select、insert、update、delete情况。通过脚本展示如何获取并分析MySQL的状态信息，如查询频率、事务提交与回滚、线程连接和运行状态等。

摘要由CSDN通过智能技术生成

查询非innodb引擎的表

select TABLE_NAME,TABLE_SCHEMA,engine from information_schema.tables where engine='MyISAM' and TABLE_SCHEMA not in ('mysql','performance_schema','information_schema');

查看当前的qps和tps以及等等

#!/bin/bash

/usr/bin/mysqladmin -P3306 -uroot -p extended-status -i1|awk 'BEGIN{local_switch=0;print "QPS Commit Rollback TPS Threads_con Threads_run \n------------------------------------------------------- "}

$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;

if(local_switch==0)

{local_switch=1; count=0}

else {

if(count>10)

{count=0;print "------------------------------------------------------- \nQPS Commit Rollback TPS Threads_con Threads_run \n------------------------------------------------------- ";}

else{

count+=1;

printf "%-6d %-8d %-7d %-8d %-10d %d \n", q,c,r,c+r,tc,tr;

}

查看多个实例select insert update delete情况

#!/bin/bash

setup_select()

{

clear

cat << menu

+++++++++ SHOW MYSQL STATUS ++++++++++

1. show mysql qps_3306

2. show mysql processlist

3. show mysql qps_3307

Q. quit

+++++++++++++++++++++++++++++++++++++++++++++

echo -e -n " Please Select [1,2,3,Q] > "

}

###########################################################

amp_setup()

{

setup_select

read select

case $select in

/usr/bin/mysqladmin -P3306 -uroot -p -r -i 1 extended-status |awk -F "|" 'BEGIN { count=0; } { if($2 ~ /Variable_name/ && ++count%15 == 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("|%8d %7d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted); printf("|%10d %11d\n",innodb_lor,innodb_phr);}}'

amp_setup

;;

/usr/bin/mysqladmin -P3306 -uroot -p pr|grep -iv sleep

sleep 10

amp_setup

;;

/usr/bin/mysqladmin -P3307 -uroot -p -r -i 1 extended-status |awk -F "|" 'BEGIN { count=0; } { if($2 ~ /Variable_name/ && ++count%15 == 1){print "----------|---------|--- MySQL Command Status --|----- Innodb row operation -----|-- Buffer Pool Read --"; print "---Time---|---QPS---|select insert update delete| read insertedupdated 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("|%8d %7d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted); printf("|%10d %11d\n",innodb_lor,innodb_phr);}}'

amp_setup

;;

Q|q)

exit 0

;;

amp_setup

;;

esac

}

start_menu()

{

amp_setup