mysql性能检查脚本-部分_MySQL
程序员文章站
2022-05-24 13:43:05
...
#!/bin/sh #ocpyang@126.com export black='\033[0m' export boldblack='\033[1;0m' export red='\033[31m' export boldred='\033[1;31m' export green='\033[32m' export boldgreen='\033[1;32m' export yellow='\033[33m' export boldyellow='\033[1;33m' export blue='\033[34m' export boldblue='\033[1;34m' export magenta='\033[35m' export boldmagenta='\033[1;35m' export cyan='\033[36m' export boldcyan='\033[1;36m' export white='\033[37m' export boldwhite='\033[1;37m' 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'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='password' #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 'Questions';" 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 '{print $1}'` qps_04=`awk 'BEGIN{print '${qps_02}' / '${qps_03}'}'` #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('Com_commit'); " tps_02="show status where Variable_name in('Com_rollback'); " 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 'BEGIN{print '${tps_03}' + '${tps_04}'}'` #shell默认不支持浮点运算 tps_uptime=`cat /proc/uptime |awk '{print $1}'` tps_avg=`awk 'BEGIN{print '${tps_sum}' / '${tps_uptime}'}'` #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 'Key_reads'; " kbrd_02="show status like 'Key_read_requests'; " 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 'BEGIN{print '${kbrd_03}' / '${kbrd_04}'}'` #shell默认不支持浮点运算 kbrd_06=`awk 'BEGIN{print '1-${kbrd_05}'}'` #shell默认不支持浮点运算 key_buffer_read_hits=`awk 'BEGIN{print '${kbrd_06}' * 100}'` 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 'Key_writes'; " kbwd_02="show status like 'Key_write_requests'; " 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 'BEGIN{print '${kbwd_03}' / '${kbwd_04}'}'` #shell默认不支持浮点运算 kbwd_06=`awk 'BEGIN{print '1-${kbwd_05}'}'` #shell默认不支持浮点运算 key_buffer_write_hits=`awk 'BEGIN{print '${kbwd_06}' * 100}'` 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 'Innodb_buffer_pool_reads'; " innob_02="show status like 'Innodb_buffer_pool_read_requests'; " 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 'BEGIN{print '${innob_03}' / '${innob_04}'}'` #shell默认不支持浮点运算 innob_06=`awk 'BEGIN{print '1-${innob_05}'}'` #shell默认不支持浮点运算 innodb_buffer_read_hits=`awk 'BEGIN{print '${innob_06}' * 100}'` 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 'Qcache_hits'; " qc_02="show status like 'Qcache_inserts'; " qc_03="show status like 'Qcache_not_cached'; " 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 'BEGIN{print '${qc_04}' + '${qc_05}' + '${qc_06}' }'` qc_08=`awk 'BEGIN{print '${qc_04}'/'${qc_07}'}'` query_cache_hits=`awk 'BEGIN{print '${qc_08}' * 100}'` 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}