實戰：判斷mysql中目前使用者的串連數-分組篩選

#connets.sh
#!/bin/sh
#ocpyang@126.com
#根據輸入參數u或d來顯示出對應的使用者名稱或資料庫名中使用者的串連數.
#也可以輸入u 具體使用者名稱或d 具體資料庫名做進一步的分組篩選

 

 

#set mysql evn
MYSQL_USER=system  #mysql的使用者名稱
MYSQL_PASS='password'  #mysql的登入使用者密碼
MYSQL_HOST=192.168.2.188

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
}

 

 

if [ "$#" -lt 1 ];then
echo "**********************************"
echo "you must input paraters"
echo "**********************************"
echo "USAGE01: $0 d |$0 d database_name"
echo "eg01: $0 d|$0 d mysql"
echo "USAGE02: $0 u  |$0 u username"
echo "eg02: $0 u  |$0 u wind"
exit 1;
fi

#Case conversion
ipt=`echo $1 |tr '[a-z]' '[A-Z]'`

#source /usr/local/mysql/scripts/mysql_env.ini

logfiledate_init="tmpinit.`date +%Y%m%d%H%M%S`.txt"

logfiledate_midd="tmpmidd.`date +%Y%m%d%H%M%S`.txt"

judegedate_01="judegedate01.`date +%Y%m%d%H%M%S`.txt"

judegedate_02="judegedate02.`date +%Y%m%d%H%M%S`.txt"

mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"show processlist;" >${logfiledate_init}

 

if [ "$#" -eq 1 ]; then
 if [ "$ipt" = 'D' ];then
 awk '{tt[$4]++} BEGIN { printf  "%-20s %-20s\n" ,"dbname","connect";} END{for (i in tt) printf "%-20s %-20s\n" ,i,tt[i]}' ${logfiledate_init}  | grep -v "NULL"
 elif  [ "$ipt" = 'U' ];then
 awk '{tt[$2]++} BEGIN { printf  "%-20s %-20s\n" ,"username","connect";} END{for (i in tt) printf "%-20s %-20s\n" ,i,tt[i]}' ${logfiledate_init}  | grep -v "NULL"
 else
 cechon "輸入錯誤!" red
 echo "                              "
 fi
elif [ "$#" -eq 2 ]; then
 grep -i $2  ${logfiledate_init} > ${logfiledate_midd}
 if [ "$ipt" = 'D'  ];then
 SCHEMA_JUDEGE01="select schema_name from information_schema.schemata where schema_name='$2';"
 mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${SCHEMA_JUDEGE01}" >${judegedate_01}
  if [ ! -s "${judegedate_01}" ];then
   cechon "you input schema_name $2 not exits,pleae check your schema_name" red
   rm -rf ${SCHEMA_JUDEGE01}
   exit 0
  else
  awk '{tt[$4]++} BEGIN { printf  "%-20s %-20s\n" ,"dbname","connect";} END{for (i in tt) printf "%-20s %-20s\n" ,i,tt[i]}' ${logfiledate_midd}  | grep -v "NULL"
  fi
 elif  [ "$ipt" = 'U' ];then
 SCHEMA_JUDEGE02="select user from mysql.user where user='$2';"
 mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${SCHEMA_JUDEGE02}" >${judegedate_02}
  if [ ! -s "${judegedate_02}" ];then
  cechon "you input username $2 not exits,pleae check your user_name" red
  rm -rf ${SCHEMA_JUDEGE02}
  exit 0
  else
  awk '{tt[$2]++} BEGIN { printf  "%-20s %-20s\n" ,"username","connect";} END{for (i in tt) printf "%-20s %-20s\n" ,i,tt[i]}' ${logfiledate_midd}  | grep -v "NULL"
  fi
 else
 cechon  "輸入錯誤!" red
 echo "                              "
 fi
fi
 

#清除臨時檔案
rm -rf ${logfiledate_init}
rm -rf ${logfiledate_midd}
rm -rf ${judegedate_01}
rm -rf ${judegedate_02}