Solved

Bash Shell Script to Create mySQL Database

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

The purpose of this article is to fix the unknown display problem in Linux Mint operating system. After installing the OS if you see Display monitor is not recognized then we can install "MESA" utilities to fix this problem or we can install additio…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…

695 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