Solved

Bash Shell Script to Create mySQL Database

Posted on 2010-11-16
3
1,167 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now