In this tutorial, I will show how to write a shell script to create MySQL user. The script will also grant relevant privileges for the user for the specified database.
Shell script to add mysql user
#!/bin/bash #Ask user to enter database name and save input to dbname variable read -p "Please Enter Database Name:" dbname #checking if database exist mysql -Bse "USE $dbname" 2> /dev/null #if database exist: if [ $? -eq 0 ]; then #ask user about username read -p "Please enter the username you wish to create : " username #ask user about allowed hostname read -p "Please Enter Host To Allow Access Eg: %,ip or hostname : " host #ask user about password read -p "Please Enter the Password for New User ($username) : " password #mysql query that will create new user, grant privileges on database with entered password query="GRANT ALL PRIVILEGES ON $dbname.* TO $username@'$host' IDENTIFIED BY '$password'"; #ask user to confirm all entered data read -p "Executing Query : $query , Please Confirm (y/n) : " confirm #if user confims then if [ "$confirm" == 'y' ]; then #run query mysql -e "$query" #update privileges, without this new user will be created but not active mysql -e "flush privileges" else #if user didn't confirm entered data read -p "Aborted, Press any key to continue.." #just exit fi else #If database not exit – warn user and exit echo "The Database: $dbname does not exist, please specify a database that exists"; fi
Running shell script
Server1:/# ./mysql_create_user.sh Please Enter Database Name:test Please enter the username you wish to create : test Please Enter Host To Allow Access Eg: %,ip or hostname : localhost Please Enter the Password for New User (test) : 12321q Executing Query : GRANT ALL PRIVILEGES ON test.* TO test@'localhost' IDENTIFIED BY '12321q' , Please Confirm (y/n) : y
Testing mysql user
Server1:/# mysql -u test -p12321q Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 54 Server version: 5.1.66-0+squeeze1 (Debian) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> Bye