#!/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