#!/bin/sh#ocpyang@126.comexport 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 inblack)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 ;;esacprintf %s\n $messagetput sgr0 # tput sgr0即恢复默认值printf $blackreturn}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 inblack)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 ;;esacprintf %s $messagetput sgr0 # tput sgr0即恢复默认值printf $blackreturn}#set mysql evnmysql_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`.txtmysql -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} redecho 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} redecho #3.客户端连接的mysql进程数conn_01=conn01.`date +%y%m%d%h%m%s`.txtconn_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} redecho rm -rf ${conn_01}#4.qps(每秒事务量)qps_01=show global status like 'questions';qps_re=qpsre.`date +%y%m%d%h%m%s`.txtmysql -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} redecho 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`.txttps_re02=tpsre02.`date +%y%m%d%h%m%s`.txtmysql -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} redecho 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`.txtkbrd_re02=kbrd02.`date +%y%m%d%h%m%s`.txtmysql -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 ];thencechon 6.1 there is no any value! greenecho elsekbrd_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} redecho firm -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`.txtkbwd_re02=kbwd02.`date +%y%m%d%h%m%s`.txtmysql -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 ] ;thencechon 6.2 there is no any value! greenecho elsekbwd_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} redecho firm -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`.txtinnob_re02=innob_re02.`date +%y%m%d%h%m%s`.txtmysql -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 ] ;thencechon 7. there is no any value! greenecho elseinnob_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} redecho firm -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`.txtqc_re02=qc_re02.`date +%y%m%d%h%m%s`.txtqc_re03=qc_re03.`date +%y%m%d%h%m%s`.txtmysql -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 ] ;thencechon 8. there is no any value! greenecho elseqc_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} redecho firm -rf ${qc_re01}rm -rf ${qc_re02}rm -rf ${qc_re03}