bash shell script, trouble creating mysql database plus user

Question

I have a shell script which has the following content:

#!/bin/sh
mysql -uqgk_user -ppw12345 -e "create database qgk; GRANT ALL PRIVILEGES ON qgk.* TO qgk_user@localhost IDENTIFIED BY 'pw12345' WITH GRANT OPTION;"

I receive this error if I run the shell script:

manager@xagate:/var/www/.../public_html/...$ sh database_setup.sh 
ERROR 1045 (28000): Access denied for user 'qgk_user'@'localhost' (using password: YES)

I don't understand why this issue is occurring. Anyone who posts a working solution for this issue can include their Ripple address within their answer to be rewarded XRP for their assistance with this issue.

Which of the two statements fails, or both? either qgk_user is not permitted to create a database, or does not have GRANT privileges on that created database. Run them separately to find out which one fails. Also login as qgk_user@localhost and SHOW GRANTS. Keep in mind that the localhost grants may possibly differ from 127.0.0.1 grants. — Michael Berkowski
– Michael Berkowski, Commented Jun 27, 2015 at 19:55
The script runs this command on one line. Please clarify how to run the SQL separately by splitting this into two lines. — user2984057
– user2984057, Commented Jun 27, 2015 at 19:59
I mean to say, execute just the first statement, then the second to see which one fails mysql -uqgk_user -ppw12345 -e "create database qgk" -- does that succeed alone? — Michael Berkowski
– Michael Berkowski, Commented Jun 27, 2015 at 20:09
Check the output from SHOW GRANTS when logged in as qgk_user with that password. It appears you don't have permission to create your database. Also, I recommend deleting that comment ^^^ because it contains details about the host you're working on. — Michael Berkowski
– Michael Berkowski, Commented Jun 27, 2015 at 20:14
I can't login using the credentials, that's the problem. It won't even tell me if I've actually even CREATED the user either by running mysql create user 'qgk_user'@'localhost'; because it just spits out mysql's help file. — user2984057
– user2984057, Commented Jun 27, 2015 at 20:19

user2984057 · Accepted Answer · 2015-06-27 20:42:01Z

This is how I solved this issue:

http://www.bluepiccadilly.com/2011/12/creating-mysql-database-and-user-command-line-and-bash-script-automate-process

manager@xagate:/var/www$ cat mysql_setup.sh 
#!/bin/bash

EXPECTED_ARGS=3
E_BADARGS=65
MYSQL=`which mysql`

Q1="CREATE DATABASE IF NOT EXISTS $1;"
Q2="GRANT USAGE ON *.* TO $2@localhost IDENTIFIED BY '$3';"
Q3="GRANT ALL PRIVILEGES ON $1.* TO $2@localhost;"
Q4="FLUSH PRIVILEGES;"
SQL="${Q1}${Q2}${Q3}${Q4}"

if [ $# -ne $EXPECTED_ARGS ]
then
 echo "Usage: $0 qgk qgk_user PASSWORD"
exit $E_BADARGS
fi

$MYSQL -uroot -p -e "$SQL"

I then had to login to Webmin an navigate to Servers > MySQL Database Server > User Permissions > root@localhost > reassigned the password.

When prompted for a password at the execution time of the mysql_setup.sh script and inputting the root@localhost user's password, the database was created along with the qgk_user and chosen password.

Thank you for your assistance, sir :)

Collectives™ on Stack Overflow

bash shell script, trouble creating mysql database plus user

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related