How to Create Mysql User in Interactive Way using Script

This shell script helps user to create a user in mysql database, when someone doesn't know about mysql, but need quickly add new user. The user should have passwordless access to mysql to run this script

Add Mysql user via script

#!/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 [email protected]'$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

RESULT

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 [email protected]'localhost' IDENTIFIED BY '12321q' , Please Confirm (y/n) : y

Testing

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

 

Bobbin Zachariah 9:49 am

About Bobbin Zachariah

Founder of LinOxide, passionate lover of Linux and technology writer. Started his career in Linux / Opensource from 2000. Love traveling, blogging and listening music. Reach Bobbin Zachariah about me page and google plus page.

Author Archive Page

Have anything to say?

Your email address will not be published. Required fields are marked *

All comments are subject to moderation.

2 Comments

  1. 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