?
Solved

Bash Shell Script to Create mySQL Database

Posted on 2010-11-16
3
Medium Priority
?
1,209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 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