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

[Robert Sager]

Hi,

I’m trying to check some rows from MySQL-database via “check program”, but I always get the error message from the title.

I’ve found a similar issue here, but that doesn’t solve my case.

The script looks like this:

#!/bin/bash

target='/var/www/bbb/server/images/';

result=''

data=$(mysql --login-path=client -e "USE *db here*; \
                                     SELECT * FROM *table here* \
                                     FROM biergarten b \ );
[...]

*if all good* exit 0, else exit 1;

‌

Monit code:

check program images with path /root/scripts/check_images
  every 144 cycles
  if status != 0 then alert

‌

Needless to say that the script works smooth when called from command line.

Can anybody give me a hint?

‌

Kind regards,

Robert

[Henning Bopp]

Hi Robert,

I think it’s a rights or path problem.

• (using password: NO) tells me that the login was attempted without password
• --login-path=client is the default - it could be deleted if everything else is working fine

As a result it seems that your config file (at ~/.mylogin.cnf) is not available/readable by monit. So it cannot obtain a username and password - therefor the mysql client uses the defaults (root:<empty>).

You might consider using an option file with contents like

[client]
user=root
password=permitA38!

You could save the file to /root/.tasker-mysql.cnf and use it with your command:

data=$(mysql --defaults-extra-file="/root/.tasker-mysql.cnf" *DB_HERE* \
       -e "SELECT * FROM biergarten b")

(I also moved the use <db> part to be a parameter of the command, because it kind of cleans the SQL query.)

Keep in mind that this file then contains a valid login to your mysql server, so secure it (i.e.: sudo chown root:root /root/.tasker-mysql.cnf && sudo chmod 0600 /root/.tasker-mysql.cnf).

One more hint regarding security: Do not use the root user for monitoring! The root user should only be used if a limited user cannot do what you want to do. You might want to create a limited user, that only has readable access to tables and cols needed. It's easier than one might think:

CREATE USER 'monit'@'localhost' IDENTIFIED BY 'permitA38!';

GRANT SELECT                  ON biergarten.tische TO 'monit'@'localhost';

GRANT SELECT (name, birthday) ON biergarten.gaeste TO 'monit'@'localhost';

FLUSH PRIVILEGES;

The first line creates the user, second one adds rights to select every column from table tische in schema biergarten. Line 3 allows to access the columns name and birthday from table gaeste, but will not allow to access (the imaginary) fields revenue nor avg_beer_before_vomit ;) . Last line simply tells the mysql server to apply the changes made.

[Robert Sager]

Hi Henning,

thank you so much for your extensive answer.

I tried your hint with the option file, but got the exact same result, so I “switched back” to the login-path.

I also use a user now that only has access to this table and nothing else.

Now the situation is as following:

A login path is defined

# mysql_config_editor print --all
[biergarten]
user = biergartenbybike
password = *****
host = localhost

Using this in my bash script (with your tip of passing the db name directly):

data=$( mysql --login-path=biergarten biergartenbybike -e "SELECT * FROM ..." );

Works perfect when running from bash (as root), but still fails when running from monit…

‌

Have to sleep now, can’t think anymore
Perhaps I’ll get a “Geistesblitz” tommorrow…

[Robert Sager]

PS: If i run the script with another user instead of root, e.g. “robert”, the error message changes appropriately:

$ ./check_images

ERROR 1045 (28000): Access denied for user 'robert'@'localhost' (using password: NO)

[Henning Bopp]

Good morning! 😉

Hmm, that's weird... I'll set up a test sys this evening... Last idea before setting this up:

Try to set the .mylogin path directly inside your script. Just before the data= line, add the line

MYSQL_TEST_LOGIN_FILE='/root/.mylogin.cnf'

[Robert Sager]

Good morning!

OK I’ll try… But where is the “MYSQL_TEST_LOGIN_FILE” used? Or should I rename it?

[Henning Bopp]

It's used by all the mysql binaries:

MySQL Doc on mysql_config_editor:

To specify an alternate login path file name, set the MYSQL_TEST_LOGIN_FILE environment variable. This variable is recognized by mysql_config_editor, by standard MySQL clients (mysql, mysqladmin, and so forth) [...]

[Robert Sager]

Hmmm, this doesn’t change a thing.

This must be a deep misconfiguring issue on my side, I can’t explain it otherwise…

What I wonder about is the fact that the check script works like a charm when started from cli.

But fails when started via monit…

[Henning Bopp]

Hey Robert!

It is not about misconfiguring. It is more about the (expected) environment and its (reality-based ) internal mechanisms. monit does not load an environment before starting the script (because in 99.9% of all cases that is only creating overhead), so you have to provide your own if needed. I thought that the setting of MYSQL_TEST_LOGIN_FILE was enough, but it wasn't. The solution was to add a set -a to advice bash to pass on the var:

#!/bin/bash
set -a

MYSQL_TEST_LOGIN_FILE='/root/.mylogin.cnf'
data=$(mysql  -e "SELECT CURRENT_USER()");
state=$?

echo "$data"

exit $state

You can test that with providing the environment by starting the command as a bash subcommand:

#!/bin/bash
bash -c 'mysql -e "SELECT CURRENT_USER()"'
exit $?

Both ways totally work like a charm on my lab-debian. I omit the --login-path=client because - as said - it is the default. I also tried creating a new set of data and set that manually. Also totally working.

And to keep up with giving extensive answers (that are far behind answering the question ), here we go : I had some trouble to build an environment to test it (on Debian), because that --login-path option is special to a the original MySQL. It is not shipped with percona (need dev package) and not even supported on MariaDB. So it might not be the most compatible command to use.

---

EDIT (+30m): After rethinking that there must be some other environment-thing that has to cause the initial problem of not reading the right .mylogin.cnf file, I came up with two things:

1. The root source is the missing $HOME env var. So it can also be solved by:

#!/bin/bash

export HOME='/root/'
data=$(mysql  -e "SELECT CURRENT_USER()");
state=$?

echo "$data"

exit $state

2. (nothing to do with the problem, just code style) You do not need to pass all variables set (as with set -a)

#!/bin/bash

export MYSQL_TEST_LOGIN_FILE='/root/.mylogin.cnf'
data=$(mysql  -e "SELECT CURRENT_USER()");
state=$?

echo "$data"

exit $state

[Tildeslash]

environment settings

[Robert Sager]

The line "export HOME='/root/'" did it, thank you very much!