#!/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 via:
- RSS feed or Weekly email newsletter
- 6 comments... add one ↓
Related Shell Scripts:
| Category | List of Unix and Linux commands |
|---|---|
| Disk space analyzers | ncdu • pydf |
| File Management | cat |
| Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
| Network Utilities | NetHogs • 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 | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
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
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 .
thinks it’s simple and very nice lol
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
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
Thanks Vivek :)
very helpful and I learned lot of new thing by reading the code :D