1.項目背景
因監控需要，我們需要在既有的每個MySQL實例上創建一個賬號。公司有數百臺 MySQL 實例，如果手動登入來創建賬號很麻煩，也不現實。所以，我們寫了一個簡單的shell腳本，用來創建批量服務器的mysql 賬號。
2.執行腳本內容;
#!/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" fidone3. 執行舉例
Step 1 將代碼放置到執行文件中，可執行文件命名為 mysql_CreateUIDMulti.sh
Step 2 請對此文件授予可執行權限，否則，提示以下錯誤。
Step 3 執行的具體命令（參數格式），例如 在 177.177.XXX.128,177.177.XXX.144 兩個 3306的實例上創建賬號
./mysql_CreateUIDMulti.sh 177.177.XXX.128,177.177.XXX.144 3306
Step 4 打印的執行結果如下
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!
4.補充說明
如果創建一個服務器上的MySQL賬號，可按照以下格式
./mysql_CreateUIDMulti.sh 177.177.XXX.128 3306
打印的Log 如下
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
總結
以上所述是小編給大家介紹的通過Shell腳本批量創建服務器上的MySQL數據庫賬號 ,希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對武林網網站的支持！
如果你覺得本文對你有幫助，歡迎轉載，煩請注明出處，謝謝！
新聞熱點
疑難解答
