MySql 1045 When using --defaults-file

Question

I am having a strange issue. I have MySql running on RHEL. I can logon to MySql with

mysql -uroot -pmyPassword

and it works fine. Also, when I try to execute a query from a .sh script as below it works fine

mysql --user=root --password=myPassword --host=localhost --port=3306 -se "SELECT 1 as testConnect" 2>&1>> $OUTPUT

But when I store the userid and password in a msql.conf file as below

[clientroot] 
user=root
password=myPassword

and then change the line in the script as below

mysql --defaults-file=msql.conf --defaults-group-suffix=root -hlocalhost -P3306 -se "SELECT 1 as testConnect" 2>&1>> $OUTPUT

When I run it, I get the error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I am running the script with sudo and the config file is at the same directory as the script

I have permission 0600 on the config file.

How do I make this work?

Answer 1

It worked for me, but it was a bit of a 'tricky' fix that isn't shown in the actual documentation. All you have to do is change

[clientroot] 
user=root
password=myPassword

to

[clientroot] 
user="root"
password="myPassword"

Basically just add the double quotes.

Then running your command:

mysql --defaults-file=msql.conf --defaults-group-suffix=root -hlocalhost -P3306 -se 
"SELECT 1 as testConnect" 2>&1>> $OUTPUT

Should work (it worked for me).

I discovered this by looking through an obscure part of the documentation on something almost unrelated, so I don't blame everyone for missing it.

Answer 2

Options files are not meant for auto-login credentials.

Try this:

export MYSQL_PWD=myPassword

And try to connect using the root user.

---

Note that this approach is "insecure", but so is the basic idea of what you're trying to do.