Actual combat: Determine the current user's connection number in MySQL-group filter

#connets. Sh
#!/bin/sh
#[email protected]
#根据输入参数u或d来显示出对应的用户名或数据库名中用户的连接数.
#也可以输入u specific user names or D specific database names for further grouping filtering

#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.
# parameter $ = message
# parameter = = Color
{
Local default_msg= "No message passed."

message=${1:-$default _msg} # is the default value default_msg if no input is entered.
Color=${2:-black} # The default value is black if no input is entered.

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 Restore default values
printf "$black"

Return
}

Cechon ()

# Color-echo.
# parameter 1 = message
# parameter 2 = = Color
{
Local default_msg= "No message passed."
# doesn ' t really need to be a local variable.

message=${1:-$default _msg} # is the default value default_msg if no input is entered.
Color=${2:-black} # The default value is black if no input is entered.

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 Restore default values
printf "$black"

Return
}

If ["$#"-lt 1];then
echo "**********************************"
echo "You must input Paraters"
echo "**********************************"
echo "USAGE01: $ D |$0 d database_name"
echo "EG01: $ d|$0 D mysql"
echo "USAGE02: $ u |$0 u username"
echo "EG02: $ u |$0 u Wind"
Exit 1;
Fi

#Case Conversion
Ipt= ' echo $ |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 "Input Error!" Red
echo ""
Fi
elif ["$#"-eq 2]; Then
Grep-i ${logfiledate_init} > ${logfiledate_midd}
If ["$ipt" = ' D '];then
schema_judege01= "Select Schema_name from Information_schema.schemata where schema_name= ' $ ';"
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 $ 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= ' $ ';"
Mysql-h${mysql_host}-u${mysql_user}-p${mysql_pass}-E "${schema_judege02}" >${judegedate_02}
If [!-S "${judegedate_02}"];then
Cechon "You input username $ 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 "Input Error!" Red
echo ""
Fi
Fi

#清除临时文件
RM-RF ${logfiledate_init}
RM-RF ${logfiledate_midd}
RM-RF ${judegedate_01}
RM-RF ${judegedate_02}