Scrape multiple MySQL servers from a single exporter instance

[zoni]

At Castor, we run a somewhat non-uniform MySQL infrastructure:

• Some MySQL servers we have direct control over where we can run mysqld_exporter on the host itself.
• Some MySQL servers we have no access to, but we have a Prometheus server in the same network capable of reaching it that we do have control over.
• And some of these are really Azure MySQL and/or AWS RDS instances, which we'd still like to monitor with mysqld_exporter instead of relying on the cloud provider's native metrics so that we have a uniform set of metrics for our databases.

The current mysqld_exporter model of "one server per instance" is consistent with the typical Prometheus model, but it does present a usability problem. In our environment, this would require us to run multiple instances of the exporter on the same host on different ports and somehow encode this "MySQL server -> exporter port" mapping into the Prometheus scrape config.

If there was some way for us to tell mysqld_exporter which MySQL server to connect to at scrape time, we could greatly simplify all of this orchestration overhead (and as an added benefit, it would reduce memory usage from running multiple copies).

Proposal

mysqld_exporter already supports reading from a .my.cnf file to get host, username, password and other connection details, but it's currently hard-coded to look for the [client] section.

Looking at the code, it appears to be relatively straightforward and non-invasive to optionally add an HTTP parameter (similar to how collect[] can be used to filter at scrape time already) to tell mysqld_exporter to get connection details under a key other than client.

We could then run a single mysqld_exporter instance on our prometheus servers, configured with a .my.cnf file containing connection details for all the hosts we're interested in under different keys.

Coupled with some relabeling (similar to the strategy used with blackbox_exporter) we could then build a scrape configuration in Prometheus to scrape multiple MySQL servers using just a single mysqld_exporter instance.

I would be willing to make a PR for this, but would like to verify such a change stands a chance at being accepted before I invest time in trying to implement this.

[SuperQ]

Adding a target parameter is something I've considered here, but haven't had time or motivation to implement.

Implementing a series of different hosts seems like it might not mesh well with the INI file that MySQL supports. It might be worth using a yaml file like the blackbox/snmp exporters to store multiple credentials keyed with a module param like those other exporters use.

So the exporter config would be something like:

modules:
  default:
    username: exporter
    password: SomePass
  my_rds_account:
    username: rds_account
    password: SomeRDSPassword

And a scrape config would look like this:

scrape_configs:
  - job_name: 'mysql'
    params:
      module: [my_rds_account]
    static_configs:
      - targets:
        - rds-server.example.com:3306
    relabel_configs:
      - source_labels: [__address__]
        target_label: __param_target
      - source_labels: [__param_target]
        target_label: instance
      - target_label: __address__
        replacement: 127.0.0.1:9104

What do you think?

[SuperQ]

On a side note, I have been thinking about dropping the DATA_SOURCE_NAME env var support from the exporter. It has some issues with some character classes, and complicates the configuration more than I like. If we swapped out the env with this kind of config, it would make me happier.

[zoni]

Thank you for your thoughts and great to hear you're open to exploring this. :smile:

Implementing a series of different hosts seems like it might not mesh well with the INI file that MySQL supports. It might be worth using a yaml file like the blackbox/snmp exporters to store multiple credentials keyed with a module param like those other exporters use.

I recently discovered this is in fact possible, albeit documented a bit subtly. Given a my.cnf file like the following:

[client_foodb]
user=foo
host=foo.example.tld
password=abc

[client_bardb]
user=bar
host=bar.example.tld
password=def
ssl-mode=VERIFY_CA
ssl-ca=/etc/ssl/certs/baz.ca.crt

It's possible to select which client entry to pick using --defaults-group-suffix. For the example above, that would be:

• mysql --defaults-group-suffix=_foodb
• mysql --defaults-group-suffix=_bardb

So regarding YAML over INI in this context I'm kind of ambivalent. Advantages of YAML include:

1. Prometheus and various exporters typically prefer YAML, so it's consistent in that sense.
2. It supports data types, whereas INI is more string limited (though that doesn't really feel like an issue here).
3. If you wanted to introduce other types of configuration in the future, it feels more natural in YAML with it's own schema as opposed to INI where I'd only expect keys which MySQL supports.

Disadvantages I can think of:

1. It potentially requires keeping a second config file around (in those cases where you already have a my.cnf file ready to use).
2. DBAs might be more familiar with the INI format (not really a strong argument though).
3. It's a breaking change with potentially little benefit at this point in time.

Another thing worth thinking about, in your example you used:

# <----snip---->
static_configs:
  - targets:
    - rds-server.example.com:3306`
# <----snip---->

I'm thinking it might be better to let target be some kind of label in this sense, like the "foodb" and "bardb" in my example above. My reasoning for this being you might have different TLS configurations for different databases, so it's potentially going to be a little confusing when host/port shows up in one place and TLS in another.

[SuperQ]

Oh, jeez, thanks MySQL for not even bothering to follow a standard like TOML where you could do [client.name] namespacing.

The good news is, it doesn't seem like . is a forbidden character in there, and the ini file reader we're using already supports [section.sub].

https://ini.unknwon.io/docs/howto/work_with_sections#parent-child-sections

I'm fine with the INI file then if we stick to . as the require delimiter so sub-section loading works easily.

As for the static_configs comment, I am very much against encoding the target host:port in the modules. It's explicitly a Prometheus best practices to keep that level of discovery out of the exporter.

Yes, a lot of people do this kind of thing, no it's not acceptable in an official exporter.

[zoni]

As for the static_configs comment, I am very much against encoding the target host:port in the modules. It's explicitly a Prometheus best practices to move that level of discovery to the exporter.

That's good point, I hadn't considered it from that perspective but yes, agreed.

It does raise an interesting question though. If we pass the host:port through a target param, what should be the desired behavior when the ini section also specifies this information?

(This makes me lean more towards YAML and not accepting host/port specification in there at all)

[SuperQ]

We can require the target param be used in the case that the module param is used. Otherwise we pick up the default [client] config.

[SuperQ]

Not exactly like, but similar to how we do param checking in the snmp_exporter.

https://github.com/prometheus/snmp_exporter/blob/f0ad4551a5c2023e383bc8dde2222f47dc760b83/main.go#L71-L88

[samitpal]

In our organization we went with an implementation using yaml configs. In our implementation the config looks something like the following.

clients:
# default_client should be sufficient if the client info is same across all hosts
  - name: default_client
    user: user1
    password: MyNewPass
    port: 3306
# In case we have hosts with its own mysql client info we can set that here. 
# Note that the 'name' attribute should be the target hostname/IP address
  - name: rds1
    user: rds_user
    password: MyNewPass
    port: 3306
    ssl-ca: ssl-ca
    ssl-cert: ssl-cert
    ssl-key: ssl-key

And on the prometheus side as discussed already in this thread, the config is as follows.

- job_name: mysql # To get metrics about the mysql exporter’s targets
  static_configs:
    - targets:
      # All rds hostnames to monitor. The target(s) here is also used to figure out the client name from the multi host yaml config.
      - rds1.example.com
      - rds2.example.com
  relabel_configs:
    - source_labels: [__address__]
      target_label: __param_target
    - source_labels: [__param_target]
      target_label: instance
    - target_label: __address__
      # The mysqld_exporter host:port
      replacement: localhost:9104

If there is any interest I can link a PR here.

[ning1875]

I have an improved version here, transformed into a probe, which supports http incoming parameters like blackbox_exporter . https://github.com/ning1875/mysqld_exporter/releases/tag/v0.12.1

[SuperQ]

@ning1875 Perhaps you can contribute, rather than spam issues.

[saklasing]

Was any form of this request to handle multiple mysql servers on one instance ever implemented? I have yet to find any examples of solutions that worked. I am also looking for alternative examples of a database prometheus proxy server that would offload mysqld_exporter from the database nodes as an alternative solution.

The net of this request this is no longer a one off request or implementation where it is becoming much more common to implement multiple micro mysql database instances where each represents a data shard. The number of instances is tailored to the size of on-prem database class servers in this case. Vitess/MySQL is proving to scale micro instances horizontally very well, thus also allowing for faster backups, recovery, alters etc when done in parallel.

I see multiple comments indicating port should not be appended to hostnames but I have found no prometheus conversations showing how to achieve the objective; with or without port appended.

Any and all comments are very much appreciated! Even though I am new to prometheus I have spent hours digging for a multi-instance solution.

[saklasing]

I also should have mentioned at the moment I have 5 mysql_exporter.services each listening to a different prometheus port running on my multi-instance nodes. The easiest solution would be as indicated earlier where the mysql_export.cnf has multiple client groups defined. [client.1] user=sameid password=samepass host=127.0.0.1 port=17100

[client.2] user=sameid password=samepass host=127.0.0.1 port=17200 . . client.n

[saklasing]

Did anyone ever implement this for a proxy, found on the prometheus google group?

export DATA_SOURCE_NAME='login:password@(hostname:port)/' ./mysqld_exporter

If so your experience would be appreciated. I realize the DATA_SOURCE_NAME may be deprecated but our shop has zoomed in on prometheus / Grafana for onprem / cloud monitoring and I have multiple production clusters being industrial proofed atm thus requiring monitoring / alerting asap.

[saklasing]

Really could use a response here, if this has been implemented please point it out. It appears the request is still open years after it was submitted, with no response as to resolution, implemented, refused.

If any have developed work arounds similar to a metrics proxy server details would be appreciated.

[SuperQ]

This will be completed in v0.15.0.

https://github.com/prometheus/mysqld_exporter/pull/744

[SuperQ]

@saklasing If you continue to spam issues in our project you will be blocked.

[saklasing]

My apology, please forgive my approach, was unaware that was considered spamming. Looking forward to v15 solution. In the mean time resolved via service reference to environment variable.

Thank you for responding.