最近好事多，团队加入了新鲜的血液，全国测评成功结束，虽然不够完美，但是留下来的遗憾正是我们前进的动力
看看我司漂亮的单身姑娘们，还有UCloud已婚的小伙子们，这两张相片把我拍的好丑，不过有一个比我还丑的存在，他叫小黑，我兄弟，研发高手，sql高手，至于是谁，大家猜猜吧
言归正传，当一次扩展的设备非常多的时候，给数据库授权变得很麻烦，手动授权那就是噩梦，要是还需要同时给多个数据库授权，想想吧。。。
本脚本实现了可以给账户和密码都相同的n个数据库一起授权。
#!/bin/bash
#####################################################################
#@@@ Author : bluewhalew(playful_bluewhale@hotmail.com)
#@@@ Name : BatchGrant.sh
#@@@ Describe : batch grant privileges
#@@@ Created in : 20170623
#@@@ Modified in : 20170623
#####################################################################
BASE_PATH=$(cd `dirname $0`; pwd)
#ip:port:dbname格式存储数据库信息
DB_INFO_FILE=$BASE_PATH/dbName.txt
Input_DB_Select_List=$BASE_PATH/inputDBSelectList.txt
Input_IP_Address_List=$BASE_PATH/inputIPAdddressList.txt
Mysql_Pri_List=$BASE_PATH/mysqlPriList.txt
Mysql_Pri_Content=$BASE_PATH/mysqlPriContent.txt
Select_Pri_Num_List=$BASE_PATH/selectPriList.txt
Username_PWD=$BASE_PATH/usernamePWD.txt
SQLDir=$BASE_PATH/sqldir
execTime=`date "+%Y%m%d%H%M%S"`
cat /dev/null > $Mysql_Pri_Content
error()
{
#echo "$1" 1>&2
echo "$1"
}
##############################################################################################################
echo -e '\e[32m##################################### All DB Info #######################################\e[m'
rowNum=1
cat $DB_INFO_FILE |while read line
do
echo $rowNum": "$line
rowNum=$(($rowNum + 1))
done
echo -e '\e[32m#########################################################################################\e[m'
echo " "
##############################################################################################################
inputCount=1
echo -e '\e[32mPlease Select the db Number, Ending with a Enter. [EXAMPLE: 1 2 4] : \e[m'
while (($inputCount<4))
do
read
inputIsTureOrFalse=`echo $REPLY|sed 's/[0-9]*//g'|sed 's/ //g'|sed '/^ *$/d'|wc -l`
if [[ $inputIsTureOrFalse -eq 0 ]] && [[ "$REPLY" != "" ]]; then
break
else
if [ $inputCount -eq 3 ]; then
echo -e '\e[31mWrong input times out of bounds, Exit!!!\e[m'
else
echo -e '\e[31mInput is error,plese input again !!!\e[m'
fi
fi
inputCount=$(($inputCount + 1))
done
if [ $inputCount -eq 4 ]; then
exit
fi
echo " "
##############################################################################################################
echo $REPLY|sed 's/ /\n/g' > $Input_DB_Select_List
echo -e '\e[32mYour selected db is :\e[m'
dbCount=`cat $DB_INFO_FILE|wc -l`
cat $Input_DB_Select_List|while read line
do
if [[ $line -gt $dbCount ]] || [[ $line -eq 0 ]]; then
echo -e '\e[31mThe db is selected('$line') is not exist !!!\e[m'
break
else
cat $DB_INFO_FILE|sed -n ''$line'p'
fi
done
echo -e '\e[32m#########################################################################################\e[m'
echo " "
##############################################################################################################
inputCount=1
echo -e '\e[32mPlease enter ip address, Ending with a Enter. [EXAMPLE: 10.10.10.1/10.10.10.2 ...] : \e[m'
while (($inputCount<4))
do
read
echo $REPLY|sed 's/\//\n/g' > $Input_IP_Address_List
inputCheck=`echo $REPLY|sed 's/ //g'|sed '/^ *$/d'|wc -l`
if [ "$REPLY" == "" ] || [ $inputCheck -eq 0 ]; then
if [ $inputCount -eq 3 ]; then
echo -e '\e[31mWrong input times out of bounds, Exit!!!\e[m'
exit
else
echo -e '\e[31mWrong, Please input ip address again !!!\e[m'
inputCount=$(($inputCount + 1))
continue
fi
fi
echo -e '\e[32mPlese Check Your inputted ip address :\e[m'
cat $Input_IP_Address_List
echo -e '\e[32mInput is true or false. [Y/N] : \e[m'
read
if [ "$REPLY" == "Y" ]; then
break
else
if [ $inputCount -eq 3 ]; then
echo -e '\e[31mWrong input times out of bounds, Exit!!!\e[m'
else
echo -e '\e[31mPlease input ip address again !!!\e[m'
fi
fi
inputCount=$(($inputCount + 1))
done
if [ $inputCount -eq 4 ]; then
exit
fi
##############################################################################################################
echo -e '\e[32m#################################### Mysql Pri List ######################################\e[m'
rowNum=1
cat $Mysql_Pri_List |while read line
do
echo $rowNum": "$line
rowNum=$(($rowNum + 1))
done
echo -e '\e[32m#########################################################################################\e[m'
echo " "
##############################################################################################################
inputCount=1
while (($inputCount<4))
do
flag=0
echo -e '\e[32mPlease select pri, Ending with a Enter. [EXAMPLE: 1/2 ...] : \e[m'
read
echo $REPLY|sed 's/\//\n/g' > $Select_Pri_Num_List
echo -e '\e[32mYour selected pri is :\e[m'
dbCount=`cat $Mysql_Pri_List|wc -l`
while read line
do
if [[ $line -gt $dbCount ]] || [[ $line -eq 0 ]] || [[ "$line" == "" ]]; then
echo -e '\e[31mThe Pri is selected('$line') is not exist !!!\e[m'
flag=1
break
else
cat $Mysql_Pri_List|sed -n ''$line'p'
cat $Mysql_Pri_List|sed -n ''$line'p' >> $Mysql_Pri_Content
fi
done < $Select_Pri_Num_List
if [ $flag -eq 1 ]; then
inputCount=$(($inputCount + 1))
if [ $inputCount -eq 4 ]; then
echo -e '\e[31mWrong input times out of bounds, Exit!!!\e[m'
fi
continue
fi
echo -e '\e[32mInput is true or false. [Y/N] : \e[m'
read
if [ "$REPLY" == "Y" ]; then
break
else
if [ $inputCount -eq 3 ]; then
echo -e '\e[31mWrong input times out of bounds, Exit!!!\e[m'
else
echo -e '\e[31mPlease select pri number again !!!\e[m'
fi
fi
inputCount=$(($inputCount + 1))
done
if [ $inputCount -eq 4 ]; then
exit
fi
echo -e '\e[32m#########################################################################################\e[m'
echo " "
##############################################################################################################
inputCount=1
while (($inputCount<4))
do
echo -e '\e[32mPlease username and password, Ending with a Enter. [EXAMPLE: username/password] : \e[m'
read
echo $REPLY|sed 's/\//\n/g' > $Username_PWD
rowCount=`cat $Username_PWD|wc -l`
if [ "$REPLY" == "" ] || [ $rowCount -ne 2 ]; then
echo -e '\e[31mInput is wrong ,plese input again!!!\e[m'
inputCount=$(($inputCount + 1))
if [ $inputCount -lt 4 ]; then
continue
else
echo -e '\e[31mWrong input times out of bounds, Exit!!!\e[m'
exit
fi
fi
echo -e '\e[32mYour Input UserName and PWD is :\e[m'
echo "UserName :" `cat $Username_PWD|head -1`
echo "Password :" `cat $Username_PWD|tail -1`
echo -e '\e[32mInput is true or false. [Y/N] : \e[m'
read
if [ "$REPLY" == "Y" ]; then
break
else
if [ $inputCount -eq 3 ]; then
echo -e '\e[31mWrong input times out of bounds, Exit!!!\e[m'
exit
else
echo -e '\e[31mPlease input username and PWD again !!!\e[m'
fi
fi
inputCount=$(($inputCount + 1))
done
echo -e '\e[32m#########################################################################################\e[m'
echo " "
##############################################################################################################
echo -e '\e[32mYour All Input Info is : \e[m'
echo -e '\e[32mTarget db info is : \e[m'
cat $Input_DB_Select_List|while read line
do
cat $DB_INFO_FILE|sed -n ''$line'p'
done
echo " "
echo -e '\e[32mAccess ip is : \e[m'
cat $Input_IP_Address_List
echo " "
echo -e '\e[32mAccess Pri is : \e[m'
cat $Select_Pri_Num_List|while read line
do
cat $Mysql_Pri_List|sed -n ''$line'p'
done
echo " "
echo -e '\e[32mUserName and Pwd is : \e[m'
echo "UserName :" `cat $Username_PWD|head -1`
echo "Password :" `cat $Username_PWD|tail -1`
echo " "
echo -e '\e[32mInput is true or false. [Y/N] : \e[m'
read
if [ "$REPLY" == "Y" ]; then
break
else
echo -e '\e[31mPlease Input Alli Info Again, exited !!!\e[m'
exit
fi
echo -e '\e[32m#########################################################################################\e[m'
echo " "
##############################################################################################################
PriList=`cat $Mysql_Pri_Content|sed ':a;N;$!ba;s/\n/,/g'`
username=`cat $Username_PWD|head -1`
password=`cat $Username_PWD|tail -1`
dbname=""
ipaddress=""
inputCount=1
Input_DB_Select_Count=`cat $Input_DB_Select_List|wc -l`
Input_DB_Select_Count=$(( $Input_DB_Select_Count + 1 ))
while (( $inputCount < $Input_DB_Select_Count ))
do
line=`cat $Input_DB_Select_List|sed -n ''$inputCount'p'`
dbname=`cat $DB_INFO_FILE|sed -n ''$line'p'|cut -d : -f 3`
dbBusinessName=`cat $DB_INFO_FILE|sed -n ''$line'p'|cut -d : -f 4`
dbIP=`cat $DB_INFO_FILE|sed -n ''$line'p'|cut -d : -f 1`
dbPort=`cat $DB_INFO_FILE|sed -n ''$line'p'|cut -d : -f 2`
ipaddress=`cat $Input_IP_Address_List|sed -n ''$line'p'|cut -d : -f 1`
subSQLDir=$SQLDir/$dbBusinessName
if [ ! -d $subSQLDir ]; then
mkdir -p $subSQLDir
fi
dbinfo="DBINFO : "`cat $DB_INFO_FILE|sed -n ''$line'p'`
echo -e '\e[32m'$dbinfo'\e[m'
cat $Input_IP_Address_List|while read line
do
echo "grant "$PriList" " on " "$dbname".* " to " '"$username"'@'"$line"' " identified by "'"$password"';" >> $subSQLDir/$execTime
done
echo "flush privileges;" >> $subSQLDir/$execTime
cat $subSQLDir/$execTime
echo -e '\e[32mPlease confirm exec sql. [Y/N] : \e[m'
read
if [ "$REPLY" == "Y" ]; then
echo "mysql -ubluewhale -pbluewhale001 -h$dbIP -P$dbPort < $subSQLDir/$execTime"
mysql -ubluewhale -pbluewhale001 -h$dbIP -P$dbPort < $subSQLDir/$execTime
echo "#"$execTime >> $subSQLDir/"Summaries_"$dbBusinessName.txt
cat $subSQLDir/$execTime >> $subSQLDir/"Summaries_"$dbBusinessName.txt
echo " " >> $subSQLDir/"Summaries_"$dbBusinessName.txt
else
echo -e '\e[31mThe current db authorization has been ignored !!!\e[m'
fi
inputCount=$(($inputCount + 1))
done
##############################################################################################################
