≡ Menu

Shell Script To Add MySQL Database, Username and Password Including Remote Host Access

#!/bin/bash
# A shell script to add mysql database, username and password.
# It can also grant remote access on fly while creating the database.
# -------------------------------------------------------------------------
# Copyright (c) 2007 nixCraft project 
# This script is licensed under GNU GPL version 2.0 or above
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------
# Last updated on Jan/09/2010
# -------------------------------------------------------------------------
_db="$1"
_user="$2"
_pass="$3"
_dbremotehost="$4"
_dbrights="$5"
## Path to mysql bins ##
mysql="/usr/bin/mysql"
## Mysql root settings ##
_madminuser='root'
_madminpwd='MySQL-PassWord'
_mhost='localhost'
# make sure we get at least 3 args, else die
[[ $# -le 2 ]] && { echo "Usage: $0 'DB_Name' 'DB_USER' 'DB_PASSORD' ['remote1|remote2|remoteN'] ['DB_RIGHTS']"; exit 1; }
# fallback to ALL rights
[[ -z "${_dbrights}" ]] && _dbrights="ALL"
# build mysql queries
_uamq="${mysql} -u "${_madminuser}" -h "${_mhost}" -p'${_madminpwd}' -e 'CREATE DATABASE ${_db};'"
_upermq1="${mysql} -u "${_madminuser}" -h "${_mhost}" -p'${_madminpwd}' -e \"GRANT ${_dbrights} ON ${_db}.* TO ${_user}@localhost IDENTIFIED BY '${_pass}';\""
# run mysql queries
$_uamq
$_upermq1
# read remote host ip in a bash loop
# build queires to grant permission to all remote webserver or hosts via ip using the same username
IFS='|'
for  i in ${_dbremotehost}
do
	_upermq2="${mysql} -u "${_madminuser}" -h "${_mhost}" -p'${_madminpwd}' -e \"GRANT ${_dbrights} ON ${_db}.* TO ${_user}@${i} IDENTIFIED BY '${_pass}';\""
	$_upermq2
done

How Do I Use This Script?

Add a database called bar with username tom and password jerry, enter:
./script.sh bar tom jerry
Add a database called bar with username tom, password jerry and allow remote access from 192.168.1.5 and 192.168.1.11, enter:
./script.sh bar tom jerry '192.168.1.5|192.168.1.11'
Add a database called bar with username tom, password jerry, allow remote access from 192.168.1.5 & 192.168.1.11, and only grant SELECT,INSERT,UPDATE,DELETE, enter:
./script.sh bar tom jerry '192.168.1.5|192.168.1.11' 'SELECT,INSERT,UPDATE,DELETE'

{ 6 comments… add one }
  • mysillygirl August 6, 2012, 3:05 am

    I ./add_database.sh: line 47: /usr/local/mysql/bin/mysql -u root -h localhost -p’wbzd’ -e “GRANT select,insert ON lijun.* TO lijun@192.168.1.31 IDENTIFIED BY ‘lijun’;”: No such file or directory

    I replacing /usr/bin/mysql with /usr/local/mysql/bin/mysql,but the same
    I used: /usr/local/mysql/bin/mysql -h localhost -u root -p’wbzd’ it’s ok

  • vam October 17, 2010, 11:16 am

    Your mysql path is wrong, try replacing /usr/bin/mysql with /usr/local/bin/mysql or find the propper path of mysql cmd.

    Nice script .

  • oussama larhmich September 6, 2010, 10:47 am

    thinks it’s simple and very nice lol

  • Cletus May 18, 2010, 5:34 pm

    Hello I tried this script but got the below password. Pls how can i correct this. I have been trying to use to create databases, username and password. Thank you

    ********Error*****
    ./mysql_script_user.sh: line 46: /usr/bin/mysql -u root -h localhost -p’ctip2010′ -e “GRANT ALL ON bar.* TO tom@–debug IDENTIFIED BY ‘jerry’;”: No such file or directory

    • vaLar October 27, 2010, 5:34 pm

      Probably, your mysql binary isn’t in /usr/bin/ try to find it:
      find / -name mysql
      if you have a different location, change the value in the script:
      ## Path to mysql bins ##
      mysql=”/usr/bin/mysql”

      Greetings

  • Vamsi January 24, 2010, 3:58 pm

    Thanks Vivek :)
    very helpful and I learned lot of new thing by reading the code :D

Security: Are you a robot or human?

Leave a Comment