mysql性能检查脚本-部分_MySQL

#!/bin/sh
#ocpyang@126.com

export black=&＃39;\033[0m&＃39;
export boldblack=&＃39;\033[1;0m&＃39;
export red=&＃39;\033[31m&＃39;
export boldred=&＃39;\033[1;31m&＃39;
export green=&＃39;\033[32m&＃39;
export boldgreen=&＃39;\033[1;32m&＃39;
export yellow=&＃39;\033[33m&＃39;
export boldyellow=&＃39;\033[1;33m&＃39;
export blue=&＃39;\033[34m&＃39;
export boldblue=&＃39;\033[1;34m&＃39;
export magenta=&＃39;\033[35m&＃39;
export boldmagenta=&＃39;\033[1;35m&＃39;
export cyan=&＃39;\033[36m&＃39;
export boldcyan=&＃39;\033[1;36m&＃39;
export white=&＃39;\033[37m&＃39;
export boldwhite=&＃39;\033[1;37m&＃39;

cecho ()

## -- Function to easliy print colored text -- ##

# Color-echo.
# 参数 $1 = message
# 参数 $2 = color
{
local default_msg="No message passed."

message=${1:-$default_msg} # 如果$1没有输入则为默认值default_msg.
color=${2:-black} # 如果$1没有输入则为默认值black.

case $color in
black)
printf "$black" ;;
boldblack)
printf "$boldblack" ;;
red)
printf "$red" ;;
boldred)
printf "$boldred" ;;
green)
printf "$green" ;;
boldgreen)
printf "$boldgreen" ;;
yellow)
printf "$yellow" ;;
boldyellow)
printf "$boldyellow" ;;
blue)
printf "$blue" ;;
boldblue)
printf "$boldblue" ;;
magenta)
printf "$magenta" ;;
boldmagenta)
printf "$boldmagenta" ;;
cyan)
printf "$cyan" ;;
boldcyan)
printf "$boldcyan" ;;
white)
printf "$white" ;;
boldwhite)
printf "$boldwhite" ;;
esac
printf "%s\n" "$message"
tput sgr0 # tput sgr0即恢复默认值
printf "$black"

return
}

cechon ()

# Color-echo.
# 参数1 $1 = message
# 参数2 $2 = color
{
local default_msg="No message passed."
# Doesn&＃39;t really need to be a local variable.

message=${1:-$default_msg} # 如果$1没有输入则为默认值default_msg.
color=${2:-black} # 如果$1没有输入则为默认值black.

case $color in
black)
printf "$black" ;;
boldblack)
printf "$boldblack" ;;
red)
printf "$red" ;;
boldred)
printf "$boldred" ;;
green)
printf "$green" ;;
boldgreen)
printf "$boldgreen" ;;
yellow)
printf "$yellow" ;;
boldyellow)
printf "$boldyellow" ;;
blue)
printf "$blue" ;;
boldblue)
printf "$boldblue" ;;
magenta)
printf "$magenta" ;;
boldmagenta)
printf "$boldmagenta" ;;
cyan)
printf "$cyan" ;;
boldcyan)
printf "$boldcyan" ;;
white)
printf "$white" ;;
boldwhite)
printf "$boldwhite" ;;
esac
printf "%s" "$message"
tput sgr0 # tput sgr0即恢复默认值
printf "$black"

return
}

#set mysql evn
MYSQL_USER=system #mysql的用户名
MYSQL_PASS=&＃39;password&＃39; #mysql的登录用户密码
MYSQL_HOST=192.168.2.188

#1.mysql版本
v_01="select @@version;"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}" >${v_02}
v_03=`cat ${v_02} | grep -v @@version`
cechon "1. mysql runing version is: ${v_03} " red
echo " "
rm -rf ${v_02}

#2.系统mysql的进程数
mysql_processnum=`ps -ef | grep "mysql" | grep -v "grep" | wc -l`
cechon "2. mysql process number is: ${mysql_processnum} " red
echo " "

#3.客户端连接的mysql进程数

conn_01="conn01.`date +%Y%m%d%H%M%S`.txt"
conn_02="show processlist;"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${conn_02}" | grep -v Id >${conn_01}
client_conn_num=`cat ${conn_01} |wc -l`
cechon "3. mysql client connect number is: ${client_conn_num} " red
echo " "
rm -rf ${conn_01}

#4.QPS(每秒事务量)

qps_01="show global status like &＃39;Questions&＃39;;"
qps_re="qpsre.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${qps_01}" |grep -v Variable_name \
|cut -f 2 >${qps_re}
qps_02=`cat ${qps_re}`
qps_03=`cat /proc/uptime |awk &＃39;{print $1}&＃39;`
qps_04=`awk &＃39;BEGIN{print &＃39;${qps_02}&＃39; / &＃39;${qps_03}&＃39;}&＃39;` #shell默认不支持浮点运算
cechon "4. current mysql server QPS is: ${qps_04} " red
echo " "
rm -rf ${qps_re}

#5.TPS(每秒事务量)
tps_01="show status where Variable_name in(&＃39;Com_commit&＃39;); "
tps_02="show status where Variable_name in(&＃39;Com_rollback&＃39;); "
tps_re01="tpsre01.`date +%Y%m%d%H%M%S`.txt"
tps_re02="tpsre02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_01}" |grep -v Variable_name \
|cut -f 2 >${tps_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_02}" |grep -v Variable_name \
|cut -f 2 >${tps_re02}
tps_03=`cat ${tps_re01}`
tps_04=`cat ${tps_re02}`
tps_sum=`awk &＃39;BEGIN{print &＃39;${tps_03}&＃39; + &＃39;${tps_04}&＃39;}&＃39;` #shell默认不支持浮点运算
tps_uptime=`cat /proc/uptime |awk &＃39;{print $1}&＃39;`
tps_avg=`awk &＃39;BEGIN{print &＃39;${tps_sum}&＃39; / &＃39;${tps_uptime}&＃39;}&＃39;` #shell默认不支持浮点运算
cechon "5. current mysql server TPS is: ${tps_avg} " red
echo " "
rm -rf ${tps_re01}
rm -rf ${tps_re02}

#6.key Buffer 命中率

#key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%

kbrd_01="show status like &＃39;Key_reads&＃39;; "
kbrd_02="show status like &＃39;Key_read_requests&＃39;; "
kbrd_re01="kbrd01.`date +%Y%m%d%H%M%S`.txt"
kbrd_re02="kbrd02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${kbrd_01}" |grep -v Variable_name \
|cut -f 2 >${kbrd_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${kbrd_02}" |grep -v Variable_name \
|cut -f 2 >${kbrd_re02}

kbrd_03=`cat ${kbrd_re01}`
kbrd_04=`cat ${kbrd_re02}`

if [ "${kbrd_03}" -eq 0 ];then
cechon "6.1 there is no any value!" green
echo " "
else
kbrd_05=`awk &＃39;BEGIN{print &＃39;${kbrd_03}&＃39; / &＃39;${kbrd_04}&＃39;}&＃39;` #shell默认不支持浮点运算
kbrd_06=`awk &＃39;BEGIN{print &＃39;1-${kbrd_05}&＃39;}&＃39;` #shell默认不支持浮点运算
key_buffer_read_hits=`awk &＃39;BEGIN{print &＃39;${kbrd_06}&＃39; * 100}&＃39;`
cechon "6.1 current mysql key_buffer_read_hits is: ${key_buffer_read_hits} " red
echo " "
fi

rm -rf ${kbrd_re01}
rm -rf ${kbrd_re02}

#key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

kbwd_01="show status like &＃39;Key_writes&＃39;; "
kbwd_02="show status like &＃39;Key_write_requests&＃39;; "
kbwd_re01="kbwd01.`date +%Y%m%d%H%M%S`.txt"
kbwd_re02="kbwd02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${kbwd_01}" |grep -v Variable_name \
|cut -f 2 >${kbwd_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${kbwd_02}" |grep -v Variable_name \
|cut -f 2 >${kbwd_re02}

kbwd_03=`cat ${kbwd_re01}`
kbwd_04=`cat ${kbwd_re02}`

if [ "${kbwd_03}" -eq 0 ] ;then
cechon "6.2 there is no any value!" green
echo " "
else
kbwd_05=`awk &＃39;BEGIN{print &＃39;${kbwd_03}&＃39; / &＃39;${kbwd_04}&＃39;}&＃39;` #shell默认不支持浮点运算
kbwd_06=`awk &＃39;BEGIN{print &＃39;1-${kbwd_05}&＃39;}&＃39;` #shell默认不支持浮点运算
key_buffer_write_hits=`awk &＃39;BEGIN{print &＃39;${kbwd_06}&＃39; * 100}&＃39;`
cechon "6.2 current mysql key_buffer_write_hits is: ${key_buffer_write_hits} " red
echo " "
fi

rm -rf ${kbwd_re01}

rm -rf ${kbwd_re02}

#7.InnoDB Buffer命中率
#Innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%
innob_01="show status like &＃39;Innodb_buffer_pool_reads&＃39;; "
innob_02="show status like &＃39;Innodb_buffer_pool_read_requests&＃39;; "
innob_re01="innob_re01.`date +%Y%m%d%H%M%S`.txt"
innob_re02="innob_re02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${innob_01}" |grep -v Variable_name \
|cut -f 2 >${innob_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${innob_02}" |grep -v Variable_name \
|cut -f 2 >${innob_re02}

innob_03=`cat ${innob_re01}`
innob_04=`cat ${innob_re02}`
if [ "${innob_03}" -eq 0 ] ;then
cechon "7. there is no any value!" green
echo " "
else
innob_05=`awk &＃39;BEGIN{print &＃39;${innob_03}&＃39; / &＃39;${innob_04}&＃39;}&＃39;` #shell默认不支持浮点运算
innob_06=`awk &＃39;BEGIN{print &＃39;1-${innob_05}&＃39;}&＃39;` #shell默认不支持浮点运算
innodb_buffer_read_hits=`awk &＃39;BEGIN{print &＃39;${innob_06}&＃39; * 100}&＃39;`
cechon "7. current mysql Innodb_buffer_read_hits is: ${innodb_buffer_read_hits} " red
echo " "
fi
rm -rf ${innob_re01}
rm -rf ${innob_re02}

#8.Query Cache命中率
#Query_cache_hits =((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)

qc_01="show status like &＃39;Qcache_hits&＃39;; "
qc_02="show status like &＃39;Qcache_inserts&＃39;; "
qc_03="show status like &＃39;Qcache_not_cached&＃39;; "

qc_re01="qc_re01.`date +%Y%m%d%H%M%S`.txt"
qc_re02="qc_re02.`date +%Y%m%d%H%M%S`.txt"
qc_re03="qc_re03.`date +%Y%m%d%H%M%S`.txt"

mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${qc_01}" |grep -v Variable_name \
|cut -f 2 >${qc_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${qc_02}" |grep -v Variable_name \
|cut -f 2 >${qc_re02}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${qc_03}" |grep -v Variable_name \
|cut -f 2 >${qc_re03}

qc_04=`cat ${qc_re01}`
qc_05=`cat ${qc_re02}`
qc_06=`cat ${qc_re03}`

if [ "${qc_04}" -eq 0 ] ;then
cechon "8. there is no any value!" green
echo " "
else
qc_07=`awk &＃39;BEGIN{print &＃39;${qc_04}&＃39; + &＃39;${qc_05}&＃39; + &＃39;${qc_06}&＃39; }&＃39;`
qc_08=`awk &＃39;BEGIN{print &＃39;${qc_04}&＃39;/&＃39;${qc_07}&＃39;}&＃39;`
query_cache_hits=`awk &＃39;BEGIN{print &＃39;${qc_08}&＃39; * 100}&＃39;`
cechon "8. current mysql query_cache_hits is: ${query_cache_hits} " red
echo " "
fi
rm -rf ${qc_re01}
rm -rf ${qc_re02}
rm -rf ${qc_re03}