shell连接mysql(通用版)

2019独角兽企业重金招聘Python工程师标准>>>

数据库信息如下：

cat /home/scripts/rds.txt
webWrite    192.168.100.100    goforit    gofoirtPass    root    rootPass

vim jinRds.sh
#!/bin/bash
#version:v3.0
#Date:2017-12-21
#作用:进入rds使用

###################################################################################################
#mysql命令路径
sqlCommand=/usr/bin/mysql

#数据库地址文本,需要将数据库地址信息记录到该文本中
mysqlFile=/home/scripts/rds.txt

#注:文本格式如下(六列)
#名字 地址 程序用户名 程序密码 最高权限用户名 最高权限密码

###################################################################################################

#程序如下
function check_file ()
{
	[ ! -e $mysqlFile -o ! -e $sqlCommand ] && echo -e "请检查\033[31mmysql命令路径和rds文本\033[0m" && exit 1
}

function processlist_of_sql ()
{
echo -e "\033[31m数据库连接数:\033[0m"
while read name db_addr webUser webPasswd supperUser supperPasswd
do
	echo -e "$name:\033[32m`$sqlCommand -h$db_addr -u$webUser -p$webPasswd -e 'show processlist;'|wc -l`\033[0m"
done<$mysqlFile
}

function login_sql ()
{
for i in `awk '{print $1}' $mysqlFile`
do
	[ "$1" == "$i" ] && webUser=`grep $i $mysqlFile|awk '{print $3}'` && webPasswd=`grep $i $mysqlFile|awk '{print $4}'` && $sqlCommand -h`grep $i $mysqlFile|awk '{print $2}'` -u$webUser -p$webPasswd -A
done
}

function kill_sql ()
{
for i in `awk '{print $1}' $mysqlFile`
do
	[ "$1" == "$i" ] && webUser=`grep $i $mysqlFile|awk '{print $3}'` && webPasswd=`grep $i $mysqlFile|awk '{print $4}'` && for j in `$sqlCommand -h$(grep $i $mysqlFile|awk '{print $2}') -u$webUser -p$webPasswd -e "show processlist;"|sed 1d|grep -i "$2"|cut -f 1`;do $sqlCommand -h$(grep $i $mysqlFile|awk '{print $2}') -u$webUser -p$webPasswd -e "kill $j;";done
done
}

function seconds_behind_master ()
{
echo -e "\033[31m数据库延迟:\033[0m"
while read name db_addr webUser webPasswd supperUser supperPasswd
do
	echo -e "$name:\033[32m`$sqlCommand -h$db_addr -u$supperUser -p$supperPasswd -e 'show slave status\G'|grep -i Seconds_Behind_Master|awk '{print $NF}'`\033[0m"
done<$mysqlFile
}

function how_to_use ()
{
i=`awk '{print $1}' $mysqlFile|tr "\n" "|" | sed 's@|$@@'`
	echo -e "\033[31mUsage:\n\t\033[32m$0 \033[31mdenglu\033[32m ${i}\033[0m"
	echo -e "\t\033[32m$0 \033[31msum|yanchi|help\033[0m"
	a=`awk '{print $1}' $mysqlFile|head -1`
	echo -e "\033[31meg:\n\t\033[32mbash $0 denglu DBNAME (\033[31m进入数据库 eg: bash $0 denglu $a)\n\t\033[32mbash $0 sum (\033[31m统计连接数)\n\t\033[32mbash $0 yanchi (\033[31m延迟s数)\n\t\033[32mbash $0 killsql DBNAME TYPE (\033[31mkill某个连接 eg: bash $0 killsql $a update)\033[0m"
}

check_file

case $1 in

sum)
	processlist_of_sql	
	;;
denglu)
	login_sql $2
	;;
killsql)
	kill_sql $2
	;;
yanchi)
	seconds_behind_master
	;;
h|help|*)
	how_to_use
	;;
esac

用法：

bash jinRds.sh help

转载于:https://my.oschina.net/2726276586/blog/1596696