Shell script - Create MySQL database and user
Presented below is an illustrative shell script for managing MySQL databases. This script facilitates the creation of databases and the establishment of users with customized access permissions.
Please feel free to utilize this script wherever it may be required.
Example
1#!/bin/bash
2# VARIABLES
3#
4_bold=$(tput bold)
5_underline=$(tput sgr 0 1)
6_reset=$(tput sgr0)
7
8_purple=$(tput setaf 171)
9_red=$(tput setaf 1)
10_green=$(tput setaf 76)
11_tan=$(tput setaf 3)
12_blue=$(tput setaf 38)
13
14#
15# HEADERS & LOGGING
16#
17function _debug()
18{
19 [ "$DEBUG" -eq 1 ] && $@
20}
21
22function _header()
23{
24 printf "\n${_bold}${_purple}========== %s ==========${_reset}\n" "$@"
25}
26
27function _arrow()
28{
29 printf "➜ $@\n"
30}
31
32function _success()
33{
34 printf "${_green}✔ %s${_reset}\n" "$@"
35}
36
37function _error() {
38 printf "${_red}✖ %s${_reset}\n" "$@"
39}
40
41function _warning()
42{
43 printf "${_tan}➜ %s${_reset}\n" "$@"
44}
45
46function _underline()
47{
48 printf "${_underline}${_bold}%s${_reset}\n" "$@"
49}
50
51function _bold()
52{
53 printf "${_bold}%s${_reset}\n" "$@"
54}
55
56function _note()
57{
58 printf "${_underline}${_bold}${_blue}Note:${_reset} ${_blue}%s${_reset}\n" "$@"
59}
60
61function _die()
62{
63 _error "$@"
64 exit 1
65}
66
67function _safeExit()
68{
69 exit 0
70}
71
72#
73# UTILITY HELPER
74#
75function _seekConfirmation()
76{
77 printf "\n${_bold}$@${_reset}"
78 read -p " (y/n) " -n 1
79 printf "\n"
80}
81
82# Test whether the result of an 'ask' is a confirmation
83function _isConfirmed()
84{
85 if [[ "$REPLY" =~ ^[Yy]$ ]]; then
86 return 0
87 fi
88 return 1
89}
90
91
92function _typeExists()
93{
94 if [ $(type -P $1) ]; then
95 return 0
96 fi
97 return 1
98}
99
100function _isOs()
101{
102 if [[ "${OSTYPE}" == $1* ]]; then
103 return 0
104 fi
105 return 1
106}
107
108function _checkRootUser()
109{
110 #if [ "$(id -u)" != "0" ]; then
111 if [ "$(whoami)" != 'root' ]; then
112 echo "You have no permission to run $0 as non-root user. Use sudo"
113 exit 1;
114 fi
115
116}
117
118function _printPoweredBy()
119{
120 cat <<"EOF"
121 _ _ __ __
122 / \ _ __ __ _ _ __ __| | \ \ / / _ __ _ ___
123 / _ \ | '_ \ / _` | '_ \ / _` | \ \ / / | | |/ _` / __|
124 / ___ \| | | | (_| | | | | (_| | \ V /| |_| | (_| \__ \
125/_/ \_\_| |_|\__,_|_| |_|\__,_| \_/ \__, |\__,_|___/
126 |___/
127EOF
128}
129################################################################################
130# SCRIPT FUNCTIONS
131################################################################################
132function generatePassword()
133{
134 echo "$(openssl rand -base64 12)"
135}
136
137function _printUsage()
138{
139 echo -n "$(basename $0) [OPTION]...
140
141Create MySQL db & user.
142Version $VERSION
143
144 Options:
145 -h, --host MySQL Host
146 -d, --database MySQL Database
147 -u, --user MySQL User
148 -p, --pass MySQL Password (If empty, auto-generated)
149 -h, --help Display this help and exit
150 -v, --version Output version information and exit
151
152 Examples:
153 $(basename $0) --help
154 $(basename $0) [--host="<host-name>"] --database="<db-name>" [--user="<db-user>"] [--pass="<user-password>"]
155
156"
157 _printPoweredBy
158 exit 1
159}
160
161function processArgs()
162{
163 # Parse Arguments
164 for arg in "$@"
165 do
166 case $arg in
167 -h=*|--host=*)
168 DB_HOST="${arg#*=}"
169 ;;
170 -d=*|--database=*)
171 DB_NAME="${arg#*=}"
172 ;;
173 -u=*|--user=*)
174 DB_USER="${arg#*=}"
175 ;;
176 -p=*|--pass=*)
177 DB_PASS="${arg#*=}"
178 ;;
179 --debug)
180 DEBUG=1
181 ;;
182 -h|--help)
183 _printUsage
184 ;;
185 *)
186 _printUsage
187 ;;
188 esac
189 done
190 [[ -z $DB_NAME ]] && _error "Database name cannot be empty." && exit 1
191 [[ $DB_USER ]] || DB_USER=$DB_NAME
192}
193
194function createMysqlDbUser()
195{
196 SQL1="CREATE DATABASE IF NOT EXISTS ${DB_NAME};"
197 SQL2="CREATE USER '${DB_USER}'@'%' IDENTIFIED BY '${DB_PASS}';"
198 SQL3="GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO '${DB_USER}'@'%';"
199 SQL4="FLUSH PRIVILEGES;"
200
201 if [[ -f "${HOME}/.my.cnf" ]]; then
202 "$BIN_MYSQL" -e "${SQL1}${SQL2}${SQL3}${SQL4}"
203 else
204 # If ~/.my.cnf doesn't exist then it'll ask for root password
205 _arrow "Please enter root user MySQL password!"
206 read rootPassword
207 "$BIN_MYSQL" -h $DB_HOST -u root -p${rootPassword} -e "${SQL1}${SQL2}${SQL3}${SQL4}"
208 fi
209}
210
211function printSuccessMessage()
212{
213 _success "MySQL DB / User creation completed!"
214
215 echo "################################################################"
216 echo ""
217 echo " >> Host : ${DB_HOST}"
218 echo " >> Database : ${DB_NAME}"
219 echo " >> User : ${DB_USER}"
220 echo " >> Pass : ${DB_PASS}"
221 echo ""
222 echo "################################################################"
223 _printPoweredBy
224
225}
226
227################################################################################
228# Main
229################################################################################
230export LC_CTYPE=C
231export LANG=C
232
233DEBUG=0 # 1|0
234_debug set -x
235VERSION="1.0.0"
236
237BIN_MYSQL=$(which mysql)
238
239DB_HOST='localhost'
240DB_NAME=
241DB_USER=
242DB_PASS=$(generatePassword)
243
244function main()
245{
246 [[ $# -lt 1 ]] && _printUsage
247 _success "Processing arguments..."
248 processArgs "$@"
249 _success "Done!"
250
251 _success "Creating MySQL db and user..."
252 createMysqlDbUser
253 _success "Done!"
254
255 printSuccessMessage
256
257 exit 0
258}
259
260main "$@"
261
262_debug set +x