Migrating MySQL Monitoring to Prometheus with mysqld_exporter: Multi‑Instance Support and Troubleshooting

The author, a senior DBA, explains the motivation for moving MySQL monitoring from Zabbix to Prometheus because the rest of the infrastructure already uses Prometheus. The goal is to use mysqld_exporter, which originally required a 1:1 exporter‑instance relationship, but newer versions support a 1:n pattern.

Environment preparation includes two MySQL clusters (ports 6666 and 23750) with master/slave roles, a mysqld_exporter instance (v0.14.0) listening on port 42001, and a Prometheus server (v2.40.2) on port 9999. User accounts named exporter, exporter1, and exporter2 are created on the appropriate clusters, and max_connections is set to distinguish the instances.

Testing the exporter shows that the default /probe endpoint returns an HTML page because the target parameter is ignored; the exporter tries to connect to 127.0.0.1:3306. The correct usage is to query /metrics?target=host:port, but the shipped release (Jan 2022) does not yet contain the multi‑target code.

# curl 'http://localhost:42001/metrics?target=192.168.168.11:6666'
ts=2022-11-18T09:35:47.063Z caller=exporter.go:149 level=error msg="Error pinging mysqld" err="dial tcp 127.0.0.1:3306: connect: connection refused"

Solution 1 – compile a newer exporter – By checking the commit history, the multi‑instance support was added after the latest release. The author downloads the main branch, sets GOPROXY, ensures Go 1.17+ (using Go 1.19), and builds the exporter with go build. After starting the newly built exporter on a different port (42002), the target parameter works and the expected metrics (e.g., mysql_global_variables_max_connections) are returned for both clusters.

# ./mysqld_exporter --config.my-cnf=my.cnf --web.listen-address=:42002 >> exporter.log 2>&1 &
# curl 'http://localhost:42002/probe?target=192.168.168.11:6666' | grep mysql_global_variables_max_connections
mysql_global_variables_max_connections 3000

Solution 2 – using auth_module for per‑cluster credentials – Because each cluster uses a different exporter user, the author adds separate sections in my.cnf ( [client-exporter1], [client-exporter2]) and passes the desired section via the auth_module query parameter. Tests confirm that the correct credentials are applied and the mysql_up metric reflects the authentication outcome.

# cat my.cnf
[client]
user = exporter
password = exporter

[client-exporter1]
user = exporter1
password = exporter1

[client-exporter2]
user = exporter2
password = exporter2

Prometheus configuration initially fails when the auth_module line is written as a plain string. The correct syntax is a list, e.g., auth_module: [client]. The author also demonstrates a more compact configuration that attaches auth_module as a label to each target, allowing a single job to monitor both clusters without redundant job blocks.

- job_name: 'mysql'
  static_configs:
    - targets:
        - 192.168.168.11:6666
        - 192.168.168.12:6666
      labels:
        auth_module: client
    - targets:
        - 192.168.168.11:23750
        - 192.168.168.14:23750
      labels:
        auth_module: client-exporter2
  metrics_path: /probe
  relabel_configs:
    - source_labels: [__address__]
      target_label: __param_target
    - source_labels: [__param_target]
      target_label: instance
    - target_label: __address__
      replacement: 192.168.168.11:42002
    - source_labels: [auth_module]
      target_label: __param_auth_module
    - action: labeldrop
      regex: auth_module

The final Prometheus UI screenshot confirms that metrics from both MySQL clusters are successfully scraped.

Summary

mysqld_exporter requires a [client] section in its configuration file.

Multi‑instance collection is only available in newer code; the released binary may lack this feature.

When passing credentials via auth_module, follow the official Prometheus syntax and ensure the correct config sections exist.

For large‑scale monitoring, deploy multiple exporter instances behind a VIP or DNS name and enable exporter‑level monitoring.