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

by on January 9, 2010 · 6 comments

  1. #!/bin/bash
  2. # A shell script to add mysql database, username and password. 
  3. # It can also grant remote access on fly while creating the database.
  4. # -------------------------------------------------------------------------
  5. # Copyright (c) 2007 nixCraft project <http://cyberciti.biz/fb/>
  6. # This script is licensed under GNU GPL version 2.0 or above
  7. # -------------------------------------------------------------------------
  8. # This script is part of nixCraft shell script collection (NSSC)
  9. # Visit http://bash.cyberciti.biz/ for more information.
  10. # -------------------------------------------------------------------------
  11. # Last updated on Jan/09/2010
  12. # -------------------------------------------------------------------------
  13. _db="$1"
  14. _user="$2"
  15. _pass="$3"
  16. _dbremotehost="$4"
  17. _dbrights="$5"
  18.  
  19. ## Path to mysql bins ##
  20. mysql="/usr/bin/mysql"
  21. ## Mysql root settings ##
  22. _madminuser='root'
  23. _madminpwd='MySQL-PassWord'
  24. _mhost='localhost'
  25.  
  26. # make sure we get at least 3 args, else die
  27. [[ $# -le 2 ]] && { echo "Usage: $0 'DB_Name' 'DB_USER' 'DB_PASSORD' ['remote1|remote2|remoteN'] ['DB_RIGHTS']"; exit 1; }
  28.  
  29. # fallback to ALL rights 
  30. [[ -z "${_dbrights}" ]] && _dbrights="ALL"
  31.  
  32. # build mysql queries 
  33. _uamq="${mysql} -u "${_madminuser}" -h "${_mhost}" -p'${_madminpwd}' -e 'CREATE DATABASE ${_db};'"
  34. _upermq1="${mysql} -u "${_madminuser}" -h "${_mhost}" -p'${_madminpwd}' -e \"GRANT ${_dbrights} ON ${_db}.* TO ${_user}@localhost IDENTIFIED BY '${_pass}';\""
  35.  
  36.  
  37. # run mysql queries
  38. $_uamq
  39. $_upermq1
  40.  
  41.  
  42. # read remote host ip in a bash loop
  43. # build queires to grant permission to all remote webserver or hosts via ip using the same username
  44. IFS='|'
  45. for  i in ${_dbremotehost}
  46. do
  47. 	_upermq2="${mysql} -u "${_madminuser}" -h "${_mhost}" -p'${_madminpwd}' -e \"GRANT ${_dbrights} ON ${_db}.* TO ${_user}@${i} IDENTIFIED BY '${_pass}';\""
  48. 	$_upermq2
  49. 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'



4000+ howtos and counting! If you enjoyed this article, join 45000+ others and get free email updates!

Click here to subscribe via email.

  • Vamsi

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

  • Cletus

    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

  • oussama larhmich

    thinks it’s simple and very nice lol

  • vam

    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 .

  • vaLar

    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

  • mysillygirl

    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

Previous Script:

Next Script: