Solved

Bash Shell Script to Create mySQL Database

Posted on 2010-11-16
3
1,180 Views
Last Modified: 2012-05-10
I'm using a domain hosting server that runs Red Hat Linux.

BACKGROUND:
I'm attempting to automate the creation of new addon domains that require a mySQL database and user with password.

QUESTION:
Can you help me write a bash shell script that will ...
1) Create a database; the name of the database needs to be generated using the domain name as a seed word, but it must have minimal encoding as well. Something like this:  mydomain > MydOM@1n
2) Create a user with password and permissions, and assign it to that database. I will need some sort of random password generator also, that will provide passwords for the user.
3) If you think Perl has better resources for this, please let me know.

0
Comment
Question by:WizeOwl
3 Comments
 
LVL 6

Expert Comment

by:Codebot
ID: 34147860
db="create database xxx;GRANT ALL PRIVILEGES ON xxx.* TO user@localhost IDENTIFIED BY 'password';FLUSH PRIVILEGES;"
mysql -u root -p$mysql_pass -e "$db
0
 

Author Comment

by:WizeOwl
ID: 34149514
1) Thank you for your attempt, but please write the code so that is is ready to run as-is. Codebot, above, appears to be pseudo-code, and does not fulfill the request as specified in my original question.

2) Something I will add, it has been suggested that granting fewer privileges to the db user is more secure. Therefore, I will need to know the code to select only these:

Select
Insert
Update
Delete
Create
Alter
0
 
LVL 3

Accepted Solution

by:
Vryali earned 500 total points
ID: 34213813
This will do most of what you're wanting, I believe.

#!/bin/bash
# This must be called with four parameters, as follows:
# createDB.sh user password encodedDomain
# $0                  $1    $2             $3         
# Note that to do this in a script you need to have your root mysql password saved
# within the script...  So make sure that this script has the appropriate permissions
# for security reasons.
# Note also that I'm not doing a load of error checking, I expect you to supply the proper
# arguments in the proper format...

if [ $# -ne 3 ]
then
    echo "Usage: USERNAME PASSWORD ENCODED_DOMAIN"
    exit 1
fi

# Proper number of arguments are there, carry on.
USER=$1
PASS=$2
ENCDBNAME=$3

# These you need to set for initial access to the DB.
DBUSER=root
DBPASS=p@$$w0rd

# and then here's the basic command for the DB footwork.
mysql -u ${DBUSER} -p${DBPASS} -e"create database ${ENCDBNAME}; create user '${USER}'@'localhost' identified by '${PASS}'; grant select,insert,update,delete,create,alter on ${ENCDBNAME}.* to '${USER}'@'localhost';"

Open in new window


That's not really clean code, but it should definitely work.  I'm not sure the value in having a random generator for passwords, but if you really wanted to add that:

 #!/bin/bash

MAXSIZE=8          

array1=(
w e r t y u p a s d f h j k z x c v b m Q W E R T Y U P A D
F H J K L Z X C V B N M 2 3 4 7 8 1 5 6 9
)                      

MODNUM=${#array1[*]}

pwd_len=0

while [ $pwd_len -lt $MAXSIZE ]
do
  index=$(($RANDOM%$MODNUM))
  password="${password}${array1[$index]}"
  ((pwd_len++))
done
echo $password

Open in new window


Instead of passing a parameter for password you'd just use that; up to you though.  The above snippet for a random password was taken from: http://hackattacking.com/?p=401
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question