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
Hey, this is a handy start point for a script I am writing, so thanks.
One thing though, would it be better to use read -s (instead of -p) for the password prompt, so the entered password isn't shown on screen?
Tom
Tom that is a good suggestion. Thanks.