Need help with mysql exporter

[Y.G Kumar]

Hi All,

We have a galera mariadb 10.6 version three node cluster. When I tried installing mysql exporter  by following  the link https://computingforgeeks.com/monitoring-mysql-mariadb-with-prometheus-in-five-minutes/?expand_article=1 ,  I could not get the exporter to detect the mysql service  in which it is running .. These are the logs:

--

/tmp# curl -s <IP>:9104/metrics | grep mysql_up
ts=2023-08-18T11:22:21.544Z caller=exporter.go:152 level=error msg="Error pinging mysqld" err="dial tcp 127.0.0.1:3306: connect: connection refused"
# HELP mysql_up Whether the MySQL server is up.
# TYPE mysql_up gauge
mysql_up 0

---

But the mariadb service is running on this node:

---

● mariadb.service - MariaDB 10.6.10 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─migrated-from-my.cnf-settings.conf, override.conf
     Active: active (running) since Wed 2023-05-10 23:10:13 UTC; 3 months 8 days ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
   Main PID: 2540463 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 1587 (limit: 618903)
        CPU: 2w 5d 9h 34min 28.076s
     CGroup: /galera.slice/mariadb.service
             └─2540463 /usr/sbin/mariadbd --wsrep_start_position=d635b506-3dd8-11e8-989e-7aec6efd1673:2862939478

---

Can anyone help me where the fault is ?  I have given enough privileges for the 'mysqld_exporter' user as well.

Thanks

Kumar

[Brian Candler]

> dial tcp 127.0.0.1:3306: connect: connection refused

Your mariadb is not listening on TCP port 3306 on the loopback address (127.0.0.1).

If you show what command you use to connect to mariadb normally (e.g. using the "mysql" command line client) it should be possible to configure the mysql export to talk the same way.  You would set this in whatever file you give to --config.my-cnf (defaults to ~/.my.cnf)

See https://github.com/prometheus/mysqld_exporter/

[Y.G Kumar]

What is the exact configuration option to mention the port number of the service ?

[Y.G Kumar]

The mariadb service is infact running on the node on 3306:

--

# telnet 10.0.0.4 3306
Trying 10.0.0.4 ...
Connected to 10.0.0.4 .
Escape character is '^]'.
u
5.5.5-10.6.10-MariaDB-1:10.6.10+maria~ubu2004-logVcYvf{X'V�_X}.iHlf<69\mysql_native_password^CConnection closed by foreign host.

---

tcp        0      0 10.0.0.4:3306       10.0.0.4:55890      ESTABLISHED 2863920/mariadbd

But still the exporter is not able to detect the service. Not sure where it is going wrong ?

[Brian Candler]

What does "telnet 127.0.0.1 3306" show?

You haven't shown the mariadb "LISTEN" socket.  If it's listening on 10.0.0.4:3306 rather than 0.0.0.0:3306 or *:3306, then it will only accept connections on 10.0.0.4:3306, and not 127.0.0.1:3306.

You can see from your original error message that the exporter connects to localhost:3306 by default, as documented under the general flags:

mysqld.address
Hostname and port used for connecting to MySQL server, format: host:port. (default: locahost:3306)

Therefore, if this is the only target you want to scrape, you could pass --mysqld.address=10.0.0.4:3306 to override this.

You could also do this through the scraping URL /probe with a "target" parameter:

curl -s <IP>:9104/probe?target=10.0.0.4:3306

This is documented here: https://github.com/prometheus/mysqld_exporter/#multi-target-support

However, that feature was only introduced in version v0.15.0.

[Y.G Kumar]

Yes you are right. Able to make it work after the mysqld option. Thanks Brian for your help. Appreciate it....

[Y.G Kumar]

How to monitor multiple databases from a single instance of the exporter as in a cluster ? We have a galera mariadb cluster.  Should I install the exporter on all the nodes of the cluster ?

Please help ...

[Brian Candler]

On Tuesday, 29 August 2023 at 07:34:58 UTC+1 Y.G Kumar wrote:

Please help ...

Please start by reading the documentation at the link I posted earlier in this thread:

https://github.com/prometheus/mysqld_exporter/#multi-target-support

Then if you are unable to make this work, you can post a specific question: show exactly what you configured, what you expected to happen, and what actually happened (with any logs or error messages).

[Y.G Kumar]

Brian,

This is what I am trying to achieve. I have a three node galera mysql cluster on three nodes A, B and C with respective IPs.

As of now, I have installed mysql exporter in all the above three nodes and trying to access it using haproxy external IP using the above three nodes as backends.  It is working fine. 

Now , I  want to keep the mysql exporter only on node A and remove it on nodes B and C and start the  multi target approach from node A only. So in the /etc/.mysqld_exporter.cnf   file , how do I mention each section of the other two nodes ?  This is my present file content in each of the nodes above:

----

[client]
user=mysqld_exporter
password=StrongPassword

---

Please suggest..

Appreciate your time.

Good day

[Brian Candler]

Have you tried it? With curl?

If all servers have the same user/password, then I think just

curl nodeA:9104/probe?target=nodeB:3306

should work, using the credentials from the [client] section of my.cnf.

If they have different usernames/passwords, then as the documentation says, you can add additional sections to my.cnf:

 [client]
user=mysqld_exporter
password=StrongPassword

 [client.secret]
user=mysqld_exporter
password=VeryStrongPassword

and call /probe?target=nodeB:3306&auth_module=client.secret

If you don't provide auth_module then the "[client]" section will be used, as the documentation I linked to before says: Will match value to child in config file. Default value is `client`

Of course, you will have to arrange that nodes B and C accept mysql connections from node A. You can test this using the 'mysql' command line tool on node A, to see if you can establish connections to B/C.

[Ben Kochie]

The exporter is meant to run as a "side car", installed on every node of your cluster.

What you are doing is not following best practices.

--
You received this message because you are subscribed to the Google Groups "Prometheus Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-users/97eb8eb4-1ac6-4d47-9f7f-a07978cd4e6an%40googlegroups.com.

[Y.G Kumar]

Ok, to clear the confusion at this point for me, is it good to have multi target approach in general ?  

Also the next question is, in the case of galera cluster, are the metrics same from each of the exporters ? since galera syncs the data among the cluster members ?  Please explain....

Appreciate your time Brian and Ben.

[Y.G Kumar]

Any update ?

[Brian Candler]

Ben's answer was clear and unambiguous, IMO.

In the case of specific questions about the galera cluster, you'd be best to ask your vendor (or test the exporter's behaviour yourself).

[Y.G Kumar]

Hi,

I am trying to find out why the multi target approach is not advisable in the case of mysql cluster ?

[Brian Candler]

You said "I  want to keep the mysql exporter only on node A and remove it on nodes B and C"

Then what happens if node A goes down? You'll lose all monitoring of B and C!

If instead you put the exporter on your prometheus server, so that it remotely contacts A, B and C, that might be better. But unless your connections across the network are properly secured (e.g. with TLS and server certificate validation) you may be opening up your mysql server to attack.

Putting the exporters directly on each node eliminates that problem. At worst, an attacker can get access to your metrics - but at least the database access credentials are not being sent across the wire.

[Y.G Kumar]

Thanks Brian for your response. I am good to go now. One last query before I go ahead and implement the exporter. Since this is a MySQL cluster, do all the three nodes report the same metrics ? Or do the metrics differ for each node ? 

Thanks

Y.G

[Brian Candler]

mysql exporter reports whatever the server it connects to reports.

As I said before: if you have specific questions about the behaviour of a galera cluster, you'd be best to ask your vendor (perhaps there's a mailing list for galera?), or test the behaviour yourself.

[Y.G Kumar]

Thanks Brian. Appreciate it...