MySQL巡检

查询非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/bashsetup_select(){ clearcat << menu +++++++++ SHOW MYSQL STATUS ++++++++++           1. show mysql qps_3306          2. show mysql processlist          3. show mysql qps_3307          Q. quit  +++++++++++++++++++++++++++++++++++++++++++++menuecho -e -n " Please Select [1,2,3,Q] > "}###########################################################amp_setup(){        setup_select        read select        case $select in                       1)           /usr/bin/mysqladmin -P3306 -uroot -p  -r -i 1 extended-status |awk -F "|" 'BEGIN { count=0; } { if($2 ~ /Variable_name/ && ++count == 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("|d d\n",innodb_lor,innodb_phr);}}'                amp_setup              ;;        2)                /usr/bin/mysqladmin -P3306 -uroot -p   pr|grep -iv sleep          sleep 10                    amp_setup              ;;       3)/usr/bin/mysqladmin -P3307 -uroot -p   -r -i 1 extended-status |awk -F "|" 'BEGIN { count=0; } { if($2 ~ /Variable_name/ && ++count == 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("|d d\n",innodb_lor,innodb_phr);}}'        amp_setup        ;;        Q|q)                 exit 0               ;;            *)                 amp_setup               ;; esac}start_menu(){amp_setup}start_menu