mysql 更改用户信息_修改MySQL用户信息及权限脚本

#!/bin/bash

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

# 名字： mysql-tool.sh# 版本： v2.3# 作者： ysh

# 功能： MySQL 数据库管理工具

# 创建日期：2019-09-23# 备注： 此脚本为合生科技 MySQL 数据管理使用

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

export MYSQL_PWD=$(dc -e 27992624244640545969914199055074927928074P)

shell_dir=$(cd "$(dirname"$0")";pwd)

bin_dir="/srv/mysql3306/bin/mysql"user="root"port="3306"host="192.168.%.%"db_conn="${bin_dir} -u${user} -P${port}"log(){

local text;local logtype;localtime

time=$(date +'%F %H:%M:%S')

logfile=${shell_dir}/mysql-tool.log

logtype=$1text=$2

case $logtype inerror)

#echo -e "\033[31m`date +'%F %H:%M:%S'`\t${logtype}\t${text}\033[0m" | tee -a ${logfile};;echo -e "\033[31m${text}\033[0m"

echo -e "\033[31m${time}\t${logtype}\t${text}\033[0m" >>${logfile};;info)

#echo -e "\033[32m`date +'%F %H:%M:%S'`\t${logtype}\t${text}\033[0m" | tee -a ${logfile};;echo -e "\033[32m${text}\033[0m"

echo -e "\033[32m${time}\t${logtype}\t${text}\033[0m" >>${logfile};;

warn)

#echo -e "\033[33m`date +'%F %H:%M:%S'`\t${logtype}\t${text}\033[0m" | tee -a ${logfile};;echo -e "\033[33m${text}\033[0m"

echo -e "\033[33m${time}\t${logtype}\t${text}\033[0m" >>${logfile};;esac}

flush(){

${db_conn}-e "flush privileges;"}

auth_user_presence(){

local select_user="select user from mysql.user;"name=$(${db_conn} -e "${select_user}" | grep -o -E -w "$1")if [ $? -eq 0 ];thenreturn0

elsereturn1

fi}

auth_database(){

local sel_data="show databases;"data_name=$(${db_conn} -e "${sel_data}" | grep -o -E -w "$1")if [ $? -eq 0 ];thenreturn0

elsereturn1

fi}

auth_database_no(){

local i=0

while (( i < 3))dolet"i += 1";

read-p "请输入数据库名称( $i or 3 ):"databaseif [ -z "$database"]thenlog warn"输入数据库名称不能为NULL,请重新输入"continuefiauth_database ${database};if [ $? -eq 1 ];thenlog warn"数据库 $database 不存在,请重新输入"continuefilet"i -= 1";

breakdone

if [ "$i" -ge 3 ];thenlog warn"您已输入 $i 次,验证失败,返回菜单"continuefi}

user_info(){

loginfo "查询数据库所有用户的信息"local select_users="select user,host from mysql.user where host = '${host}';"${db_conn}-e "${select_users}"}

auth_user_no(){

local i=0

while (( i < 3))dolet"i += 1";

read-p "请输入用户名( $i or 3 ):"usernameif [ -z "$username"]thenlog warn"您输入的用户名不能为NULL,请重新输入"continuefiauth_user_presence ${username};if [ $? -eq 1 ];thenlog warn"您输入的用户名不存在,请重新输入"continuefilet"i -= 1";

breakdone

if [ "$i" -ge 3 ];thenlog warn"您已输入 $i 次,验证失败,返回菜单"continuefi}

create_user(){

loginfo "正在创建用户信息"local i=0

while (( i < 3))dolet"i += 1";

read-p "请输入用户名( $i or 3 ):"usernameif [ -z "$username"]thenlog warn"您输入的用户名不能为NULL,请重新输入"continuefiauth_user_presence ${username};if [ $? -eq 0 ];thenlog warn"您输入的用户名不存在,请重新输入"continuefilet"i -= 1";

breakdone

if [ "$i" -ge 3 ];thenlog warn"您已输入 $i 次,验证失败,返回菜单"continuefilocal a=0

while (( a < 3))doread-p "请输入密码( $a or 3 ):"password

read-p "请再次输入密码( $a or 3 ):"retypepasswordif [ -z "$password" ]; thenlog warn"您输入的密码不能为NULL,请重新输入"continuefi

if [ "x$password" != "x$retypepassword" ]; thenlog warn"两次输入的密码不相同,请重新输入"continuefilet"a -= 1";

breakdone

if [ "$a" -ge 3 ];thenlog warn"您已输入 $a 次,验证失败,返回菜单"continuefilocal createuser="create user '${username}'@'${host}' identified by '${password}';"${db_conn}-e "${createuser}"flush;

loginfo "添加用户成功,用户名：${username} 密码：${password}"}

pwd_update(){

loginfo "正在修改用户密码"auth_user_no;

local a=0

while (( a < 3))doread-p "请输入新的密码( $a or 3 ):"password

read-p "请重新输入密码( $a or 3 ):"retypepasswordif [ -z "$password" ]; thenlog warn"您输入的密码不能为NULL,请重新输入"continuefi

if [ "x$password" != "x$retypepassword" ]; thenlog warn"两次输入的密码不相同,请重新输入"continuefilet"a -= 1";

breakdone

if [ "$a" -ge 3 ];thenlog warn"您已输入 $a 次,验证失败,返回菜单"continuefilocal update_pwd="update mysql.user set authentication_string=password('${password}') where user='${username}';"${db_conn}-e "${update_pwd}"flush;

loginfo "用户密码修改成功,用户名：${username} 新密码：${password}"}

drop_user(){

loginfo "正在删除用户信息"auth_user_no;

local user_drop="drop user '${username}'@'${host}';"${db_conn}-e "${user_drop}"flush

loginfo "删除用户 ${username} 成功"}

re_enter(){

log warn"对不起,不识别您输入的序号 $1 ,请重新输入"}

add_grant(){

loginfo "正在增加用户权限信息"auth_user_no;

auth_database_no;

read-p "请输入增加的权限(用逗号隔开,如:select,insert,update,delete,create,alter,drop):"grants

local add_user_grant="grant ${grants} on ${database}.* to '${username}'@'${host}';"${db_conn}-e "${add_user_grant}"flush

}

sel_grant(){

loginfo "正在查询用户权限信息"auth_user_no;

local sel_user_grant="show grants for '${username}'@'${host}';"${db_conn}-e "${sel_user_grant}"}

remove_grant(){

loginfo "正在移除用户权限信息"auth_user_no;

auth_database_no;

read-p "请输入移除的权限(用逗号隔开,如:select,insert,update,delete,create,alter,drop):"grants

local rev_user_grant="revoke ${grants} on ${database}.* from '${username}'@'${host}';"${db_conn}-e "${rev_user_grant}"flush

}

database_info(){

local show_database="show databases;"${db_conn}-e "${show_database}"}

usage(){case $choice in

1)while:do

echo -e "\033[33m请选择操作项序号：\033[0m"

echo -e "\033[36m 1 添加用户 \033[0m"

echo -e "\033[36m 2 修改用户密码 \033[0m"

echo -e "\033[36m 3 删除用户 \033[0m"

echo -e "\033[36m 4 查询所有用户信息 \033[0m"

echo -e "\033[36m 0 返回主菜单 \033[0m"

echo -e "\033[36m 9 退出脚本 \033[0m"

echo ""read-p "请输入:"YES_OR_NOif [ "$YES_OR_NO" = "1" ]; thencreate_user;

continueelif [ "$YES_OR_NO" = "2" ]; thenpwd_update;

continueelif [ "$YES_OR_NO" = "3" ]; thendrop_user;

continueelif [ "$YES_OR_NO" = "4" ]; thenuser_info;

continueelif [ "$YES_OR_NO" = "0" ]; thenloginfo "返回主菜单"

echo " "breakelif [ "$YES_OR_NO" = "9" ]; thenloginfo "*******即将退出本程序*******"exit0

elsere_enter $YES_OR_NO;fi

done;;2)while:do

echo -e "\033[33m请选择操作项序号：\033[0m"

echo -e "\033[36m 1 查询用户权限 \033[0m"

echo -e "\033[36m 2 增加用户权限 \033[0m"

echo -e "\033[36m 3 移除用户权限 \033[0m"

echo -e "\033[36m 4 数据库信息 \033[0m"

echo -e "\033[36m 0 返回主菜单 \033[0m"

echo -e "\033[36m 9 退出脚本 \033[0m"

echo ""read-p "请输入:"YES_OR_NOif [ "$YES_OR_NO" = "1" ]; thensel_grant;

continueelif [ "$YES_OR_NO" = "2" ]; thenadd_grant;

continueelif [ "$YES_OR_NO" = "3" ]; thenremove_grant;

continueelif [ "$YES_OR_NO" = "4" ]; thendatabase_info;

continueelif [ "$YES_OR_NO" = "0" ]; thenloginfo "*******返回主菜单*******"

echo " "breakelif [ "$YES_OR_NO" = "9" ]; thenloginfo "*******即将退出本程序*******"exit0

elsere_enter $YES_OR_NO;fi

done;;0)

loginfo "*******即将退出本程序*******"exit0;;*)echo -e "\033[33m对不起,不能识别您输入的序号 ${choice} ,请重新输入.\033[0m";;esac}while:do

cat<

添加用户,修改用户密码,删除用户,查询所有用户信息

按2权限管理:

查询用户权限,增加用户权限,移除用户权限,数据库信息

按0退出脚本******************************************************************************************EOF

read-p "请输入你的选择:"choice

usagedone