ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
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
Comments (10)
-
-
reporter 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… -
reporter 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)
-
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 thedata=
line, add the lineMYSQL_TEST_LOGIN_FILE='/root/.mylogin.cnf'
-
reporter Good morning!
OK I’ll try… But where is the “MYSQL_TEST_LOGIN_FILE” used? Or should I rename it?
-
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) [...]
-
reporter 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…
-
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 aset -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:- 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
- (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
- The root source is the missing
-
repo owner - changed status to closed
environment settings
-
reporter - changed status to resolved
The line "export HOME='/root/'" did it, thank you very much!
- Log in to comment
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 fineAs 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
You could save the file to
/root/.tasker-mysql.cnf
and use it with your command:(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:
The first line creates the user, second one adds rights to select every column from table
tische
in schemabiergarten
. Line 3 allows to access the columnsname
andbirthday
from tablegaeste
, but will not allow to access (the imaginary) fieldsrevenue
noravg_beer_before_vomit
;) . Last line simply tells the mysql server to apply the changes made.