实战：判断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} -eshow 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}