mysql效能檢查指令碼-部分，mysql效能檢查指令碼

mysql效能檢查指令碼-部分，mysql效能檢查指令碼

#!/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}

 

伺服器維護-查看mysql的各項性可以指標

日常維護有很多方面的工作：資料庫狀態監控、效能分析、SQL程式碼分析與最佳化等等。資料庫巡檢等等工作，你可以參考國內上海愛可生公司網站上提供的MySQL服務相關的內容來寫，呵呵。還可以諮詢他們。
 
在mysql中，執行的sql指令碼中不需要執行的部分要怎加註釋？

mysql的注釋有三種，你可以使用以下任意一種。
1、#...
2、"-- ..."
3、/*...*/