Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm building a Bash script for some tasks. One of those tasks is create a MySQL DB from within the same bash script. What I'm doing right now is creating two vars: one for store user name and the other for store password. This is the relevant part of my script:

MYSQL_USER=root
MYSQL_PASS=mypass_goes_here

touch /tmp/$PROY.sql && echo "CREATE DATABASE $DB_NAME;" > /tmp/script.sql
mysql --user=$MYSQL_USER --password="$MYSQL_PASS" < /tmp/script.sql
rm -rf /tmp/script.sql

But always get a error saying access denied for user root with NO PASSWORD, what I'm doing wrong? I need to do the same for PostgreSQL.

share|improve this question
    
Do some debugging (-: Try echo "$MYSQL_PASS" before you pass it to the mysql line. Does it have the correct password? –  KM. Jun 15 '12 at 19:46
    
Can you login as usual without the script? –  qweet Jun 15 '12 at 19:55
3  
-h is missing. It is also not a good idea to put passwords on a command-line, since they will be visible for everyone calling ps wwaux. –  Nils Jun 15 '12 at 20:02
add comment

4 Answers 4

up vote 9 down vote accepted

Both for MySQL and PostgreSQL you can specify your user and password in local config file. .my.cnf for MySQL and .pgpass for PostgreSQL. These files should be in your home directory (i.e. ~/.my.cnf).

.my.cnf:

[mysql]
user=user
password=password

.pgpass:

host:port:database:user:password

You can have a wildcard entry here, substituting any field for *.

PS: Do. Not. Ever. Specify. You. Password. On. Commandline. This can be perfectly visible with ps if your system is not configured to not show processes that belongs to other users.

@thinice: If you want to create those files really secure you should do:

umask 077
touch .my.new.config
umask 022 # or whatever was your default

This way the file would be created with secure permissions from the start and no eavesdropper would have a chance leeching your password.

PostgreSQL will refuse to use the file with permissions higher the 0600 anyway.

share|improve this answer
1  
For mysql the mysql client you can point at a specific config file with --defaults-extra-file=filename. This might be useful if you want to place it somewhere non-standard, or just create a temporary file. Is suspect their is a similar option with PostgreSQL, but I am not familiar with that. –  Zoredache Jun 15 '12 at 20:09
    
And for the love of happy gilmore's shorts chmod that file 0600 –  thinice Jun 15 '12 at 20:12
    
@kworr I'll aready try that but my MySQL doesn't start changing this parameters at /etc/my.cnf :( didn't know the cause. I'll try again later –  ReynierPM Jun 15 '12 at 21:21
    
It's not the /etc/my.cnf. Updating answer. –  kworr Jun 16 '12 at 5:33
    
@kworr I try your solution but doesn't work at all. The DB is created by empty and doesn't appear in phpMyAdmin or any other GUI tool. I set the data directory in /etc/my.cnf to datadir=/data/var/lib/mysql and set permissions to 0755 at /data/var/lib/mysql and also owner to mysql:mysql, why the database is empty? where is the problem? –  ReynierPM Jun 18 '12 at 14:39
show 4 more comments
MYSQL_USER="root"
MYSQL_PASS="mypass_goes_here"

touch /tmp/$PROY.sql && echo "CREATE DATABASE $DB_NAME;" > /tmp/script.sql
mysql --user=$MYSQL_USER --password=$MYSQL_PASS < /tmp/script.sql
rm -rf /tmp/script.sql

be sure how you write your pass and it doesn't escape

--defaults-extra-file= is a good thing (tm) (c)

share|improve this answer
add comment

Don't put quotes around the password because if you do the quotes are considered to be part of the password.

share|improve this answer
add comment
MYSQL_USER="root"
MYSQL_PASSWORD="PASSWORD"
DBNAME="DB_NAME"

mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "CREATE DATABASE $DBNAME;" 2> /tmp/error1

STATUS=$? 
if [ $STATUS -eq 0 ];
then 
    echo -e "Database '$DBNAME' is created"
elif (grep -i "^ERROR 1007" /tmp/error1 > /dev/null);
then
    echo -e "Database '$DBNAME' already exists"
else
    echo -e "Failed to create database '$DBNAME'"
fi

rm -r /tmp/error1 

This will do the trick Thanks

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.