How to monitor multiple MySQL instances on a single host?

General
1

Hello CheckMK community!

We have a server with five instances of mysql running on it, each on their own ip address. I have spent a few days trying to figure this out, but I’ve yet to land on the correct configuration/solution. First off I’ve read through the following post, and subsequent post referenced in it, which are close to what we’re trying to do, but I can’t seem to get the configs quite right:

Here’s my setup of the database server I’m trying to monitor:

One server, with one primary interface, and five sub-interfaces off of that same primary interface. For example (interface - ip - hostname):
eth0 - 10.1.1.2 - server1.example.com <–this is the ip of the primary interface on the server.
eth0:1 - 10.1.1.3 - inst1.server1.example.com <–first mysql instance, first sub-interface.
eth0:2 - 10.1.1.4 - inst2.server1.example.com <–second mysql instance, second sub-interface.
etc…

We created a user on the database and granted permissions. I’ve copied the mk_mysql script from the CheckMK server over to the database server in the proper directory, and then later created the mysql.cfg file in /etc/check_mk/ directory (after following this guide: Monitoring MySQL). Oh, and the Check MK Agent has also been installed on the database server.

/etc/check_mk/mysql.cfg file looks like this:

[client]
user=monitoruser
password=**********
ssl-mode=REQUIRED

I’ve also got the following inside of the /etc/check_mk/mysql.local.cfg file:

# This file is created because some versions of mysqladmin
# issue a warning if there are missing includes.

socket=/tmp/inst1.server1.example.com.mysql.sock
aliases=inst1.server1.example.com

With the above configuration in place, I can create a new host server1.example.com to monitor, and CheckMK will find all of the base services of the server, and then in addition find the inst1.server1.example.com database information. :+1:

The problem arises when I attempt to try and monitor the other database instances (inst2 through inst5). Adding a second socket=/tmp/inst2.server1.example.com.mysql.sock to the mysql.local.cfg file doesn’t work, nor does it work if I add that to the mysql.cfg file.

If I try and add a new host to monitor like inst2.server1.example.com, it doesn’t work because the agent is installed and registered to the hostname server1.example.com; therefore, nothing ever gets returned to CheckMK for those hosts.

Whether that is the correct way to configure those files, I’m not sure. As I understand it, I can run the command sudo /usr/bin/check_mk_agent and it will verify whether or not the database checks will work. When I add the second socket=... line into the mysql.cfg file or the mysql.local.cfg file, the error I get is:

<<<mysql_ping>>>
[[/tmp/inst2.server1.example.com.mysql.sock]]
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'monitoruser'@'localhost' (using password: YES)'

I’m working in a non-production environment; therefore, I have the ability to make any change to try and get these checks working for the other database instances.

I’d be more than willing to provide any further information if needed. Thank you (in advance) for your time/consideration on this topic.

2

One possible solution would be to re-write the existing mysql-plugin.
You need to implement a way to:
→ 1. Find all existing MySQL-Instances
→ 2. Locate all “sock”-Files for the instances
→ 3. Loop throught all instances and change the settings, before they get used

This way you should be able to get all necessary information of all your instances running on that host.
If that’s not possible, i think there is no further solution to that problem, but maybe i’m wrong. Haven’t re-written that MySQL-Plugin since 1.5.

Regards

3

Thank you @Kruzgoth. That is an option I hadn’t seriously considered, but I may have to look into further if all else fails. :slight_smile:

4

I figured out the configuration! And, what’s more, I didn’t have to modify the mk_mysql script after all, which was nice! Here’s how I was able to get the script to work out of the box, while have multiple MySQL instances running on the same host:

/etc/check_mk/mysql.cfg file contents:

[client]
user=monitoruser
password=**********
ssl-mode=REQUIRED

Having ssl-mode=REQUIRED enforces TLS connections to the database for the monitoruser.

/etc/check_mk/mysql.local.cfg file contents:

# This file is created because some versions of mysqladmin
# issue a warning if there are missing includes.
socket=/tmp/inst1.server1.example.com.mysql.sock
socket=/tmp/inst2.server1.example.com.mysql.sock
socket=/tmp/inst3.server1.example.com.mysql.sock
socket=/tmp/inst4.server1.example.com.mysql.sock
socket=/tmp/inst5.server1.example.com.mysql.sock
aliases=inst1.server1.example.com,inst2.server1.example.com,inst3.server1.example.com,inst4.server1.example.com,inst5.server1.example.com

Listing the contents of each file like I listed above will allow the CheckMK server to successfully query all five databases, using their alias names, and discover all the database information corresponding to each database instance.

To be clear, the only host that was created on the CheckMK server was the primary host. Then, all the databases are checked via their socket connections using the configuration I posted above.

Thanks @Kruzgoth for giving me the nudge I needed to dig in further to the script and ultimately find the solution I was looking for. Also, thanks to whomever wrote that mk_mysql script because it does work with multiple instances running on the same server. The config to do that was just difficult to figure out (for me at least… :wink: ). Hopefully this topic benefits someone in the future!

1 Like
5

This was introduced by the following werk mk_mysql: Support for multiple sockets and aliases and should be configurable via Agent Bakery.

1 Like
6

Thank you LaSoe! I just tested our configuration starting from the example mysql.cfg configuration file provided in the Setup > Agents > Other operating systems > Example Configurations section of CheckMK.

I can confirm that the configuration worked for us! I removed the configuration I had been using in mysql.local.cfg and I placed that into the relevant sections of the provided mysql.cfg file. After doing that, I re-ran the /usr/bin/check_mk_agent and verified that all my database connections remained and all the checks are still working to the databases. :+1:

I marked that as the preferred solution. Thank you again!