Shell Script to Create Mysql User (Interactive Way)

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