Shell +Cygwinterminal+WinMySQL 传参数授权

前言:新公司因为部分业务原因有好几百组win机器装MySQL授权登录比较麻烦，简单的写了一个shell传值自动授权的脚本，保存复用。

#!/bin/bash
#author liding@zlhy.com

function helpfunc(){
         echo -e "\033[36m  Please check your parameters, -D for db name, -M main library IP, -I for authorized IP address
           options:
           -D   Need to authorize the db name      
           -M   Execute on this IP 
           -I   Authorized IP
           -T   Authorization type 1 is the program account, 2 read-only account, 3 administrator account ; you can specify multiple to ',' separation
           eg : mysqlha_winreplication.sh -D  -M -I -T
           Generate 3 accounts:
                  1st: program link account. permissions: SELECT, INSERT, UPDATE, DELETE, EXECUTE, no DROP, CREATE;
                  2nd: read-only account. permissions: Select
                  3rd: administrator account. permissions: ALL \033[0m"

}
############################################
type=2

if [ $# -lt 1 ] ;
    then
        helpfunc
         exit 1
else

       while getopts "M:D:I:T:" Option
       do
               case $Option in
                       D) db=$OPTARG;;
                       M) master_ip=$OPTARG;;
                       I) grant_ip=$OPTARG;;
                       T) type=$OPTARG;;
                        *) helpfunc; exit 1; ;;
               esac
       done

fi

type=$(echo $type |tr ',' ' ')
function GetPwd(){
status=$1
if [ $status -eq 0 ];then
        A=`head -c 500 /dev/urandom | tr -dc A-Z |head -c 1`
        B=`head -c 500 /dev/urandom | tr -dc [:alnum:]| head -c 6`
        C=`echo $RANDOM$RANDOM|cut -c 2`
        echo $A$B$C
else
        A=`head -c 500 /dev/urandom | tr -dc A-Z |head -c 1`
        B=`head -c 500 /dev/urandom | tr -dc [:alnum:]| head -c 12`
        C=`echo $RANDOM$RANDOM|cut -c 2`
        echo $A$B$C
fi
}

        #       echo -e "\033[42;37m     \033[0m"
function GetProgram(){
pro_pwd=$(GetPwd 0)
#ssh administrator@${master_ip}  "cd /cygdrive/d/data/mysqld_master && cmd /c sql.bat \"$sql\"
##读写
pro_sql="GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON ${db}.* TO ${db}@'"localhost"' IDENTIFIED BY '"$pro_pwd"'; "
pro_sql1="GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON ${db}.* TO ${db}@'"${grant_ip}"' IDENTIFIED BY '"$pro_pwd"'; "
for sql in "$pro_sql" "$pro_sql1"
do
        if ssh administrator@${master_ip}  "cd /cygdrive/d/data/mysqld_master && cmd /c sql.bat \"$sql\" ";then
                :
        else
                echo -e "\033[31m  To grant authorization{IP:${master_ip} 权限:读写 } faild \033[0m"
        fi
done
echo -e "\033[32m   To grant authorization{IP:${master_ip} 权限:读写 } sucess   \033[0m"
echo -e "\033[32m   读写账户:${db}                                                      \033[0m"
echo -e "\033[32m   读写密码:${pro_pwd}                                                 \033[0m"
}


function GetSelect(){
select_pwd=$(GetPwd 1)
##只读
select_sql="GRANT SELECT ON ${db}.* TO ${db}_r@'"localhost"' IDENTIFIED BY '"$select_pwd"';"
select_sql1="GRANT SELECT ON ${db}.* TO ${db}_r@'"${grant_ip}"' IDENTIFIED BY '"$select_pwd"';"
for sql in "$select_sql" "$select_sql1"
do
        if ssh administrator@${master_ip}  "cd /cygdrive/d/data/mysqld_master && cmd /c sql.bat \"$sql\" ";then
                :
        else
                echo -e "\033[31m  To grant authorization{IP:${master_ip} 权限:只读 } faild \033[0m"
        fi
        else
                echo -e "\033[31m  To grant authorization{IP:${master_ip} 权限:只读 } faild \033[0m"
        fi
done
echo -e "\033[32m   To grant authorization{IP:${master_ip} 权限:只读 } sucess   \033[0m"
echo -e "\033[32m   只读账户:${db}_r                                            \033[0m"
echo -e "\033[32m   只读密码:${select_pwd}                                              \033[0m"

}


function GetAdmin(){
admin_pwd=$(GetPwd 2)
#管理员
admin_sql="grant select,insert,update,delete,create,drop,alter,index,lock tables ,CREATE TEMPORARY TABLES  ON ${db}.* TO ${db}_admin@'"localhost"' IDENTIFIED BY  '"$admin_pwd"';"
admin_sql="grant select,insert,update,delete,create,drop,alter,index,lock tables ,CREATE TEMPORARY TABLES  ON ${db}.* TO ${db}_admin@'"127.0.0.1"' IDENTIFIED BY  '"$admin_pwd"';"
for sql in "$admin_sql" "$admin_sql1"
do
        if ssh administrator@${master_ip}  "cd /cygdrive/d/data/mysqld_master && cmd /c sql.bat \"$sql\" ";then
                :
        else
                echo -e "\033[31m  To grant authorization{IP:${master_ip} 权限:管理员 } faild \033[0m"
        fi
done
echo -e "\033[32m   To grant authorization{IP:localhost 权限:管理员 } sucess   \033[0m"
echo -e "\033[32m   管理员账户:${db}_admin                                              \033[0m"
echo -e "\033[32m   管理员密码:${admin_pwd}                                             \033[0m"
}

function main(){
for stu in $type
do
        if [ $stu -eq 1 ];then
                GetProgram
        elif [ $stu -eq 2 ];then
                GetSelect
        elif [ $stu -eq 3 ];then
                GetAdmin
        else
                echo -e "\033[31m  Please check if your type parameter is in range \033[0m"
        fi
done
}
main

调用win sql.bat

@echo off

set allparam=%1

D:\data\mysqld_master\bin\mysql.exe -uroot -e %allparam%

or +port

@echo off

set allparam=%2
set port=%1

echo %1
echo %2
D:\data\mysqld_slave\bin\mysql.exe -uroot -P%port% -e %allparam%

eg : mysqlha_winreplication.sh -D  -M -I -T