[omd-users] Need help with mysql in OMD

Chris Moody chris at node-nine.com
Tue Apr 10 21:09:10 CEST 2018


Figured out the problem.

It's due to the default behavior of MariaDB on ubuntu 16.x installs.  I
found a couple other threads indicating similar behavior where the
actual 'root' user could connect into mysql/maria but non-root users
could not connect, even with the same credentials.  It all boils down to
how MariaDB is defaulting new installs.

https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/

===[ Brief summary
This plugin allows the user to use operating system credentials when
connecting to MariaDB via Unix socket. It works by retrieving |uid| of
the process that has connected to the socket (using the |getsockopt(...,
SO_PEERCRED, ...)| call) and allowing to connect to the MariaDB account
with the corresponding user name.
===

Logging into the site specific mysql socket as the actual root user, I
checked the following.

==========
MariaDB [mysql]> SELECT user,host,plugin FROM user;
+------+-----------+-------------+
| user | host      | plugin      |
+------+-----------+-------------+
| root | localhost | *unix_socket* |
+------+-----------+-------------+
1 row in set (0.00 sec)
==========

*unix_socket* is the culprit.


Running the following clears this default plugin setting and allows the
site-specific user to connect successfully.

==========
MariaDB [mysql]> UPDATE user SET plugin="";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> SELECT user,host,plugin FROM user;
+------+-----------+--------+
| user | host      | plugin |
+------+-----------+--------+
| root | localhost |        |
+------+-----------+--------+
1 row in set (0.00 sec)

MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
==========

Now all of the init scripts work (and the mysqladmin ping command that
was complaining) and OMD is happy again.  :o)

This might be something that needs factored into the OMD deployment
routines so that this plugin value gets cleared (or explicitly set) upon
creation of a new site instance...otherwise every new site created will
end up with the same problem.  I just created a new site and verified. 

==========
root at omd1:~# omd create testsql
Adding /omd/sites/testsql/tmp to /etc/fstab.
Creating temporary filesystem /omd/sites/testsql/tmp...OK
Apache port 5000 is in use. I've choosen 5003 instead.
Restarting Apache...OK
Created new site testsql with version 2.70-labs-edition.

  The site can be started with omd start testsql.
  The default web UI is available at http://omd1.node-nine.com/testsql/
  The admin user for the web applications is omdadmin with password omd.
  Please do a su - testsql for administration of this site.

root at omd1:~# su - testsql
OMD[testsql]:~$ omd config set MYSQL on
OMD[testsql]:~$ omd start
Starting MySQL... first start of MySQL, creating initial database...
.OK.
Starting rrdcached...OK
Starting npcd...OK
Starting naemon...OK
Starting dedicated Apache for site testsql...OK
Initializing Crontab...OK
OMD[testsql]:~$ omd restart
Removing Crontab...OK
Stopping dedicated Apache for site testsql....OK
Stopping naemon....OK
Stopping npcd...OK
Stopping rrdcached...waiting for termination....OK
Stopping MySQL... mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost''
..........ERROR.
Starting MySQL... already running. ERROR.
Starting rrdcached...OK
Starting npcd...OK
Starting naemon...OK
Starting dedicated Apache for site testsql...OK
Initializing Crontab...OK
OMD[testsql]:~$
==========




Thanks again for the pointers in the right direction to track down what
was even spitting out the errors in the first place.

Cheers,
-Chris



On 4/9/18 4:41 PM, Chris Moody wrote:
> Thank you for the pointer Sven.
>
> I executed that in the /omd/sites/<site> directory and su'd to the
> <site> user in question since I noticed the proceeding '.' before the
> path.  Here's the results.
>
> root at omd1:/omd/sites/nsight# su - nsight
> OMD[nsight]:~$
> OMD[nsight]:~$
> OMD[nsight]:~$
> OMD[nsight]:~$ bash -x ./etc/init.d/mysql start
> + cd /omd/sites/nsight
> + . lib/omd/init_profile
> + . etc/omd/site.conf
> ++ CONFIG_ADMIN_MAIL=hostmaster at node-nine.com
> ++ CONFIG_ALERTMANAGER=off
> ++ CONFIG_ALERTMANAGER_TCP_ADDR=127.0.0.1
> ++ CONFIG_ALERTMANAGER_TCP_PORT=9094
> ++ CONFIG_APACHE_MODE=own
> ++ CONFIG_APACHE_TCP_ADDR=127.0.0.1
> ++ CONFIG_APACHE_TCP_PORT=5001
> ++ CONFIG_AUTOSTART=on
> ++ CONFIG_BLACKBOX_EXPORTER=off
> ++ CONFIG_BLACKBOX_EXPORTER_TCP_ADDR=127.0.0.1
> ++ CONFIG_BLACKBOX_EXPORTER_TCP_PORT=9116
> ++ CONFIG_CORE=nagios
> ++ CONFIG_CRONTAB=on
> ++ CONFIG_DATASCRYER=on
> ++ CONFIG_DEFAULT_GUI=thruk
> ++ CONFIG_DOKUWIKI_AUTH=off
> ++ CONFIG_DOWNTIMEAPI=on
> ++ CONFIG_GEARMAND=on
> ++ CONFIG_GEARMAND_PORT=localhost:4730
> ++ CONFIG_GEARMAN_NEB=on
> ++ CONFIG_GEARMAN_WORKER=on
> ++ CONFIG_GRAFANA=on
> ++ CONFIG_GRAFANA_TCP_PORT=8004
> ++ CONFIG_INFLUXDB=on
> ++ CONFIG_INFLUXDB_HTTP_TCP_PORT=127.0.0.1:8086
> ++ CONFIG_INFLUXDB_META_TCP_PORT=8089
> ++ CONFIG_INFLUXDB_RETENTION=104
> ++ CONFIG_LIVESTATUS_TCP=on
> ++ CONFIG_LIVESTATUS_TCP_PORT=6557
> ++ CONFIG_MKEVENTD=on
> ++ CONFIG_MKEVENTD_SNMPTRAP=on
> ++ CONFIG_MKEVENTD_SYSLOG=on
> ++ CONFIG_MKEVENTD_SYSLOG_TCP=off
> ++ CONFIG_MOD_GEARMAN=on
> ++ CONFIG_MULTISITE_AUTHORISATION=off
> ++ CONFIG_MULTISITE_COOKIE_AUTH=off
> ++ CONFIG_MYSQL=on
> ++ CONFIG_NAGFLUX=on
> ++ CONFIG_NAGIOS_THEME=classicui
> ++ CONFIG_NAGVIS_URLS=thruk
> ++ CONFIG_NSCA=on
> ++ CONFIG_NSCA_TCP_PORT=5667
> ++ CONFIG_PNP4NAGIOS=on
> ++ CONFIG_PROMETHEUS=on
> ++ CONFIG_PROMETHEUS_SNMP_ADDR=127.0.0.1
> ++ CONFIG_PROMETHEUS_SNMP_EXPORTER=off
> ++ CONFIG_PROMETHEUS_SNMP_PORT=9217
> ++ CONFIG_PROMETHEUS_TCP_ADDR=127.0.0.1
> ++ CONFIG_PROMETHEUS_TCP_PORT=9091
> ++ CONFIG_SNMPTRAPD=off
> ++ CONFIG_SNMPTRAPD_UDP_PORT=9163
> ++ CONFIG_THRUK_COOKIE_AUTH=on
> ++ CONFIG_THRUK_LOGCACHE=on
> ++ CONFIG_TMPFS=on
> + '[' on '!=' on ']'
> + MYSQLD_PID=tmp/run/mysqld/mysqld.pid
> + MYSQL_CNF=.my.cnf
> + __init_hook ./etc/init.d/mysql start pre
> + '[' -h ./etc/init.d/mysql ']'
> + file=./etc/init.d/mysql
> ++ printf /omd/sites/nsight/etc/init-hooks.d/%s-%s-%s mysql start pre
> + hook=/omd/sites/nsight/etc/init-hooks.d/mysql-start-pre
> + '[' -x /omd/sites/nsight/etc/init-hooks.d/mysql-start-pre ']'
> + '[' -e /omd/sites/nsight/etc/init-hooks.d/mysql-start-pre ']'
> + case $1 in
> + echo -n 'Starting MySQL... '
> Starting MySQL... + check_install_db
> + '[' '!' -e /omd/sites/nsight/var/mysql/mysql ']'
> + mysql_wait_start
> + mysqld_status check_alive nowarn
> ++ mysqladmin --defaults-file=.my.cnf ping
> *+ ping_output='mysqladmin: connect to server at '\''localhost'\'' failed*
> *error: '\''Access denied for user '\''root'\''@'\''localhost'\'''\'''*
> + ping_alive=1
> + ps_alive=0
> + '[' -f tmp/run/mysqld/mysqld.pid ']'
> ++ cat tmp/run/mysqld/mysqld.pid
> + ps 8643
> + ps_alive=1
> + '[' check_alive = check_alive -a 1 = 1 ']'
> + return 0
> + echo -n 'already running. '
> already running. + return 1
> + echo ERROR.
> ERROR.
> + __init_hook ./etc/init.d/mysql start post 1
> + '[' -h ./etc/init.d/mysql ']'
> + file=./etc/init.d/mysql
> ++ printf /omd/sites/nsight/etc/init-hooks.d/%s-%s-%s mysql start post
> + hook=/omd/sites/nsight/etc/init-hooks.d/mysql-start-post
> + '[' -x /omd/sites/nsight/etc/init-hooks.d/mysql-start-post ']'
> + '[' -e /omd/sites/nsight/etc/init-hooks.d/mysql-start-post ']'
> + exit 1
> OMD[nsight]:~$
>
>
> Reading the init script (which again, thank you for the pointer - I
> didn't even think that there would be localized ./etc/init.d/ scripts
> per site/user.  I need to explore the filesystem layout a bit more)
> it's the mysqladmin 'ping' that's complaining in the init script.
>
> Testing a bit more, when su'd as the site-user, mysqladmin throws the
> error upon trying to login/connect however when just logged in as
> root, a user can get in just fine.
>
> Now I at least know where to keep digging and adjusting to help get
> the permissions functioning.
>
> I'll post my results once I have it functioning.
>
> Cheers,
> -Chris
>
>
> On 4/8/18 11:40 PM, Sven Nierlein wrote:
>> bash -x ./etc/init.d/mysql start
>
>
>
> _______________________________________________
> omd-users mailing list
> omd-users at lists.mathias-kettner.de
> http://lists.mathias-kettner.de/mailman/listinfo/omd-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mathias-kettner.de/pipermail/omd-users/attachments/20180410/3b924110/attachment.html>


More information about the omd-users mailing list