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 <http://cyberciti.biz/fb/>
# 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'

Get the latest tutorials on SysAdmin, Linux/Unix, Open Source, and DevOps topics:
CategoryList of Unix and Linux commands
File Management cat
Firewall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilities dig host ip nmap
OpenVPN CentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Manager apk apt
Processes Management bg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searching grep whereis which
User Information groups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPN CentOS 8 Debian 10 Firewall Ubuntu 20.04
6 comments… add one
  • mysillygirl Aug 6, 2012 @ 3:05

    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 Oct 17, 2010 @ 11:16

    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 Sep 6, 2010 @ 10:47

    thinks it’s simple and very nice lol

  • Cletus May 18, 2010 @ 17:34

    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 Oct 27, 2010 @ 17:34

      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 Jan 24, 2010 @ 15:58

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

Leave a Reply

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

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.