#connets.sh #!/bin/sh #ocpyang@126测试数据 #根据输入参数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}
查看更多关于实战：判断mysql中当前用户的连接数-分组筛选的详细内容...