Mk_mysql plugin simply ignores my mysql.cfg

[anand]

Hi,

mk_mysql plugin in my ubuntu setup takes only root as user. if i define user name etc in mysql.cfg, i can see it reads the cfg file, but it is ignored. If anyone helps me with the format, it will be extremely helpful.

I use checkmk, p13 version in ubuntu 20.04

[Dirk]

It’s hard to tell what went wrong without seeing your configuration file. But if you access the DB as root without any password, then try

[client]
user=root
password=""
socket=/var/run/mysqld/mysqld.sock
!include /etc/check_mk/mysql.local.cfg

[anand]

Dirk, Thanks for the reply. This mysql.cfg didn’t solve my purpose, as I want to run it as a different user. If I replace the ‘root’ with the user i want (cmkadmin), it stubbornly takes ‘root’ only.
A solution to this will be helpful.
Also the ‘socket’ and ‘!include’ are necessary?

[Dirk]

So your checkmk agent runs as user cmkadmin and your MySQL instance only allows access for user root?

Then I’d suggest you create a user whithin MySQL (can be cmkadmin or some other monitoring user) and configure a password for that user. The manual explains this quite good.

Then configure that very same user and password in mysql.cfg.

The !include is only neccessary if you deploy the file via the Bakery and want to overwrite or add some settings locally on the machine.

About the socket I’m not sure. Maybe the value shown is the default value anyways.

[andreas-doehler (Andreas)]

@anand

If anyone helps me with the format

Then it would be good to see your configuration.

@anand

i can see it reads the cfg file

Where do you see it?

@Dirk

So your checkmk agent runs as user cmkadmin and your MySQL instance only allows access for user root ?

I think it should be vice versa

[anand]

Yes, my checkmk agent runs as user root and your MySQL instance only allows access for user checkmk
My simple cfg file:

[client]
user=checkmk
password=“xxxxxxxxxxxx”

[anand]

Can anyone help?

[Dirk]

@Dirk

Then I’d suggest you create a user whithin MySQL (can be cmkadmin or some other monitoring user) and configure a password for that user. The manual explains this quite good.

Have you tried that?

[anand]

it is done and the user cmkadmin works in mysql.
But mk_mysql always complains “cannot connect as user root to mysql”
it should connect as cmkadmin. That’s my problem.

[andreas-doehler (Andreas)]

Then it don’t loads your config file.
That’s the reason why i asked “Where do you see that it uses your config?”

You can make a small debug output in your “mk_mysql” check that you see that your config file is used.

[Dirk]

@andreas-doehler

You can make a small debug output in your “mk_mysql” check that you see that your config file is used.

Either that or – if the inotify tools are installed – run

inotifywait -m /etc/check_mk/mysql.cfg

and wait a while. Probably andreas’ suggestion is better because you can then also print the values actually read.

[anand]

inotifywait -m /etc/check_mk/mysql.cfg produces the following result:
/etc/check_mk/mysql.cfg OPEN
/etc/check_mk/mysql.cfg ACCESS
/etc/check_mk/mysql.cfg CLOSE_NOWRITE,CLOSE
/etc/check_mk/mysql.cfg OPEN
/etc/check_mk/mysql.cfg ACCESS
/etc/check_mk/mysql.cfg CLOSE_NOWRITE,CLOSE
/etc/check_mk/mysql.cfg OPEN
/etc/check_mk/mysql.cfg ACCESS
/etc/check_mk/mysql.cfg CLOSE_NOWRITE,CLOSE…

[Dirk]

@anand

inotifywait -m /etc/check_mk/mysql.cfg produces the following result:
/etc/check_mk/mysql.cfg OPEN
/etc/check_mk/mysql.cfg ACCESS
/etc/check_mk/mysql.cfg CLOSE_NOWRITE,CLOSE

This means that someone reads the file.

What happens if you run the following code as user root? Does it show some output or does it complain?

export MK_CONFDIR=/etc/check_mk
mysql --defaults-extra-file="$MK_CONFDIR"/mysql.cfg -sN \
     -e "show global status ; show global variables ;"

This is one of the commands the mysql plugin issues.

[anand]

Again this returns:
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@‘localhost’ (using password: YES)’
<<>>
[[]]
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
<<<mysql_capacity>>>
[[]]
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
<<<mysql_slave>>>
…
…
Thing is local root does not have permission, but user checkmk has. but the script takes only root as the user.
Can’t we modify the script user as checkmk anywhere?

[Dirk]

That is not the output of the command I suggested. However. I suspect another config file overwrites the settings given in /etc/check_mk/mysql.cfg.

Run the following commands as root:

export MK_CONFDIR=/etc/check_mk
strace mysqladmin --defaults-extra-file="$MK_CONFDIR"/mysql.cfg ping |& grep "^stat"

On my machine this gives the following output (truncated):

...
stat("/etc/my.cnf", 0x7fffa309ed90)     = -1 ENOENT (No such file or directory)
stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=682, ...}) = 0
stat("/etc/mysql/conf.d/mysql.cnf", {st_mode=S_IFREG|0644, st_size=8, ...}) = 0
stat("/etc/mysql/conf.d/mysqldump.cnf", {st_mode=S_IFREG|0644, st_size=55, ...}) = 0
stat("/etc/mysql/mysql.conf.d/mysqld.cnf", {st_mode=S_IFREG|0644, st_size=3052, ...}) = 0
stat("/etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf", {st_mode=S_IFREG|0644, st_size=21, ...}) = 0
stat("/etc/check_mk/mysql.cfg", {st_mode=S_IFREG|0640, st_size=261, ...}) = 0
stat("/etc/check_mk/mysql.local.cfg", 0x7fffa309a810) = -1 ENOENT (No such file or directory)
stat("/root/.my.cnf", 0x7fffa309ed90)   = -1 ENOENT (No such file or directory)
stat("/root/.mylogin.cnf", 0x7fffa309ed90) = -1 ENOENT (No such file or directory)
...

That is: various config files are read (or tried to be read) by mysqladmin (and mysql as well), some of which don’t exist.

But if you have a file /root/.my.cnf or /root/.mylogin.cnf then they will overwrite the settings made in “previous” files. Check these two files. I suspect they exist and contain a section [client] with username=root.