通过Shell脚本批量创建服务器上的MySQL数据库账号

#!/bin/bash## 此段shell 脚本的主要功能是实现在多个SQL Server IP实例上，创建账号。输入参数是两个，第一个是数据库所在的IPs，即多个Server IP构成的字符串，IP间用逗号隔开。第二个参数是 端口（3306 或 3307）##MySQL程序所在路径--mysql bin 文件所在路径；如果由建立软连接，可直接是mysqlcommand_linebin="/data/mysql5720/bin/mysql"##用来连接MySQ的账号和密码username="DBA_MYSQLACC"password="DBAACCTEST109211706DF"## 新创建的账号和密码createuid="testuid"createpwd="testpwd"##指定新创建的用户在那个主机上可以登录，如果是本地用户可用localhost；如果指定规则的可以使用通配符%phost="177.177.%"mysqlserverIPs=$1echo $mysqlserverIPs## 按“,”分割，将字符串转换为数组。IParr=(${mysqlserverIPs//,/ })echo $IParrfor ((i=0;i<${#IParr[@]};i++))do  IP=${IParr[$i]}  echo "${IP}"select_sql="select * from user where user=/"$createuid/""msg=$(${command_linebin} -h ${IP} -P $2 -u$username -p$password -s mysql -e "${select_sql}")echo $msg##创建账号前，先检查需要创建的账号是否已经存在，如果已经存在了，则直接退出。if [[ $msg = "" ]] ;then  echo $(date -d today +"%Y%m%d%H%M%S") $mip "The Condition is OK,permit to create UID."else  echo $(date -d today +"%Y%m%d%H%M%S") $IP "The UID you want create has been exited, please check it! The Act Quit!"  exit fi## 以下几行代码是创建的关键${command_linebin} -h ${IP} -P $2 -u$username -p$password -s mysql <<EOFCREATE USER '$createuid'@'$phost' IDENTIFIED BY '$createpwd';GRANT Select,PROCESS ON *.* TO '$createuid'@'$phost';flush privileges;EOF##创建后，再次检查账号看否已将存在。如果不存在，则说明创建失败，直接退出。select_sql="select * from user where user=/"$createuid/""msg=$(${command_linebin} -h ${IP} -P $2 -u$username -p$password -s mysql -e "${select_sql}")echo $msgif [[ $msg = "" ]] ;then  echo $(date -d today +"%Y%m%d%H%M%S") ${IP} "The action to create UID Error,Please Check it! The Act Quit! "  exitelse  echo $(date -d today +"%Y%m%d%H%M%S") ${IP} "Congratulation! Create UID successful" fidone

177.177.XXX.128,177.177.XXX.144177.177.XXX.128177.177.XXX.128mysql: [Warning] Using a password on the command line interface can be insecure.20180529171802 The Condition is OK,permit to create UID.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.177.177.% testuid Y N N N N N N N Y N N N N N N N N N N N N N N N N N N N N 0 0 0 0 mysql_native_password *22CBF14EBDE8814586FF12332FA2B6023A7603BB N 2018-05-29 17:18:02 NULL N20180529171802 177.177.XXX.128 Congratulation! Create UID successful177.177.XXX.144mysql: [Warning] Using a password on the command line interface can be insecure.177.177.% testuid Y N N N N N N N Y N N N N N N N N N N N N N N N N N N N N 0 0 0 0 mysql_native_password *22CBF14EBDE8814586FF12332FA2B6023A7603BB N 2018-05-30 00:56:38 NULL N20180529171802 177.177.XXX.144 The UID you want create has been exited, please check it! The Act Quit!

177.177.XXX.128177.177.XXX.128177.177.XXX.128mysql: [Warning] Using a password on the command line interface can be insecure.20180529173517 The Condition is OK,permit to create UID.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.177.177.% testuid Y N N N N N N N Y N N N N N N N N N N N N N N N N N N N N 0 0 0 0 mysql_native_password *22CBF14EBDE8814586FF12332FA2B6023A7603BB N 2018-07-29 17:35:17 NULL N20180529173517 177.177.XXX.128 Congratulation! Create UID successful